浅谈MySQL索引优化

一、索引简介

什么是索引？

• MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

• 可以简单理解为“排好序的快速查找数据结构”。

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

索引的优点

• 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

索引结构

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等。

下面简单介绍下BTree索引、B+Tree索引、聚簇索引与非聚簇索引

• BTree索引

• 

• B+Tree索引

InnoDB使用B+Tree作为索引结构

• 

拓展：B+Tree与B-Tree 的区别

1）B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

2）在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B树多，树高比B树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。
　

思考：为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？

1) B+树的磁盘读写代价更低
　　
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
2) B+树的查询效率更加稳定
　　
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

• 聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

• 

聚簇索引的好处：

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的io操作。

聚簇索引的限制：

对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

MySQL索引分类

• 单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

单独建单值索引：

CREATE INDEX idx_customer_name ON customer(customer_name);

删除索引：

DROP INDEX idx_customer_name on customer;

• 唯一索引

索引列的值必须唯一，但允许有空值

单独建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

删除索引：

DROP INDEX idx_customer_no on customer;

• 主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

单独建主键索引：

ALTER TABLE customer add PRIMARY KEY customer(customer_no);

删除建主键索引：

ALTER TABLE customer drop PRIMARY KEY;

• 复合索引

即一个索引包含多个列

单独建索引：

CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

删除索引：

DROP INDEX idx_no_name on customer;

• 基本语法总结

创建:

CREATE [UNIQUE ] INDEX [indexName] ON table_name(column));

删除:

DROP INDEX [indexName] ON mytable;

查看:

SHOW INDEX FROM table_name;


还可以使用ALTER命令

哪些情况需要创建索引？☆

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该创建索引

• 查询中与其它表关联的字段，外键关系建立索引

• 单键/组合索引的选择问题， 组合索引性价比更高

• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

• 查询中统计或者分组字段

哪些情况不要创建索引？☆

• 表记录太少

• 经常增删改的表或者字段

虽然提高了查询速度，但是会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

• Where条件里用不到的字段不创建索引

• 过滤性不好的不适合建索引

二、借助Explain进行性能分析

Explain是什么(查看执行计划)

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

Explain用途

• 查看表的读取顺序

• 查看哪些索引可以使用

• 查看数据读取操作的操作类型

• 查看哪些索引被实际使用

• 查看表之间的引用

• 查看每张表有多少行被物理查询

Explain使用方法

• Explain + SQL语句

• 执行计划包含的信息:

  

各字段解释:☆

• id

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

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

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

id相同不同，同时存在

每个id号码，表示一趟独立的查询。一个sql的查询趟数越少越好。

 

• select_type

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

• table

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

• partitions

  代表分区表中的命中情况，非分区表，该项为null

• type☆

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

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

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

 

• possible_keys

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

• key

  实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

• key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分的利用了索引。

• ref

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

• rows

  rows列显示MySQL认为它执行查询时必须检查的行数。rows越小，性能越高。

• filtered

  这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

• Extra

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

三、查询优化策略☆

单表优化策略

• 全列匹配

• 最左前缀法则

  如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始命中并且不跳过索引中的列。

  注意：由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。

• 不要在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

• 存储引擎不能使用索引中范围条件右边的列

• mysql 在使用不等于(!= 或者<>)的时候无法使用索引,会导致全表扫描

• is not null 无法使用索引,但is null是可以使用索引的

• 若like以通配符开头('%abc…')，则mysql索引失效,会变成全表扫描的操作

• 字符串不加单引号索引失效

• 练习

 

 

• 对于单键索引，尽量选择针对当前query过滤性更好的索引

• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

• 书写sql语句时，尽量避免造成索引失效的情况

多表关联查询优化策略

• 保证被驱动表的join字段已经被索引

• left join 时，选择小表作为驱动表，大表作为被驱动表。

• inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

• 子查询尽量不要放在被驱动表，有可能使用不到索引。

• 能够直接多表关联的尽量直接关联，不用子查询。

子查询优化

• 尽量不要使用not in或者not exists。用left outer join on xxx is null 替代。

排序分组优化

• ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

• 当范围条件是group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

• 如果不在索引列上，filesort有两种算法：
  mysql就要启动双路排序和单路排序

• GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。