Mysql 查询优化

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

使用索引

准备工作：

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。

上一篇： MySql 锁机制