做商城网站外包,网站生成静态页面工具,电话营销网站建设,软件开发和网站开发的区别5. 排序优化
5.1 排序优化
问题#xff1a;在 WHERE 条件字段上加索引#xff0c;但是为什么在 ORDER BY 字段上还要加索引呢#xff1f; 优化建议#xff1a; SQL 中#xff0c;可以在 WHERE 子句和 ORDER BY 子句中使用索引#xff0c;目的是在 WHERE 子句中避免全表…5. 排序优化
5.1 排序优化
问题在 WHERE 条件字段上加索引但是为什么在 ORDER BY 字段上还要加索引呢 优化建议 SQL 中可以在 WHERE 子句和 ORDER BY 子句中使用索引目的是在 WHERE 子句中避免全表扫描在 ORDER BY 子句避免使用 FileSort 排序。当然某些情况下全表扫描或者 FileSort 排序不一定比索引慢。但总的来说我们还是要避免以提高查询效率。 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列如果不同就使用联合索引。 无法使用 Index 时需要对 FileSort 方式进行调优。
INDEX 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 ORDER BY b,c
- WHERE a const AND b const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g const ORDER BY b,c /*丢失a索引*/
- WHERE a const ORDER BY c /*丢失b索引*/
- WHERE a const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说多个相等条件也是范围查询*/5.2 案例实战
ORDER BY子句尽量使用Index方式排序避免使用FileSort方式排序。 执行案例前先清除student上的索引只留主键
DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid_stuno ON student;
DROP INDEX idx_age_classid_name ON student;
#或者
call proc_drop_index(atguigudb2,student);场景:查询年龄为30岁的且学生编号小于101000的学生按用户名称排序
mysql EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age 30 AND stuno 101000 ORDER BY- NAME ;
-------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498917 | 3.33 | Using where; Using filesort |
-------------------------------------------------------------------------------------------------------------------------------
1 row in set, 2 warnings (0.00 sec)查询结果如下
mysql SELECT SQL_NO_CACHE * FROM student WHERE age 30 AND stuno 101000 ORDER BY- NAME ;
------------------------------------
| id | stuno | name | age | classId |
------------------------------------
| 695 | 100695 | bXLNEI | 30 | 979 |
| 322 | 100322 | CeOJNY | 30 | 40 |
| 993 | 100993 | DVVPnT | 30 | 340 |
| 983 | 100983 | fmUNei | 30 | 433 |
| 946 | 100946 | iSPxRQ | 30 | 511 |
| 469 | 100469 | LTktoo | 30 | 69 |
| 45 | 100045 | mBZrKC | 30 | 280 |
| 635 | 100635 | nQnUJL | 30 | 732 |
| 16 | 100016 | NzjxKh | 30 | 539 |
| 363 | 100363 | OMuKtM | 30 | 695 |
| 293 | 100293 | qOYywO | 30 | 586 |
| 169 | 100169 | qUElsg | 30 | 526 |
| 798 | 100798 | rhHPdX | 30 | 71 |
| 749 | 100749 | TCgaJe | 30 | 697 |
| 157 | 100157 | TUQtvY | 30 | 22 |
| 580 | 100580 | UHDUOj | 30 | 423 |
| 532 | 100532 | XvmZkc | 30 | 861 |
| 939 | 100939 | yBlCbB | 30 | 320 |
| 710 | 100710 | yhmRvD | 30 | 219 |
| 266 | 100266 | YueogP | 30 | 524 |
------------------------------------
20 rows in set, 1 warning (0.16 sec)结论type 是 ALL即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。 优化思路
方案一: 为了去掉filesort我们可以把索引建成
#创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);方案二: 尽量让where的过滤条件和排序使用上索引
建一个三个字段的组合索引
DROP INDEX idx_age_name ON student;
CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age 30 AND stuno 101000 ORDER BY NAME;
mysql EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age 30 AND stuno 101000 ORDER BY NAME;
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | student | NULL | range | idx_age_stuno_name | idx_age_stuno_name | 9 | NULL | 20 | 100.00 | Using index condition; Using filesort |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set, 2 warnings (0.00 sec)mysql SELECT SQL_NO_CACHE * FROM student- WHERE age 30 AND stuno 101000 ORDER BY NAME ;
------------------------------------
| id | stuno | name | age | classId |
------------------------------------
| 695 | 100695 | bXLNEI | 30 | 979 |
| 322 | 100322 | CeOJNY | 30 | 40 |
| 993 | 100993 | DVVPnT | 30 | 340 |
| 983 | 100983 | fmUNei | 30 | 433 |
| 946 | 100946 | iSPxRQ | 30 | 511 |
| 469 | 100469 | LTktoo | 30 | 69 |
| 45 | 100045 | mBZrKC | 30 | 280 |
| 635 | 100635 | nQnUJL | 30 | 732 |
| 16 | 100016 | NzjxKh | 30 | 539 |
| 363 | 100363 | OMuKtM | 30 | 695 |
| 293 | 100293 | qOYywO | 30 | 586 |
| 169 | 100169 | qUElsg | 30 | 526 |
| 798 | 100798 | rhHPdX | 30 | 71 |
| 749 | 100749 | TCgaJe | 30 | 697 |
| 157 | 100157 | TUQtvY | 30 | 22 |
| 580 | 100580 | UHDUOj | 30 | 423 |
| 532 | 100532 | XvmZkc | 30 | 861 |
| 939 | 100939 | yBlCbB | 30 | 320 |
| 710 | 100710 | yhmRvD | 30 | 219 |
| 266 | 100266 | YueogP | 30 | 524 |
------------------------------------
20 rows in set, 1 warning (0.00 sec)结果竟然有 filesort的 sql 运行速度 超过了已经优化掉 filesort的 sql 而且快了很多几乎一瞬间就出现了结果。 结论 两个索引同时存在mysql自动选择最优的方案。对于这个例子mysql选择idx_age_stuno_name。但是 随着数据量的变化选择的索引也会随之变化的。当【范围条件】和【group by 或者 order by】的字段出现二选一时优先观察条件字段的过滤数量如果过滤的数据足够多而需要排序的数据并不多时优先把索引放在范围字段上。反之亦然。 5.3 filesort算法双路排序和单路排序
双路排序 慢 MySQL 4.1之前是使用双路排序字面意思就是两次扫描磁盘最终得到数据 读取行指针和order by列对他们进行排序然后扫描已经排序好的列表按照列表中的值重新从列表中读取对应的数据输出 从磁盘取排序字段在buffer进行排序再从磁盘取其他字段。
取一批数据要对磁盘进行两次扫描众所周知IO是很耗时的所以在mysql4.1之后出现了第二种改进的算法就是单路排序。
单路排序 快
从磁盘读取查询需要的所有列按照order by列在buffer对它们进行排序然后扫描排序后的列表进行输出 它的效率更快一些避免了第二次读取数据。并且把随机IO变成了顺序IO但是它会使用更多的空间 因为它把每一行都保存在内存中了。
结论及引申出的问题
由于单路是后出的总体而言好过双路但是用单路有问题
6. GROUP BY优化
by 使用索引的原则几乎跟order by一致 group by 即使没有过滤条件用到索引也可以直接使用索引。group by 先排序再分组遵照索引建的最佳左前缀法则当无法使用索引列增大max_length_for_sort_data 和sort_buffer_size 参数的设置where效率高于having能写在where限定的条件就不要写在having中了减少使用order by和业务沟通能不排序就不排序或将排序放到程序端去做。Order by、groupby、distinct这些语句较为耗费CPU数据库的CPU资源是极其宝贵的。包含了order by、group by、distinct这些查询的语句where条件过滤出来的结果集请保持在1000行以内否则SQL会很慢。
7. 优化分页查询
优化思路一
在索引上完成排序分页操作最后根据主键关联回原表查询所需要的其他列内容
mysql EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)- a- WHERE t.id a.id;
-----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | derived2 | NULL | ALL | NULL | NULL | NULL | NULL | 498917 | 100.00 | NULL |
| 1 | PRIMARY | t | NULL | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | 100.00 | NULL |
| 2 | DERIVED | student | NULL | index | NULL | PRIMARY | 4 | NULL | 498917 | 100.00 | Using index |
-----------------------------------------------------------------------------------------------------------------------
3 rows in set, 1 warning (0.00 sec)优化思路二
该方案适用于主键自增的表可以把Limit 查询转换成某个位置的查询 。
mysql EXPLAIN SELECT * FROM student WHERE id 2000000 LIMIT 10;
-----------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)8. 优先考虑覆盖索引
8.1 什么是覆盖索引
理解方式一索引是高效找到行的一个方法但是一般数据库也能使用索引找到一个列的数据因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据当能通过读取索引就可以得到想要的数据那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二非聚簇复合索引的一种形式它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 即建索引的字段正好是覆盖查询条件中所涉及的字段。简单说就是 索引列主键 包含 SELECT 到 FROM之间查询的列。
8.2 覆盖索引的利弊
好处 避免Innodb表进行索引的二次查询回表 可以把随机IO变成顺序IO加快查询效率
弊端 索引字段的维护总是有代价的。因此在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA或者称为业务数据架构师的工作。
