大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子
我今天刷头条的时候 看到了一位叫《老猿说开发》总结的索引不能使用口诀 模型数空运最快
我觉得挺好，但是因为没有具体的案例，还有没说是哪个数据库这些需要完善的地方，所以写一篇文章。
模：模糊查询%在前面情况不能使用索引，这种情况非得说一个特例就是不回表情况
[email protected]>[employees]>desc select dept_no from t_group2 where dept_no like '%005';
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t_group2 | NULL | index | NULL | ix_dept_no2 | 16 | NULL | 10 | 11.11 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
型：数据类型有变化的时候不能使用
[email protected]>[employees]>desc test1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | MUL | NULL | |
| n | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
[email protected]>[employees]>select * from test1 ;
+------+------+
| id | n |
+------+------+
| 1000 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
[email protected]>[employees]>show index from test1 ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1 | 1 | ix_id | 1 | id | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
[email protected]>[employees]>desc select * from test1 where id=1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | ALL | ix_id | NULL | NULL | NULL | 2 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
[email protected]>[employees]>desc select * from test1 where id='1000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test1 | NULL | ref | ix_id | ix_id | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
数：索引列套用函数不能使用索引，这时候mysql 5.7可以用虚拟列，8.0可以直接使用函数索引，5.6的时候可以利用类似解方程方法解开
[email protected]>[employees]>desc select * from test1 where id='1000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test1 | NULL | ref | ix_id | ix_id | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
[email protected]>[employees]>desc select * from test1 where concat(id,'')='1000';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
[email protected]>[employees]>create index idx_t1_ke1 on test1((concat(id,'')));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
[email protected]>[employees]>desc select * from test1 where concat(id,'')='1000';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test1 | NULL | ref | idx_t1_ke1 | idx_t1_ke1 | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
空：索引列有空值使用is null 就不能使用索引，这个在oracle 是没问题的 但是在mysql 这个是错的
[email protected]>[employees]>desc test1 ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(10) | YES | MUL | NULL | |
| n | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
[email protected]>[employees]>select * from test1 ;
+------+------+
| id | n |
+------+------+
| 1000 | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
[email protected]>[employees]>show index from test1 ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| test1 | 1 | ix_id | 1 | id | A | 2 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
[email protected]>[employees]>desc select * from test1 where id is null;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test1 | NULL | ref | ix_id | ix_id | 43 | const | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
[email protected]>[employees]>desc select * from test1 where id is not null;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test1 | NULL | range | ix_id | ix_id | 43 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
运：索引列有数字运算的时候不能使用索引
[email protected]>[employees]>desc select * from test1 where id='1000';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|1| SIMPLE | test1 | NULL | ref | ix_id | ix_id | 43 | const | 1 |100.00| NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
[email protected]>[employees]>desc select * from test1 where id+0='1000';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1| SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 |100.00| Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
最：复合索引，最左边原则，这个也需要分情况分版本，因为有skip index的存在
[email protected]>[employees]>show index from dept_emp2 ;
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept_emp2 | 1 | ix_dept_emp | 1 | dept_no | A | 8 | NULL | NULL | | BTREE | | | YES | NULL |
| dept_emp2 | 1 | ix_dept_emp | 2 | emp_no | A | 315555 | NULL | NULL | | BTREE | | | YES | NULL |
+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
[email protected]>[employees]>desc select * from dept_emp2 where emp_no=10001 limit 10 ;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | dept_emp2 | NULL | ALL | NULL | NULL | NULL | NULL | 331008 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
[email protected]>[employees]>desc select emp_no,dept_no from dept_emp2 where emp_no=10001 limit 10 ;
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------+
| 1 | SIMPLE | dept_emp2 | NULL | range | ix_dept_emp | ix_dept_emp | 20 | NULL | 33100 | 100.00 | Using where; Using index for skip scan |
+----+-------------+-----------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
快：如果优化器认为使用全表比使用索引快，那就不会使用索引，说白了就是基于优化器或者统计信息，这个就是所谓的百分比，这里最重要的一点是使用索引一般情况下是回表，也就是发生随机IO,这个随着回表的量的大小变大，一般OLTP都是在表下查询少量数据情况，所以还是使用索引的情况会好
当然上面的情况之外，还有别的情况，如日期类型，还有字符串比较的时候等等，这些在课堂中有详细的解答。 我是知数堂SQL 优化班老师~ ^^
最新一期SQL优化课，在12月份开始。
如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化
高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588
欢迎加入 知数堂大家庭。
我的微信公众号：SQL开发与优化(sqlturning)
