生活_傻大方

  1. 首页 > 潮·科技 > >

mysql优化篇（基于索引）( 五 )

  • 检查的行数过多 ， 且没有使用覆盖索引
  • ORDER BY中的列不包含在相同的索引 ， 也就是使用了不同的索引
  • 对索引列同时使用了ASC和DESC
  • where语句或者ORDER BY语句中索引列使用了表达式 ， 包括函数表达式
  • where 语句与ORDER BY语句组合满足最左前缀 ， 但where语句中使用了条件查询 。
    • 同样我们使用3.3节中的实验数据（看完下面的结果可以好好体会下上面说的filesort的情况）：
    mysql优化篇（基于索引）文章插图
    在join条件下的order by情况：order by的子句只引用了联接中的第一个表 ， MySQL会先对第一个表进行排序 ， 然后进行联接 。 也就是expain中的Extra的Using Filesort.否则MySQL先把结果保存到临时表(Temporary Table),然后再对临时表的数据进行排序.此时expain中的Extra的显示Using temporary Using Filesort（这里有个坑 ， 就是使用inner join的情况下 ， 默认会使用小表驱动大表 ， 所以就算你把大表写在前面 ， 然后排序只用了大表的字段 ， 但是mysql优化器会把小表放在前面 ， 然后使用了大表的排序 ， 这样就会生成临时表）
    mysql优化篇（基于索引）文章插图
    图1：第一种方法用于第一个非常量表中存在ORDER BY所依赖的列的索引 ， 那就可直接使用已经有序的索引来查找关联表的数据 ， 这种方式是性能最优的 ， 因为不需要额外的排序动作
    图2：第二种方式用于ORDER BY所依赖的列全部属于第一张查询表且没有索引 ， 那么我们可以先对第一张表的记录进行filesort（模式可能是模式1也可能是模式2） ， 得到有序行索引 ， 然后再做关联查询 ， filesort的结果可能是在内存中 ， 也可能在硬盘上 ， 这取决于系统变量sort_buffer_size
    图3：第三种方法用于当ORDER BY的元素不属于第一张表时 ， 需要把关联查询的结果放入临时表 ， 最后对临时表进行filesort
    mysql优化篇（基于索引）文章插图
    mysql> explain select * from department d left join user u on u.d_id = d.id order by d.d_name; #在没有索引的情况下 ， 除了全表扫描 ， 还有临时表 ， filesort+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|1 | SIMPLE| d| NULL| ALL| NULL| NULL | NULL| NULL |6 |100.00 | Using temporary; Using filesort||1 | SIMPLE| u| NULL| ALL| NULL| NULL | NULL| NULL |9 |100.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.00 sec)mysql> explain select * from department d left join user u on u.d_id = d.id order by d.id;#在没有索引的情况下 ， 除了全表扫描 ， 还有临时表 ， filsort+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|1 | SIMPLE| d| NULL| ALL| NULL| NULL | NULL| NULL |6 |100.00 | Using temporary; Using filesort||1 | SIMPLE| u| NULL| ALL| NULL| NULL | NULL| NULL |9 |100.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+2 rows in set, 1 warning (0.00 sec)mysql> create index idx_d_id on user(d_id);#创建索引Query OK, 0 rows affected (0.03 sec)Records: 0Duplicates: 0Warnings: 0mysql> explain select * from department d left join user u on u.d_id = d.id order by d.id; #创建索引后user表的rows为1 ， 没有使用filesort和临时表+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------+| id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------+|1 | SIMPLE| d| NULL| index | NULL| PRIMARY| 4| NULL|6 |100.00 | NULL||1 | SIMPLE| u| NULL| ref| idx_d_id| idx_d_id | 5| foundation_item.d.id |1 |100.00 | NULL|+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------+2 rows in set, 1 warning (0.00 sec)mysql> explain select * from department d left join user u on u.d_id = d.id order by u.d_id; #把排序条件换成user的字段后 ， 使用了临时表和filesort+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+|1 | SIMPLE| d| NULL| ALL| NULL| NULL| NULL| NULL|6 |100.00 | Using temporary; Using filesort ||1 | SIMPLE| u| NULL| ref| idx_d_id| idx_d_id | 5| foundation_item.d.id |1 |100.00 | NULL|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+2 rows in set, 1 warning (0.00 sec)mysql> explain select * from department d left join user u on u.d_id = d.id order by d.d_name; #把排序字段换成department表的d_name列（没有索引） ， 使用了filesort+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+|1 | SIMPLE| d| NULL| ALL| NULL| NULL| NULL| NULL|6 |100.00 | Using filesort ||1 | SIMPLE| u| NULL| ref| idx_d_id| idx_d_id | 5| foundation_item.d.id |1 |100.00 | NULL|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+2 rows in set, 1 warning (0.00 sec)mysql> explain select * from department d inner join user u on u.d_id = d.id order by d.d_name,u.id; #使用了user表的id排序 ， 新增临时表+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+|1 | SIMPLE| d| NULL| ALL| PRIMARY| NULL| NULL| NULL|6 |100.00 | Using temporary; Using filesort ||1 | SIMPLE| u| NULL| ref| idx_d_id| idx_d_id | 5| foundation_item.d.id |1 |100.00 | NULL|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+2 rows in set, 1 warning (0.00 sec)mysql> explain select d.* from department d inner join user u on u.d_id = d.id order by d.d_name,u.id; #这里只返回department表的全部列 ， 还是使用了临时表和filesort+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+|1 | SIMPLE| d| NULL| ALL| PRIMARY| NULL| NULL| NULL|6 |100.00 | Using temporary; Using filesort ||1 | SIMPLE| u| NULL| ref| idx_d_id| idx_d_id | 5| foundation_item.d.id |1 |100.00 | Using index|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+---------------------------------+2 rows in set, 1 warning (0.00 sec)mysql> explain select d.* from department d inner join user u on u.d_id = d.id order by d.d_name; #去掉user的id列排序后少了临时表+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+|1 | SIMPLE| d| NULL| ALL| PRIMARY| NULL| NULL| NULL|6 |100.00 | Using filesort ||1 | SIMPLE| u| NULL| ref| idx_d_id| idx_d_id | 5| foundation_item.d.id |1 |100.00 | Using index|+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+----------------+2 rows in set, 1 warning (0.00 sec)mysql> explain select d.* from department d inner join user u on u.d_id = d.id order by d.id; #使用department的id排序 ， 没了filesort+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+| id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+|1 | SIMPLE| d| NULL| index | PRIMARY| PRIMARY| 4| NULL|6 |100.00 | NULL||1 | SIMPLE| u| NULL| ref| idx_d_id| idx_d_id | 5| foundation_item.d.id |1 |100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)



    上一篇：如何实现 MySQL 删除重复记录并且只保留一条

    下一篇：江淮新能源商用车荣获“十佳商用车品牌服务商”奖