石锤淡啤酒

生活就是等待戈多

浅谈mysql的索引

发表于 更新于 分类于 当前热度： ℃
本文字数： 7.8k 阅读时长 ≈ 7 分钟
索引是一个好东西，那么啥情况会导致索引失效？

哪些情况不走索引

  1. 隐式转化，说白了就是两边的参数的格式不一致，详情可见https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html： 
    1
    2
    1. 表字段类型和传参类型不一致，导致MySQL进行了类型转换。比如传入了`数值型`的值，而表结构中这个字段是`字符串`的。MySQL在处理这个类型不一致的时候，会进行隐式转化，转换成浮点型的类型，导致MySQL无法使用索引。
    2. JOIN 的时候，两张表JOIN的字段对应字符集不一致，也会导致隐式转化。
  2. 函数运算：在字段上进行函数运算。如 where a+1 = 1 ; where DATE_FORMAT(c,'%Y') = '2024' where upper(lastname)='smith'这样的。
  3. like百分号前缀匹配: where c1 like '%xxxx';
  4. 非值查询： 
    1
    2
    1. where a not in (1,2,3)
    2. where a != 1
  5. 当cpu、内存、磁盘容量很高的时候，可能也会导致优化器没有充足资源去评估可能的执行计划，从而选错索引，所以数据库的系统水位和系统配置还是要保持健康。(我曾经见过一次客户端链接风暴，2分钟内创建了3万个链接，每个链接是256k内存，一下子就吃掉了7.5G内存，导致实例出现oom)

如何使用explain

当我们分析为啥一个sql这么慢，我们肯定要用到explain命令，我们需要重点关注：

  1. id列：当 id 相同时，执行顺序由上向下；当 id 不同时，id 值越大，优先级越高，越先执行。
  2. type列：如果出现ALL INDEX 需要重点关注，表示的是该表在执行计划中是全表扫描或者全索引扫描，性能分别是倒数第一和倒数第二。
  3. rows列：表示的是通过索引过滤后有多少记录。一般来说，执行计划中这个列如果出现比较大的，就需要注意。如果出现扫描行数大于1000，但是只返回2条，那说明索引、SQL等存在优化的空间。
  4. extra列：如果出现了Using filesort或者Using temporary，说明MySQL没有办法利用索引本身的有序性进行排序。此时，需要关注前面的rows是否有超过2000行以上。如果超过，排序成本会变高。
  5. filtered列：表示的是返回行数和通过索引扫描的行数比值，最大值100，表示不需要回表过滤。filter 越小，说明索引优化的空间越大。

举个例子：
paradin

优化法则

  1. 单表查询，返回的数据行数，小于整个表的30%，建议在这个字段上加上索引（非绝对）。
  2. 返回行数 / 扫描行数 < 1/50，需要确认选择性好的字段是否都在索引里面。
  3. SQL查询要遵守MySQL最左匹配原则，尽量将选择性好的字段放在索引的前面。
  4. 当过滤成本和排序成本两者不可兼得时，可以考虑牺牲成本较小的操作，来成全成本较大部分。
  5. 等值查询的字段尽量放在组合索引的左侧，范围查询的字段放在组合索引的右侧。
  6. 提高SQL filter。1：避免回表查询；2：降低无效索引扫描

基础知识，留给你面试装逼用

  1. MySQL 索引是B+Tree数，是有序树。
  2. MySQL组合索引idx_ab(a, b,)中，是先根据a排序。a相同的情况下，再根据b排序。
  3. MySQL索引使用遵循最左匹配原则，针对a,b字段组合索引，需要先判断where条件里面有无a字段，如果没有，大概率是没办法走a,b 的组合索引的；如果有的话，再去判断b 字段是否在where条件里面

使用索引的关注点：1. 过滤能否走索引，2. 排序能否走索引。

索引字段选择性：

  1. 字段选择性代表这个字段在这个表里面的唯一性程度，例如我们需要查找某个员工的具体信息，已知道的条件是 where 部门=’数据库产品事业部’ and 名字=’张三’ and 性别=’男’，那按照我们对数据分布的理解，“名字”是三个条件中选择率最高的列，如果创建单列索引，“名字”列上建索引是最好的选择。
  2. 字段选择性 = COUNT(DISTINCT col) / COUNT(*) ，字段选择性越趋向于1，选择性越好。要提高定位效率，就要在选择率高的列上创建索引。
  3. 字段选择性越好，该字段上的查询越适合建立索引。

常见优化经典案例

  1. 缺失索引
    sql:select c1,c2 from t where c3=10086;
    表结构：
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE `t` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `c1` bigint(20) DEFAULT '0',
      `c2` bigint(20) DEFAULT '0',
      `c3` bigint(20) DEFAULT '0',
      PRIMARY KEY (`id`)
    )

查询扫描了100万行，返回了1行。原因是由于c3上面没有索引，导致SQL执行了全表扫描。explain一下的结果是：

1
2
3
4
5
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      |    t   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000000 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+

因此可以在字段c3 上面添加一个普通索引。如果c3 是唯一，可以添加唯一索引。alter table t add index idx_c3(c3);

  1. 索引优化1
    sql:explain SELECT * FROM slow_log_history where sql_id='88891227eb51726145bf033966bc4574' and 'slow_date' ='2024-07-15';

表结构：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `slow_log_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  ...
  `sql_id` varchar(100) DEFAULT NULL COMMENT '库名',
 ...
  `slow_date` date DEFAULT '1970-01-01',
  PRIMARY KEY (`id`),
  KEY `idx_sql_id` (`sql_id`),
) ENGINE=InnoDB AUTO_INCREMENT=2124700 DEFAULT CHARSET=utf8
;

查询扫描了133行，返回了1行。其中filter 只有3.07，说明很多数据都需要回表判断。explain一下的结果是：

1
2
3
4
5
6
7
mysql> explain  SELECT * FROM slow_log_history where sql_id='88891227eb51726145bf033966bc4574' and 'slow_date' ='2024-07-15';
+----+-------------+------------------+------------+------+----------------------------------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table            | partitions | type | possible_keys                          | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------------+------------+------+----------------------------------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | slow_log_history | NULL       | ref  | uniq_ins_id,idx_sql_id,idx_date_ins_db | idx_sql_id | 303     | const |  133 |     3.07 | Using where |
+----+-------------+------------------+------------+------+----------------------------------------+------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

因此可以将idx_sql_id (sql_id) 改成 idx_sql_id_date (sql_id, slow_date)

  1. 索引优化2
    表结构：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE `slow_log_history` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      ...
      `sql_id` varchar(100) DEFAULT NULL COMMENT '库名',
     ...
      `slow_date` date DEFAULT '1970-01-01',
      PRIMARY KEY (`id`),
      KEY `idx_date_sql_id` (`slow_date`, `sql_id`),
    ) ENGINE=InnoDB AUTO_INCREMENT=2124700 DEFAULT CHARSET=utf8
    ;
    在该查询中，需要获取2024-07-10 之后特定sql_id 的所有记录。
    由于我们的索引是(slow_date, sql_id) 的组合，我们的查询完全命中索引，可以直接在索引里面定位到第一条满足的记录，然后开始向右遍历。但是遍历的过程中，2024-07-10 这一天是有很多记录的，会导致不是我们需要的sql_id数据也被我们扫描到了。

同样的，2024-07-10 的记录遍历完后，开始遍历2024-07-11号的，一直到遍历到该索引的最右边。
相当于扫描的数据总量为 slow_date =’2024-07-10’ and sql_id>=’xxx’ 加上 slow_date > ‘2024-07-10’

paradin

1
2
3
4
5
6
7
mysql> explain  SELECT * FROM slow_log_history force index(idx_date_sql_id)  where sql_id='88891227eb51726145bf033966bc4574' and `slow_date` >='2024-07-10';
+----+-------------+------------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
| id | select_type | table            | partitions | type  | possible_keys   | key             | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+------------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | slow_log_history | NULL       | range | idx_date_sql_id | idx_date_sql_id | 307     | NULL | 381138 |    10.00 | Using index condition |
+----+-------------+------------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

paradin

其实一个sql_id，一天就一条记录，SQL整体返回行数就只有5条左右。按照刚刚的理论，我们希望位于利用索引去查询，是能够定位到最小的一个范围，避免无效扫描。那我们的索引可以调整下顺序，改成 KEY idx_sql_id_date (sql_id,slow_date)这样我们的索引中，同样的sql_id的情况下，不同的slow_date是相邻是顺序排列的。

paradin

1
2
3
4
5
6
7
mysql> explain  SELECT * FROM slow_log_history force index(idx_sql_id_date)  where sql_id='88891227eb51726145bf033966bc4574' and `slow_date` >='2024-07-10';
+----+-------------+------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table            | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | slow_log_history | NULL       | range | idx_sql_id_date | idx_sql_id_date | 307     | NULL |    7 |   100.00 | Using index condition |
+----+-------------+------------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

新的索引，只需要扫描7行，效率大大提升，高下立判。

  1. 排序优化
    sql:
    select c4, c5 from t where c1=x and c2 in (x, y) order by c3 limit 100;

假设1
c1 条件过滤后，有102万条记录。c1,c2 条件过滤后，有100万条记录。
如果我们建的索引是(c1,c2,c3)。由于c2是多个值，那么c3在索引里面是一个无序的状态，无法使用索引排序，在执行计划里面就会出现filesort。100万条记录进行filesort会比较慢。
优化方式：
调整索引 idx_c1_c3_c2(c1,c3,c2)
由于c3 前面的c1字段是等值查询，那么c3 在索引里面就是一个有序的状态。而c2 在二级索引，可以利用MySQL的ICP 特性完成过滤，去除了filesort成本。

注：
a. filesort表示MySQL无法使用索引的有序性完成排序，需要进行额外排序。可能采用堆排，归并排序等方式。
b. ICP 是MySQL的一个特性，未使用该特性的时候，某些过滤条件就算在索引里面，MySQL也无法使用索引过滤，被迫需要回表过滤。启用ICP特性后，Server层可以将该过滤条件传递到引擎，让引擎完成过滤，降低回表次数。（该特性会让rows_examined 指标失真）

假设2
c1 条件过滤后，有102万条记录。c1,c2 条件过滤后，有100条记录。
c1, c2 条件通过索引过滤后，剩余的数据只有100条记录，排序成本已经非常低。
优化方式：
调整索引 idx_c1_c2(c1,c2)

注：本例的Case，需要综合评估排序成本以及索引成本，将大头的成本用索引解决。如果两种成本差不多，需要考虑应用侧拆成多个等值查询，应用侧做合并。

  1. 索引覆盖
    部分查询，像做计数，高频查询字段和条件，可以放在索引里面，避免回表的损耗。
    Case1：
    select count(*) from t where c1=x and c2=y;
    如果count 没办法避免，则可以将c1,c2都加到索引里面，使用索引覆盖，提高count效率
    解决方案就是：添加索引idx_c1_c2(c1,c2)

Case2:
select c3 from t where c1 = x and c2 = y;
假设该查询是一个特别高频的，假设达到了1W+的QPS，可以考虑将c3 字段添加到索引里面，避免回表
解决方案就是：添加索引idx_c1_c2_c3(c1,c2,c3)

当你新增一个索引的代价

当数据库管理系统向表中添加一行时，它必须在每一个索引上都添加相应的行。在当前的硬件条件下，在一个索引上添加一行，插入操作所花费的时间就是增加10ms，因为必须从磁盘上读取一个叶子页。

当一个事务向一张有10个索引的表里插入1行数据时，索引的维护就会使响应时间增加10*10ms=100ms，这可能是可接受的。然而，如果一个事务向一张有10个索引的表里插入20行数据的话，索引的维护就会需要181次随机读，即耗费1.8s。这个估算基于的前提假设是，新的索引行会把表上其中一个索引（一直增大的键值上的索引）添加到同一个叶子页上，而会把其余9个索引添加到20个不同的叶子页上。从响应时间的角度来看，在一个有10个索引的大表上进行大的事务操作（每个事务中有许多插入或删除操作）可能是无法忍受的。

感谢您请我喝咖啡~O(∩_∩)O，如果要联系请直接发我邮箱chenx1242@163.com，我会回复你的
-------------本文结束感谢您的阅读-------------