php mysql语句优化_php面试专题---MySQL常用SQL语句优化

最新推荐文章于 2023-09-19 09:06:37 发布

weixin_39856208

最新推荐文章于 2023-09-19 09:06:37 发布

阅读量55

点赞数

文章标签： php mysql语句优化

本文链接：https://blog.csdn.net/weixin_39856208/article/details/113260853

版权

mysql> show index fromcustomer;+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00sec)

mysql>

1.MySQL中有两种排序方式

第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用explain分析查询时显示为Using Index，不需要额外的排序，性能是最优的。

mysql> explain select customer_id from customer order bystore_id ;+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+-----------------+---------+------+------+-------------+

1 row in set (0.00sec)

mysql>

因为查询主键，然后store_id列是辅助索引(二级索引)，辅助索引上存放了索引键值+对应行的主键，所以直接扫描辅助索引返回有序数据。

mysql> explain select * from customer order bycustomer_id;+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

1 row in set (0.00sec)

mysql>

这种排序方式直接使用了主键，也可以说成是使用了聚集索引。因为innodb是索引组织表(index-organized table)，通过主键聚集数据，数据都是按照主键排序存放。而聚集索引就是按照没张表的主键构造一颗B+树，同时叶子节点中存放的即为正张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

第二种是通过对返回数据进行排序，也就是通常说的Filesort排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或者临时表，取决于mysql服务器对排序参数的设置和需要排序数据的大小。

mysql> explain select * from customer order bystore_id ;+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

+----+-------------+----------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00sec)

mysql>

那么这里优化器为什么不使用store_id列上的辅助索引进行排序呢？

当通过辅助索引来查找数据时，innodb存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那么需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

使用mysql5.6 的trace功能来查看一下强制使用辅助索引和全表扫描的开销。(mysql5.6的trace真心不错，给个赞^_^)

{"rows_estimation": [

{"table": "`customer` FORCE INDEX (`idx_fk_store_id`)","table_scan": {"rows": 599,"cost": 5}/*table_scan*/}

]/*rows_estimation*/},

{"considered_execution_plans": [

{"plan_prefix": [

]/*plan_prefix*/,"table": "`customer` FORCE INDEX (`idx_fk_store_id`)","best_access_path": {"considered_access_paths": [

{"access_type": "scan","rows": 599,"cost": 719.8,"chosen": true,"use_tmp_table": true}

可以清楚的看见优化器使用全表扫描开销更小。

再来看一种情况

mysql> alter table customer add keyidx_stored_email ( store_id , email );

Query OK,0 rows affected (0.19sec)

Records:0 Duplicates: 0 Warnings: 0mysql>

mysql> explain select store_id , email from customer order byemail ;+----+-------------+----------+-------+---------------+------------------+---------+------+------+-----------------------------+

+----+-------------+----------+-------+---------------+------------------+---------+------+------+-----------------------------+

+----+-------------+----------+-------+---------------+------------------+---------+------+------+-----------------------------+

1 row in set (0.00sec)

mysql>

这里为什么又是filesort呢？不是使用了using index吗？虽然使用了覆盖索引(只访问索引的查询，即查询只需要访问索引，而无须访问数据行，最简单的理解，比如翻开一本书，从目录页查找某些内容，但是目录就写的比较详细，我们在目录就找到了自己想看的内容)。但是请别忘记了，idx_stored_email是复合索引，必须遵循最左前缀的原则。

我们改成如下SQL，就可以看见效果了：

mysql> explain select store_id , email from customer order bystore_id ;+----+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+

+----+-------------+----------+-------+---------------+------------------+---------+------+------+-------------+

1 row in set (0.00sec)

mysql>

Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，它就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以在同一个时刻，mysql中存在多个sort buffer排序区。该值不要设置的太大，避免耗尽服务器内存。

简单来说，尽量减少额外排序，通过索引直接返回有序数据。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同。并且order by的字段都是升序或者降序。否则肯定需要额外的排序操作，这样就会出现Filesort。

mysql> explain select store_id , email , customer_id from customer where store_id =1 order by email desc;+----+-------------+----------+------+----------------------------------+------------------+---------+-------+------+--------------------------+

+----+-------------+----------+------+----------------------------------+------------------+---------+-------+------+--------------------------+

+----+-------------+----------+------+----------------------------------+------------------+---------+-------+------+--------------------------+

1 row in set (0.00sec)

mysql>

针对Filesort优化MySQL Server

通过创建合适的索引当然能够减少Filesort的出现。但是在某些特殊情况下，条件限制不能让Filesort消失，那就需要想办法加快Filesort的操作。对于Filesort，mysql有两种排序算法。

1.两次扫描算法(Two Passes)

首先根据条件取出排序字段和行指针信息，之后在排序区sort buffer中排序。如果sort buffer不够，则在临时表Temporary Table中存储排序结果。完成排序后根据行指针回表读取记录。该算法是MySQL 4.1之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针记录获取记录，尤其是第二次读取操作可能导致大量随机I/O操作，优点是排序的时候内存开销比较小。

2.一次扫描算法(Single passes)

一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法高。

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小来判断使用哪种排序算法。如果max_length_for_sort_data更大，那么使用第二种排序算法，否则使用第一种。

适当加大系统变量max_length_for_sort_data的值，能够让MySQL选择更优化的排序算法，即第二种算法。当然设置max_length_for_sort_data 过大，会造成CPU利用率过低和磁盘I/O过高，CPU和I/O利用平衡就足够了。

适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行，当然也不能无限制加大sort_buffer_size排序区，因为sort_buffer_szie参数是每个线程独占，设置过大，会导致服务器SWAP严重。

尽量只使用必要的字段，SELECT具体的字段名称，而不是SELECT * 选择所有字段，这样可以减少排序区的使用。提高SQL性能。

4.优化GROUP BY 语句

默认情况下，mysql对所有GROUP BY col1，col2，的字段进行排序。这与在查询中指定ORDER BY col1，col2类似。因此，如果显式包括一个包含相同列的ORDER BY子句，则对mysql的实际性能没有什么影响。如果查询包括GROUP BY,但我们想要避免排序带来的性能损耗，则可以指定ORDER BY NULL禁止排序，示例如下：

mysql> explain select payment_date , sum(amount) from payment group bypayment_date;+----+-------------+---------+------+---------------+------+---------+------+-------+---------------------------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+---------------------------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+---------------------------------+

1 row in set (0.00sec)

mysql>

可以看见使用了Filesort，还使用了内存临时表，这条SQL严重影响性能，所以需要优化：

首先禁止排序，ORDER BY NULL

mysql> explain select payment_date , sum(amount) from payment group by payment_date order by null;+----+-------------+---------+------+---------------+------+---------+------+-------+-----------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+-----------------+

+----+-------------+---------+------+---------------+------+---------+------+-------+-----------------+

1 row in set (0.00sec)

mysql>

可以看见已经没有使用Filesort，但是还是使用了内存临时表，这是我们可以创建一个复合索引来优化性能

mysql> alter table payment add keyidx_pal (payment_date,amount,last_update);

Query OK,0 rows affected (0.13sec)

Records:0 Duplicates: 0 Warnings: 0mysql> explain select payment_date , sum(amount) from payment group bypayment_date;+----+-------------+---------+-------+---------------+---------+---------+------+-------+-------------+

+----+-------------+---------+-------+---------------+---------+---------+------+-------+-------------+

+----+-------------+---------+-------+---------------+---------+---------+------+-------+-------------+

1 row in set (0.01sec)

mysql>

5.优化子查询

MySQL 4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另外一个SELECT语句中。使用子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也非常easy，but，在有些情况下，子查询效率非常低下，我们可以使用比较高大上的写法，那就是连接(JOIN)取而代之.^_^,下面是一个列子：

mysql> explain select * from customer where customer_id not in ( select customer_id frompayment);+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+------+-------------+

+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+------+-------------+

+----+--------------------+----------+----------------+--------------------+--------------------+---------+------+------+-------------+

2 rows in set (0.00sec)

mysql>

我解释一下这里的执行计划：

第二行，id为2，说明优先级最高，最先执行，DEPENDENT SUBQUERY子查询中的第一个SELECT(意味着select依赖于外层查询中的数据)，type为index_subquery,与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询,using index使用了覆盖索引。

第一行，id为1，说明优先级最低，可以看见select_type列是PRIMARY，意思是最外层的SELECT查询，可以看见使用了全表扫描。

如果使用连接(join)来完成这个查询，速度将会快很多。尤其是连接条件有索引的情况下：

mysql> explain select * from customer left join payment on customer.customer_id = payment.customer_id where payment.customer_id is null;+----+-------------+----------+------+--------------------+--------------------+---------+-----------------------------+------+-------------------------+

+----+-------------+----------+------+--------------------+--------------------+---------+-----------------------------+------+-------------------------+

+----+-------------+----------+------+--------------------+--------------------+---------+-----------------------------+------+-------------------------+

2 rows in set (0.00sec)

mysql>

从执行计划看出查询关联类型从index_subquery调整为了ref，在mysql5.5(包含mysql5.5)，子查询效率还是不如关联查询(join),连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

6.优化OR条件

对于含有OR的查询语句，则无法使用单列索引，但是可以使用复合索引

mysql> explain select * from film where language_id=1 or title ='ACADEMY DINOSAUR';+----+-------------+-------+------+------------------------------+------+---------+------+------+-------------+

+----+-------------+-------+------+------------------------------+------+---------+------+------+-------------+

+----+-------------+-------+------+------------------------------+------+---------+------+------+-------------+

1 row in set (0.00sec)

mysql>

下面看一个较简单明了的例子：

mysql> show create tablett\G*************************** 1. row ***************************

Table: ttCreate Table: CREATE TABLE`tt` (

`id`int(11) DEFAULT NULL,

`age`int(11) DEFAULT NULL,KEY`id` (`id`),KEY`age` (`age`)

) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

mysql> select * fromtt;+------+------+

| id | age |

+------+------+

| 1 | 23 |

| 2 | 36 |

+------+------+

2 rows in set (0.00sec)

mysql>

可以看见表tt有两个单列索引，我们使用如下SQL查询，看是否会使用索引

mysql> explain select * from tt where id=1 or age=36;+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.01sec)

mysql>

可以看见虽然显示有id，age索引可用，但是没有使用，即全表扫描。我们可以这样优化：

mysql> explain select * from tt where id=1 union all select * from tt where age=36;+----+--------------+------------+------+---------------+------+---------+-------+------+-------------+

+----+--------------+------------+------+---------------+------+---------+-------+------+-------------+

| 1 | PRIMARY | tt | ref | id | id | 5 | const | 1 | Using where |

| 2 | UNION | tt | ref | age | age | 5 | const | 1 | Using where |

+----+--------------+------------+------+---------------+------+---------+-------+------+-------------+

3 rows in set (0.00sec)

mysql>

可以看见已经使用了索引，至于这里的执行计划，我就不再说明。有机会我会写一篇mysql执行计划的文章。

看看使用复合索引查询的情况：

mysql> show create tablett\G*************************** 1. row ***************************

Table: ttCreate Table: CREATE TABLE`tt` (

`id`int(11) DEFAULT NULL,

`age`int(11) DEFAULT NULL,KEY`idx_id_age` (`id`,`age`)

) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00sec)

mysql> explain select * from tt where id=1 or age=36;+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+

1 row in set (0.00sec)

mysql>

7.优化分页查询(LIMIT)

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。比较常见的一个分页查询是limit 1000,20，这种最蛋碎了，此时mysql排序出前1020条记录后仅仅返回第1001到1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

在索引上完成排序分页操作，最后根据主键关联回表查询所需要的其他列内容。(使用到了自连接)例如下面的SQL语句：

mysql> explain select film_id , description from film order by title limit 50,5;+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

1 row in set (0.00sec)

mysql>

可以看见实际上使用了全表扫描，如果表有上百万记录，那么这将是一条致命SQL

我们改写成按照索引分页后回表读取行的方式，从执行计划中看不到全表扫描了

mysql> explain select a.film_id , a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id;+----+-------------+------------+--------+---------------+-----------+---------+-----------+------+-------------+

+----+-------------+------------+--------+---------------+-----------+---------+-----------+------+-------------+

| 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 2 | b.film_id | 1 | |

+----+-------------+------------+--------+---------------+-----------+---------+-----------+------+-------------+

3 rows in set (0.00sec)

mysql>

这里我大概解释一下执行计划：

第三行：

id为2，优先级最高，最先执行

select_type为DERIVED 用来表示包含在from子句中的子查询的select，mysql会递归执行并将结果放到一个临时表中。服务器内部称为“派生表”，因为该临时表是从子查询中派生出来的。

type列为index表示索引树全扫描，mysql遍历整个索引来查询匹配的行，这里就把film_id查询出来了。

Extra列为using index 表示使用覆盖索引

第二行：

select_type为PRIMARY，即复杂查询的最外层，当然这里还不算是最最外层。

table列为a，即film表的别名a，

type列为eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

第一行：

select_type列的primary表示该查询为外层查询

table列被标记为，表示查询结果来自一个衍生表，其中2代表该查询衍生自第2个select查询，即id为2的select

7.其他优化手段

当然还有其他的优化手段，比如索引提示，我这里简单列举一下就行了，因为大部分的时候mysql 优化器都工作的很好。

USE INDEX

提供给优化器参考的索引列表(优化器不一定给你面子哦)

IGNORE INDEX

提示优化器忽略一个或者多个索引

FORCE INDEX

强制优化器使用某个特定索引

总结一下：

其实SQL语句优化的过程中，无非就是对mysql的执行计划理解，以及B+树索引的理解，其实只要我们理解执行计划和B+树以后，优化SQL语句还是比较简单的，当然还有特别复杂的SQL，我这里只是一些简单例子，当然再复杂的SQL，还是逃脱不了原理性的东西。呵呵。^_^

weixin_39856208

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
php mysql语句优化_php面试专题---MySQL常用SQL语句优化

mysql> show index fromcustomer;+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+...
复制链接

扫一扫