SQL查询优化

目录

一、获取有性能问题SQL的三种方法

二、慢查询日志介绍

    1、使用慢查询日志获取有性能问题的SQL

三、慢查询日志实例

四、实时获取性能问题

五、SQL的解析预处理及生成执行计划

    1、查询速度为什么会慢

    2、查询缓存对SQL性能的影响

六、如何确定查询处理各个阶段所消耗的时间

    1、使用profile

2、使用performance_schema

七、特定SQL的查询优化

    1、大表的数据修改最好要分批处理

    2、大表的删除 

 3、如何修改大表的表结构

​ ​4、如何优化not in和<>查询      

    5、使用汇总表优化查询    

---

一、获取有性能问题SQL的三种方法

    通过用户反馈获取存在性能问题的SQL
    通过慢查询日志获取存在性能问题的SQL
    实时获取存在性能问题的SQL

二、慢查询日志介绍

    1、使用慢查询日志获取有性能问题的SQL

        (1) 参数配置

            slow_query_log 启动停止记录慢查询日志

                . 启动：ON；

                . 如果在已经运行的Mysql中启动慢查询日志功能，可以直接使用set global命令;

                . 为了避免慢查询日志占用太多的存储空间，如果需要在指定的某一时间段内开启慢查询日志功能，可以通过脚本来定时的开关。

            slow_query_log_file 指定慢查询日志的存储路径及文件

                . 默认情况下保存在MYSQL的数据目录中

                . 日志存储和数据存储分开存储

            long_query_time 指定记录慢查询日志SQL执行时间的伐值        

                . 默认值为10秒

                . 通常改为0.001秒也就是1毫秒可能比较合适

                . 记录范围：记录所有符合条件的SQL；包括查询语句；数据修改语句；已经回滚的SQL

            log_queries_not_using_indexes 是否记录未使用索引的SQL

        (2) 慢查询日志中记录的内容

            第一行：记录运行这条SQL的用户信息和用户线程ID号

            第二行：记录了执行这条SQL所使用的时间（精确到毫秒）

            第三行：记录了执行这条SQL所使用的锁的时间（精确到毫秒）

            第四行：记录了执行这条SQL返回的数据的行数

            第五行：记录了执行这条SQL扫描的数据的行数

            第六行：记录了执行这条SQL所用的时间（时间戳的形式）

            第七行：记录了我们所执行的SQL

        (3) 常用的慢查询日志分析工具（mysqldumpslow）

            汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

            mysqldumpslow -s r -t 10 slow-mysql.log

            -s order（c, t, l, r, at, al, ar）

                指定按那种排序方式输出结果

            

  -t top

                指定取前几条作为结束输出

        (4) 常用的慢查询日志分析工具（pt-query-digest）

            安装过程自行百度

            pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord iZwz948fbj8fd62q3tskypZ-slow.log

三、慢查询日志实例

 

 

四、实时获取性能问题

    可以通过mysql的information_schema数据库下的PROCESSLIST表实时的发现具有性能问题的SQL

 

set global long_query_time=0;

set global slow_query_log=on;

select id, user, host, db, command, time, state, info from information_schema.processlist;

select id, user, host, db, command, time, state, info from information_schema.processlist\G;    

 

五、SQL的解析预处理及生成执行计划

    1、查询速度为什么会慢

        (1) MySQL服务器处理查询请求的整个过程

            . 客户端发送SQL请求给服务器

            . 服务器检查是否可以在查询缓存中命中该SQL

            . 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划

            . 根据执行计划，调用存储引擎API来查询数据

            . 将结果返回给客户端

            在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。

            Hash查找只能进行全值匹配，所以请求的查询和缓存中的查询，即使只有一个字节的不同，那么也不会匹配到缓存中的结果。

            如果当前的查询恰好命中了查询缓存，那么在返回结果之前，MySQL就会检查用户权限，这任然是无需解析查询SQL语句的，因为查询缓存中已经存在了当前查询需要访问的一些表的信息，如果权限没有问题，MySQL会跳过所有的其他阶段，直接从缓存中拿到结果，并返回给客户端。这种情况下，查询是不会被解析的，也不会生成查询计划，不会被执行。

    2、查询缓存对SQL性能的影响

        从查询缓存中直接返回结果并不容易。

        每次在缓存中检查SQL是否命中时，都要对缓存加锁，所以对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率。所以在这种情况下建议大家不要使用查询缓存。

        (1) 对查询缓存存在影响的一些系统参数

            query_cache_type 设置查询缓存是否可用

                ON, OFF, DEMAND

                DEMAND表示只有在查询语句中使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存。

            query_cache_size 设置查询缓存的内存大小

                单元字节必须是1024的整数倍。

            query_cache_limit 设置查询缓存可用存储的最大值

                超过这个值就不会被缓存了，如果预先我们知道结果很大，不会被缓存，那么我们在查询上加上SQL_NO_CACHE可以提高效率。

            query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据

                默认关闭。

            query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

        (2) MySQL依照这个执行计划和存储引擎进行交互

            这个阶段包括了多个子过程：

                解析SQL，预处理，优化SQL执行计划。

                . 语法解析阶段是通过关键字对MySQL语句进行解析，并生成一棵对应的“解析树”；

                    MySQL解析器将使用MySQL语法规则验证和解析查询

                        检查语法是否使用了正确的关键字

                        关键字的顺序是否正确

                . 预处理阶段是根据MySQL规则进一步检查解析树是否合法；

                    检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等

                语法检查全都通过了，查询优化器就可以生成查询计划了。

        (3) 会造成MySQL生成错误的执行计划的原因

            . 统计信息不准确

                存储引擎提供的信息。

            . 执行计划中的成本估算不等同于实际的执行计划的成本

                MySQL服务器层并不知道哪些页面在内存中；哪些页面在磁盘上；哪些需要顺序读取；哪些页面要随机读取

            . MySQL优化器所认为的最优可能与你所认为的最优不一样

            . MySQL从不考虑其他并发的查询，这可能会影响当前查询的速度

            . MySQL有时候也会基于一些固定的规则来生成执行计划

            . MySQL不会考虑不受其控制的成本

                存储过程、用户自定义的函数

        (4) MySQL优化器可优化的SQL类型

            . 重新定义表的关联顺序

            . 将外连接转化成内连接

                where条件和库表结构等都可以使外连接等价于内连接。

            . 使用等价变换规则

                (5=5 and a > 5)将被改写为a > 5

            . 优化count()、min()和max()

                select tables optimized away

                优化器已经从执行计划中移除了该表，并以一个常熟取而代之

            . 将一个表达式转化为常数表达式

            . 子查询优化

                子查询转换为关联查询，可以减少查询的次数

            . 提前终止查询

                表中dilm_id字段为无符号整型，当其值为-1时，就会终止查询。

 . 对in()条件进行优化

                MySQL将in列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。

六、如何确定查询处理各个阶段所消耗的时间

    1、使用profile

        set profiling = 1;

        启动profile

            这是一个session级的配置，只有在当前session下，才能起作用。启动profile后，在服务器上执行的所有语句都会记录其消耗的时间和其他一些查询执行的状态。

        执行查询

        show profiles;

            查看每一个查询所消耗的总时间的信息。

        show profile for query N;

            查询每个阶段所消耗的时间。

        实例：

            set profiling = 1;

   select count(*) from film;

  show profiles;

show profile for query 1;

  show profile cpu for query 1;

2、使用performance_schema

        设置语句：           

 UPDATE 'setup_instruments' SET enabled='YES', TIMED='YES' WHERE NAME LIKE 'stage%';

 UPDATE setup_consumers SET enabled='YES' WHERE NAME LIKE 'events%';

        执行语句：

select a.thread_id, sql_text, c.event_name, (c.timer_end - c.timer_start)/1000000000 as 'duration(ms)' 
from events_statements_history_long a 
join threads b on a.thread_id=b.thread_id 
join events_stages_history_long c on c.thread_id=b.thread_id and c.event_id between a.event_id and a.end_event_id 
where b.processlist_id=connection_id() and a.event_name='statement/sql/select' 
order by a.thread_id, c.event_id

执行结果：

七、特定SQL的查询优化

    1、大表的数据修改最好要分批处理

        1000万行记录的表中删除/更新100万行记录一次只删除/更新5000行记录

        为了减少主从复制同步带来的压力，我们可以在每次修改数据后暂停几秒，给主从复制集群提供一个同步数据的时间。    

    2、大表的删除 

 delimiter$$
 use 'imooc'$$
 drop procedure if exists 'p_delete_rows'$$
 create definer='root'@'127.0.0.1' procedure 'p_delete_rows'()
 begin
	declare v_rows int;
	set v_rows = 1; 
	while v_rows > 0 
	do 
		delete from 'sbtest1' where id >= 9000 and id <= 19000 limit 5000;
		select row_count() into v_rows;
		select sleep(5); 
	end while;
end$$
delimiter;  

 3、如何修改大表的表结构

        对表中的列的字段类型进行修改

        改变字段的宽度时还是会锁表

        无法解决主从数据库延迟的问题

        (1) 方案一：

            利用主从复制服务器架构，先在从服务器上进行修改，

 然后进行主从切换，

 (2) 方案二：

            在主服务器上建立一个新的表，这个新表的结构就是要修改之后的这个表的结构；

            然后再把老表的数据导入到新表中；

            并且在老表上建立一系列的触发器，把老表数据的修改也同步更新到新表中；

            当老表和新表数据同步后，对老表加一个排它锁，重新命名新表和老表的名字；

            最后删除重命名后的老表。

由于实现过程复杂，我们可以借助工具：

                alter：对语句进行的修改

                user：执行这个修改的执行用户的用户名，用户对需要修改表结构的表要有修改其表结构的权限

                password：执行这个修改的执行用户的密码

                D：要修改的表所在的数据库名

                t：要修改的表的表名

                charset：指定表的字符集          

                execute：指定是否执行这个修改               

 pt-online-schema-change --alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT ''" --user=root --password=PassWord D=imooc,t=sbtest4 --charset=utf8 --execute

 4、如何优化not in和<>查询      

  SELECT customer_id, first_name, last_name, email

        FROM customer

        WHERE customer_id

        NOT IN (SELECT customer_id FROM payment)



        SELECT customer_id, first_name, last_name, email

        FROM customer a

        LEFT JOIN payment b ON a.customer_id=b.customer_id

        WHERE b.customer_id IS NULL

    5、使用汇总表优化查询    

    SELECT COUNT(*) FROM product_comment WHERE product_id = 999

        汇总表就是提前以要统计的数据进行汇总并记录到表中以备后续的查询使用。

        如上SQL查询可进行如下优化：

            新创建一个表，统计出截止前一天每一个商品评论数数据的汇总         

   CREATE TABLE product_comment_cnt(product_id INT, cnt INT);

            显示每个商品的评论数：               

 SELECT SUM(cnt) FROM (

                 SELECT cnt FROM product_comment_cnt WHERE product_id=999

                 UNION ALL

                 SELECT COUNT(*) FROM product_comment WHERE product_id=999 AND timestr>DATE(NOW())

                ) a