索引应用

索引创建

create table时创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。创建表时创建索引的基本语法如下：

CREATE TABLE table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]

其中，UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；INDEX与KEY为同义词，两者作用相同，用来指定创建索引。

例如，可以按照如下方式，在id字段上使用UNIQUE关键字创建唯一索引：

CREATE TABLE t1 (  id INT NOT NULL,     name CHAR(30) NOT NULL,     UNIQUE INDEX UniqIdx(id) );

alter table时创建索引

ALTER TABLE创建索引的基本语法如下：

ALTER TABLE table_name ADD  [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

例如，可以按照如下方式，在bookId字段上建立名称为UniqidIdx的唯一索引：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx (bookId);

create index创建索引

CREATE INDEX创建索引的基本语法如下：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name  ON table_name (col_name [length],...) [ASC|DESC]

例如，可以按照如下方式，在bookId字段上建立名称为UniqidIdx的唯一索引：

CREATE UNIQUE INDEX UniqidIdx ON book (bookId);

一个索引创建时的问题

如下SQL会创建几个索引？

CREATE TABLE tb_user (
    id BIGINT,
    username VARCHAR(100),
    email VARCHAR(100),
    INDEX idx_username (username),
    UNIQUE KEY udx_email (email)
);

答案是三个，一个是idx_username，一个是udx_email，最后一个是因为该表中没有主键且没有非NULL的唯一索引列，所以会新建一个新的索引

使用如下SQL只能查到一般的索引，系统生成的索引查询不到

show index from tb_user;

需要使用

select * from information_schema.INNODB_SYS_TABLES where name = 'db_test/tb_user';
select * from information_schema.INNODB_SYS_INDEXES where table_id = 111;

索引重建

索引重建情形

1. 表上频繁发生update,delete操作；
2. 表上发生了alter table ..move操作（move操作导致了rowid变化）

索引重建判断

1. 一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析

   analyze index index_name validate structure;
   

2. 在相同的session中查询index_stats表

   select height,DEL_LF_ROWS/LF_ROWS from index_stats;
   

   当查询的height>=4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情况下，就应该考虑重建该索引。

索引重建方法

• drop原索引，然后再创建索引：

  drop index index_name;
  create index index_name on table_name (index_column);
  

  这种方式相当耗时，一般不建议使用

• 直接重建索引：

  alter index indexname rebuild;
  alter index indexname rebuild online;
  

  此方法较快，建议使用。

rebuild是快速重建索引的一种有效的办法，因为它是一种使用现有索引项来重建新索引的方法。如果重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在，因此，使用这种重建方法需要有额外的磁盘空间可供临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。

rebuild重建索引的过程：

1. Rebuild以index fast full scan或table full scan方式（采用那种方式取决于cost）读取原索引中的数据来构建一个新的索引，重建过程中有排序操作，rebuild online执行表扫描获取数据，重建过程中有排序的操作；

2. Rebuild会阻塞DML操作，rebuild online不会阻塞DML操作；

3. rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。

重建索引过程中的注意事项：

1. 执行rebuild操作时，需要检查表空间是否足够；
2. 虽然说rebuild online操作允许DML操作，但还是建议在业务不繁忙时间段进行；
3. Rebuild操作会产生大量Redo Log；

索引使用原则

索引使用必要性判断

1. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

2. 索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。

3. 只要创建了索引，就一定会走索引吗，比如，在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。

   举例，假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放，索引可以搜索id、（id,name）、（id, name, age）字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用该索引查询。

4. select in语句中如何使用索引，索引是否起作用，主要取决于字段类型：

   • 如果字段类型为字符串，需要给in查询中的数值与字符串值都需要添加引号，索引才能起作用。
   • 如果字段类型为int，则in查询中的值不需要添加引号，索引也会起作用。

   IN的字段，在联合索引中，按以上方法，也会起作用。

5. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

6. 下列几种情况，是不适合创建索引的：

   1. 频繁更新的字段不适合建立索引；
   2. where条件中用不到的字段不适合建立索引；
   3. 数据比较少的表不需要建索引；
   4. 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值；
   5. 参与列计算的列不适合建索引。

索引使用合理性判断

建议按照如下的原则来设计索引：

1. 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

2. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

3. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。

4. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

5. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

6. 模糊查询语句中如何使用索引？

   在MySQL中模糊查询 mobile like ‘%8765’，这种情况是不能使用 mobile 上的索引的，那么如果需要根据手机号码后四位进行模糊查询，可以用一下方法进行改造。

   我们可以加入冗余列（MySQL5.7之后加入了虚拟列，使用虚拟列更合适，思路相同），比如 mobile_reverse，内部存储为 mobile 的倒叙文本，如 mobile为17312345678，那么 mobile_reverse 存储 87654321371，为 mobile_reverse 列建立索引，查询中使用语句 mobile_reverse like reverse(’%5678’) 即可。

   reverse 是 MySQL 中的反转函数，这条语句相当于 mobile_reverse like ‘8765%’ ，这种语句是可以使用索引的。

   左模糊优先考虑走Elasticsearch

索引失效处理

可以采用以下几种方式，来避免索引失效：

1. 使用组合索引时，需要遵循“最左前缀”原则；
2. 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
3. 尽量使用覆盖索引（之访问索引列的查询），减少 select * 覆盖索引能减少回表次数；
4. MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
5. LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
6. 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
7. 少用or，用它来连接时会索引失效。

索引原理

Hash索引

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。它们有以下的不同：

• hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
• hash索引不支持使用索引进行排序，原理同上。
• hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。
• hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
• hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

索引设计原则

4.1 索引命名规范

单值索引，建议以 idx_ 为开头，字母全部小写。

例如：alter table t1 add key idx_r1(r1)；

组合索引，建议以 dx_multi_ 开头，字母全部小写。

例如：alter table t1 add key idx_multi_1(r1,r2,r3) ;

唯一索引，建议以 udx_ 为开头，字母全部小写；如果是多值唯一索引，则命名方式类似 udx_multi_1 等。

例如：
alter table t1 add unique key udx_f1(r1)；
或者
alter table t1 add key udx_multi_1(r1,r2,r3);

全文索引，建议以 ft_ 开头，字母全部小写，并且建议默认用 ngram 插件。

例如：alter table t1 add fulltext ft_r1(r1) with parser ngram;

前缀索引，建议以 idx_ 开头，以 _prefix 结尾。

例如： alter table t1 add key idx_r1_prefix(r1(10));

函数索引，建议以 idx_func_ 开头，字母全部小写。

例如： alter table t1 add key idx_func_r1((mod(r1,4)));

4.2 尽量选择整型列做索引

索引本身有有序的，尽量选择整型列做索引，

所以，尽量不用uuid，而是使用雪花id，页段id，等整数id去建立索引 。

雪花id，页段id的源码和原理，请参见尼恩的《视频第32章：超高并发、超高可用1000W级 ID组件 架构与实操》

如果避免不了，只有字符串做索引，可以选择对字符类型做 HASH ，再基于 HASH 结果做索引；

主键列数据类型最好也是整型，

避免对不规则的字符串建立主键（由于 INNODB 表即索引，所以应该避免掉。不仅仅 UUID 非有序，而是因为单个 UUID 太大）

4.3 优先建立唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。

如果使用姓名的话，可能存在同名现象，从而降低查询速度。

4.4 为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。

如果为其建立索引，可以有效地避免排序操作。

4.5 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。

因此，为这样的字段建立索引，可以提高整个表的查询速度。

4.6 限制索引的数目

索引的数目不是越多越好。

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

修改表时，对索引的重构和更新很麻烦。

越多的索引，会使更新表变得很浪费时间。

4.7 尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。

例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

4.9 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。

如果只检索字段的前面的若干个字符，这样可以提高检索速度。

4.10 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。

数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

4.11 最左前缀匹配原则，非常重要的原则。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，

比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

注意：=和in可以乱序。

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，

mysql的查询优化器会帮你优化成索引可以识别的形式

4.12 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，

唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，

那可能有人会问，这个比例有什么经验值吗？

使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录

4.13 索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。

所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

4.14 尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

4.15 考虑建立联合索引来提高查询效率

当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率

注意：选择索引的最终目的是为了使查询的速度变快。

这里存在的问题是，有些失效场景我不能理解

https://developer.aliyun.com/article/1509769?spm=a2c6h.12873639.article-detail.9.72ba6560NaswWP

在上述表结构中有三个索引：

• id：为数据库主键;
• union_idx：为id_no、username、age构成的联合索引;
• create_time_idx：是由create_time构成的普通索引;

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
   `target_rating` int
  `subjective_rating` int,
  `objective_rating` int,
    `actual_rating` int


    

  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`,`age`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (null, '1004', 'Tom4', 14, '2023-02-25 09:04:23');

用于生成大量数据的存储过程

-- 删除历史存储过程
DROP PROCEDURE IF EXISTS `insert_t_user`

-- 创建存储过程
delimiter $

CREATE PROCEDURE insert_t_user(IN limit_num int)
BEGIN
 DECLARE i INT DEFAULT 10;
    DECLARE id_no varchar(18) ;
    DECLARE username varchar(32) ;
    DECLARE age TINYINT DEFAULT 1;
    WHILE i < limit_num DO
        SET id_no = CONCAT("NO", i);
        SET username = CONCAT("Tom",i);
        SET age = FLOOR(10 + RAND()*2);
        INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
        SET i = i + 1;
    END WHILE;

END $
-- 调用存储过程
call insert_t_user(100);

版本

select version();

分析索引是否失效，有一个重要的前提条件，就是是否使用覆盖索引，能使用覆盖索引的情况通常更容易使用索引

1. 联合索引不满足最左前缀匹配原则

可以用到索引

explain select * from t_user where id_no = '098765432123456789' and username = 'Eve';

只能用一部分

explain select * from t_user where id_no = '098765432123456789' and age = 28;

完全用不了索引

explain select * from t_user where username = 'Eve' and age = 28;

2.左模糊

一定会导致索引失效

explain select * from t_user where id_no like '%12%';
explain select * from t_user where id_no like '%12';

2.索引列参与计算，或作为函数参数，或发生隐式类型转换

直接来看示例：

-- 查询所有3年后恰好三十五岁的用户
explain select * from t_user where age + 3 = 35;
-- 以
explain select * from t_user where target_rating + actual_rating = 119;

可以看到，即便id列有索引，由于进行了计算处理，导致无法正常走索引。

针对这种情况，其实不单单是索引的问题，还会增加数据库的计算负担。就以上述SQL语句为例，数据库需要全表扫描出所有的id字段值，然后对其计算，计算之后再与参数值进行比较。如果每次执行都经历上述步骤，性能损耗可想而知。

建议的使用方式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。

针对上述示例的优化如下：

-- 内存计算，得知要查询的id为1
explain select * from t_user where id = 1 ;
-- 参数侧计算
explain select * from t_user where id = 2 - 1 ;

第三种索引失效情况：索引列参与了运算，会导致全表扫描，索引失效。

explain select * from t_user where SUBSTR(id_no,1,3) = '100';

上述示例中，索引列使用了函数(SUBSTR，字符串截取)，导致索引失效。

此时，索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题。

示例中只列举了SUBSTR函数，像CONCAT等类似的函数，也都会出现类似的情况。解决方案可参考第三种场景，可考虑先通过内存计算或其他方式减少数据库来进行内容的处理。

id_no列发生隐式类型转换

explain select * from t_user where id_no = 99999999999;

比较操作

两列比较

两列数据做比较，无论是哪种比较，即便两列都创建了索引，索引也会失效。

explain select * from t_user where target_rating > actual_rating;
explain select * from t_user where target_rating = actual_rating;
explain select * from t_user where target_rating < actual_rating;

不等比较

explain select * from t_user where id_no <> '1002';

当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是。

explain select * from t_user where create_time != '2022-02-27 09:56:42';

上述SQL中，由于“2022-02-27 09:56:42”是存储过程在同一秒生成的，大量数据是这个时间。执行之后会发现，当查询结果集占比比较小时，会走索引，占比比较大时不会走索引。此处与结果集与总体的占比有关。

需要注意的是：上述语句如果是id进行不等操作，则正常走索引。因为id是唯一的，该值仅仅会出现一次，很好把其他数据过滤出来

explain select * from t_user where id != 2;

is null、is not null

需要看优化器能否走索引

explain select * from t_user where id_no is not null;

11. not in和not exists

在日常中使用比较多的范围查询有in、exists、not in、not exists、between and等。

explain select * from t_user where id in (2,3);

explain select * from t_user where id_no in ('1001','1002');

explain select * from t_user u1 where exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

explain select * from t_user where id_no between '1002' and '1003';

上述四种语句执行时都会正常走索引，具体的explain结果就不再展示。主要看不走索引的情况：

explain select * from t_user where id_no not in('1002' , '1003');

explain结果：

当使用not in时，不走索引?把条件列换成主键试试：

explain select * from t_user where id not in (2,3);

explain结果：

如果是主键，则正常走索引。

第十一种索引失效情况：查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。

再来看看not exists：

explain select * from t_user u1 where not exists (select 1 from t_user u2 where u2.id  = 2 and u2.id = u1.id);

explain结果：

当查询条件使用not exists时，不走索引。

第十一种索引失效情况：查询条件使用not exists时，索引失效。

优化

索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

• 存储引擎读取索引记录；
• 根据索引中的主键值，定位并读取完整的行记录；
• 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

• 存储引擎读取索引记录（不是完整的行记录）；
• 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
• 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

索引条件下推的具体实践

理论比较抽象，我们来上一个实践。

使用一张用户表tuser，表里创建联合索引（name, age）。

如果现在有一个需求：检索出表中名字第一个字是张，而且年龄是10岁的所有用户。那么，SQL语句是这么写的：

sql

 代码解读
复制代码select * from tuser where name like '张%' and age=10;

假如你了解索引最左匹配原则，那么就知道这个语句在搜索索引树的时候，只能用 张，找到的第一个满足条件的记录id为1。

那接下来的步骤是什么呢？

没有使用ICP

在MySQL 5.6之前，存储引擎根据通过联合索引找到name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。

我们看一下示意图：

可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

使用ICP

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name like '张%'，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

我们看一下示意图：

可以看到只回表了一次。

除此之外我们还可以看一下执行计划，看到Extra一列里 Using index condition，这就是用到了索引下推。

sql 代码解读复制代码+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

索引条件下推使用条件

• 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
• 只能用于InnoDB和 MyISAM存储引擎及其分区表；
• 对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

• 引用了子查询的条件不能下推；
• 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

相关系统参数

索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

手动切换状态

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

死锁

ERROR:Lock wait timeout exceeded; try restarting transaction

delete from tb_order where id in (1, 2, ..., 99999999999)
insert into tb_order (1d)

delete执行的慢insert时获取锁超时

innodb_lock_wait_timeout（默认是50s）

delete from table1 where id in (1, 2, ...);
show full processlist;
kill 2023;
select * from infomation_schema.INNODB_TRX

事务相关表

INNODB_TRX 当前运行的所有事务

INNODB_LOCKS 当前出现的锁，查看正在锁的事务

INNODB_LOCK_WAITS 锁等待的对应关系，查看等待锁的事务