MySQL系列之索引（一）

索引

索引存储在存储引擎，而不是服务器层，不同的存储引擎的索引的工作方式不一样，并不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

索引就是把无序的数据变成有序的查询，把创建了索引的列的内容进行排序，可以用来快速寻找那些特定值的记录，如果没有索引，一般要执行查询时遍历整表

创建索引：create index npe on sys_user(name,phone,email);

索引基本原理

索引就是对排序结果生成倒排表，在倒排表内容上拼上数据地址链，在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

索引影响

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高查询性能但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件

除了数据表占数据空间之外，每一个索引还要占一定的物理空间；如果要建立聚簇索引，那么需要的空间就会更大，如果非聚簇索引很多，一旦聚簇索引改变，那么所有非聚簇索引都会跟着变。

如何建立索引

索引的选择性（Selectivity）：是指不重复的索引值（也叫基数，Cardinality）与表记录数的比值：

索引的选择性=基数/记录数

选择性的取值范围为(0,1)，选择性越高的索引价值越大，如果选择性等于1，就代表这个列的不重复值和表记录数是一样的，那么建立索引是非常合适的，如果选择性非常小，那么就代表这个列的重复值是很多的，不适合建立索引。

创建索引原则

选择唯一性索引

为经常需要排序、分组和联合操作的字段建立索引

为常作为查询条件的字段建立索引

限制索引的数目，删除不再使用或者很少使用的索引

使用短索引对长字符串进行索引，尽量使用前缀来索引，节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。

出现在where子句中的列，连接子句中指定的列是适合索引的列

不要过度索引，索引需要额外的磁盘空间，并降低写操作的性能，在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长，所以只保持需要的索引有利于查询即可。

定义有外键的数据列一定要建立索引

更新频繁字段不适合创建索引

尽量的扩展索引而不是新建索引，若表中已有a的索引，现在加（a,b）索引，修改原来的索引即可。

对于查找中很少涉及到的列，重复值比较多不能有效区分数据的列（如性别）不建立索引

索引阿里规约

单表索引数量控制在5个以内

不允许存在重复索引和冗余索引

防止字段隐式转换导致索引失效

sql优化目标，要达到range级别

利用覆盖索引避免回表操作

禁止超过三个表的join

在varchar上建立索引，指定索引长度

索引字段值不允许设置为null，必须设置默认值

单表数量控制在1000万以内

字段列数量建议在30以内

不建议使用mysql分区表

单表行数超过500万或单表容量达到2G建议分库分表

覆盖索引

b+树中的叶子节点存储的就是要查询的数据，不需要进行回表操作

例如：主键索引叶子节点存的是ID，若只查ID就会是覆盖索引，这样就避免了回表操作

前缀索引

用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。是能使索引更小，更快的有效方法。

对于BLOB,TEXT或者很长的varchar如果要用索引的话，必须使用前缀索引，因为mysql不允许索引这些列的完整长度

如何选择合适长度的前缀：

1.分别查看left（user_name，3）left（user_name，4）left（user_name，5）left（user_name，6）等的数量，查看哪个与完整user_name的数量相近，最相近的就可以作为前缀索引的长度

2.计算完整列的选择性，并使前缀的选择性接近于完整列的选择性

count(distinct user_name)，count(*)会得到一个值，这个就是完整列的选择性

再分别计算count(distinct left（user_name，3）)、count(distinct left（user_name，4）)、count(distinct left（user_name，5）)、count(distinct left（user_name，6）)的值，这些值称为前缀的选择性；

最终前缀选择性和完整列的选择性最相近的就为前缀的长度

注意：如果有多个前缀的选择性与之相近，则要考虑数据的分布，要使数据尽量的分布均匀

创建前缀索引：alter table user add key(user_name(7));

缺点：mysql无法使用前缀索引做order by和group by，也无法做覆盖扫描

后缀索引

如果找某个域名的所有电子邮件地址，会用到后缀索引，但是原生mysql并不支持后缀索引，所以需要先将字符串反转后存储，并基于此建立前缀索引，这样就可以维护索引了

主键索引

是一种特殊的唯一索引，同时也是聚簇索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。叶子节点存储的是整行的数据。

聚簇索引

将数据存储与索引放到了一块，并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的

InnoDB对数据的存储必须依赖于主键，主键对应的索引也叫聚簇索引。假设你没有建主键，InnoDB会从表中寻找第一个非空的唯一索引作为聚簇索引，但如果这也找不到，会生成一个不可见的ROW_ID的列，该列是一个6字节的自增数字，用来生成聚簇索引。

ROW_ID列的自增实现是来自一个全局自增序列，意味着所有使用到ROW_ID作为聚簇索引的表都共享该序列，在高并发的情况下，就有可能保证不了唯一性。

非聚簇索引

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这点类似书的目录，比如要找第5章第1节，先在这个目录里面找，找到对应的页码后再去对应的页码看文章。

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

MyISM使用的是非聚簇索引，没有聚簇索引。如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MySAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成。

聚簇、非聚簇索引比较

优势：

1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高。

2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的。

3、聚簇索引适合用在排序的场合，非聚簇索引不适合。

劣势：

1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过optimize table优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片。

2、若使用UUID(随机ID)作为主键，使数据存储稀疏，就会出现聚簇索引有可能有比全表扫面更慢，所以建议不使用UUID作为主键。

3、若主键比较大，那辅助索引将会更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间。

联合索引

可以覆盖多个数据列，如像index(columnA, columnB)索引。

联合索引先比较第一个字段，若不相等就可以比较出来，若相同，则比较下一个字段，以此类推，如果三个字段都相同，那就是非主键索引，叶子节点的数据存储的就是主键，若查询的过程中需要查询整行信息或者不属于主键和联合索引中字段的信息，则需要回表。

最左前缀法则

MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

联合索引中，如果设置的索引为三个字段a,b,c，如果执行a=XXX，a=XXX and b=XXX，a=XXX and b=XXX and c=XXX都可以走索引；

注意的是，如果选择了a>XXX，且要查找的字段中有除了索引字段和主键字段的其他字段，则不能走索引；因为在联合索引的叶子节点，存放的数据不是一整条，而是这条数据的主键，需要根据主键去查找到整条数据，如果要查找a>XXX的所有数据的话，则需要去遍历每条数据的主键，还不如走全表扫描，因此不适合，不能走索引

索引类型

normal

普通索引，允许被索引的数据列包含重复的值。

unique

唯一索引，表示唯一的，不允许重复的索引，如果该字段信息保证不会重复就可设置为该属性

primarykey=unique+notnull

fulltext

全文索引，表示全文搜索的索引，用户搜索很长一篇文章的时候效果较好，如果普通一两行使用index也可以

通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。

创建全文索引：ALTER TABLE tablename ADD FULLTEXT (column)

spatial

空间索引，是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOTNULL，空间索引只能在存储引擎为MYISAM的表中创建

索引方法(数据结构)

索引的数据结构和具体存储引擎的实现有关，对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快，其余大部分场景，建议选择BTree索引。

B+树

b+树的每一个节点都是一个page，大小为16KB，是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。

mysql建表的时候默认索引方法是BTREE

1.非叶子节点不存储data，只存储索引（冗余），可以放更多的索引

2.叶子节点包含所有索引字段

3.叶子节点用双向指针连接，提高区间访问的性能

4.叶子节点的每个元素都是从左到右依次递增的，节点与节点之间也是从左到右依次递增的

B树

叶节点具有相同的深度，叶节点的指针为空；所有索引元素不重复；节点中的数据索引从左到右依次递增，叶子节点之间没有双向指针，没办法很好的支撑范围查找，效率低

b+tree和btree的区别

b+tree叶子节点之间是双向指针，btree叶子节点之间没有双向指针。

b+tree的索引是没有数据的，只有主键或者可以作为主键的值，btree的索引中有数据。

b+tree索引的关键字检索效率比较平均，不像btree那样波动幅度大。

b+tree高度为3的话，可以存放两千多万的数据量，第一层存储的数据如果是bigint的话，可以存放8byte的数据，空白处默认是6byte(指向下一个大节点磁盘文件的地址指针)，而Innodb_page_size是16384（约等于16kb），这样一层可以存储的数据为16kb/(8+6)B=1170；第二层也是1170；第三层需要存放data，所以一个相当于1Kb，这样的话可以存放16kb；因此一个三层的B+树可以存放1170*1170*16kb的数据。

btree因为索引中也是有数据的，所以，第一层16kb，最多只能存储16个索引，如果要存两千多万的数据，则要16的n次方=2000万，这个层数是绝对大于B+树的3层的。

#16384
show global status like'Innodb_page_size';

Hash

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值，前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据。

如果是范围查询，哈希索引就没法使用了，hash是无序的，经过哈希算法后，是不连续的，就没办法再利用索引完成范围查询。

哈希索引也没办法利用索引完成排序，以及like"xxx%”这样的部分模糊查询(这种部分模糊查询，其实本质上也是范围查询)。

哈希索引也不支持多列联合索引的最左匹配规则。

索引下推

explain中extra字段：Using index condition；

索引条件下推（Index Condition Pushdown，ICP），是在MySQL5.6之后完善的功能，ICP是默认开启的，对于二级索引，只要把条件给存储引擎，存储引擎就会进行过滤，不需要我们干预。

索引的使用是在存储引擎中进行的，而数据记录的比较是在Server层中进行的。

引入索引下推：过滤的动作由下层的存储引擎层通过使用索引来完成，而不需要上推到Server层进行处理。

例如：create index n on sys_user(name,phone);

select * from sys_user where name='a'  and phone like '%1234';

联合索引的叶子节点的记录是先按照name字段排序；name字段相同的情况下再按照phone字段排序，因此把%加在phone字段前面的时候，是无法利用索引的顺序性来快速比较，该查询语句中只有name字段可以使用索引进行快速比较和过滤。

不使用索引下推流程：

• InnoDB使用联合索引查出所有name为a的二级索引数据，得到全部主键值
• 拿到主键索引进行回表，到主键索引中找到上述主键值的完整用户记录；
• InnoDB把这些完整用户记录返回给MySQL的Server层，在Server层过滤出尾号为1234的用户。

假如数据表中10万条记录符合name='a'，只有1条符合phone LIKE '%1234'，而InnoDB需要将99999条无效的记录也传输给Server层让其筛选，更严重的是，这99999条数据还需要回表。

使用索引下推流程：

• 通过name = 'a'在联合索引的叶子节点中找到了符合条件的记录，而且phone字段也恰好在联合索引的叶子节点中。
• 直接在联合索引的叶子节点中进行遍历，筛选出尾号为1234的记录，找到主键值为222的记录
• 最后只需要进行1次回表操作找到符合全部条件的1条记录，返回给Server层。

使用ICP的方式能有效减少回表的次数。

注：即使满足索引下推的使用条件，查询优化器也未必会使用索引下推，可能存在更高效的方式。

索引下推应用：所求查询字段(select列)不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。

#查看索引下推状态
SHOW VARIABLES LIKE 'optimizer_switch';
#关闭索引下推
SET optimizer_switch="index_condition_pushdown=off";

强制索引

force index（key）：让mysql强制使用某个索引

SELECT * FROM user u force index(idx_update_time) where u.id=100 order by u.update_time

ignore index：强制忽略索引

SELECT * FROM user u ignore index(idx_update_time) where u.id=100 order by u.update_time

索引失效情况

违反最左前缀原则

使用反向查询（!=, <>,NOT LIKE）

like以通配符开头

对索引列使用函数、使用表达式、使用类型转换

使用or时前面的是索引列后面的不是

order by使用索引情况

order by默认升序（asc）排序

在MySQL中的order by有两种排序实现方式：

1. 利用有序索引获取有序数据
2. 文件排序

在使用explain分析查询的时候，利用有序索引获取有序数据显示Using index。而文件排序显示Using filesort。

利用有序索引获取有序数据

取出满足过滤条件、作为排序条件的字段，以及可以直接定位到行数据的行指针信息，在 Sort Buffer 中进行实际的排序操作，然后利用排好序的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端。

按照索引对结果进行排序：order by 使用索引时条件：

• 只包括在有select的列上
• order by中所有的列必须在同一个索引，并且索引的顺序和order by子句中的顺序完全一致，并且所有列的排序方向（升序或者降序）一样才有（混合使用ASC模式和DESC模式则不使用索引）
• where语句与order by语句组合满足最左前缀
• 如果查询联接了多个表，order by子句的所有列引用的是第一个表的列才可以

文件排序

filesort 并不是说通过磁盘文件进行排序，而是告诉我们进行了一个排序操作。即在MySQL Query Optimizer 所给出的执行计划中被称为文件排序（filesort）。

文件排序是通过相应的排序算法，将取得的数据在内存中进行排序: MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。

实现算法有以下两种：

双路排序：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。在MySQL4.1版本之前只有这一种排序算法。

单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。MySQL4.1开始改进的算法，主要目的是为了减少双路排序中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sortbuffer 空间。MySQL4.1以后所有版本同时也支持第一种算法。

判定使用哪种排序算法：MySQL主要通过比较系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和。若 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。若希望 order by 操作的效率尽可能的高，则应注意max_length_for_sort_data参数。

问题：数据库出现大量的排序等待，造成系统负载很高，响应时间变长。

解决：因为MySQL 使用了传统的第一种排序算法而导致，加大了max_length_for_sort_data 参数值之后，系统负载马上得到了较大缓解，响应变快。

以下情况使用文件排序：

• where语句与order by语句，使用了不同的索引
• 检查的行数过多，且没有使用覆盖索引
• order by中的列使用了不同的索引
• 对索引列同时使用了ASC和DESC
• where语句或者order by语句中索引列使用了表达式，包括函数表达式
• where 语句与order by语句组合满足最左前缀，但where语句中使用了条件查询。虽然where与order by构成了索引最左有缀的条件，但是where子句中使用的是条件查询。
• 当使用left join，使用右边的表字段排序