sql优化-2-索引失效
避免索引失效口诀：模型数空运最快 【来自 抖音：老猿说开发】
第1个字是模 就是代表模糊查询的意思，使用like关键字的时候要是%开头那索引就会失效
第2个字是型 代表数据类型 数据类型错误了，索引也会失效
第3个字是数 对索引字段使用内部函数，索引也会失效 这种情况呢应该建立基于函数的索引
第4个字是空 索引不允许空值时，使用IS NULL或IS NOT NULL会导致索引失效
第5个字是运 对索引列进行加减乘除等运算会导致索引失效
这6个字是最 最左原则的意思，在复合索引中索引列的顺序啊非常重要，如果不是按照索引列最左列开始进行查找则无法使用索引
第7个字是快 全表扫描更快的时候，数据库预计使用全表扫描比使用索引更快那它就不会使用索引
(1) 模糊查询尽量以'常量'开头,不要以'%'头,否则索引失效
-- 如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖挽救一部分
mysql> explain select * from user where name like '%z%'; --name索引失效
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from user where name like 'z%';
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | user | range | index_name | index_name | 10 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select name from user where name like '%z%';
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| 1 | SIMPLE | user | index | NULL | index_name | 10 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
(2) 数据类型不对,导致发生类型转换(显示、隐式),否则索引失效
mysql> explain select * from user where name = 'abc';
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | index_name | index_name | 10 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from user where name = 123;
-- //程序底层将 123 ->'123'，即进行了类型转换，因此索引失效
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | index_name | NULL | NULL | NULL | 37 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
(3) 索引不允许空值时，使用IS NULL或IS NOT NULL会导致索引失效
在创建索引时，如果索引定义为不允许NULL值（即NOT NULL约束），那么在索引中就不会包含任何NULL值。这意味着，如果查询条件是基于索引列的IS NULL或IS NOT NULL，索引可能不会被有效地使用，因为索引中不存在这些条件对应的记录。
- 主键索引，不允许为null，使用 is not null 判断会导致索引失效
CREATE TABLE `test01` (
`tid` int(3) NOT NULL DEFAULT '0',
`tname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> explain select * from test01 where tid = 1;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | test01 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.06 sec)
mysql> explain select * from test01 where tid is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.07 sec)
mysql>
- 唯一索引，可以为null，使用 is not null 判断不会导致索引失效
CREATE TABLE `test01` (
`tid` int(3) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL,
UNIQUE KEY `uni_tid` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> explain select * from test01 where tid = 1;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | test01 | const | uni_tid | uni_tid | 5 | const | 1 | NULL |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.06 sec)
mysql> explain select * from test01 where tid is null;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | test01 | ref | uni_tid | uni_tid | 5 | const | 1 | Using index condition |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.06 sec)
mysql>
(4) 不要在索引上进行任何操作(内部函数、加减乘除计算),否则索引失效
mysql> explain select * from user where id*10 = 10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
mysql> explain select * from user where id = 10;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | user | ref | index_id_name_age | index_id_name_age | 5 | const | 1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)
(5) 复合索引未使用最左原则
a.复合索引，不要跨列或无序使用(最佳左前缀)
b.复合索引，尽量使用全索引匹配
(6) 全表扫描比使用索引更快
数据量小的情况下，全局扫描比使用索引更快
(7) 使用一些运算符导致索引失效（不是100%失效）
复合索引不能使用不等于(!= <>)或 is null (is not null),否则自身以及右侧所有全部失效
单值索引使用
mysql> explain select * from user where id = 10 and name= 'tz';
+----+-------------+-------+------+---------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | index_id,index_name | index_id | 5 | const | 1 | Using where |
+----+-------------+-------+------+---------------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from user where id != 10 and name= 'tz'; -- index_id 索引失效
+----+-------------+-------+------+---------------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | index_id,index_name | index_name | 10 | const | 1 | Using where |
+----+-------------+-------+------+---------------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from user where id is null;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | index_id | index_id | 5 | const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
复合索引
mysql> explain select * from user where id != 10 and age =20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | index_id_age | NULL | NULL | NULL | 37 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from user where id is null and age = 20;
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| 1 | SIMPLE | user | ref | index_id_age | index_id_age | 10 | const,const | 1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from user where id is not null and age = 20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | index_id_age | NULL | NULL | NULL | 37 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
一般而言,范围査询(> < in),之后的索引失效
SQL优化,是一种概率层面的优化。至于是否实际使用了我们的优化,需要通过explain进行推测。
我们学习索引优化,是一个大部分情况适用的结论,但由于SQL优化器等原因该结论不是100%正确。
体验概率情况(< > =):原因是服务层中有SQL优化器,可能会影响我们的优化。
mysql> explain select * from user where id = 1 and age =2 ; --复合索引 全部使用
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| 1 | SIMPLE | user | ref | index_id_age | index_id_age | 10 | const,const | 1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
复合索引中如果有>,则自身及右侧全部失效（5.5版本），但实际上 key_len为5，也就是使用了一个索引，但在插入37行数据之后，索引全部失效了
mysql> explain select * from user where id > 1 and age = 2 ; --复合索引 只用到了1个索引
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | user | range | index_id_age | index_id_age | 5 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)
-- 插入37行数据后
mysql> explain select * from user where id > 1 and age = 2 ; --复合索引全部失效
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | index_id_age | NULL | NULL | NULL | 37 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
理论上 age失效，key_len 为5，但实际这里为10，也就是说索引全部使用了
mysql> explain select * from user where id = 1 and age > 2 ;-- 复合索引 全部使用
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| 1 | SIMPLE | user | range | index_id_age | index_id_age | 10 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)
尽量不要使用or,否则索引失效
1.or前后没有同时使用索引
参考别再问我MySQL为啥没走索引？就这几种原因，全都告诉你
mysql> explain select * from user where id=10 or age=18;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | index_id | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
2.单值索引使用or 将or左侧的id失效和后面的age全部失效
mysql> explain select * from user where id = 100 or age = 1000;
+----+-------------+-------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | idx_id,idx_age | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+----------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
3.复合索引使用or 将or左侧的id失效和后面的age全部失效（但不一定）
mysql> explain select * from user2 where id = 100 or name = 'tz'; -- 索引全部失效
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user2 | ALL | index_id_name | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
-- 但是索引加到三个的时候
mysql> explain select * from user2 where id = 100 or name = 'tz'; -- 索引全部生效
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | user2 | index | index_id_name_age | index_id_name_age | 17 | NULL | 1 | Using where; Using index |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from user2 where id = 100 or name = 'tz' or age =100 ;-- 索引全部生效
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+--------------------------+
| 1 | SIMPLE | user2 | index | index_id_name_age | index_id_name_age | 17 | NULL | 37 | Using where; Using index |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
-- 索引全部失效
mysql> explain select * from user3 where id = 100 or age = 1000 or age2 = 122;
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user3 | ALL | index_id_age_age2 | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
