MYSQL查询截取优化分析

MYSQL查询截取优化分析

如何捕获慢SQL

1.观察。至少跑一天，看看生产的慢SQL情况

2.开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL,并将它抓取出来

3.explain+慢SQL优化

4.show profile:可用于sql的调优的测量，查询SQL执行的资源消耗情况和生命周期情况，

5.进行SQL数据库服务器的参数调优

SQL优化

1) 永远小表驱动大表

for( int i = 5;…)

​ for(int i =10000…)

for( int i = 100000;…)

​ for(int i =5…)

小表驱动大表又衍生出in/exits的查询优化

# 优化原则:小表驱动大表，即小的数据集驱动大的数据集。
#############原理(RBO)#####################
select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id = B.id
当B表的数据集必须小于A表的数据集时，用in优于exists

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id
当A表的数据集系小于B表的数据集时，用exists优于in。
(注意:A表与B表的ID字段应建立索引。)

in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表大多需要查询，不可避免
exits是对外表做loop循环，每次loop循环在对内表（子查询）进行查询，那么因为对内表查询使用索引（内表的效率高。故可用大表），而外表有多大都需要遍历，不可避免

EXISTS
SELECT ... FROM table WHERE EXISTS (subquery)
该语法可以理解为:#将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE)来决定主查询的数据结果是否得以保留。#
提示
1）EXSTS (subquey)只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别
2）EXSTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
3）EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

2）Order by关键字优化

排序不适用索引时，会使用fileSort;

fileSort有两种算法：

双路排序：MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出【从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段】

取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了第二种改进算法，就是单路排序。

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO,但是他会使用更多的空间，因为他把每一行都保存在内存中。

结论及引申出的问题：由于单路是后出的，总体而言好过双路，但是单路有缺陷
在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再取取
sort_buffer容量大小，再排……从而多次I/O。
本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

提高order By的速度
1.Order by时select*是一个大忌只Query需要的字段，这点非常重要。在这里的影响是:
1.1当Query的字段大小总和小于max _length_for_sort data而且排序字段不是TEXTIBLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
1.2两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
2.尝试提高sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
3.尝试提高max_length_for_sort_data
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.

#单路排序会把所有需要查询的字段都放到sort-buffer中，而双路排序只会把主键和需要排序的字段放到sort-buffer中进行排序，然后再通过主键回到原表查询需要的字段

所以写oder by时，避免使用FileSort方式排序，尽量用index方式排序，应为组织数据时数据本身就已经按创建了索引的列排好了序。

准备：

create table tblA(
age int,
birth timestamp not null
);

insert into tblA(age,birth) values(22,now());
insert into tblA(age,birth) values(23,now());
insert into tblA(age,birth) values(24,now());

create index idx_A_ageBirth on tblA(age,birth);
select*from tblA;

用到的SQL语句：

EXPLAIN SELECT* FROM tblA WHERE age>20 ORDER BY age;
EXPLAIN SELECT* FROM tblA WHERE age>20 ORDER BY age,birth;
EXPLAIN SELECT* FROM tblA WHERE age>20 ORDER BY birth;
EXPLAIN SELECT* FROM tblA WHERE age>20 ORDER BY birth,age;
EXPLAIN SELECT* FROM tblA ORDER BY age ASC,birth DESC;

从图中可看出如果oder by 的字段不满足最左列索引原则会产生排序

order by满足两种情况，会使用Index方式排序：

order by语句中使用索引最佳左前缀

使用where子句和order by子句条件列组合满足索引最左前列

在建立联合索引时，可以指定每个字段是升序还是降序，如果排序时保证最左原则的前提下，还满足对应字段的升降序，也不会出现filesort

如果不在索引列上，file有两种算法：mysql就启动双路排序和单路排序

总结：为排序使用索引

MySQL的两种排序方式：文件排序 | 索引排序
KEY a_b_c(a,b,c)
order by能使用素引最左前缀
- ORDER BY a
- ORDER BY a b-ORDER BY a.b.c
- ORDER BY a DESC，b DESC,c DESC
如果WHERE使用素引的最左前缀定义为常量，则order by能使用素引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c 
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY  aASC,b DESC, c DESC /*排序不一致*/
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*什丢失b素引*/
- WHERE a = const ORDER BY a, d /*d不是素引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说,多个相等条件也是范围查询*/

上述能使用的前提是需要有过滤条件

无过滤不索引

3） GROUP BY关键字优化

group by实质是先排序后进行分组， 使用索引的原则几乎跟 order by 一致 ，遵照索引建的最佳左前缀,唯一区别是 group by 即使没有过滤条件用到索引，也可以直接使用索引

疑问：为什么order by一定要有where才能使用索引（在写的时候有些即使没有where，也可以用上index，为什么呢），而group by没有where过滤条件用到索引，也可以直接使用索引

当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

where高于having,能在where限定条件就不要去having限定

4) 使用覆盖索引

覆盖索引：select的数据列只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

explain SELECT * FROM t_emp WHERE age <> 40;
explain SELECT id,age,name FROM t_emp WHERE age <> 40;

5) 使用慢查询日志捕获慢SQL

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
• 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
• 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

默认情况下，默认情况下，MySQL数据库没有开启慢查询臣志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

慢查询日志支持将日志记录写入文件。

（1）开启设置

SQL语句	描述	备注
SHOW VARIABLES LIKE ‘%slow_query_log%’;	查看慢查询日志是否开启	默认情况下 slow_query_log 的值为 OFF， 表示慢查询日志是禁用的
set global slow_query_log=1;	开启慢查询日志
SHOW VARIABLES LIKE ‘long_query_time%’;	查看慢查询设定阈值	单位秒
set long_query_time=1	设定慢查询阈值	单位秒
show global status like ‘%Slow_queries%’;	查询当前系统中有多少慢查询记录

上述中若设置后查看还是没变，需要重新连接过打开新的一个会话，再次查看才能看到

(2)如永久生效需要修改配置文件 my.cnf 中[mysqld]下配

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysql-slow.log
long_query_time=3
log_output=FILE

#关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log(如果没有指定参数slow_query_log_file的话)

（3） 运行查询时间长的 sql，打开慢查询日志查

示例：

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | ON                                |
| slow_query_log_file | /var/lib/mysql/hadoop100-slow.log |
+---------------------+-----------------------------------+
2 rows in set (0.03 sec)


mysql> SHOW VARIABLES LIKE '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> 
mysql> select sleep(4);   #执行一条超过3秒的sql语句，
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)


[crown@hadoop100 mysql]$ sudo cat /var/lib/mysql/hadoop100-slow.log 
/usr/sbin/mysqld, Version: 5.5.48-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 211128 13:51:39
# User@Host: root[root] @ localhost []
# Query_time: 4.001805  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use emp;		#当前用到的数据库
SET timestamp=1638078699;
select sleep(4);	#出现问题的SQL
[crown@hadoop100 mysql]$ 

再生产环境中，如果要手工分析日志，查找，分析SQL,但比较麻烦，MySQL提供了日志分析工具

日志分析工具： mysqldumpslow

[crown@hadoop100 mysql]$ mysqldumpslow -help	#查看mysqldumpslow的帮助信息

参数	描述
-s	是表示按照何种方式排序
c	访问次数
l	锁定时间
r	返回记录
t	查询时间
al	平均锁定时间
ar	平均返回记录数
at	平均查询时间
-t	即为返回前面多少条的数据
-g	后边搭配一个正则匹配模式，大小写不敏感

（1） 查看mysqldumpslow的帮助信息

得到返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log 
得到访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop100-slow.log 
得到按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop100-slow.log 
另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log  | more

6) Show Profile

正常企业中进行sql的数据调优和排查大致流程：首先如果要进行sql的数据调优和排查，正常企业中的流程差不多都是这些步骤，第一个，一定是dba或者运维工程师从监控系统中收到报站，系统变慢了，一般重要的核心系统都有一套辅助的的系统监控，会监控为什么慢，也许是网络原因，也许是死锁，程序内存泄漏，数据库，SQL写的烂等，假如是sql原因，要先把sql抓出来，将sql跑一下，尽量在测试环境中测试我们的故障，这时候就要做的事就是打开慢查询日志，把有问题的sql提取出来，然后用explain查看问题具体原因。
一般用explain+SQL大部分问题都能解决，若想更进一步查看问题，就用show profile，再解决不了就对系统参数调优和修改
简而言之，数据库优化步骤：1）发现问题，2）慢查询日志抓取 3）explain分析sql 4)show profile详解 5）mysql服务器参数调优

show profile：mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可用于sql的调优的测量

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤：

#1.是否支持，看看当前的mysql版本是否支持
show variables like 'profiling';
#2.开启功能，默认是关闭的，使用前需要开启
set profiling=on;
#3.运行SQL
select*from emp group by id%10 limit 150000;
select*from emp group by id%20 order by 5;
#4.查看结果
ysql> show profiles;#查看所有执行的指令
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00137400 | show variables like 'profiling'             |
|        2 | 0.00515600 | select*from emp group by id%10 limit 150000 |
|        3 | 0.00074775 | select*from emp group by id%20 order by 5   |
+----------+------------+---------------------------------------------+
3 rows in set (0.00 sec)

#5.诊断SQL，show profile cpu,block io for query上一步前面的问题SQL数字号码；
mysql> show profile cpu,block io for query 2;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000063 | 0.000061 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000024 | 0.000023 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.001180 | 0.000692 |   0.000000 |          192 |             0 |
| checking permissions           | 0.000010 | 0.000008 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000036 | 0.000037 |   0.000000 |            0 |             0 |
| System lock                    | 0.000038 | 0.000037 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000055 | 0.000055 |   0.000000 |            0 |             0 |
| init                           | 0.000426 | 0.000226 |   0.000000 |          184 |             0 |
| optimizing                     | 0.000023 | 0.000021 |   0.000000 |            0 |             0 |
| statistics                     | 0.000645 | 0.000000 |   0.000217 |          144 |             0 |
| preparing                      | 0.000146 | 0.000000 |   0.000146 |           56 |             0 |
| Creating tmp table             | 0.000243 | 0.000000 |   0.000244 |           56 |             0 |
| executing                      | 0.000004 | 0.000000 |   0.000003 |            0 |             0 |
| Copying to tmp table           | 0.000798 | 0.000540 |   0.000000 |          240 |             0 |
| Sorting result                 | 0.000805 | 0.000421 |   0.000000 |          264 |             0 |
| Sending data                   | 0.000020 | 0.000019 |   0.000000 |            0 |             0 |
| end                            | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| removing tmp table             | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| end                            | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
| query end                      | 0.000049 | 0.000050 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000006 | 0.000005 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000544 | 0.000406 |   0.000000 |          144 |             0 |
| Waiting for query cache lock   | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000020 | 0.000021 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000002 | 0.000001 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000001 | 0.000002 |   0.000000 |            0 |             0 |
| storing result in query cache  | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000003 | 0.000002 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
29 rows in set (0.00 sec)

#上述展示了一个SQL在底层从开始到执行结束每一步用时，记录了它的完整生命周期
+ 在上述信息中如果出现了如下如下信息，则表示该SQL有大问题
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了
- Creating tmp table 创建临时表【为什么创建临时表费事，一，创建临时表。二，拷贝数据到临时表，三，用完后删除】
- Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

show profile 参数1，参数2…for query SQLId参数备注：

type	信息
ALL	显示所有开销信息
BLOCK IO	显示块IO相关信息
CONTEXT SWITCHES	上下文切换相关信息
CPU	显示CPU相关信息
IPC	显示发送和接受相关开销信息
MEMORY	显示内存相关开销信息
PAGE FAULTS	显示页面错误相关开销信息
SOURCE	显示和Source_function,Source_file,Source_line相关开销信息
SWAPS	显示交换次数相关开销的信息

7) 全局查询日志

全局日志查询不用于生产环境，只能用在测试环境

#配置启用
在mysql的my.cnf中，设置如下：
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

#编码启用
set global general_log=1;
set global log_output='TABLE";
#此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;

数据库优化总结

• 选择正确的数据库引擎myISM（提供高速存储和检索以及全文搜索能力)，INNODB（支持事务，具有ACID属性，可应用于频繁insert,update的场景）
• 根据服务层面：配置mysql性能优化参数；
• 从系统层面增强mysql的性能：优化数据表结构、字段类型(尽量使用小的数据类型)，为合适的字段建立索引，尽量每张表都有主键id
• 明确的字段使用enum(性别，国家，省市)：查询速度块
• 从数据库层面增强性能：优化SQL语句，合理使用字段索引，如覆盖索引，最左前缀匹配等。避免会是数据库索引失效而引起全表扫描的情况。避免使用select*(查询字段越多，速度就越慢，且数据多对网络的传输也会负债过重)，
• 不常使用的数据迁移备份，避免每次都在海量数据中去检索。
• 分表，分库、读写分离等等。
• 使用缓存优化查询（进行多次相同的查询，结果就会被放入缓存中，后续再进行同样的查询，就直接从缓存中提取，不会到表中提取）
• 使用explain 、show profile 乐意查看sql是怎么运行的，怎么处理的，帮助我们分析sql的瓶颈
• 代码层面增强性能：使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。