数据库优化~慢查询查找并优化

1. 慢查询日志查看基础
    1> 查看慢查询日志是否开启
        > show variables like 'slow_query_log';
    2> 查看日志相关配置
        > show variables like '%log%';
    3> 设置为 “将未使用索引的查询计入慢查询日志”
        > set global log_queries_not_using_indexs = on;
    4> 查看慢查询的启用时间设置
        > show variables like 'long_query_time';
        默认为0，会记录所有sql日志，浪费磁盘空间，生产中应当设置合理的慢查询临界时间，
    如80ms，这样只会记录下执行时间大于80ms的sql
    5> 开启慢查询日志
        > set global slow_query_log = on;
    6> 查看慢查询日志文件储存位置等信息
        > show variables like '%log_file%'

2. 使用mysqldumpslow工具查看分析日志
    该工具为安装MySql时候自动安装的辅助工具
    使用：
        mysqldumpslow -t 10 logfilepath
        分析结果中包含相应sql的执行次数、执行时间、锁定时间、影响行数、执行用户及IP、sql详情

3. 使用pt-query-digest
    使用：
        pt-query-digest logfilepath | more
    分析结果显示：
        1> 日志的时间范围、总的查询次数、（unique）sql数量、总执行时间、发送行数、扫描行
    数、占比以及相应的最小值、最大值、平均值等
        2> 涉及到的表以及相关操作（insert、update等）的统计信息
        3> 具体sql以及该sql的统计信息

4. 如何通过慢查询日志发现有问题的sql
    1> 查询次数过多而且每次查询时间都很长的sql
    2> IO操作频繁的sql，注意pt工具分析中的扫描行数（Rows examine）即代表了IO操作（因为mysql存储数据使用文件来存储，
        扫描数据就会加载文件，即IO操作）
    3> 未命中索引的sql，即pt工具分析中Rows examine远大于Rows Send（实际发送行数）的sql

5. pt-query-digest工具的安装
    yum -y install perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes
    wget percona.com/get/pt-query-digest
    chmod u+x pt-query-digest （授予当前用户执行权限）
    mv /root/pt-query-digest /usr/bin/   （这句是让pt-query-digest命令直接可用）
    如果报如下错误
        Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 
        /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) 
        at /usr/bin/pt-query-digest line 2470. BEGIN failed--compilation aborted at /usr/bin/pt-query-digest line 2470.
    可通过下面方式解决：
        执行安装perl-Digest-MD5的命令，如果能连上网，直接yum安装： 
        yum -y install perl-Digest-MD5

6. 使用explain分析sql的执行计划
    使用：
        explain select * from user u where u.name like 'zc%';
        (linux环境下在上面语句之后加上\G会竖行显示信息，便于查看)
    一定要注意Extra列展示中如果出现Using filesorted或Using temporary时，需要优化查询

7. 聚合函数max() 函数优化
    原sql：select max(money) from account;
    优化：给字段money加上索引，之后再次执行查询时会直接检索索引文件得到结果，不必全表扫描（这种索引也叫覆盖索引）

8. 优化子查询
    原sql：select * from t where t.id in (select t1.tid from t1);
    优化：select * from t join t1 on t.id = t1.tid;
    如果存在一对多关系，为防止查出字段冗余可以使用distinct关键字去重；

9. 优化group by
    原sql：
    select actor.first_name, actor.last_name, count(*) from film_actor inner join actor using (actor_id) 
        group by film_actor.actor_id;
        改查询会对actor表进行全表扫描并生成中间表，而且出现了using filesorted
    优化：
    select actor.first_name, actor.last_name, c.cn from actor inner join (select actor_id, count(*) as cnt 
        from film_actor group by actor_id) as c using (actor_id);

10. 优化limit与order by
    两者一般都是一起使用
    原sql：
    select film_id, description from film order by title limit 20, 5;
    explain分析 进行了全表到秒并出现using filesorted
    优化：
        1> 使用优索引的列或主键进行order by排序
        例如：select film_id, description from film order by film_id limit 30, 5;
        这时候会扫描到前35行，越往后扫描数量越多，仍需继续优化
    2> 记录上次返回的数据中最后一条数据的主键id，用于下次查询进行过滤
        例如：select film_id, description from film where film_id > 35 and film_id <= 40 order by film_id limit 0, 5;
        这时候，每次分页查询都只会扫描pagesize行数据，后面的limit也是可有可无的；
        注意：如果主键为uuid等非自增列，可新增一个连续自增字段来实现。

11. 建立索引的建议
        并不是任何字段都需要建立索引，建立索引最好的备选数据列是出现在where、join、order by或group by中的列；
    而输出列并不是建立索引的优选列。
    1> 将离散程度较高的列放在联合索引的首位
        查看离散程度：
        select count(distinct uid), count(distince sid) from table;
        查得数值越大的，离散程度越高
    2> 索引并非越多越好，及时移除无用索引
        使用索引可以提高查询效率但是会降低写入效率，即影响insert、update操作效率
        数据库在使用索引前会进行分析，索引越多，分析越耗时
        去除重复索引和冗余索引

12. 数据库结构优化
    1> 选择合适的数据类型
        使用可以存下你的数据的最小的数据类型
        使用简单的数据类型。int要比varchar类型在mysql处理上更简单
        尽可能使用not null定义字段
        尽量少用或不用text类型，非用不可时最好考虑分表，将大文本字段存入附加表中

    例如：可使用int类型来存储时间，bigint来存储ip地址等
        INSERT INTO test(timestr) VALUES (UNIX_TIMESTAMP('2018-08-12 11:23:22'));
        SELECT FROM_UNIXTIME(timestr) FROM test;

        INSERT INTO sessions(ipaddr) VALUES (INET_ATON('192.168.1.102'));
        SELECT INET_NTOA(ipaddr) FROM sessions;
    2> 合理使用范式化和反范式化设计表结构
        范式化一般是数据库的三范式
        反范式化指的是有时候为了提高查询效率会建立一些可能违反范式化的冗余字段
    3> 垂直拆分
        提出一些很影响查询或io效率的字段为一张附加表
    4> 水平拆分
        即进行分表操作，hash(userid) % 3 安取模结果分别放入不同数据库的同一张表或者统一数据库的不同表中，
    这里的不同表是由与数据量过大，将一张表拆分成了几张结构相同的表分别存储数据来提高操作效率

13. 系统配置优化
    1> 操作系统配置优化
        增加tcp支持的队列数量：
        在/etc/sysctl.conf文件中增加 net.ipv4.tcp_max_syn_backlog = 65535
        缩短断开连接时资源回收的时间，保持活跃连接数量最多
        在/etc/sysctl.conf文件中增加
        net.ipv4.tcp_max_tw_buckets = 8000
        net.ipv4.tcp_tw_reuse = 1
        net.ipv4.tcp_tw_recycle = 1
        net.ipv4.tcp_fin_timeout = 10
    2> mysql配置文件优化
        配置文件路径一般是 /etc/my.cnf或/etc/mysql/my.cnf文件
        常用参数：
            innodb_buffer_pool_size     innodb缓冲池大小
            innodb_buffer_pool_instances     innodb缓冲池数量（5.5之后的新增参数）
            innodb_log_buffer_size     缓冲日志的大小
            innodb_flush_log_at_trx_commit 
                对innodb的IO效率影响很大。默认值为1，可以0、1、2三个值：
                1 表示每次提交都将数据变更刷新到磁盘上（安全性最高）
                0 每次提交时不会立即刷新到磁盘上，而是每隔1秒刷新到磁盘一次
                2 表示每次提交都会刷新到缓冲区，再每隔1秒从缓冲区刷新到磁盘一次
            innodb_read_io_threads    读取的线程数量，默认4（5.5之后的新增参数）
            innodb_write_io_threads    写入的线程数量，默认4（5.5之后的新增参数）
            innodb_file_per_table      
                控制Innodb每个表的独立空间，默认为OFF，即所有表都会建立在共享表空间中
                建议设置为ON，可以增加读写效率
            innodb_stats_on_metadata    
                mysql刷新innodb表的统计信息的时机，默认为ON，建议OFF

        快速配置的第三方工具：
            https://tools.percona.com/wizard

14. 服务器硬件优化
    尽量选择单核性能较高的cpu，因为mysql很多操作都只能单核运行
    硬盘优化