Skip to main content

One post tagged with "SQL优化"

View All Tags

· 10 min read

大家好,我是知数堂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)