MySQL

MySQL 05 - 索引优化

简介:索引优化

1. 单表优化

将salaries的联合主键(emp_no, from_date)清除后,salaries表的状态如下:

  • mysql> desc salaries;
  • +-----------+---------+------+-----+---------+-------+
  • | Field | Type | Null | Key | Default | Extra |
  • +-----------+---------+------+-----+---------+-------+
  • | emp_no | int(11) | NO | | NULL | |
  • | salary | int(11) | NO | | NULL | |
  • | from_date | date | NO | | NULL | |
  • | to_date | date | NO | | NULL | |
  • +-----------+---------+------+-----+---------+-------+
  • 4 rows in set (0.00 sec)

然后进行下面的查询Explain分析:

  • mysql> explain select emp_no from salaries where emp_no = 10005 and from_date > '1988-01-01' order by salary desc limit 5;
  • +----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
  • | 1 | SIMPLE | salaries | NULL | ALL | NULL | NULL | NULL | NULL | 2733572 | 3.33 | Using where; Using filesort |
  • +----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
  • 1 row in set, 1 warning (0.00 sec)

会发现此时type字段为ALL,同时Extra出现了Using filesort,因此这个语句是需要优化的。下面尝试对查询中涉及的三个字段添加联合索引:

  • mysql> create index idx_emp_no__from_date__salary on salaries(emp_no, from_date, salary);
  • Query OK, 0 rows affected (4.55 sec)
  • Records: 0 Duplicates: 0 Warnings: 0

再次进行查询,会发现确实用到了idx_emp_no__from_date__salary索引,且type字段变为了range,但Extra中还是出现了Using filesort

  • mysql> explain select * from salaries where emp_no = 10005 and from_date > '1988-01-01' order by salary desc limit 5;
  • +----+-------------+----------+------------+-------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
  • |
  • +----+-------------+----------+------------+-------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------+
  • | 1 | SIMPLE | salaries | NULL | range | idx_emp_no__from_date__salary | idx_emp_no__from_date__salary | 7 | NULL | 13 | 100.00 | Using index condition; Using filesort |
  • +----+-------------+----------+------------+-------+-------------------------------+-------------------------------+---------+------+------+----------+---------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

分析原因,这是由于虽然我们添加了联合索引idx_emp_no__from_date__salary(emp_no, from_date, salary),但在查询语句中的from_date字段出现了范围判断,因此只有emp_no字段使用到了索引,排序所涉及的salary并没有用到索引,导致出现Using filesort;我们可以尝试只建立(emp_no, salary)这两个字段的联合索引再进行查询分析:

  • mysql> create index idx_emp_no__salary on salaries(emp_no, salary);
  • Query OK, 0 rows affected (4.01 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> explain select * from salaries where emp_no = 10005 and from_date > '1988-01-01' order by salary desc limit 5;
  • +----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
  • | 1 | SIMPLE | salaries | NULL | ref | idx_emp_no__salary | idx_emp_no__salary | 4 | const | 13 | 33.33 | Using where |
  • +----+-------------+----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

从分析结果可以看出,Extra中已经没有了Using filesort,同时type也变为了ref

2. 双表优化

将employees表和dept_emp表的索引全部清除,然后测试左连接查询分析:

  • mysql> explain select * from employees left join dept_emp on employees.emp_no = dept_emp.emp_no;
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299289 | 100.00 | NULL |
  • | 1 | SIMPLE | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331008 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • 2 rows in set, 1 warning (0.00 sec)

从结果中的type可以得知,这个查询操作进行了两次全表扫描,因此需要添加索引进行优化。测试在employees表的emp_no字段上添加索引后进行查询分析:

  • mysql> create index idx_emp_no on employees(emp_no);
  • Query OK, 0 rows affected (0.69 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> explain select * from employees left join dept_emp on employees.emp_no = dept_emp.emp_no;
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299289 | 100.00 | NULL |
  • | 1 | SIMPLE | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331008 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • 2 rows in set, 1 warning (0.00 sec)

发现此时并没有什么改善。我们删除employees表上的索引idx_emp_no,将索引加在右表dept_emp上再进行查询分析:

  • mysql> drop index idx_emp_no on employees;
  • Query OK, 0 rows affected (0.02 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> create index idx_emp_no on dept_emp(emp_no);
  • Query OK, 0 rows affected (0.79 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> explain select * from employees left join dept_emp on employees.emp_no = dept_emp.emp_no;
  • +----+-------------+-----------+------------+------+---------------+------------+---------+----------------------------+--------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------------+---------+----------------------------+--------+----------+-------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299289 | 100.00 | NULL |
  • | 1 | SIMPLE | dept_emp | NULL | ref | idx_emp_no | idx_emp_no | 5 | employees.employees.emp_no | 1 | 100.00 | NULL |
  • +----+-------------+-----------+------------+------+---------------+------------+---------+----------------------------+--------+----------+-------+
  • 2 rows in set, 1 warning (0.00 sec)

对于两表连接的查询,索引添加有规则:左连接将索引添加在右表上,右连接将索引添加在左表上。这是由左右连接的特性决定的,左连接中,左表数据全部都需要,而右表数据只需要一部分,因此对左表添加索引并没有多大的效果,同理右连接的情况下也一样。

3. 三表优化

将employees表、departments表和dept_emp表的索引全部清除,然后测试三表左连接连接查询分析:

  • mysql> explain select * from departments left join dept_emp on departments.dept_no = dept_emp.dept_no left join employees on dept_emp.emp_no = employees.emp_no;
  • +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • | 1 | SIMPLE | departments | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | NULL |
  • | 1 | SIMPLE | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331008 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299289 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
  • +----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
  • 3 rows in set, 1 warning (0.00 sec)

从分析结果可以得知,三个表的type都是ALL,性能非常差。我们尝试在两张右表dept_emp和employees的相关字段上添加索引:

  • mysql> create index idx_dept_no on dept_emp(dept_no);
  • Query OK, 0 rows affected (0.81 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> create index idx_emp_no on dept_emp(emp_no);
  • Query OK, 0 rows affected (0.66 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> create index idx_emp_no on employees(emp_no);
  • Query OK, 0 rows affected (0.64 sec)
  • Records: 0 Duplicates: 0 Warnings: 0

然后再次进行查询分析可以发现分析结果得到了明显的改善:

  • mysql> explain select * from departments left join dept_emp on departments.dept_no = dept_emp.dept_no left join employees on dept_emp.emp_no = employees.emp_no;
  • +----+-------------+-------------+------------+------+---------------+-------------+---------+-------------------------------+-------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------------+------------+------+---------------+-------------+---------+-------------------------------+-------+----------+-------+
  • | 1 | SIMPLE | departments | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 100.00 | NULL |
  • | 1 | SIMPLE | dept_emp | NULL | ref | idx_dept_no | idx_dept_no | 5 | employees.departments.dept_no | 41376 | 100.00 | NULL |
  • | 1 | SIMPLE | employees | NULL | ref | idx_emp_no | idx_emp_no | 4 | employees.dept_emp.emp_no | 1 | 100.00 | NULL |
  • +----+-------------+-------------+------------+------+---------------+-------------+---------+-------------------------------+-------+----------+-------+
  • 3 rows in set, 1 warning (0.00 sec)

对于join语句的优化,有以下的结论:

  1. 尽可能减少join语句中的NestedLoop的循环总次数;永远用小的结果集驱动大的结果集。
  2. 有限优化NestedLoop的内层循环。
  3. 保证join语句中被驱动表上join条件字段已经被索引。
  4. 当无法保证被驱动表的join条件字段被索引且内存资源充足的情况下,不要太吝啬JoinBuffer的设置。

4. 最佳左前缀法则

我们通过下面的案例来认识最佳左前缀法则。在employees表上只建立了一个联合索引idx_emp_no__first_name__gender,由emp_no、first_name和gender三个字段组成:

  • mysql> show index from employees;
  • +-----------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  • +-----------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • | employees | 1 | idx_emp_no__first_name__gender | 1 | emp_no | A | 299289 | NULL | NULL | | BTREE | | |
  • | employees | 1 | idx_emp_no__first_name__gender | 2 | first_name | A | 299289 | NULL | NULL | | BTREE | | |
  • | employees | 1 | idx_emp_no__first_name__gender | 3 | gender | A | 299289 | NULL | NULL | | BTREE | | |
  • +-----------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • 3 rows in set (0.00 sec)

我们分析以下的查询:

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

可以发现,此时where条件只有emp_no字段,查询中是用到了索引idx_emp_no__first_name__gender的。继续分析下面的查询:

  • mysql> explain select * from employees where emp_no = 13562 and first_name = 'Mary';
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------+------+----------+-------+
  • | 1 | SIMPLE | employees | NULL | ref | idx_emp_no__first_name__gender | idx_emp_no__first_name__gender | 20 | const,const | 1 | 100.00 | NULL |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)

同样的,where条件中有emp_no和first_name字段,也用到了索引idx_emp_no__first_name__gender的。下面测试使用三个字段的where条件:

  • mysql> explain select * from employees where emp_no = 13562 and first_name = 'Mary' and gender = 'M';
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------+
  • | 1 | SIMPLE | employees | NULL | ref | idx_emp_no__first_name__gender | idx_emp_no__first_name__gender | 21 | const,const,const | 1 | 100.00 | NULL |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)

可以发现也查询用到了idx_emp_no__first_name__gender索引。

从上面的三个查询分析中的key_len字段可知,三次查询所使用的索引个数依次增多。其中,emp_no字段是int类型的,索引为4字节长,first_name字段是长度为14的varchar类型,可为空,因此索引为16字节长,gender字段是enum类型的,索引为1个字节长度。

我们尝试改变where条件,只使用emp_no和gender字段的where条件,然后进行查询分析:

  • mysql> explain select * from employees where emp_no = 13562 and gender = 'M';
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
  • | 1 | SIMPLE | employees | NULL | ref | idx_emp_no__first_name__gender | idx_emp_no__first_name__gender | 4 | const | 1 | 50.00 | Using index condition |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)

从查询分析的key_len的值可知只用到了emp_no字段的索引。

而当只使用first_name和gender字段为where条件时,会发现索引失效:

  • mysql> explain select * from employees where first_name = 'Mary' and gender = 'M';
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299289 | 5.00 | Using where |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

在最佳左前缀法则中,使用联合索引时,MySQL会一直向右匹配直到遇到范围查询(><betweenlike)的字段就停止匹配,比如a = 1 and b = 2 and 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的查询优化器会优化成索引可以识别的形式。

5. 避免索引操作法则

避免索引操作法则的意思是,避免在索引列上添加计算、函数、类型转换(无论自动或者手动)等操作,这会导致索引失效。

在上面的例子中,employees上建立了联合索引idx_emp_no__first_name__gender,由emp_no、first_name和gender三个字段组成,如果我们直接使用单个索引字段进行查询,是一定能够使用到索引的:

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

但如果我们在索引列上做了一些操作将会导致索引失效,如下面查询对emp_no做了left函数操作,会导致索引失效:

  • mysql> explain select * from employees where left(emp_no, 3) = 135;
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299289 | 100.00 | Using where |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

6. 范围条件后的索引将失效

在我们进行条件查询时,如果碰到范围条件,如><betweenlike,那么范围条件之后的条件字段的索引会直接失效。依旧以employees表为例,它有emp_no、first_name和gender三个字段的联合索引,分析下面的查询:

  • mysql> explain select * from employees where emp_no = 13562 and first_name like '%Mary%' and gender = 'M';
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
  • | 1 | SIMPLE | employees | NULL | ref | idx_emp_no__first_name__gender | idx_emp_no__first_name__gender | 4 | const | 1 | 5.56 | Using index condition |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)

会发现上面的查询只用到了key_len为4的索引,只用到了emp_no字段的索引。

注:><可能会用到索引用于排序。

7. 尽量使用覆盖索引

当我们在编写SQL语句时,应该尽量减少查询的列,只查询我们需要的列,避免select *式的全列查询。同时,当我们查询的列是索引覆盖的列时,将会提高查询效率。可以对比下面的查询:

  • mysql> explain select * from employees where emp_no = 13562 and first_name = 'Mary' and gender = 'M';
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------+
  • | 1 | SIMPLE | employees | NULL | ref | idx_emp_no__first_name__gender | idx_emp_no__first_name__gender | 21 | const,const,const | 1 | 100.00 | NULL |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select emp_no, first_name, gender from employees where emp_no = 13562 and first_name = 'Mary' and gender = 'M';
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ref | idx_emp_no__first_name__gender | idx_emp_no__first_name__gender | 21 | const,const,const | 1 | 100.00 | Using index |
  • +----+-------------+-----------+------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

会发现使用select emp_no, first_name, gender时比select *在Extra列多了Using index,这是效率高的体现。

8. 避免使用!=或<>条件

!=<>条件是会让索引失效的:

  • mysql> explain select * from employees where emp_no != 10010;
  • +----+-------------+-----------+------------+------+--------------------------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | idx_emp_no__first_name__gender | NULL | NULL | NULL | 299289 | 50.00 | Using where |
  • +----+-------------+-----------+------------+------+--------------------------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

9. 避免使用is null或is not null条件

is nullis not null条件也会让索引失效:

  • mysql> explain select * from employees where emp_no is null;
  • +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
  • | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
  • +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select * from employees where emp_no is not null;
  • +----+-------------+-----------+------------+------+--------------------------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+--------------------------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | idx_emp_no__first_name__gender | NULL | NULL | NULL | 299289 | 90.00 | Using where |
  • +----+-------------+-----------+------------+------+--------------------------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

10. like查询使用覆盖索引

对于like条件,如果一个字段有索引,那么%...%%...的模式匹配会造成索引失效,但...%的模式匹配不会:

  • mysql> create index idx_first_name on employees(first_name);
  • Query OK, 0 rows affected (0.90 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> explain select * from employees where first_name like '%Ma%';
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299242 | 11.11 | Using where |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select * from employees where first_name like '%Ma';
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299242 | 11.11 | Using where |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select * from employees where first_name like 'Ma%';
  • +----+-------------+-----------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
  • | 1 | SIMPLE | employees | NULL | range | idx_first_name | idx_first_name | 16 | NULL | 36192 | 100.00 | Using index condition |
  • +----+-------------+-----------+------------+-------+----------------+----------------+---------+------+-------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)

从上述的查询分析中可以清晰地看出%...的模式匹配使用到了索引。

但在日常开发中,%...%的模式匹配是用的最多的,如果想要使用此类模式匹配且避免索引失效,可以尝试使用索引覆盖来避免,如:

  • mysql> create index idx_first_name on employees(first_name);
  • Query OK, 0 rows affected (0.67 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> explain select first_name from employees where first_name like '%Ma%';
  • +----+-------------+-----------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+
  • | 1 | SIMPLE | employees | NULL | index | NULL | idx_first_name | 16 | NULL | 299242 | 11.11 | Using where; Using index |
  • +----+-------------+-----------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)

或者对于联合索引的索引覆盖:

  • -- 创建索引
  • mysql> create index idx_emp_no__first_name on employees(emp_no, first_name);
  • Query OK, 0 rows affected (0.78 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • -- 联合索引的两个字段
  • mysql> explain select emp_no, first_name from employees where first_name like '%Ma%';
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • | 1 | SIMPLE | employees | NULL | index | NULL | idx_emp_no__first_name | 20 | NULL | 299242 | 11.11 | Using where; Using index |
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)
  • -- 联合索引的两个字段
  • mysql> explain select first_name, emp_no from employees where first_name like '%Ma%';
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • | 1 | SIMPLE | employees | NULL | index | NULL | idx_emp_no__first_name | 20 | NULL | 299242 | 11.11 | Using where; Using index |
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)
  • -- 联合索引的一个字段
  • mysql> explain select emp_no from employees where first_name like '%Ma%';
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • | 1 | SIMPLE | employees | NULL | index | NULL | idx_emp_no__first_name | 20 | NULL | 299242 | 11.11 | Using where; Using index |
  • +----+-------------+-----------+------------+-------+---------------+------------------------+---------+------+--------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)

但是一旦出现索引无法覆盖查询的字段,就会出现索引失效:

  • -- gender字段不处于联合索引中,没有被索引覆盖
  • mysql> explain select emp_no, first_name, gender from employees where first_name like '%Ma%';
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299242 | 11.11 | Using where |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)
  • -- select *中有多个字段没有被索引覆盖
  • mysql> explain select * from employees where first_name like '%Ma%';
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299242 | 11.11 | Using where |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

11. 字符串匹配一定要加单引号

当我们进行where过滤的某个字符串字段建立了索引之后,在查询时一定要在where条件中加单引号,否则会有隐式的类型转换,导致索引失效;而反过来不会导致索引失效。

  • -- departments表的dept_no字段上是有索引的
  • mysql> show index from departments;
  • +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  • +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • | departments | 1 | idx_dept_no | 1 | dept_no | A | 9 | NULL | NULL | | BTREE | | |
  • +-------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  • 1 row in set (0.00 sec)
  • -- 并且添加了一行dept_no为2000(字符串)的数据
  • mysql> select * from departments where dept_no = 2000;
  • +---------+-------------+
  • | dept_no | dept_name |
  • +---------+-------------+
  • | 2000 | Development |
  • +---------+-------------+
  • 1 row in set, 8 warnings (0.00 sec)
  • -- 使用数字2000进行匹配会发现索引失效
  • mysql> explain select * from departments where dept_no = 2000;
  • +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • | 1 | SIMPLE | departments | NULL | ALL | idx_dept_no | NULL | NULL | NULL | 9 | 11.11 | Using where |
  • +----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
  • 1 row in set, 3 warnings (0.00 sec)
  • -- 而使用字符串'2000'匹配索引是生效的
  • mysql> explain select * from departments where dept_no = '2000';
  • +----+-------------+-------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
  • | 1 | SIMPLE | departments | NULL | ref | idx_dept_no | idx_dept_no | 4 | const | 1 | 100.00 | NULL |
  • +----+-------------+-------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
  • 1 row in set, 1 warning (0.00 sec)

12. or条件也会使索引失效

当我们使用or对某个建立了索引的字段进行where条件判断时,会导致联合索引失效:

  • mysql> create index idx_emp_no__first_name on employees(emp_no, first_name);
  • Query OK, 0 rows affected (0.75 sec)
  • Records: 0 Duplicates: 0 Warnings: 0
  • mysql> explain select * from employees where emp_no = 10010 or emp_no = 10020;
  • +----+-------------+-----------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
  • | 1 | SIMPLE | employees | NULL | range | idx_emp_no__first_name | idx_emp_no__first_name | 4 | NULL | 2 | 100.00 | Using index condition |
  • +----+-------------+-----------+------------+-------+------------------------+------------------------+---------+------+------+----------+-----------------------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select * from employees where first_name = 'Mary' or first_name = 'Mark';
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299242 | 19.00 | Using where |
  • +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  • 1 row in set, 1 warning (0.00 sec)

13. exists和in的优化

优化准则:小表驱动大表,即小的数据集驱动大的数据集。

对于A表和B表,假设两表的id字段是是关联外键,那么:

select * from A where id in (select id from B)等价于:

  • for select id from B
  • for select * from A ``where` `A.id = B.id

select * from A where exists (select 1 from B where B.id = A.id)等价于:

  • for select * from A
  • for select * from B where B.id = A.id

相当于将A中的数据查询出来后放在子查询select 1 from B where B.id = A.id中验证从而决定是否保留查询的结果。

有结论如下:

  • 当B表的数据集小于A表的数据集时,用in优于exists
  • 当A表的数据集小于B表的数据集时,用exists优于in

not innot exists用法类似。

14. order by相关优化

我们使用salary表来进行测试,该表结构如下:

  • mysql> desc salaries;
  • +-----------+---------+------+-----+---------+-------+
  • | Field | Type | Null | Key | Default | Extra |
  • +-----------+---------+------+-----+---------+-------+
  • | emp_no | int(11) | NO | | NULL | |
  • | salary | int(11) | NO | | NULL | |
  • | from_date | date | NO | | NULL | |
  • | to_date | date | NO | | NULL | |
  • +-----------+---------+------+-----+---------+-------+
  • 4 rows in set (0.00 sec)

我们给(emp_no, salary)建立联合索引:

  • mysql> create index idx_emp_no__salary on salaries(emp_no, salary);
  • Query OK, 0 rows affected (3.82 sec)
  • Records: 0 Duplicates: 0 Warnings: 0

然后进行查询分析:

  • mysql> explain select emp_no, salary from salaries where emp_no > 10010 order by emp_no;
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
  • | 1 | SIMPLE | salaries | NULL | range | idx_emp_no__salary | idx_emp_no__salary | 4 | NULL | 1366786 | 100.00 | Using where; Using index |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select emp_no, salary from salaries where emp_no > 10010 order by emp_no, salary;
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
  • | 1 | SIMPLE | salaries | NULL | range | idx_emp_no__salary | idx_emp_no__salary | 4 | NULL | 1366786 | 100.00 | Using where; Using index |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)

以上两种情况都使用到了emp_no字段的索引,同时也使用到了索引进行排序。

  • mysql> explain select emp_no, salary from salaries where emp_no > 10010 order by salary;
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • | 1 | SIMPLE | salaries | NULL | range | idx_emp_no__salary | idx_emp_no__salary | 4 | NULL | 1366786 | 100.00 | Using where; Using index; Using filesort |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)
  • mysql> explain select emp_no, salary from salaries where emp_no > 10010 order by salary, emp_no;
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • | 1 | SIMPLE | salaries | NULL | range | idx_emp_no__salary | idx_emp_no__salary | 4 | NULL | 1366786 | 100.00 | Using where; Using index; Using filesort |
  • +----+-------------+----------+------------+-------+--------------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

以上两种情况虽然使用了emp_no字段的索引做条件判断的排序支持,但由于order by使用的字段的顺序出现了改变,因此出现了Using filesort。

  • mysql> explain select emp_no, salary from salaries order by salary;
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+-----------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+-----------------------------+
  • | 1 | SIMPLE | salaries | NULL | index | NULL | idx_emp_no__salary | 8 | NULL | 2733572 | 100.00 | Using index; Using filesort |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+-----------------------------+
  • 1 row in set, 1 warning (0.00 sec)

以上情况salary的索引由于断层无法使用,会导致order by的Using filesort排序操作。

  • mysql> explain select emp_no, salary from salaries where salary > 10000 order by salary;
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • | 1 | SIMPLE | salaries | NULL | index | NULL | idx_emp_no__salary | 8 | NULL | 2733572 | 33.33 | Using where; Using index; Using filesort |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+------------------------------------------+
  • 1 row in set, 1 warning (0.00 sec)

以上情况salary的索引由于断层无法使用,会导致order by的Using filesort排序操作。

  • mysql> explain select emp_no, salary from salaries where salary > 10000 order by emp_no;
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+--------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+--------------------------+
  • | 1 | SIMPLE | salaries | NULL | index | NULL | idx_emp_no__salary | 8 | NULL | 2733572 | 33.33 | Using where; Using index |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+--------------------------+
  • 1 row in set, 1 warning (0.00 sec)

以上情况emp_no的索引会用于order by的排序操作。

  • mysql> explain select emp_no, salary from salaries order by emp_no asc, salary desc;
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+-----------------------------+
  • | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+-----------------------------+
  • | 1 | SIMPLE | salaries | NULL | index | NULL | idx_emp_no__salary | 8 | NULL | 2733572 | 100.00 | Using index; Using filesort |
  • +----+-------------+----------+------------+-------+---------------+--------------------+---------+------+---------+----------+-----------------------------+
  • 1 row in set, 1 warning (0.00 sec)

以上情况order by默认是升序,因此order by emp_no asc, salary desc会出现Using filesort。

order by操作支持Using index和Using filesort两种排序操作,前一种排序操作的效率绝对由于后一种。order by满足两种情况,会使用Using index方式排序:

  1. order by语句使用索引最左前列;
  2. 使用where子句与order by子句条件列组合满足索引最左前列;where子句中如果出现索引的范围查询(即explain中出现type为range)会导致order by索引失效。

因此我们应该尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀。如果不在索引列上,Using filesort有两种算法:

  1. 双路排序。MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。即从磁盘取排序字段,在Buffer中进行排序,再从磁盘取其他字段。取一批数据,要对磁盘进行了两次扫描,众所周知,I/O是很耗时的,所以在MySQL 4.1之后,出现了第二种改进的算法,就是单路排序。
  2. 单路排序。从磁盘读取查询需要的所有列,按照order by列在Buffer对它们进行排序,然后扫描排序后的列表进行输出。它的效率更快一些,避免了第二次读取数据。并且把随机I/O变成了顺序I/O,但是它会使用更多的空间,因为它把每一行都保存在内存中了。

多路排序需要借助磁盘来进行排序。所以取数据,排好了取数据。两次I/O操作比较慢。单路排序将排好的数据存在内存中,省去了一次I/O操作,所以比较快,但是需要内存空间足够。由于单路是后出的,总体而言好过双路,但是用单路有问题,单路排序要比多路排序要多占用很多空间,因为单路排序是把所有字段都取出, 所以有可能取出的数据的总大小超出了Sort Buffer的容量,导致需要多次取Sort Buffer容量大小的数据进行排序(创建tmp文件,多路合并),反而导致了大量的I/O操作。一般需要通过调整MySQL的参数来进行优化:

  • 增大sort_buffer_size参数的值,用于设置单路排序的内存大小,这个参数是针对每个进程的。
  • 增大max_length_for_sort_data参数的值,用于设置单次排序字段大小,会增加用改进算法的概率,但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。
  • 精简select需要的字段,order by操作时select *会非常消耗性能,当查询字段的大小总和小于max_length_for_sort_data且排序字段不是TEXT或BLOB类型时,会用改进后的单路排序算法,否则用老的多路排序算法。两种算法的数据都有可能超出Sort Buffer的容量,会创建tmp文件进行合并排序,导致多次I/O。

对于a、b、c三个字段有索引(a, b, c),则order by操作中,满足索引最左前缀法则的情况都可以使用索引,如:

  • order by a
  • order by a, b
  • order by a, b, c
  • order by a desc, b desc, c desc(同升或者同降是可以使用索引的,突升突将不能)

如果where条件使用索引的最左前缀定义为常量,则order by可以使用索引,如:

  • where a = const order by b, c
  • where a = const and b = const order by c
  • where a = const and b > const order by b, c(虽然b > const会导致索引断层,但order b, c会与a = const衔接为顺序符合的前缀)

不能使用索引进行排序的情况有以下几种:

  • order by a asc, b desc, c desc(突升突将,排序不一致)
  • where d = const order by b, c(丢失a的索引)
  • where a = const order by c(丢失b的索引)
  • where a = const order by a, d(d不是索引的一部分)
  • where a = const and b > const order by c(丢失b的索引,对比前面的第三种情况)
  • where a in (...) order by b, c(对于排序来说,in条件(即多个相等条件)也是范围查询)

15. group by相关优化

  • group by实质是先排序后进行分组,遵照索引建的最佳左前缀。
  • 当无法使用索引列,增大max_length_for_sort_data参数的设置,增大sort_buffer_size参数的设置。
  • where高于having,能写在where限定里的条件就不要写在having限定里。

16. limit相关优化