Mysql 查询优化

逆流者 2021年04月05日 291次浏览

使用索引

准备工作:

drop table if EXISTS staffs;
CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('admin',30,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('Tom',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
 
ALTER TABLE staffs ADD INDEX index_staffs_name_age_pos(name, age, pos);

SELECT * FROM staffs;

索引规范建议

  • 按顺序全值匹配
  • 最佳左前缀法则
    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  • 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
  • mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
  • is not null 也无法使用索引,但是is null是可以使用索引的
  • like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
  • 字符串不加单引号索引失效
  • 少用or, 用它来连接时会索引失效

按顺序全值匹配

索引 index_staffs_name_age_pos 建立索引时 以 name , age ,pos 的顺序建立的,全值匹配表示按顺序匹配的。

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.10 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 79      | const,const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.07 sec)

mysql> 
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 141     | const,const,const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
1 row in set (0.12 sec)

最佳左前缀法则

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在,会自动优化。
表staffs 索引index_staffs_name_age_pos 索引 id 为主键

  • 当使用覆盖索引的方式时,(select name, age, id from staffs where age=10 (后面没有其他没有索引的字段条件)),即使不是以 name 开头,也会使用 index_staffs_name_age_pos 索引。
mysql> explain select age, name, id from staffs where age=10;
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key                       | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | NULL          | index_staffs_name_age_pos | 141     | NULL |    4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
1 row in set (0.09 sec)

既 select 后的字段 有索引,where 后的字段也有索引,则无关执行顺序。

  • 除开上述条件 才满足最左前缀法则。
mysql> EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';
 
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.08 sec)

mysql> 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.09 sec)

不在索引列上做任何操作

不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.03 sec)

存储引擎不能使用索引中范围条件右边的列

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.10 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 79      | const,const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.07 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 141     | const,const,const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
1 row in set (0.12 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 25 AND pos = 'dev';
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys             | key                       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | range | index_staffs_name_age_pos | index_staffs_name_age_pos | 79      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
1 row in set (0.06 sec)

观察key_len 字段的值变化

尽量使用覆盖索引

尽量使用覆盖索引,索引列和查询列一致,减少select *。

mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+--------------------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref               | rows | Extra                    |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 141     | const,const,const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+--------------------------+
1 row in set (0.04 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 141     | const,const,const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------------+------+-------------+
1 row in set (0.05 sec)

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.05 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME != 'July';
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys             | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_name_age_pos | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.12 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys             | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_name_age_pos | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)

is not null 也无法使用索引,但是is null是可以使用索引的

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is null;
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.04 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is not null;
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys             | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_name_age_pos | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

  • like ‘%abc%’ type 类型会变成 all
  • like ‘abc%’ type 类型为 range ,算是范围,可以使用索引
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'Jul%';
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys             | key                       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | range | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
1 row in set (0.05 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%Jul';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'Ju%l';
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys             | key                       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | range | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------------------+---------------------------+---------+------+------+-------------+
1 row in set (0.09 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%Ju%l';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)

那如何解决like '%字符串%'时索引不被使用问题呢?

使用覆盖索引

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%Jul%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.07 sec)

mysql> EXPLAIN SELECT name FROM staffs WHERE NAME like '%Jul%';
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key                       | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | staffs | index | NULL          | index_staffs_name_age_pos | 141     | NULL |    4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------------------------+---------+------+------+--------------------------+
1 row in set (0.16 sec)

字符串不加单引号索引失效

底层进行转换使索引失效,使用了函数造成索引失效

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = '1234';
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_name_age_pos | index_staffs_name_age_pos | 75      | const |    1 | Using where |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------+------+-------------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = 1234;
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys             | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_name_age_pos | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.03 sec)

少用or, 用它来连接时会索引失效

mysql> EXPLAIN SELECT * FROM staffs WHERE NAME = '1234' or name = '2345';
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys             | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_name_age_pos | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+--------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.06 sec)

小测试总结

假设index(a,b,c)

Where语句索引是否被使用
where a = 3Y,使用到a
where a = 3 and b = 5Y,使用到a,b
where a = 3 and b = 5 and c = 4Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4N
where a = 3 and c = 5使用到a, 但是c不可以,b中间断了
where a = 3 and b > 4 and c = 5使用到a和b, c不能用在范围之后,b后断了
where a = 3 and b like 'kk%' and c = 4Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4Y,只用到a
where a = 3 and b like '%kk%' and c = 4Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4Y,使用到a,b,c

索引建议

  • 对于单键索引,尽量选择针对当前query过滤性更好的索引;
  • 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。(避免索引过滤性好的索引失效)
  • 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引;
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

单表查询优化

建表SQL:

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
 
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');
 
SELECT * FROM article;

查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。

mysql> SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  3 |         1 |
+----+-----------+
1 row in set (0.08 sec)

查询当然可以查出来,下面用explain来分析下:

第一次explain

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.04 sec)

很明显, type 是 ALL,即最坏的情况(全表扫描)。Extra 里还出现了 Using filesort (文件内排序),也是最坏的情况。

开始优化:

第一次新建索引

create index idx_article_ccv on article(category_id,comments,views);

第2次explain

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 | Using where; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
1 row in set (0.05 sec)

type 变成了 range, 这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。

但是我们已经建立了索引,为啥没用呢?
这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

第二次新建索引

# 先删除第一次的索引
DROP INDEX idx_article_ccv ON article;
create index idx_article_cv on article(category_id,views);

第3次explain

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | idx_article_cv | idx_article_cv | 4       | const |    2 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
1 row in set (0.06 sec)

可以看到, type 变为了 ref, Extra 中的 Using filesort 也消失了,结果非常理想。

所以说索引建的准很重要!!!

关联查询优化

建表语句:

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  1. 开始explain分析
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.09 sec)

type 有All

  1. 添加索引优化(右表)
ALTER TABLE `book` ADD INDEX Y ( `card`);
  1. 第2次explain
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                 | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL                |   20 |             |
|  1 | SIMPLE      | book  | ref  | Y             | Y    | 4       | index_db.class.card |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+---------------------+------+-------------+
2 rows in set (0.09 sec)

可以看到第二行的 type 变为了 ref, rows 也变成了优化比较明显。
这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。

  1. 左边表新建索引
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
  1. 第3次explain
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | class | index | NULL          | X    | 4       | NULL |   20 | Using index |
|  1 | SIMPLE      | book  | ALL   | NULL          | NULL | NULL    | NULL |   20 |             |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
2 rows in set (0.09 sec)

看结果 row,并没有明显优化

优化建议

1、保证被驱动表的join字段已经被索引。
2、left join 时,选择小表作为驱动表,大表作为被驱动表。
3、inner join 时,mysql会自己帮你把小结果集的表选为驱动表。
4、子查询尽量不要放在被驱动表,有可能使用不到索引。

子查询优化

用 in 还是 exists

优化原则:小表驱动大表

select * from A where id in (select id from B)

上面先遍历B表,再遍历A表,这里B表是驱动表;
当B表的数据集小于A表的数据集时,用in优于exists。

select * from A where exists (select 'a' from B where B.id = A.id)

上面先遍历A表,再遍历B表,这里A表是驱动表;
当A表的数据集小于B表的数据集时,用exists优于in。

上述A和B表的ID字段应建立索引。

order by关键字优化

ORDER BY子句,尽量使用Index方式排序, 避免使用FileSort方式排序

建表SQL

CREATE TABLE tblA(
  id int primary key not null auto_increment,
  age INT,
  birth TIMESTAMP NOT NULL,
  name varchar(200)
);
 
INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');
 
CREATE INDEX idx_A_ageBirth ON tblA(age,birth);
 
SELECT * FROM tblA; 

MySQL支持二种方式的排序,FileSort和Index,Index效率高. 它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况,会使用Index方式排序:

  • ORDER BY 语句使用索引最左前列
  • 使用Where子句与Order BY子句条件列组合满足索引最左前列
    where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀原则。

在这里插入图片描述

如果不在索引列上,filesort有两种算法: mysql就要启动双路排序和单路排序

双路排序和单路排序

双路排序

  • MySQL 4.1之前是使用双路排序, 字面意思就是两次扫描磁盘,最终得到数据, 读取行指针和orderby列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。
  • 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。

取一批数据,要对磁盘进行了两次扫描,众所周知,I\O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。

单路排序

从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出, 它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间, 因为它把每一行都保存在内存中了。

结论及引申出的问题

  • 由于单路是后出的,总体而言好过双路
  • 但是用单路有问题
    在sort_buffer中,方法B比方法A要多占用很多空间,因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取取sort_buffer容量大小,再排……从而多次I/O。
    本来想省一次I/O操作,反而导致了大量的I/O操作,反而得不偿失。

优化策略:

  • 增大sort_buffer_size参数的设置
    用于单路排序的内存大小
  • 增大max_length_for_sort_data参数的设置
    单次排序字段大小。(单次排序请求)
  • 去掉select 后面不需要的字段
    select 后的多了,排序的时候也会带着一起,很占内存,所以去掉没有用的

分页查询的优化(limit)

参考这篇文章创建的emp表,并且表中批量插入了150万数据,这样能更好的看到优化的效果

mysql> explain select sql_no_cache * from emp order by deptno limit 10000,40;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 1500209 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.09 sec)

上面出现了 Using filesort

那我们就给deptno这个字段加上索引吧。

mysql> create index index_emp_deptno on emp(deptno);
Query OK, 0 rows affected (7.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引后再试一下:

mysql> explain select sql_no_cache * from emp order by deptno limit 10000,40;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 1500209 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.08 sec)

然而并没有用。

继续优化: 先利用覆盖索引把要取的数据行的主键取到,然后再用这个主键列与数据表做关联:(查询的数据量小了后)

mysql> EXPLAIN  SELECT  SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id;
+----+-------------+------------+--------+---------------+------------------+---------+------+-------+-------------+
| id | select_type | table      | type   | possible_keys | key              | key_len | ref  | rows  | Extra       |
+----+-------------+------------+--------+---------------+------------------+---------+------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL             | NULL    | NULL |    40 |             |
|  1 | PRIMARY     | emp        | eq_ref | PRIMARY       | PRIMARY          | 4       | a.id |     1 |             |
|  2 | DERIVED     | e          | index  | NULL          | index_emp_deptno | 3       | NULL | 10040 | Using index |
+----+-------------+------------+--------+---------------+------------------+---------+------+-------+-------------+
3 rows in set (0.07 sec)

最后比较一下查询速度:
优化前:

mysql> select sql_no_cache * from emp order by deptno limit 10000,40;
+-------+-------+--------+----------+-----+------------+---------+--------+--------+
| id    | empno | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+
| 98137 | 99138 | yMppGj | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98148 | 99149 | ppDVRA | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98150 | 99151 | KdnfNy | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98176 | 99177 | uCEqQf | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98183 | 99184 | jlFsYP | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98203 | 99204 | dNEHzA | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98221 | 99222 | cRfbPU | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98234 | 99235 | lkpXRq | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98305 | 99306 | vCzSNn | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98307 | 99308 | jwHXOd | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98334 | 99335 | lIGgNy | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98335 | 99336 | PRQDum | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98340 | 99341 | NLrABg | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98345 | 99346 | YdwWsb | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98365 | 99366 | IvehAg | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98371 | 99372 | qRmMVs | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98379 | 99380 | lQvFOe | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98406 | 99407 | pQjwIa | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98413 | 99414 | KwdbWE | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98427 | 99428 | xzhjCg | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98432 | 99433 | QMnczp | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98449 | 99450 | QFGnxz | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98461 | 99462 | RWjfZF | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98472 | 99473 | OpiXms | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98478 | 99479 | mYfIXP | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98481 | 99482 | Iaabfy | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98484 | 99485 | WiaCMc | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98487 | 99488 | YsUuqv | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98500 | 99501 | cbdjLc | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98505 | 99506 | AkwBvx | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98507 | 99508 | auWyCr | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98511 | 99512 | msciKb | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98516 | 99517 | WwuHey | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98523 | 99524 | XgPEBW | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98529 | 99530 | Kepoxz | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98559 | 99560 | VpPdXE | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98567 | 99568 | jnLRbG | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98586 | 99587 | huByLm | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98603 | 99604 | ftCEqP | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
| 98608 | 99609 | RnOgqk | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+
40 rows in set (1.52 sec)

优化后:

mysql> SELECT  SQL_NO_CACHE * FROM emp INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id;
+-------+-------+--------+----------+-----+------------+---------+--------+--------+-------+
| id    | empno | ename  | job      | mgr | hiredate   | sal     | comm   | deptno | id    |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+-------+
| 98137 | 99138 | yMppGj | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98137 |
| 98148 | 99149 | ppDVRA | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98148 |
| 98150 | 99151 | KdnfNy | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98150 |
| 98176 | 99177 | uCEqQf | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98176 |
| 98183 | 99184 | jlFsYP | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98183 |
| 98203 | 99204 | dNEHzA | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98203 |
| 98221 | 99222 | cRfbPU | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98221 |
| 98234 | 99235 | lkpXRq | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98234 |
| 98305 | 99306 | vCzSNn | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98305 |
| 98307 | 99308 | jwHXOd | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98307 |
| 98334 | 99335 | lIGgNy | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98334 |
| 98335 | 99336 | PRQDum | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98335 |
| 98340 | 99341 | NLrABg | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98340 |
| 98345 | 99346 | YdwWsb | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98345 |
| 98365 | 99366 | IvehAg | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98365 |
| 98371 | 99372 | qRmMVs | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98371 |
| 98379 | 99380 | lQvFOe | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98379 |
| 98406 | 99407 | pQjwIa | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98406 |
| 98413 | 99414 | KwdbWE | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98413 |
| 98427 | 99428 | xzhjCg | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98427 |
| 98432 | 99433 | QMnczp | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98432 |
| 98449 | 99450 | QFGnxz | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98449 |
| 98461 | 99462 | RWjfZF | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98461 |
| 98472 | 99473 | OpiXms | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98472 |
| 98478 | 99479 | mYfIXP | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98478 |
| 98481 | 99482 | Iaabfy | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98481 |
| 98484 | 99485 | WiaCMc | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98484 |
| 98487 | 99488 | YsUuqv | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98487 |
| 98500 | 99501 | cbdjLc | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98500 |
| 98505 | 99506 | AkwBvx | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98505 |
| 98507 | 99508 | auWyCr | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98507 |
| 98511 | 99512 | msciKb | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98511 |
| 98516 | 99517 | WwuHey | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98516 |
| 98523 | 99524 | XgPEBW | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98523 |
| 98529 | 99530 | Kepoxz | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98529 |
| 98559 | 99560 | VpPdXE | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98559 |
| 98567 | 99568 | jnLRbG | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98567 |
| 98586 | 99587 | huByLm | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98586 |
| 98603 | 99604 | ftCEqP | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98603 |
| 98608 | 99609 | RnOgqk | salesman |   1 | 2021-04-04 | 2000.00 | 400.00 |    100 | 98608 |
+-------+-------+--------+----------+-----+------------+---------+--------+--------+-------+
40 rows in set (0.13 sec)

优化结果很明显,查询时间缩短了10倍多。

实践证明:
order by 后的字段(XXX)有索引 , sql 中有 limit 时,

  • 当 select id 或 XXX字段索引包含字段时 ,显示 using index
  • 当 select 后的字段含有 order by 字段, 索引不包含的字段时,将显示 using filesort

GROUP BY关键字优化

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

去重优化

尽量不要使用 distinct 关键字去重。

能用group by 代替的可以用group by。