MySQL查询优化和索引的总结

NingC
711 阅读12分钟

B+树

B Tree

  • 度(Degree)-节点的数据存储个数
  • 叶节点具有相同的深度
  • 叶节点的指针为空
  • 节点中的数据key从左到右递增排列

B+ Tree

  • 非叶子节点不存储data，只存储key，可以增大度

  • 叶子节点不存储指针

  • 顺序访问指针，提高区间访问的性能

为什么MySQL的索引要使用B+树

一般来说B+ Tree比B Tree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4 KB，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+ Tree比B Tree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

因为B Tree不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。

聚簇索引

GW4NAf.png
GW4NAf.png
  • 按主键值的大小进行记录和页的排序
  • B+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）

具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在 MySQL 语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

聚簇索引只有在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。可以为指定的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。

查询优化

如何建立索引

考虑索引的选择性

索引的选择性（ Selectivity），是指不重复的索引值（也叫基数， Cardinality）与表记录数的比值；选择性的取值范围为(0, 1]，选择性越高的索引价值越大。如果选择性等于1，就代表这个列的不重复值和表记录数是一样的，那么对这个列建立索引是非常合适的，如果选择性非常小，那么就代表这个列的重复值是很多的，不适合建立索引。 

SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM
employees.employees;

使用短索引

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

employees表只有一个索引，那么如果我们想按名字搜索一个人，就只能全表扫描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND
last_name='Anido';

ALTER TABLE employees.employees ADD INDEX `first_name_last_name4` (first_name,
last_name(4));

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于覆盖索
引。

索引的使用

  • 一定要避免全表扫描，如果扫一张大表的数据就会造成慢查询，导致数据的连接池直接塞满,导致事故。首先考虑在 where 和 order by 设计的列上建立索引。

  • 尽量使用覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称 之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次,这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

  • 全值匹配

查询优化器会分析这些查询条件并且按照可以使用的索引中列的顺序来决定先使用哪个查询条件

  • 匹配左边的列

因为B+树先是按照 t1 列的值排序的，在 t1 列的值相同的情况下才使用 t2 列进行排序，也就是说 t1 列的值不同的记录中 t2 的值可能是无序的。而现在你跳过 t1 列直接根据 t2 的值去查找，这是做不到的。

  • 不要在列上进行运算

  • 不使用NOT IN、！=、<>操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的

  • like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。

  • 范围之后全失效
select * from t1 where b > 1 and c > 1;

这样子对于联合索引来说，只能用到 b 列的部分，而用不到 c 列的部分，因为只有 b 值相同的情况下才能用 c 列的值进行排序，而这个查询中通过 b 进行范围查找的记录中可能并不是按照 c 列进行排序的，所以在搜索条件中继续以 c 列进行查找时是用不到这个B+树索引的

  • 精准匹配某一列并范围匹配另一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比方说这样：

select * from t1 where b = 1 and c > 1;

  • 排序
select * from t1 order by b, c, d;

这个查询的结果集需要先按照b值排序，如果记录的b值相同，则需要按照c来排序，如果c的值相同，则需要按照d排序。因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

  • 分组
select b, c, d, count(*) from t1 group by b, c, d;

如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有索引的话，正好这个分组顺序又和B+树中的索引列的顺序是一致的，所以可以直接使用B+树索引进行分组。

索引失效的场景

8fUWk9.png
8fUWk9.png

查询优化

关于join的优化

不管是内连接还是左右连接，都需要一个驱动表和一个被驱动表，对于内连接来说，选取哪个表为驱动表都没关
系，而外连接的驱动表是固定的，也就是说左连接的驱动表就是左边的那个表，右连接的驱动表就是右边的那个
表。连接的大致原理是：

  1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的访问形式来执行对驱动表的单表查询。
  2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

减少请求的数据量

  • 只返回必要的列：最好不要使用 SELECT * 语句。
  • 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
  • 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

查询优化器日志

-- 开启
set optimizer_trace="enabled=on";
-- 执行sql
-- 查看日志信息
select * from information_schema.OPTIMIZER_TRACE;
-- 关闭
set optimizer_trace="enabled=off";

查询优化器 一条 SQL 语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划。 优化过程大致如下： 1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个

Explain关键字

Explain关键字解析

8fR0Qe.png
8fR0Qe.png

type

  • system

  • 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、 Memory，那么对该表的访问方法就是system

  • const

  • 当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const

  • eq_ref

  • 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref 

    explain select g.*, mg.stock_count, mg.start_date, mg.end_date, mg.miaosha_price from miaosha_goods mg left join goods g on mg.goods_id = g.id;

  • ref

  • 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref

  • range

  • 对索引使用范围查询

    explain select * from goods where id > 1;

  • index

  • 当我们可以使用覆盖索引，但需要扫描全部的索引记录时，该表的访问方法就是index。

    explain select id from goods;
    explain select user_id from miaosha_order where goods_id = 1;

  • all

  • 全表扫描

possible_keys 和 key

possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key列表示实际用到
的索引有哪些。不过有一点比较特别，就是在使用index访问方法来查询某个表时， possible_keys列是空的，而key列展示的是实际使用到的索引

possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时
间，所以如果可以的话，尽量删除那些用不到的索引

key_len

key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

  • 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定
    字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是
    UTF-8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。
  • 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。
  • 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

Extra

  • Using index - 用了覆盖索引

    当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息

  • Using where - 从索引查出来数据后继续用where条件过滤

    当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。

  • Using filesort - 用了文件排序，排序的时候没有用到索引

    很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名： filesort）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示。

  • Using temporary - 用了临时表（优化策略：增加条件以减少结果集、增加索引，思路就是要么减少待排序的数量，要么就提前排好序）

    在许多查询的执行过程中， MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、 GROUP BY、 UNION等子句的查询过程中，如果不能有效利用索引来完成查询， MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示。

avatar
文章
阅读
粉丝