mysql优化之索引优化详解

mysql优化之索引优化

1、索引是什么？

官网给出的答案是：索引(Index)是帮助MySQL高效获取数据的数据结构。其本质就是数据结构，排好序的快速查找的数据结构。

2、索引的数据结构

• 二叉树（二叉树在极端的情况下会变成一个链表（比方说添加的数据都是递增的）在这种情况下二叉树的查询速度是特别低的）
• 红黑树（红黑树是一种平衡二叉树，每一个节点的子节点都是2，当插入的节点个数都是递增的话，红黑树会通过自旋将自己维护成一个平衡的二叉树，但是会导致树的层级很高，需进行多次 I/O，而且IO的次数多少，取决于增加的数据）
• Hash 表（哈希表存储，其实是将存入的值，进行hash计算，得到一个散列值，将这个散列值，与数据的内存地址进行绑定，精确查询的速度很快，但是但不适合范围查找）
• B-Tree（B树是一个树状的数据结构，但是B树的每个节点都存储了key和data,key是索引的值，data存的是索引所在行的内存地址，但是不适合范围查找）
• B+Tree（这是索引使用的数据结构）

2.1、B树

B树是一颗多路平衡查找树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。

特点
1.所有键值分布在整颗树中（索引值和具体data都在每个节点里）
2. 每一个关键字有且只出现一次
3. 所有关键字按照从小到大的顺序进行排列
4. 每个节点除了存储关键字，还存储数据
5. 若经常访问的元素离根节点较近，则访问更加迅速
6. 叶子节点存储在同一层
7. 黑色阴影处存储的是下一个节点在磁盘中的地址

2.2、B+树

B+树是B-树的变体，也是一种多路搜索树

B+ 树特点？

1. B+ 树将 data 信息挪到了叶子节点，非叶子节点不存放 data 信息，只存储索引（冗余），这就意味着非叶子节点可以存放更多的索引
2. 叶子节点包含所有索引字段，叶子节点没有子节点，不用存储下一个字节的磁盘地址，所以没有那个白色的框框，但是叶子节点包含索引的 data 域
3. 叶子节点用指针连接，提高区间访问的性能，这也恰好是 B 树索引和 Hash 索引劣势所在，比如执行如下 SQL ：select * from t where col1 > 6 ，对于 B 树索引和 Hash 索引，他们根本不知道大于 6 的数据有哪些
4. 假设索引为主键索引，我们使用 BigInt 类型，占用 8 个字节（对应着色并且带着数字的框框），MySQL 中的磁盘地址指针为 6个字节（对应空白框框），那么我们存储一个索引则需要 14 个字节，那么一个 MySQL 磁盘页可以存储 16KB/14B = 1170个索引字段
5. 不同数据库对 data 域的实现方式不同，有些数据库 data 域存放的是索引行所在的磁盘地址指针，有些数据库 data 域存储的是索引行所有其他字段的信息，我们保守估计，假设每个 data 域占用 1KB，那么一个 MySQL 磁盘页可以存储个16KB/1KB = 16 个索引字段和 data 域
6. 假设三层 B+ 树被撑满了，能存放的数据量为 1170 * 1170 * 16 = 21902400 条，能存放两千多万条数据

B+树与 B- 树的不同之处在于:

1. 所有关键字存储在叶子节点内部，非叶子节点并不存储真正的 data
2. 为所有叶子结点增加了一个链指针
3. 因为内节点并不存储 data，所以一般B+树的叶节点和内节点大小不同，而B-树的每个节点大小一般是相同的

mysql使用B+树的原因？

1. B+ 树将 data 信息挪到了叶子节点，非叶子节点不存放 data 信息，只存储索引（冗余），这就意味着非叶子节点可以存放更多的索引
2. B+树的查找路径是由根到叶子节点，每次查找路径长度比较稳定
3. 范围遍历，B+树的叶子节点构成一条链表，访问更加方便
4. B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高；
5. B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描；

查看 MySQL 磁盘页的大小，默认 16KB

SHOW GLOBAL STATUS like 'Innodb_page_size';

2.3、MyISAM

MyISAM :

• 存储引擎是基于表的
• mysql 5.5之前的默认存储引擎是MyISAM
• MyISAM 索引文件和数据文件是分离的（非聚集索引）
• MyISAM 使用 B+ 树作为索引，图中的 data 域存储的是下一个磁盘的地址

举例说明 select * from t where col1=‘49’ 查找的过程：

• 加载第一层（根节点）的磁盘文件至内存，发现 15<49<56，拿到到第二层的磁盘地址
• 根据第一步拿到的磁盘地址，到第二层加载磁盘文件至内存，发现 49>=49 ，拿到到第三层的磁盘地址
• 根据第二步拿到的磁盘地址，到根节点找到 col1=49 的索引列，拿到索引所在行在磁盘中的地址
• 根据第三步拿到的磁盘地址，区磁盘中加载索引所在行的数据，返回给客户端

总共发生了 4 次 I/O

基于MyISAM 数据表各种文件的含义

• .frm：表结构的定义
• .MYD：数据文件
• .MYI：索引文件

2.4、InnoDB

InnoDB:

• 表数据文件（.ibd 文件）本身就是按B+Tree组织的一个索引结构文件
• InnoDB 中叶节点包含了完整的数据记录（ data 与存放了索引所在行其他所有字段的信息）
• 聚集（簇）索引：索引文件和数据文件是存储在一个文件里面，InnoDB 的主键索引就是聚集索引
• 聚集索引的优势：比非聚集索引的查找效率高，因为非聚集索引需要在 .MYD 文件和 .MYI 文件中查找，而聚集索引只需要在一个 .ibd 文件中查找
• 查询数据发生的io次数要比MyISAM的要少

举例说明 select * from t where col1=‘49’ 查找的过程：

• 加载第一层（根节点）的磁盘文件至内存，发现 15<49<56，拿到到第二层的磁盘地址
• 根据第一步拿到的磁盘地址，到第二层加载磁盘文件至内存，发现 49>=49 ，拿到到第三层的磁盘地址
• 根据第二步拿到的磁盘地址，到根节点找到 col1=49 的索引列，然后直接拿出索引所在行的数据，返回给客户端。

总共发生了 3 次 I/O

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？
InnoDB 中索引her数据存储是在同一个文件中，该文件就是按照 B+ 树去组织构建的，如果没有主键，InnoDB 没办法组织这个数据文件。在 MySQL 如果我们没有建立主键，那么MySQL 会自动选择一个具有唯一索引的列作为主键，如果找不到，就帮我们自动地新建一列，由mysql自己去维护。

为什么推荐主键为整形？
自增主键数据类型为整形，比较效率高，不要用 UUID 作为主键，字符串之间的比较效率低，并且整形数据占用的空间也比 UUID 小很多
为什么推荐主键自增？

首先，操作系统进行数据读取的时候，是按页的大小进行读取的(页大小通常为 4 kb)，MySQL 的页，默认值为 16 KB，也就是说对于 B+ 树的节点，最好设置成页的大小(16 KB)，这样一个 B+ 树上的节点就只会有一次 IO 读。推荐主键自增的原因，就牵扯到了页分裂和也合并了。

页分裂和也合并？
B+ 树为了维护索引的有序性，每插入或更新一条记录的时候，会对索引进行更新。

假设原来基于身份证作索引的 B+ 树如下(假设为二叉树 ，图中只列出了身份证的前四位)

现在有一个开头是 3604 的身份证对应的记录插入 db ，此时要更新索引，按排序来更新的话，显然这个 3604 的身份证号应该插到左边节点 3504 后面(如下图示，假设为二叉树，B+树 可以考虑容量大小)

如果把 3604 这个身份证号插入到 3504 后面的话，这个节点的元素个数就有 3 个了，显然不符合二叉树的条件，此时就会造成页分裂，就需要调整这个节点以让它符合二叉树的条件

这种由于页分裂造成的调整必然导致性能的下降，尤其是以身份证作为主键的话，由于身份证的随机性，必然造成大量的随机结点中的插入，进而造成大量的页分裂，进而造成性能的急剧下降。

那如果是以自增 id 作为主键？
由于新插入的表中生成的 id 比索引中所有的值都大，所以它要么合到已存在的节点(元素个数未满)中，要么放入新建的节点中(如下图示)所以如果是以自增 id 作为主键，就不存在页分裂的问题了。

有页分裂就必然有页合并，什么时候会发生页合并呢，当删除表记录的时候，索引也要删除，此时就有可能发生页合并，如图示
当我们删除 id 为 7，9 对应行的时候，上图中的索引就要更新，把 7，9 删掉，此时 8，10 就应该合到一个节点，不然 8，10 分散在两个节点上，可能造成两次 IO 读，势必会影响查找效率!

这个页大小是不是越大越好呢？
设置大一点，节点可容纳的数据就越多，树高越小，IO 不就越小了吗，这里要注意，页大小并不是越大越好，InnoDB 是通过内存中的缓存池(pool buffer)来管理从磁盘中读取的页数据的。页太大的话，很快就把这个缓存池撑满了，可能会造成页在内存与磁盘间频繁换入换出，影响性能。

关于推荐 InnoDB 主键自增？还有一个参考

InnoDB 使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

自增的情况

1. 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：
2. 这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

非自增的情况

1. 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：
2. 此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

基于InnoDB数据表各种文件的含义

• .frm：表结构的定义
• .ibd ：索引和数据文件

3、联合索引的结构？

我们在三个列上建立了一个联合索引：整形 id 、字符串 name、日期 birthDate

排序规则：建立索引时，哪个列写在前面，就先按照他排序

大致查找流程：
先按照 id 查找，如果 id 能比出大小，就按照 id 字段排序，如果 id 字段都相同，就按照第二个 name 字段进行比较，以此类推 …

比如 select* from employee where name=‘Staff’ 就不会走索引，
因为 name 是依靠着 id 进行排序的，再找不到id的情况下就会进行全盘扫描

4、什么情况下需要建立索引？

1. 主键自动建立唯一索引
2. 频繁作为查询的条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重IO负担
5. Where条件里用不到的字段不创建索引
6. 在高并发下倾向创建组合索引
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
8. 查询中统计或者分组字段
9. 经常增删改的表不需要建立索引
10. 表记录太少不需要建立索引
11. 数据重复且分布平均的表字段不需要建立索引

5、单表、双表、三表的如何建立索引？

单表：
单表根据where后面的查询条件建立索引。
双表：
左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引
右表连接左表，则需要拿着右表的数据去左表里面查，索引需要在左表中建立索引
三表：
使用小表驱动大表，在大表中建立了索引

6、MySQL 索引分类

1. 普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
2. 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
3. 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
4. 复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
5. 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配
6. 覆盖索引：slelect要查询的列在定义的索引中就能够查询到。不必在读取数据行，可以理解为要查询的列要被所建的索引覆盖。

7、MySQL 常见瓶颈

1. CPU 瓶颈：CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候
2. IO 瓶颈：磁盘I/O瓶颈发生在装入数据远大于内存容量时
3. 服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态

8、Explain是使用

Explain 是查看执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈

作用？

• 表的读取顺序（id 字段）
• 数据读取操作的操作类型（select_type 字段）
• 哪些索引可以使用（possible_keys 字段）
• 哪些索引被实际使用（keys 字段）
• 表之间的引用（ref 字段）
• 每张表有多少行被优化器查询（rows 字段）

9、Explain 参数详解

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

id 取值的三种情况：

• id相同，执行顺序由上至下
  

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

• id相同不同，同时存在：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行；衍生=DERIVED
  

select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询

• SIMPLE：简单的select查询，查询中不包含子查询或者UNION
• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
• SUBQUERY：在SELECT或者WHERE列表中包含了子查询
• DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里
• UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
• UNION RESULT：从UNION表获取结果的SELECT

table：显示这一行的数据是关于哪张表的

type：访问类型排列，显示查询使用了何种类型

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是

system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

挑重要的来说：

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
3. eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
4. ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束于另一点，不用扫描全部索引
6. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘数据库文件中读的）
7. all：FullTable Scan，将遍历全表以找到匹配的行（全表扫描）

possible_keys 可能会用到的索引

1. 显示可能应用在这张表中的索引，一个或多个
2. 若查询涉及的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key 实际上用到的索引

key_len  表示索引中使用的字节数

ref  显示索引被哪一列使用了 如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值

例如：由key_len可知t1表的索引idx_col1_col2被充分使用，t1表的col1匹配t2表的col1，t1表的col2匹配了一个常量，即’ac’

rows  根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra：包含不适合在其他列中显示但十分重要的额外信息

• Using filesort（文件排序）：mysql无法利用自己建立的索引排序规则完成排序操作，达到最后的结果，从而导致mysql自己内部重新建立排序规则来达到自己想要的结果，mysql自己内排序会消耗大量的时间
• Using temporary（创建临时表）：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
• Using index（覆盖索引）： 指的是要查询的列，在索引表中直接返回，没有去查询数据表，避免访问了表的数据行，效率比较高。
• Using where：表明使用了where过滤
• Using join buffer：表明使用了连接缓存

10、索引失效（应该避免）

• 全值匹配我最爱
• 最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
• mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
• is null，is not null也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）
• like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作
• 字符串不加单引号索引失效
• 少用or，用它连接时会索引失效

解决【like ‘%str%’ 】索引失效的问题：覆盖索引

口诀：

• 全值匹配我最爱， 最左前缀要遵守；
• 带头大哥不能死， 中间兄弟不能断；
• 索引列上少计算， 范围之后全失效；
• LIKE 百分写最右，覆盖索引不写 *；
• 不等空值还有 OR， 索引失效要少用；
• 字符引号不可丢， SQL 优化有诀窍。