MySQL索引优化及查询优化

MySQL索引优化

索引不是越多越好

1. MySQL的查询计划：查询优化器会自动计算查询记录时的cost成本并生成执行计划，（例如是使用全表扫描，还是使用索引1、索引2等等…）如果索引过多，这个计算过程反而会影响到查询效率。
2. 每个索引都是一个B+树，以主键索引举例，B+树中每个节点里的记录是以主键从小到大顺序采用单向链表的方式串联的，此时删除主键为1的记录时会触发节点的重新排序以及可能带来的数据页回收或者分裂等操作。当索引过多时，对记录的增删改操作会带来大量上述的操作，非常影响性能。
3. 索引的数据都是存储在磁盘上的，越多的索引代表占用了越多的磁盘空间。

索引的创建原则

1. 不为离散度低的列创建索引。例如：字段值只有0和1，这种情况没有必要创建索引。
2. 只为搜索、排序、分组的列创建索引，也就是为出现在 Where 条件、order by、group by 中的字段创建索引。
3. 用好联合索引，不要为联合索引的第一个索引列单独创建索引，这是浪费，也正因如此，建立联合索引时要将最常用的列放在最左边。
4. 为长字段创建前缀索引。即只取字段值的前N个字符作为索引。
5. 频繁更新的值不要作为主键或者索引，因为涉及数据页分裂的情况会影响性能。

索引失效

1. 使用到联合索引查询时违反最左匹配原则。
   违反最左匹配原则的情况有：

   • 不使用联合索引的最左索引列作为查询条件。
   • 使用最左索引列但对索引列使用了Like模糊查询。

2. 条件使用反向查询，如 where name != “xxx”;

3. 对索引列做任何操作，使用最左索引列查询时使用函数、类型转化、数值计算等操作也会导致索引失效。如：where LEFT(name,3) = “xxx”;

4. 查询类型不匹配。例如phone字段为varchar，使用数值查询时就会触发转换，则索引失效。索引字段类型为字符串时，使用数字类型进行搜索不会用到索引。索引字段类型为数字类型时，使用字符串类型进行搜索会使用到索引。

5. 使用OR条件，查询非索引列数据时，带不带非索引列条件都不会触发索引；查询索引列数据时，使用And并且条件都是索引列时触发索引，使用OR触发的Extra为Using where; Using index。

啥是回表？为啥要减少回表？

原则：能不回表就不回表，必须回表就减少回表次数。
回表：从普通索引中获取到主键值再根据主键值回到主键索引的叶子结点中获取完整的记录的过程就叫回表。
回表的代价：回表的过程会将磁盘的数据页加载到内存中，这个过程是比较耗时的。
MySQL 5.6后引入ICP索引下推的优化，会自动减少回表操作。

啥是索引下推？

没有ICP之前的查询逻辑是，联合索引根据最左匹配原则查询到的数据后再回表查询完整记录，将记录返回server层做比较。
使用索引下推后查询逻辑是，联合索引查询到若干条数据后，直接在存储引擎中做匹配，减少了回表的次数。

-- 开启/关闭 icp索引下推
set optimizer_switch="index_condition_pushdown=on";
set optimizer_switch="index_condition_pushdown=off";

MySQL查询优化

• 不要使用select *，用到什么数据就查什么数据。
• 尽量不要联表查询，必须联表查询时不要超过3个，而且要使用小表驱动大表。
• 适当的把数据过大的表做拆分，基于冷热数据拆分原则，将热点数据保留，冷门数据拆出来放次表。
• 联合索引遵循索引的最左匹配原则，使用Explain查看执行计划是否命中索引。
• 不要建立过多索引，要适当拓展已有索引。

其他

聚簇索引和非聚簇索引

一张表里只有一个聚簇索引，聚簇索引树使用主键构成，所以主键索引就是聚簇索引， 如果一张表中没有主键，就用第一个unique键，没有unique键就生成一个隐藏的DB_ROW_ID列来构造。
除聚簇索引外的索引都是非聚簇索引，也叫二级索引。

MySQL查询过程

1. 客户端给服务端发送SQL查询语句。
2. 服务端解析SQL语句，进行预处理，再由优化器生成执行计划。
3. MySQL根据执行计划，调用存储引擎的API执行。
4. 把结果返回客户端。