【Mysql调优】5、SQL优化

来源：易妖游戏网

1、多表关联查询原理与优化

1）嵌套循环关联（NLJ - Nested Loop Join）

# 其中tab1就作为外循环的表，tab2就作为内循环的表
select
    tab1.col1,tab2.col2
from 
    tab1,tab2
where
    tab1.col3 = tab2.col3
    and tab1.col3 = 1

# 现有sql
explain select
    gc.*,g.title
from
    t_goods g,t_goods_cover gc
where
    g.goods_id = gc.goods_id
    and g.category_id = 44;
# type的含义
# ALL 全表扫描
# eq_ref 联表查询的情况，按联表的主键或唯一键联合查询

从上述发现，嵌套循环的次数太多，查询效率非常低，但是如何去优化呢？

# 优化要点：
# 1、外键加索引；
create index idx_goods_id on t_goods_cover(goods_id);
# 2、查询条件加索引;
create index idx_category_id on t_goods(category_id);

# 然后再次执行解析计划
explain select
    gc.*,g.title
from
    t_goods g,t_goods_cover gc
where
    g.goods_id = gc.goods_id
    and g.category_id = 44;

# 首先关闭数据缓存，因为之前执行过innodb引擎就会有缓存
# 设置查询缓存空间为0
set global query_cache_size=0
# 关闭查询缓存
set global query_cache_type=0

import org.junit.Test;

import java.sql.*;

public class SqlOptimize {
    @Test
    public void testSQL1() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///babytun?useUnicode=true&characterEncoding=UTF-8&useSSL=false", "root", "root");
        PreparedStatement pstmt = connection.prepareStatement("select gc.*, g.title from  t_goods g , t_goods_cover gc where g.goods_id = gc.goods_id and g.category_id = 44");
        long st = new java.util.Date().getTime();
        for(int i = 0 ; i < 10000 ;i++) {
            ResultSet rs = pstmt.executeQuery();
            rs.close();
        }
        long et = new java.util.Date().getTime();
        System.out.println("1万次执行时间:" + (et-st) + "毫秒,平均执行时间:" + (et-st)/10000f + "毫秒");
        connection.close();
    }

}

优化前：
优化后：

2）关联查询优化点

降低驱动表的扫描次数，从而减少驱动表对连接列的循环次数
驱动表循环次数较低的情况下，尽量保证连接列上存在索引

2、慢SQL日志

MySQL是支持对数据库当中执行速度较慢sql进行记录的，但是慢sql查询默认时关闭的，需要我们手动开启，使用命令：set GLOBAL slow_query_log=on;；
但是执行时间超过多久才算慢sql呢？这个我们可以手动设置set GLOBAL long_query_time=0.001;，注意它的单位是秒；
但是我们的慢sql会保存到哪里呢？也需要我们手动设置set GLOBAL. slow_query_log_file="slow-sql.log"，该文件默认存放在mysql安装目录的data文件夹下。
注意：上述三项配置，使用命令在数据库会话中执行之后要新开会话才会生效；但如果数据库重启配置会丢失，如果想要持久化配置，则需要将其配置在my.cnf文件中。

1）如何定义SQL为慢SQL？

日常应用当中，一般来说SQL的执行不能超过300ms。

2）定位没有使用索引的SQL语句？

在实际开发如果项目刚上线，程序的运行速度还是比较快的，但是当数据量增大之后，程序越来越慢，这时候我们就需要查看是否有SQL语句没有使用索引？命令：set GLOBAL log_queries_not_using_indexes=on;这样一来当没有使用索引的SQL也会被记录到slow-sql.log当中，但是需要注意的是，MySQL在执行的过程中，如果使用到了系统表，他也没有索引相应执行的SQL语句也是会被记录下来的。

3）总结：

建议慢SQL的时间不超过300ms
慢SQL日志分析，Query_time的值越小查询时间越短，rows_sent的值越小查询过程中扫描的行数越少，获取结果行的速度越快
可以开启对没有使用索引的SQL进行慢SQL日志记录

3、Explain详解

explain select (select 1 fromactor where id =1)
from (select * from where id = 1) der;

1）id：select出现的序列号

有几个select就有几个id，也就是几行数据，并且id是根据select出现的顺序进行的顺序增长的；需要注意的是 id=1的表为驱动表。

2）select_type：查询类型

3）table：explain正在访问的表

如果是上图中单纯的表名就很好理解;
<derived3>代表，正在访问select_type为derived，id为3的select形成的结果集；
<union3,4>代表，正在访问id为3的select和id为4的select通过union所形成的结果集。

4）partitions：查询作用在的哪个分区表

分区表：把大表按条件单独存储到多个不同的“物理小表”中，再构建出的完整“逻辑表”。

查询是先查询逻辑表，快速过滤出数据在那张表，然后查询指定的物理表即可。

5）type：关联类型和访问类型

由执行效率从高到低排序中（🌟为常用）：

1、system：

2、const🌟：常量引用，mysql能对查询的某部分进行优化并将其转换为一个常量，一般用于主键筛选或union key的所有列和常数做对比，所以表最多有一个匹配行，读取1次，速度快
explain select * from (select * from film whre id = 1) tmp;

3、eq_ref🌟：关联查询，primary 或 unique key 索引的所有部分被连接使用，最多返回一条符合条件的记录，简单的select查询不会出现这种type。
explain select * from film_actor left jion film on film_actor.film_id = film.film_id;
相当于就是主外键等唯一索引进行连接查询的时候。

4、ref🌟：非主键或非唯一性索引字段进行检索，要和某个值相比较，可能出现多个符合条件的行。
explain select * from film where name = “film1”;

5、fulltext：全文索引

6、ref_or_null🌟：类似于ref，但是包含null情况的索引扫描，可以搜索到值为NULL的行。
explain select * from film where name = “film1” or name is null;

7、index_merge：

8、unique_suquery：

9、index_subquery：

10、range🌟：范围扫描，通常出现在in()、between、> 、< 、>= 、<= 等等操作中，使用一个索引来检索给定范围的行。
explain select * from actor where id > 1;

11、index🌟：使用索引进行全表扫描，相比ALL来讲，只需扫描索引树查找数据，通常比ALL快一些。
explain select count(*) from film;

12、ALL🌟：全表扫描，从表的第一行数据往后查找直至到最后一行数据，通常需要使用索引进行优化。
explain select * from actor;

6）possible_key：可能使用到的索引

只是代表可能是用得到，即备选项。

7）key：使用到的索引

查询优化的时候使用到的索引名称

8）key_len：使用的索引的长度

mysql在索引里是使用的字节数，通过这个值可以算出具体使用了索引中的哪些列

9）ref：使用的列或常量

在key列记录的索引中，表查找所用到的列或常量，常见的有const（常量）、func（函数）、NULL、字段名（例如：film.id）

10）rows：查询时扫描的行数

mysql在查询时的一个估值，是需要进行读取并检测的行数

11）filtered：百分比估值

代表（rows * filtered）/ 100 得到一个数值，就是可能产生的结果行数，这个结果将于前表产生交互

12）Extra：扩展列说明

distinct：
一旦mysql找到了与行联合匹配的行，就不再进行搜索了，也就是去重
explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
Using index：
查询的列都是在同一索引上的时候，返回索引中的信息，没有访问表中的其他列信息（这种情况是性能高的一种表现）
这种情况也称为“索引覆盖”。
explain select id from film order by id;
Using where：
mysql在存储引擎检索行数据后再进行过滤，也就是读取行数据，在按照where后的条件进行过滤（效率较差）
explain select * from film where id > 1;
Using temporary：
使用了临时表来处理查询，通常需要优化这种情况(一般针对查询结果加索引)
explain select distinct name from actor;
Using filesort：
使用了文件扫描对结果进行了计算排序，效率很差需要优化
expalain select * from actor order by name;

索引排序优化，select字段要覆盖到排序字段才能使用索引，索引章节有提到
如：explain select name from actor order by name;

5、关联查询慢SQL调优案例

数据准备（该案例为阿里云慢SQL挑战赛当中的真实案例）
执行结果，花费190ms

1）解读

通过a表的seller_name关联到b表的seller_name，b表的user_id关联到c表的user_id并且c表的user_id等于17，a表的gmt_create
创建时间为当前时间前后十分钟内的数据，并且将结果按照a表数据的创建时间进行正序排序，执行结果只有一条数据，并且花费190ms。

2）优化

(1) 进行explain解析SQL

每一张表都是全表扫描，并且数据量都不算小。

注意：该案例较早，explain执行计划的结果并没有filtered那一列。

(2) 确定驱动表

关联查询，那么首先就需要确定驱动表，并且确定最优的驱动表。进行分析那张表作为驱动表：

对三张表分别进行分析的时候，只要去掉表之间的关联关系，然后将单表条件跟随其后进行count（*），驱动表最优即为嵌套循环进行次数最少的外循环，所以a表和c表作为驱动表的话，外循环均只需要循环一次即可，但是SQL当中结果需要按照a表的创建时间进行排序，既然优化肯定要使用索引，并提高索引的使用率，所以将a表作为驱动表（可根据实际情况斟酌）。

(3) 创建索引

首先是在a表的gmt_create上创建索引，因为结果需要根据该字段进行排序，所以优化排序需要对该字段进行创建索引
创建联合索引意义如图所示，防止回表数据量过大，做的冗余。

回表：
	先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

	因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供MySQL查询速度。

(4) 再次执行explain

这是我们发现虽然建立了索引，但是a、b两张表还是进行了全标扫描，没有使用索引。这时候可以使用explain extended SQL对sql再次进行分析，可以得到更加详细的执行计划：

(5) 发现问题

a表：表结构当中gmt_create为Varchar存储，但是SQL当中使用了日期函数进行计算，发生 隐式转换导致索引失效
b表：a表的seller_name的COLLATE使用到了utf8_bin，而b表没有使用数据库默认导致COLLATE不一致，也发生 隐式转换导致索引失效（数据库5.7之后修正了，就不会发生该情况）
c表：如图（数据库5.7之后修正了，就不会发生该情况）

(6) 解决问题

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文

全部频道