Mysql优化

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看 SQL 语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。

Explain语法：explain select … from … [where ...]

例如：explain select * from news;

输出：
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

下面对各个属性进行了解：

1、id：这是SELECT的查询序列号

2、select_type：select_type就是select的类型，可以有以下几种：

SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)

3、table：显示这一行的数据是关于哪张表的

4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。

结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行

6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

8、ref：显示使用哪个列或常数与key一起从表中选择行。

9、rows：显示MySQL认为它执行查询时必须检查的行数。

10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。

Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，
就不再搜索了
Range checked for each
Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort
看 到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行
Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候
Using temporary
看到这个的时候，查询需要优化了。这 里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题

Using filesort优化

filesort 使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中filesort可以使用的内存空间大小为参数 sort_buffer_size 的值，默认为2M。当排序记录太多 sort_buffer_size 不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序。可以增大 sort_buffer_size 来解决 filesort 慢问题

using filesort 一般出现在使用了 order by 语句当中。

using filesort不一定引起mysql的性能问题。但是如果查询次数非常多，那么每次在mysql中进行排序，还是会有影响的。

这里的优化方式是在order by 的字段建立索引，例如语句：

SELECT * FROM yw_syjgb ORDER BY result_date desc LIMIT 0,1000;

查看执行计划：

则需要在result_date 建立索引：

此时查看执行计划：

可以看到执行计划中使用索引后没有 Using filesort

需要注意的是：由于 Using filesort是使用算法在内存中进行排序，MySQL对于排序的记录的大小也是有做限制：max_length_for_sort_data，默认为1024
show variables like '%max_length_for_sort_data%';

+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
经过笔者测试，如果排序查询的数据两大于这个默认值的话，还是会使用Using filesort。

总结一句，当排序查询的数据量在默认值的范围内是，在排序的字段上加上索引可以提升MySQL查询的速度。

JOIN优化

MySQL对JOIN的处理采用了一种叫做BLOCK Nested-Loop 的算法。 Block Nested-Loop 算法是通过驱动表(可以简单理解为前面的表)的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 JOIN，则再通过前面两个表的 JOIN 结果集作为JOIN的基础数据，再一次通过循环查询条件到第三个表中查询数据。结论：驱动表(前面表)的数据量决定了总扫描数。 MySQL 优化器会帮助选择出最优的驱动表，所以不需要我们去定义哪个是驱动表。　

posted @ 2020-11-03 11:48 小鑫学JAVA 阅读(58) 评论(0) 收藏举报

刷新页面返回顶部

Mysql优化

公告