Mysql优化学习笔记

看了一些关于数据库优化的视频，把我的总结记录于此(数据来源于sakila数据库)，有些地方总结不足，理解不到位，还请指正:
数据库优化目的：
    避免出现页面访问错误：
        由于数据库连接timeout产生页面5xx错误
        由于慢查询造成页面无法加载
        由于阻塞造成数据无法提交
    增加数据库的稳定性：
        很多数据库问题都是由于低效的查询引起的
    优化用户体验：
        流畅页面的访问速度
        良好的网站功能体验
数据库优化方向：硬件->系统配置->数据库表结构->sql及索引(成本越来越低，效果越来越好)

一 sql优化
    查看是否开启慢查询日志  show variables like 'slow_query_log';
    看哪些sql没有使用索引  show variables like '%log%';
    设置日志路径  set global slow_query_log_file = '/home/mysql/sql_log/slow.log';
    是否将没有使用索引的sql记录到日志中  set global log_queries_not_using_indexes = on;
    超过多少秒的查询记录到慢查询中   set global long_query_time = 1(这里是大于1s,通常100ms)
    开启慢查询日志  set global slow_query_log = on;
    慢查询日志所包含的内容：执行SQL的主机信息  SQL的执行信息  SQL执行时间  SQL的内容

慢查询日志分析工具
    1 mysqldumpslow
    Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
    mysqldumpslow  -h 查看参数

    2 pt-query-digest
    输出到文件 pt-query-digest slow-log > slow_log.report
    输出到数据库表  pt-query-digest slow.log -review \
    h = 127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
    --create-reviewtable \
    --review-history t=hostname_slow
    pt-query-digest  --help 查看参数

如何通过慢查询日志发现有问题的SQL
    1  查询次数多且每次查询占用时间长的SQL
        通常为pt-query-digest分析的前几个查询
    2 IO大的SQL
        注意pt-query-digest分析中的Rows examine项
    3 未命中索引的SQL
        注意pt-query-digest分析中Rows examine(索引扫描行数)和Rows Send(索引发送行数)对比
        索引扫描行数远远大于索引发送行数说明命中率低
        数据库的主要瓶颈主要出现在IO上

如何分析SQL查询
    使用explain查询SQL的执行计划
    for example: explain select customer_id,first_name,last_name from customer;
    table:显示这一行的数据是关于哪张表的
    type:重要列！显示连接使用何种类型。最好到最差为:const(常数查找),eq_reg(范围查找),ref(常见于连接的查询),range(基于索引的范围查找),
    index(对索引的扫描进行操作),all(表扫描)
    possible_key:显示可能应用在这张表中的索引。如果为空，没有可能的索引。
    key:实际使用的索引。如果为NULL，则没有使用索引。
    key_len:使用的索引的长度。在不损失精确性的情况下，长度越短越好。
    ref:显示索引的哪一列被使用了，如果可能的话，是一个常数
    rows:MYSQL认为必须检查的用来返回请求数据的行数
    extra: Using filesort看到这个的时候，查询需要优化了，MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序
    键值和匹配条件的全部行的行指针来    排序全部行
    Using temporary看到这个的时候，查询需要优化了，这里，MYSQL需要创建一个临时表来存储结果，
    这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

优化max()
   在查询列上建索引 (索引是按顺序排列的)
优化count()
   需要具体SQL进行分析，但是要注意count()函数的计算不包含NULL
子查询优化
   通常情况下，需要把自查询优化为join查询，但在优化时要注意关键键是否有一对多的关系，要注意重复数据。
   select * from t where t.id in (select t1.tid from t1);
   select distinct t.id from t join t1 on t.id = t1.tid;
GROUP BY查询优化
   原语句：explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor \
              inner join sakila.actor using(actor_id) \
              group by film_actor.actor_id;
   优化后：explain select actor.first_name,actor.last_name,c.cnt from sakila.actor \
              inner join(select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c using(actor_id);
优化limit查询
   limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用filesorts这样会造成大量的IO问题  
   原语句：select film_id,description from sakila.film order by title limit 50,5;
   1 使用有索引的列或主键进行order by操作
       select film_id,description from sakila.film order by film_id limit 50,5;
   2 记录赏赐返回的主键，在下次查询时使用主键过滤
       select file_id,description from sakila.film where film_id > 55 and file_id <= 60 order by film_id limit 50,5;
       (要求主键事先排好序)  避免数据量大时扫描过多的记录


二 索引优化
  如何选择合适的列建立索引
    1 在where从句，group by从句，order by从句，on从句中出现的列
    2 索引字段越小越好
    3 离散度大的列放到联合索引的前面
        select * from payment where staff_id = 2 and customer_id = 584;
        是index(staff_id,customer_id)好?还是index(customer_id,staff_id)好?
        如何比较离散度？select count(distinct customer_id),count(distinct staff_id) from payment;
        由于customer_id离散度更大，所以应该使用index(customer_id.staff_id)
    索引的维护及优化--查找重复及冗余索引
    工具:pt-duplicate-key-checker 检查重复及冗余索引
    pt-deplicate-key-checker -uroot -p'passwd' -h 127.0.0.1
    索引的维护及优化--删除不用索引
    目前mysql中还没有记录索引的使用情况，但是在PersonMysql和MariaDB中可以通过INDEX——STATITICS表来查看哪些索引未使用，
    但在Mysql中目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析
    pt-index-usage -uroot -p'passwd' mysql-slow.log
    pt工具下载地址:

三 数据库结构优化
    1 选择合适的数据类型
        使用可以存下数据的最小数据类型
        使用简单的数据类型，INT要比VARCHAR类型在Mysql上 处理简单点
        尽可能的使用NOT NULL定义字段
        尽量少用text类型，非用不可时最好考虑分表
    例子：
        1 使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数进行转换
        CREATE TABLE test(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,timestr INT NOT NULL);
        INSERT INTO test(timestr) VALUES(UNIX_TIMESTAMP('2014-11-22 17:12:00'));
        SELECT FROM_UNIXTIME(timestr) FROM test;
        2 使用bigint来存储IP地址，利用INET_ATON(),INET_NTOA()两个函数进行转换
        CREATE TABLE sessions(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,ipaddress BIGINT NOT NULL);
        INSERT INTO sessions(ipadress) VALUES(INET_ATON('192.168.0.100'));
        SELECT INET_NTOA(ipaddress) FROM sessions;
    2 表的范式化与反范式化
        范式化：是指数据库设计的规范，一般指第三设计范式，也就是要求数据表中不存在非关键字段对任意候选字段的传递函数依赖则符合第三范式
        反范式化：是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优优化查询效率的目的，反范式化是一种以空间换时间的操作
    3 表的垂直拆分与水平拆分

四 系统配置优化
    1 操作系统配置优化
        vim /etc/sysctl.conf
        #增加tcp支持的队列数
        net.ipv4.tcp_max_syn_backlog = 65535
        #减少断开连接时，资源回收
        net.ipv4.tcp_max_tw_bucket = 8000
        net.ipv4.tcp_tw_reuse = 1
        net.ipv4.tcp_tw_recycle = 1
        net.ipv4.tcp_fin_timeout = 10
    打开文件数的限制  ulimit -a查看目录的各位限制
        vim /etc/security/limits.conf
            * soft nofile 65535
            * hard nofile 65535
    2 配置文件优化
        innodb_buffer_pool_size 用于配置innodb的缓冲池，如果数据库只有innodb，推荐为总内存的75%
        innodb_buffer_pool_instances  Mysql5.5中新增加参数，可以控制缓冲池的个数，默认为一个
        innodb_log_buffer_size  innodb log缓冲的大小，由于日志最长每秒钟就会刷新，一般不用
        innodb_flush_log_at_trx_commit  关键参数，对innodb的IO效率影响很大，默认为1，可以取0，1，2三个值，一般建议为2，对数据安全高则使用1
        innodb_read_io_threads  innodb_write_io_threads 决定innodb读写的IO进程数，默认为4
        innodb_file_per_table 关键参数，控制innodb每一个表使用独立的表空间，默认为Ooff，也就是所有表都会建立在共享表空间中，建议为on
        innodb_stats_on_metadata  决定Mysql在什么情况下会刷新innodb表的统计信息，建议为onff
    3 借助第三工具进行配置
        

五 服务器硬件优化
    1 Mysql有些工作只能使用单核CPU
        Replicate,SQL....
    2 Mysql对CPU核数的支持并不是越多越好
        Mysql5.5使用的服务器不要超过32核
    3 Disk IO优化 建议使用RAID 1+0  SAN和NAT是否适合数据库？