MySQL 笔记

-- 整理自：IT楠老师

索引

一、数据结构

B+ 树

【B+树】是【B 树】的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

1. 所有关键字存储在叶子节点
2. 为所有叶子结点增加了一个双向指针

简化 B+树 如下图：

选型缘由

问题一：为什么在b 树或b+树中选择？

• mysql数据模型更适合用这类数据结构，一条数据中通常包含【id】+【其他列数据】，我们可以很轻松的根据id组织一颗B+树。
• 我们知道innodb使用【页】（这是inndb管理数据的最小单位）保存数据，一页（16k），b+树中的每个节点都是一页数据。

问题二：为什么选择B+树？

• 相同的空间，不存放【整行数据】就能存【更多的id】，b+树能使每个节点能检索的【范围更大、更精确，极大的减少了I/O操作，保证b+树的层高较低，通常3到4层的层高就能支持百万级别的访问】。
• Mysql是一种关系型数据库，【区间访问】是很常见的一种情况，B+树叶节点增加的双向指针，加强了区间访问性，可使用在范围区间查询的情况

二、索引的分类和创建

1、聚簇索引和非聚簇索引

【主键和数据】共存的索引被称之为【聚簇索引】，其他的，比如我们使用【姓名列+主键】建立的索引，可以称为【非聚簇索引】，或者【辅助索引】，或者【二级索引】，同时聚簇索引只有在innodb引擎中才存在，而在myIsam中是不存在的，如下图：

MyISM使用的是【非聚簇索引】，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的[叶子节点]都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

tips：

• 聚簇索引【默认使用主键】，如果表中没有定义主键，InnoDB 会选择一个【唯一且非空】的列代替。如果没有这样的列，InnoDB 会隐式定义一个主键【类似oracle中的RowId】rowid来作为聚簇索引的列。
• 如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyIsam占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

小问题：主键为什么建议使用自增id?

• 主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源。
• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

2、普通索引 (常规 normal)

理论上任何列都可以当做普通索引，创建方式如下：

create index idx_user_name on user(user_name);   -- 整个索引创建时间为24.502s

drop index idx_user_name on user; 

-- （1）创建email列的索引，索引可以截取length长度，只使用这一列的前几个字符
create index idx_email on user(email(5));     

-- （2）使用修改表的方式添加索引
alter table user add index idx_email (email);

-- （3）建表时时，同时创建索引
create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    index [indexName] (fieldName(length))
)

3、唯一索引(UNIQUE)

对列的要求：索引列的值不能重复

-- 邮箱，用户名就应该创建唯一索引，姓名就应该是普通索引
create unique index idx_email on user(email);

-- 通过alter语句添加索引
ALTER table mytable ADD UNIQUE [ux_indexName] (username(length))

唯一索引和主键的区别：

• 唯一索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为非空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

唯一约束 和 唯一索引的区别：

• 唯一约束和唯一索引，都可以实现列数据的唯一，列值可以为null
• 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引,唯一约束是通过唯一索引来实现数据唯一。
• 创建一个唯一索引，这个索引就是独立的索引，可以单独删除。
• 如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。

4、多个二级索引的组合使用

记住一点：mysql在执行查询语句的时候一般只会使用【一个索引】，除非是使【用or连接的两个索引列】会产生索引合并。

我们针对某电商平台的检索功能做了优化，添加了三个索引，三个字段分别为【品牌】、【价格】、【销量】这三个的索引结构如下:

（1）品牌的索引结构：

（2）价格的索引结构：

（3）销量的索引结构：

针对以上的索引我们进行如下的查询，分析检索过程：

1. 我们要检索品牌为阿玛尼（Armani）的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的商品id，回表查询，得到结果。

   结论： 会使用一个索引。

2. 我们要检索名称为阿玛尼（Armani），价格在1万到3万之间的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的商品id。

   第二步： 直接回表扫描，根据剩余条件检索结果。

   结论： 只会使用第一个索引。

3. 我们要检索名称为阿玛尼（Armani），价格为26800 且 销量在50以上的包包

   查询的步骤如下：

   第一步： 通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

   第二步： 直接回表扫描，根据剩余条件检索结果。

   结论： 只会使用第一个索引。

4. 我们要检索名称为阿玛尼（Armani）或 名称为LV的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的商品id，得到结果。

   结论： 我们经常听说，有or索引会失效，但是像这样的type ='Armani' or type = 'LV'并不会，他相当于一个in关键字，会使用一个索引。

5. 我们要检索名称为阿玛尼（Armani）或 价格大于8000的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

   第二步： 通过【价格索引】检索出价格在5万到7万之间的商品id，这是一个连接条件带有【or的查询】，所以需要和上一步的结果进行【并集】，得到结果。

   结论： 这个过程叫【索引合并】当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】。

6. 我们要检索名称为阿玛尼（Armani），且 价格大于8000，且 【产地（该列无索引）】在北京的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的商品id。

   第二步： 直接回表扫描，根据剩余条件检索结果。

   结论： 只会使用第一个索引。

7. 我们要检索名称为阿玛尼（Armani）或价格大于8000，或【产地（该列无索引）】在北京的包包

   第一步： 优化器发现【产地列】无索引，同时连接的逻辑是【or】没有办法利用【索引】优化，只能全表扫描，索引失效。

   结论： 发生全表扫描，索引失效，条件中有没建立索引的列，同时关联条件是or。

5、复合索引（联合索引）重要

当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。

alert table test add idx_a1_a2_a3 table (a1,a2,a3) 

复合索引的结构如下，复合索引会优先按照第一列排序，第一列相同的情况下会按照第二列排序，以此类推，如下图：

品牌	价格	销量	id
Armani	16800	35	13,24,76
Armani	26800	35	12,14,16
Armani	26800	100	34,56,17
Armani	68888	15	1,4,5,6,7
GUCCI	8999	135	78,92
LV	9999	326	55,63
LV	12888	99	57,99
LV	42888	69	11,22
PRADA	9588	125	111,202

认真阅读了上边的介绍和图形，我们再次思考以下几个问题：

1. 我们要检索名称为阿玛尼（Armani）的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的商品id，回表查询，得到结果。

   结论： 会使用第一部分索引。

2. 我们要检索名称为阿玛尼（Armani），价格在1万到3万之间的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的叶子节点。

   第二步： 在【满足上一步条件的叶子节点中】查询价格在1万到3万之间的包包的列，查询出对应的id，回表查询列数据。

   结论： 会使用复合索引的两个部分。

3. @我们要检索名称为阿玛尼（Armani）或 价格大于8000的包包

   第一步： 优化器发现我们并没有一个【价格列】的单独的二级索引，此时要查询价格大于8000的包，必须进行全表扫描。

   结论： 但凡查询的条件中没有【复合索引的第一部分】，索引直接【失效】，全表扫描。

4. 我们要检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼的叶子节点。

   第二步： 在【满足上一步条件的叶子节点中】查询价格大于8000元的包包的叶子节点。

   第三步： 因为【产地列】无索引，但是是【and】的关系，我们只需要将上一步得到的结果回表查询，在这个很小的范围内，检索产地是不是北京即可。

   结论： 可以使用复合索引的两个部分。

5. 我们要检索名称为阿玛尼（Armani）和LV之间，价格为在1万到3万的包包

   第一步： 通过【品牌索引】检索出所有阿玛尼和LV的所有叶子节点。

   第二步： 我们本想在第一步的结果中，快速定位价格的范围，但是发现一个问题，由于第一步不是等值查询，会导致后边的结果不连续，必须对【上一步的结果】全部遍历，才能拿到对应的结果。

   结论： 只会使用复合索引的第一个部分，这个就引出了【复合索引中特别重要的一个概念】-【最左前缀原则】。

重点： 最左前缀原则：

（1）最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（2）=和in可以乱序，比如a = 1 and b < 2 and c = 3 ，咱们建立的索引就可以是（a,c,b）或者（c,a,b）。

思考： 为什么联合索引的性能会比索引的组合使用效率高。

通俗理解： 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

重点：

多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！  但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

6、全文索引（FULLTEXT）

做全文检索（不如百度的搜索功能）使用的索引，但是这种场景，我们有更好的替代品，如：ElacticSearch，所以实际使用不多，只当了解。

使用全文索引的注意

1. 使用全文索引前，搞清楚版本支持情况。
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题。
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
4. 对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

全文索引的版本支持

1. MySQL 5.6 以前的版本，只有 MyIsam 存储引擎支持全文索引。
2. MySQL 5.6 及以后的版本，MyIsam 和 InnoDB 存储引擎均支持全文索引。
3. 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

-- 在已存在的表上创建全文索引
create fulltext index fulltext_text  on ydlclass_user(text);

-- 通过 SQL 语句 ALTER TABLE 创建全文索引
alter table ydlclass_user add fulltext index fulltext_text(text);

-- 全文检索的语法
select * from ydlclass_user where match(text) against('高号便法还历只办二主厂向际');

8、hash索引

hash索引是Memory存储引擎的默认方式，而且只有memory引擎支持hash索引，memory的数据是放在内存中的，一旦服务关闭，表中的数据就会丢失 我们最好在其中存储一些公共的、常用的、不经常发生改变的数据，比如一些字典数据、配置数据等。同时，这些数据最好持久化在一些其他的地方，比如配置文件、其他的表，在程序启动的时候，主动的进行加载，我们可以使用如下sql，将一张表的数据加载到内存中：

insert into hash_user select * from ydl_user where user_id < 2000000;

-- 这个表使用的内存满了，放不下了，我们只需要调节下边两个参数，修改配置文件重启即可：
tmp_table_size = 4096M
max_heap_table_size = 4096M

-- 创建一个hash索引
create index hash_idx_user_name using hash on hash_user(email);

关于hash索引需要了解的几点：

• hash是一种key-value形式的数据结构。实现一般是数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。当然还有其他的解决hash冲突的方法。hash这种数据结构是很常用的，比如我们系统使用HashMap来构建热点数据缓存，存取效率很好。
• 即使是相近的key，hash的取值也完全没有规律，索引hash索引不支持范围查询。
• hash索引存储的是hash值和行指针，所以通过hash索引查询数据需要进行两次查询（首先查询行的位置，然后找到具体的数据）。
• hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的。
• 只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

三、explain的用法

执行explain会产生以下11列内容，如下：

列号	列	说明
1	id	select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2	select_type	查询类型
3	table	正在访问哪个表
4	partitions	匹配的分区
5	type	访问的类型
6	possible_keys	显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
7	key	实际使用到的索引，如果为NULL，则没有使用索引
8	key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
9	ref	显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
10	rows	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
11	filtered	查询的表行占表的百分比
12	Extra	包含不适合在其它列中显示但十分重要的额外信息

id 字段

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

（1） id相同

id如果相同，可以认为是一组，执行顺序从上至下

explain select * from student s, scores sc where s.id = sc.s_id

（2） id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行例子

explain select * from student where age > (
	select age from student where name = '连宇栋'
);

（3）id部分相同部分不同

id如果相同，可以认为是一组，从上往下顺序执行在所有组中，id值越大，优先级越高，越先执行例子：

explain 
select * from student s, scores sc where s.id = sc.s_id
union
select * from student s, scores sc where s.id = sc.s_id;

type 字段

最好到最差备注：掌握以下10种常见的即可

NULL > system  > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配， 常见于主键或唯一索引扫描例子： 被驱动表使用主键索面，结果唯一

explain select * from scores sc left join student s on s.id = sc.s_id

ref

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体例子：

explain select * from student s left join scores sc on s.id = sc.s_id

index（全索引扫描）

Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。例子：

explain select name from student; -- name创建了索引，select的为覆盖索引

ALL（全表扫）

Full Table Scan，将遍历全表以找到匹配行例子：

explain select * from student; 

三、使用索引的问题

1、哪些情况下适合建索引

• 频繁作为where条件语句查询的字段
• 关联字段需要建立索引
• 分组，排序字段可以建立索引
• 统计字段可以建立索引，例如count()，max()等

小案例：还记得在学习临时表时，分析过group by的执行流程吗（分组字段没有索引）？有了 age索引之后的分组执行流程如下：直接使用索引信息，统计每个组的人数，直接返回

select age,count(*) from student group by age order by age

2、哪些情况下不适合建索引

• 频繁更新的字段不适合建立索引
• where条件中用不到的字段不适合建立索引
• 表数据可以确定比较少的不需要建索引
• 数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值
• 参与列计算的列不适合建索引，索引会失效

3、能用复合索引的要使用复合索引

4、null值也是可以走索引的，他被处理成最小值放在b+树的最左侧

5、使用短索引

对字符串的列创建索引，如果可能，应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

6，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列创建复合索引。

7、MySQL索引失效的几种情况

• 如果条件中有or，即使其中有条件带索引也不会使用走索引，除非全部条件都有索引
• 复合索引不满足最左原则就不能使用全部索引
• like查询以%开头
• 存在列计算 explain select * from student where age = (18 - 1)
• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引，比如结果的量很大
• 存在类型转化

-- 索引不失效
explain select * from student where age = '18'  -- age(int)
explain select * from ydl_user where login_date = '2008-05-31 17:20:54' -- login_date(datetime)

-- 索引失效 本来是字符串，你使用数字和他比较
explain select * from student where gander = 1 -- gander(varchar)