nosqlmysql高级篇

查询截取

本文字数: 2.4k 阅读时长: 9 分钟
阅读次数次

前言之对如何sql优化

  • 1.观察至少跑一天，看看生产慢的sql
  • 2.开启慢查询日志，设置阈值，比如超过5秒的就是慢sql，将它抓取出来
  • 3.explain + 慢sql分析如果explain解析不出来进入第四步
  • 4.show profile 查询sql在mysql服务器里面的执行细节和生命周期情况
  • 5.运维经理或dba进行sql数据库服务器的参数调优
1
2
3
4
5
数据优化四部曲：
1.慢查询的抓取
2.explain
3.show profiles
4.参数调优

exists和in

小表驱动大表，主要却别为执行sql的顺序，先执行小表查询为优。

  • in

    1
    2
    3
    4
    select * from A where id in (select id from B)
    	等价于：
    	for id in B:
    		for id in A:

    这个时候in会先执行子查询，在去匹配A的记录，这个时候无疑先查小表肯定更快
    当B表的数据集必须小于A表的时候，用in优于exists

  • exists

    1
    2
    3
    4
    select * from A where exists(select id from B where B.id=A.id)
    等价于：
    for id in A:
    	for id in B:

    这个时候exists会先执行主查询，然后一一匹配exists之后的内容
    当A表的数据集小于B表的数据集时，用exists优于in

not in 和not exists

  • 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引
  • 而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，
    用not exists都比not in要快。

order by 和 group by的查询

order by

  • 1.尽量使用index排序避免使用filesort方式排序
  • 2.order by满足最左前缀原则
  • 3.尽可能使用索引列排序
  • 4.如果不在索引列排序filesort有两种算法：
    • 双路排序和单路排序

双路排序算法

（取一次数据要对磁盘进行两次扫描）固定两次

  • 1.从磁盘取排序字段，在buffer中进行排序
  • 2.再从磁盘取出其他字段放入，两次扫描磁盘，最终得到数据
    读取行指针和order by的列对他们排序，然后扫描已经排序好的列表，
    按照列表中的值重新从列表中读取对应的数据输出

单路排序算法

只需要一次磁盘扫描IO

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

单路引发的问题

如果一次单路抓不完数据，就会变成多路，性能还不如双路。
在sort_buffer中方法B比方法A要多占用很多的空间，因为方法B是把所有的字段都取出，
所以有可能取出的数据的总大小超出sort_buffer的容量，导致每次只能取sort_buffer
容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer的容量大
小的数据,再排序如此反复多次，最后多路合并。造成多次IO
很显然，mysql自己会根据大小选择单路还是双路排序。（超过max_length_for_sort_data用双路）

优化策略

sql服务器调优

  • 1.增大sort_buffer_size参数的设置
  • 2.增大max_length_for_sort_data参数的设置
  • 3.不要使用select * 因为query的字段大小会影响优化器对算法的选择。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    index(age, birth)
    select * from tblA where order by age asc, birth desc;
    这种方式会造成filesort
    总结order by
    index(a, b, c)
    可以的形式
    order by a
    order by a,b
    order by a,b,c
    order by a desc, b desc, c desc

group by

  • 1.group by和order by基本一致
  • 2.group by实质是先排序后进行分组， 遵照索引的最佳左缀
  • 3.当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置
  • 4.where高于having，能写在where限定的条件就不要取having限定了。

数据优化之慢查询日志

记录响应时间超过阀值的语句，具体指运行时间超过long_query_time
值的sql，则会被记录到慢查询日志中。

  • 1.默认情况下mysql没有开启慢查询日志，需要手动设置。
  • 2.如果不是调优需要，不建议开启慢查询。影响性能

开启慢查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1.show variables like “%slow_query_log%”;
2.set global slow_query_log=1;
3.使用set global slow_query_log=1;开启只对当前数据库有效，如果mysql重启以后则失效了。
4.如果想要永久生效，必须修改配置文件my.cnf
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/systemname-slow.log
long_query_time = 3;
log_output = FILE

5.设置后默认10秒为慢查询
show variables like “%long_query_time%”;
set global long_query_time=3;
show global variables like “%long_query_time%”;
需要重新连接或新开一个会话才能看到修改值，或退出重连。
select sleep(4);
var/lib/mysql/tianyun-slow.log
show global status like “%Slow_queries%”;查询有多少条慢查询

mysqldumpslow

在生产环境中，如果要手工分析日志，查找，分析sql，显然是个体力活，
mysql提供了日志分析工具mysqldumpslow。

  • 通过mysqldumpslow –help查询相关命令

    1
    2
    3
    4
    5
    6
    7
    8
    9
    查询8种形式
    1.s：表示按照何种方式排序
    2.c：访问次数
    3.l：锁的时间
    4.r：返回记录
    5.t：查询时间
    6.al：平均锁的时间
    7.ar：平均返回记录次数
    8.g：后边搭配一个正则匹配模式，大小写不敏感;

  • 各种组合慢查询分析：

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

存储过程批量插入数据

  • 1.新建库

    1
    2
    create database  xxxx;
    use xxxx;

  • 2.建表

    1
    2
    3
    4
    5
    6
    create table if not exists dept(
    xxxx
    xxxx
    xxxx
    );
    create table emp();

  • 3.开启二进制日志
    由于插入大数据时mysql自身经常报一个错，this function none of deterministic
    由于开启过慢查询日志，我们开启了bin-log，我们就必须为我们的function指定一个参数。下面

    1
    2
    show variables like “log_bin_trust_function_creators”;
    set global log_bin_trust_function_creators = 1;开启二进制日志

4.创建函数，保证每条数据都不同

1
2
3
4
5
6
7
8
9
10
11
12
delimiter $$
create function random_string(n int) reuturns varchar(255)
begin
declare chars_str varchar(100)default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
declare return_str varchar(255) default ‘’;
declare i int default 0;
while I < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set I = i+1
end while;
return return_str;
end $$
1
2
3
4
5
6
7
delimiter $$
create function random_num() returns int(5)
begin
declare i int default 0;
set I = floor(100+rand()*10);
return I;
end $$

  • 5.创建存储过程

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    create procedure insert_emp(in start int(10), in max_nums int(10))
    begin
    declare i int default 0;
    repeat 
    set i = i+1;
    insert into emp(empno, ename, job, mgr, hiredate, sal,comm, deptno) values ((start+i),random_string(6), ‘salesman’,0001, curdate,2000, 400, random_num());
    until i = max_nums
    end repeat;
    commit;
    end $$
    create procedure insert_dept()……

  • 6.调用存储过程

    1
    2
    3
    delimiter ;
    call insert_dept(100, 10);
    call insert_emp(100001, 500000);插50万条

show profiles

  • 1.如果你要进行sql的调优和排查，让故障重现
  • 2.如果你需要进入一个比explain更加细的排查就需要用show profiles
    到show profiles之后，95%的问题可以解决，如果还有解决不了的问题，请配合DBA优化my.cnf调优修改。

概述

show profiles 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql的调优的测量。
默认情况下参数处于关闭状态，并保存最近15次的运行结果。你现在运行的sql分别是什么都可以记录。

查看当前版本是否支持并开启profile

1
2
show variables like ‘%profiling%’;
set profiling=on;

执行几条sql查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
show profiles
诊断
show profiles cpu, block io for query 0-10;
（显示一条sql从开始到结束明细）
参数备注：
all
block io：显示块io开销
context switches：上下文切换相关开销
cpu
ipc： 显示发送和接收相关开销信息
memory： 显示内存相关开销信息
page faults： 显示页面错误相关开销信息
source： 显示和source_function, source_file, source_line相关的开销信息
swaps：显示交换次数相关开销信息
  • 如果在show profiles for query 里面出现了下面4个：
    • 1.converting heap to myISAM查询结果太大，内存不够用了，往磁盘上搬了。
    • 2.creating tmp table 拷贝数据到临时表，用完再删掉
    • 3.copying to tmp table on disk 把内存中的临时表复制到磁盘，危！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
    • 4.locked

全局查询日志

这招只能在测试环境用！！！！！！！！！！！！！！！！！！！！！

开启全局查询日志

  • 方法1
    在mysql的my.cnf中设置如下

    1
    2
    3
    4
    5
    6
    #开启
    general_log=1
    #记录日志文件的路径
    general_log_file=/path/logfile
    #输出格式
    log_output=FILE

  • 方法2

    1
    2
    3
    4
    set global general_log=1;
    set global log_output=’TABLE’;
    此时你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
    select * from mysql.general_log;

测试先写来两个sql

1
2
3
4
5
6
然后select * from mysql.general_log;
这里我们的general_log为16进制的话，我们需要：
1.show create table mysql.general_log;
可以看到argument为mediumbolb一种二进制
所以我们需要转换下
2.select a.*, convert(a.argument using utf8) from mysql.general_log;
分享到