MySQL索引失效与MySQL8新添加的索引特性

已于 2024-04-22 09:53:09 修改
阅读量784 收藏 15
点赞数 8
分类专栏： MySQL 文章标签： mysql 数据库
于 2024-02-29 13:33:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_57408211/article/details/136347456
版权

目录

MySQL索引失效的场景

数据表和索引的准备

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

2.对索引进行表达式计算

3.对索引使用函数

4.对索引隐式类型转换

5.like的不当使用

6.where子句中的 or

7.not int导致索引部分失效

MySQL8新增的索引特性

函数索引

索引跳跃扫描(Index Skip Scan)

 倒序索引

隐藏索引

MySQL索引失效的场景

数据表和索引的准备

创建数据表和索引

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `idcard` varchar(18) DEFAULT NULL COMMENT '身份编号',
  `username` varchar(32) DEFAULT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_idcard_username_age` (`idcard`,`username`,`age`),
  KEY `idx_create_time` (`create_time`)
);

 用存储过程来插入数据的，这里我贴出来方便你复现：

delimiter ;;
CREATE PROCEDURE insert_user(IN limit_num int)
begin
DECLARE i INT DEFAULT 1;
    DECLARE idcard varchar(18) ;
    DECLARE username varchar(32) ;
    DECLARE age TINYINT DEFAULT 1;
    WHILE i < limit_num DO
        SET idcard = CONCAT("NO", i);
        SET username = CONCAT("Tom",i);
        SET age = FLOOR(10 + RAND()*2);
        INSERT INTO `user` VALUES (NULL, idcard, username, age, NOW());
        SET i = i + 1;
    END WHILE;
end;;
delimiter ;
call insert_user(10000);

mysql版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

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

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，创建一个 (a, b, c) 联合索引，若查询条件是以下这几种，就可以匹配上联合索引：

  • where a=1；
  • where a=1 and b=2 and c=3；
  • where a=1 and b=2；
  • where b=1 and a=2 and c=3；

注意:因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效,如下：

  • where b=2；
  • where c=3；
  • where b=2 and c=3；

那么还有另一种情况 where a=1 and c=3。这种是 联合索引中间的字段去掉了。这种情况在不同mysql版本有不同的表现。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。 

 代码测试：

--使用了联合索引idx_idcard_username_age
mysql> explain select * from user where username= 'sdf' and idcard='123' and age=32;
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_idcard_username_age | idx_idcard_username_age | 211     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

--不符合最右匹配原则
mysql> explain select * from user where age=32;                                     
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9800 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

--不符合最右匹配原则，但查找的是联合索引中的字段，使用了联合索引，但是查询的行数基本等于总行数
mysql> explain select username from user where username= 'sd' and age=32; 
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | user  | NULL       | index | idx_idcard_username_age | idx_idcard_username_age | 211     | NULL | 9800 |     1.00 | Using where; Using index |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

--使用了索引，索引下推功能(Using index condition)优化
mysql> explain select * from user where idcard='2323' and age=32;                                              
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_idcard_username_age | idx_idcard_username_age | 75      | const |    1 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

这里讲解一下key_len的计算：

  • idcard 类型为varchar(18)，字符集为utf8mb4，也就是使用4个字节来表示一个完整的UTF-8。此时，key_len = 18* 4 = 72；
  • 由于该字段类型varchar为变长数据类型，需要再额外添加2个字节。此时，key_len = 72 + 2 = 74；
  • 由于该字段运行为NULL（default NULL），需要再添加1个字节。此时，key_len = 74 + 1 = 75；

所以在联合索引中只使用到idcard字段时候，其key_len就是75，其他的依次类推。

还有MySQL8中新增了Index Skip Scan，这个可能就在某些情况和最左匹配原则会有冲突，后面会讲解该情况。

2.对索引进行表达式计算

看例子：

mysql> explain select * from user where id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where id -1=10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9800 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

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

建议在查询条件中不要对索引进行表达式计算。

3.对索引使用函数

看例子

mysql> explain select * from user where substr(idcard,1,4) = '1100';  
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9800 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where idcard = '1100';
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_idcard_username_age | idx_idcard_username_age | 75      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

和对索引进行表达式计算一样，因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

但是在MySQL8.0 中推出了函数索引的特性。后序会讲解。

4.对索引隐式类型转换

例子1：

idcard是varchar字符串类型。条件查询中，输入的参数是整型，会进行隐式类型转换，不会使用索引。

mysql> explain select * from user where idcard = 1100;       
+----+-------------+-------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_idcard_username_age | NULL | NULL    | NULL | 9800 |    10.00 | Using where |
+----+-------------+-------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> explain select * from user where idcard = '1100';
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_idcard_username_age | idx_idcard_username_age | 75      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

例子2:另外一种情况，id是int整数类型，而条件查询中，输入的参数是字符串。这里却是使用了索引。

mysql> explain select * from user where id ='1';       
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select '10'>9;
+--------+
| '10'>9 |
+--------+
|      1 |
+--------+

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

这是从上面select '10'>9;的结果1得出的。(《mysql45讲》中提供的测试方法)

  • 如果规则是 MySQL 会自动将「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是 1；
  • 如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select "10" > "9"，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按ascii码） ，那么"10"字符串相当于 “1”和“0”字符的组合，所以先是拿 “1” 字符和 “9” 字符比较，因为 “1” 字符比 “9” 字符小，所以结果应该是 0。

那么select * from user where id ='1';  语句就相当于

select * from user where id = CAST("1" AS signed int);

 这不是在索引中使用函数，所以还是可以使用索引的。

而explain select * from user where idcard = 1100; 就相当于

explain select * from user where  CAST(idcard AS int) = 1100;

这是在索引字段使用了函数，所以最终就不会走索引。

5.like的不当使用

看例子：

mysql> explain select * from user where idcard like '%32';    
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9800 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where idcard like '32%';
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_idcard_username_age | idx_idcard_username_age | 75      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where idcard like '%32%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9800 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

模糊查询时（like语句），模糊匹配的占位符位于条件的首部会导致索引失效。

索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

6.where子句中的 or

看例子：

mysql> explain select * from user where id=21 or age=23;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 9800 |    10.01 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from user where age=32 or id=323;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 9800 |    10.01 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

--or前后字段都建立索引，使用>,<也是使用了索引的
mysql> explain select * from user where id=21 or idcard='323';
+----+-------------+-------+------------+-------------+---------------------------------+---------------------------------+---------+------+------+----------+----------------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                   | key                             | key_len | ref  | rows | filtered | Extra                                                          |
+----+-------------+-------+------------+-------------+---------------------------------+---------------------------------+---------+------+------+----------+----------------------------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | index_merge | PRIMARY,idx_idcard_username_age | idx_idcard_username_age,PRIMARY | 75,4    | NULL |    2 |   100.00 | Using sort_union(idx_idcard_username_age,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+---------------------------------+---------------------------------+---------+------+------+----------+----------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select idcard from user where id<12 or id>100; 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 4911 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

or前后的两个字段都需要有索引才不会失效。

可以这样想，若单独使用age字段作为条件很显然是全表扫描。既然已经进行了全表扫描了，前面id的条件再走一次索引反而是浪费了。所以，在使用or关键字时，切记两个条件都要添加索引，否则会导致索引失效。

7.not int导致索引部分失效

看例子：

mysql> explain select * from user where id not in( 32, 11,44);    
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 4941 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where idcard not in( '322', '1002');
+----+-------------+-------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_idcard_username_age | NULL | NULL    | NULL | 9800 |    50.02 | Using where |
+----+-------------+-------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from user where id in( 22, 12);   
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where idcard in( '322', '1002');    
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_idcard_username_age | idx_idcard_username_age | 75      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。

MySQL8新增的索引特性

函数索引

这里说的函数索引不是说对某列做了索引，之后就可以对该列进行函数操作。而是可以对该列操作函数后，把这个当做索引，之后就可以对该列使用函数。

在MySQL8.0之前对条件字段做函数操作、或者做运算都将不会使用字段上的索引。

mysql> show index from user;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user  |          0 | PRIMARY                 |            1 | id          | A         |        9800 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user  |          1 | idx_idcard_username_age |            1 | idcard      | A         |        9800 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| user  |          1 | idx_idcard_username_age |            2 | username    | A         |        9800 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| user  |          1 | idx_idcard_username_age |            3 | age         | A         |        9800 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| user  |          1 | idx_create_time         |            1 | create_time | A         |          74 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| user  |          1 | idx_age                 |            1 | age         | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.00 sec)

mysql> explain select * from user where month(create_time)=9;;            
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9800 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

可以看到执行计划的type是ALL，并没有使用索引。在MySQL8.0中推出了函数索引的特性，其是通过虚拟列来实现的，接着就来看看通过函数索引实现相同的需求。

注意看索引名idx_create_time_month对应的列名是null，所以是虚拟列。

--要用()把month函数括起来，不然就报错
mysql> create  index idx_create_time_month on user((month(create_time)));
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+----------------------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression           |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+----------------------+
| user  |          0 | PRIMARY                 |            1 | id          | A         |        9800 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL                 |
| user  |          1 | idx_idcard_username_age |            1 | idcard      | A         |        9800 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                 |
| user  |          1 | idx_idcard_username_age |            2 | username    | A         |        9800 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                 |
| user  |          1 | idx_idcard_username_age |            3 | age         | A         |        9800 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                 |
| user  |          1 | idx_create_time         |            1 | create_time | A         |          74 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL                 |
| user  |          1 | idx_create_time_month   |            1 | NULL        | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | month(`create_time`) |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+----------------------+
6 rows in set (0.00 sec)

mysql> explain select * from user where month(create_time)=9;
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_create_time_month | idx_create_time_month | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

创建函数索引后，这里就是使用索引的了。

注意：在使用函数索引时必须依照定义时的语法进行使用，否则优化器无法识别。

索引跳跃扫描(Index Skip Scan)

MySQL 8.0.13 版本对于range查询，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES(1,1), (1,2), (1,3), (1,4), (1,5),(2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;
EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;


执行ANALYZE TABLE，MySQL会分析指定表的键的值（主键、唯一键、外键等，也可以看成就是索引列的值）分布情况，并会记录分布情况。
不执行的话，mysql可能不会使用index for skip scan。应该执行ANALYZE TABLE，就记录了数据分布情况。

结果：
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 = 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   16 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

explain返回的Extra中就有Using index for skip scan。

这种优化一般是适用于左边字段唯一性较差的情况，例如性别之类的值，否则优化器则不会使用Index Skip Scan来进行优化。

跳跃范围扫描使用也是有一些限制：

  • 表上至少存在一个联合索引([A_1,A_2...A_k],B_1,B_2...B_m,C,[,D_1,...,D_n])，其中A部分以及D部分可以为空，但是B和C部分不能为空。A_1,A_2..等代表字段值
  • 只针对单表查询
  • 查询中不包含GROUP BY或者DISTINCT
  • SELECT查询的字段全部被包含在索引组成部分，即符合覆盖索引规范

 倒序索引

--添加倒序索引(表t2之前没有建立倒序索引)
mysql> alter table t2 add index idx_age_desc(age desc);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int NOT NULL,
  `age` int DEFAULT NULL,
  KEY `idx_age` (`age`),
  KEY `idx_age_desc` (`age` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 注意：只有InnoDB存储引擎支持降序索引

隐藏索引

索引的维护是需要较大的成本的，数据量越大，建立索引花费的成本也就越大。但是，往往我们在调优的时候有这样的需求，我们想要看看禁用掉这个索引对查询性能的影响。

在8.0版本以前，都是要删除这个索引，之后发现这个索引有用，又要加回来，极大的增加了操作成本。所以隐藏索引最明显的一个作用类似索引回收站。

--创建（添加）索引的时候设置索引的隐藏属性
ALTER TABLE 表名 ADD INDEX 索引名(列名) INVISIBLE;
create index 索引名 on 表名(字段1,字段2....) INVISIBLE;

--对已经存在的索引切换隐藏或者显示
ALTER TABLE 表名 ALTER INDEX 索引名 INVISIBLE;
ALTER TABLE 表名 ALTER INDEX 索引名 VISIBLE; 

例子：
mysql> show index from t2;
Empty set (0.00 sec)

mysql> create index idx_age on t2(age) INVISIBLE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

--结果查看Visible字段
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t2    |          1 | idx_age  |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | NO      | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

mysql> alter table t2 alter index idx_age VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t2    |          1 | idx_age  |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

确实可以
关注
  • 8
    点赞
  • 踩
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql索引失效的常见原因&如何用好索引
H_Jason_的博客
10-11 1369
本篇文章主要介绍了索引失效的常见原因和如何用好索引，并以案例的形式进行了说明
一文搞懂为什么Mysql索引会失效
通往IT大道的专栏
07-17 160
当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效；当我们在查询条件中对索引列使用函数，就会导致索引失效。当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
MySQL（8.0.19）索引失效的分析
Myd
03-22 987
因为利用没有索引的字段做搜索条件会搜索整个表，而有索引的字段只需要根据B+树做搜索搜索次数是成指数减少的。因为还要拿着查询到的主键id到主键索引树再查询一次，如果没有筛选掉大部分的数据，拿着90%的id主键到主键的B+树上查询，相当于是来了2次全表查询，效率会相当低。因为给age加了索引，因此搜索首先会搜索age的索引，也就是age值组成的B+树；当搜索找到了age=7的时候只能够拿到主键id的值，而我们需要的是整个数据行的值，因此还需要用id的值去搜索主键的索引（B+树）也就是所谓的返表。
mysql索引失效的常见9种原因详解
热门推荐
qq_63815371的博客
04-22 2万+
目录 前言： 1.最佳左前缀法则 2.主键插入顺序 3.计算、函数、类型转换(自动或手动)导致索引失效 4.范围条件右边的列索引失效 5.不等于(!= 或者<>)导致索引失效 6.is null可以使用索引，is not null无法使用索引 7.like以通配符%开头索引失效 8.OR 前后只要存在非索引的列，都会导致索引失效 9.数据库和表的字符集统一使用utf8mb4 特别鸣谢： 前言： MySQL中提高性能的一个最有效的方式是对数据表设计合...
MySQL索引失效的原因及应对策略
沐雨风栉
06-30 1815
索引是提高MySQL查询性能的重要工具，但在某些情况下，索引可能会失效。了解索引失效的原因并找到对应的优化策略，能够帮助我们在实际应用中更好地使用索引，从而提高数据库的性能。同时，我们也需要注意，过多的索引会对数据库的写操作带来影响，因此，要在提高查询效率和保持写操作性能之间找到一个平衡。
索引失效-浅谈MySQL索引失效（组合索引）.pptx
05-25
自己整理有关MySQL的索引失效相关的信息，重点在组合索引的失效，有兴趣可以看看，看看是否对你有些许帮助
mysql索引失效的五种情况分析
12-15
索引并不是时时都会生效的，比如以下几种情况，将导致索引失效： 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 　 　注意：要想使用or，又想让索引生效，只能将or条件中的每个...
MySQL中索引失效的常见场景与规避方法
12-15
前言 之前有看过许多类似的文章内容，...MySQL索引失效的常见场景 在验证下面的场景时，请准备足够多的数据量，因为数据量少时，MySQL的优化器有时会判定全表扫描无伤大雅，就不会命中索引了。 1. where语句中包含or
MySQL索引失效场景以及解决方案
程序小白进阶之路
07-21 5126
在对SQL语句进行索引查询时会遇到索引失效的时候，对于该语句的可行性以及性能效率方面有至关重要的影响，本篇剖析索引为何失效，有哪些情况会导致索引失效以及对于索引失效时的优化解决方案，其中着重介绍最左前缀匹配原则、MySQL逻辑架构和优化器、索引失效场景以及为何会失效......
详解MySQL索引失效的几种情况
m0_57614677的博客
02-28 1万+
MySQL索引失效的几种情况
Mysql索引失效的几种情况总结
情深深几许
07-20 4324
总结了Mysql索引失效的几种情况，对于新手好上理解，对于老手相当于总结。
索引会在什么情况下失效？（InnoDB，mysql8.0）
不服输的南瓜的博客
07-21 1279
条件中有or 如果条件中有or，其中一个字段是索引字段，另一个是非索引字段，此时索引不会被使用，全表扫描。 如果条件中有or，左右是两个不同的独立索引字段，此时两个索引字段都会用。 但其实完全可以用union来代替： select * from xd_student where id = 2 union select * from xd_studen...
Mysql索引失效的几种情况（面试必问）
weixin_50838394的博客
07-27 5635
Mysql索引失效的几种情况（面试必问）全网最全
mysql索引失效情况
path blog
05-10 2万+
1、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。前提条件：表中已添加复合索引（username,password,age）分析：该查询缺少username，查询条件复合索引最左侧username缺少，违反了最佳左前缀原则，导致索引失效，变为ALL，全表扫描分析：查询条件缺少username，password，查询条件复合索引最左侧us...
MYSQL 索引类型、什么情况下用不上索引、什么情况下不推荐使用索引
软件猿扫地僧
11-29 2万+
 mysql explain的使用：http://blog.csdn.net/kaka1121/article/details/53394426 索引类型在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表代码如下:CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ...
MySQL索引失效原因
weixin_46429290的博客
10-14 919
在MySQL中，建立了索引之后，有时候也会出现查询类型是all的情况，造成这种问题的原因是索引没有使用上，造成了索引失效。 索引失效的原因： 全值匹配：在查询语句中使用到了索引中所有的字段 最佳左侧前缀法则：在索引的使用中从左到右不能跳着使用，不能跳过索引中的列 不在索引列上做任何操作（计算、函数、自动\手动类型转换） 存储引擎不能使用索引中范围条件右边的列，既是范围之后的列全部会失效 尽量使用覆盖索引（只访问索引的查询），减少select* mysql在使用不等于的时候(!=或<>)无法使用
Mysql有效索引和无效索引的介绍
weixin_34185512的博客
01-22 372
为什么80%的码农都做不了架构师？>>> ...
mysql 索引失效
最新发布
08-24
MySQL索引失效是指在查询过程中，尽管建立了索引，但查询计划却没有使用索引，导致查询性能下降的情况。根据引用中提到的常见原因，可以导致MySQL索引失效的原因有以下几点： 1. 索引未被充分利用：有时候虽然使用了索引，但并未完全利用到索引的所有列。例如，在一个联合索引中，只使用了部分列进行筛选，而未使用到其他列。 2. 不等于操作符导致索引失效：使用不等于操作符（!=或<>）会导致索引失效，因为MySQL无法高效地利用索引来处理不等于的查询。 3. is null和is not null的差异：使用is null条件可以利用索引进行查询优化，而is not null无法使用索引进行优化。 4. like以通配符%开头：当使用like操作符以通配符%开头时，索引会失效。因为通配符%开头的模糊匹配无法使用B-tree索引。 5. OR语句中存在非索引列：OR语句中，只要存在非索引列，就会导致索引失效。MySQL无法同时使用多个索引来处理这种情况。 综上所述，为避免MySQL索引失效，我们需要注意以下几点：优化索引设计，充分利用索引的所有列；避免使用不等于操作符；注意使用is null和is not null的差异；避免在like操作符中以通配符%开头；尽量避免使用OR语句中存在非索引列的查询。这样可以提高查询性能并避免索引失效。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [mysql索引失效的常见9种原因详解](https://blog.csdn.net/qq_63815371/article/details/124337932)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* *3* [【第三篇】MySQL 索引失效的常见原因【重点】](https://blog.csdn.net/weixin_42039228/article/details/123255722)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值