Mysql优化

1、show variables like 'slow_query_log'
2、set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'//指定慢查询存储位置
3、set global log_queries_not_using_indexes=on;//指定是否要把没有使用索引的sql记录到慢查询日志中
4、set global long_query_time=1//（秒）把超过一定时间的sql记录到慢查询日志中


如何通过慢查询日志发现有问题的SQL:
1.查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询
2.IO大的SQL
注意pt-query-digest分析中的ROWS examine项（扫描的行数越大，占用IO越多）
3.未命中索引的SQL
注意pt-query-digest分析中的ROWS examine（扫描行数）和ROWS Send（发送行数）的对比
如果rows examine 远远大于rows send 说明索引命中率低

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

子查询优化
    通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。
 优化group by查询
    eg:(Extra 返回信息：using filesort，using temporary)
    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;
    优化后：(Extra 返回信息using INDEX)
    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;(Extra 返回USING filesort)
    优化步骤1：使用有索引的列或主键进行ORDER BY 操作
        SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5;
    优化步骤2(避免数据量大时扫描过多的记录)：记录上次返回的主键，在下次查询时使用主键过滤
    SELECT film_id,description FROM sakila.film WHERE
    film_id>55 and film_id<=60 ORDER BY film_id LIMIT 1,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)好？还是index
    由于customer_id 的离散度更大，所以应该使用index(customer_id,staff_id)
 当索引包含了查询中所有列，称为覆盖索引

 判断离散度：
    desc tablename;
    查看字段的唯一值，唯一值越多离散度越高
    SELECT COUNT(distinct customer_id),COUNT(distinct staff_id) FROM payment;

索引的维护及优化---删除不用索引
    目前MySql中还没有记录索引的使用情况，但是在PerconMySql和MariaDB中可以通过INDEX_STATISTICS
    表来查看哪些索引未使用，但在MySql中目前只能通过慢查询日志配合pt-index-usage工具来进行索引使用
    情况的分析。
    pt-index-usage  -uroot -p '' mysql-slow.log
索引的维护及优化--重复及冗余索引
    重复索引是指相同的顺序建立的索引，如下表中primary key和ID列上的索引就是重复索引
     create table test(
     id it not null primary key,（主键一般就是唯一索引，下面又建立了唯一索引，造成重复）
     name varchar(10) not null,
     title varchar(50) not null,
     unique(id))engine=innodb;
     冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个列子中key(name,id)
     就是一个冗余索引。(innodb会在联合索引的后面加上主键，认为在联合索引后面添加主键，造成冗余索引)
    create table test(
     id it not null primary key,
     name varchar(10) not null,
     title varchar(50) not null,
     key(name,id)
     )engine=innodb;
     查找重复及冗余索引
     SELECT a.TABLE_SCHEMA AS '数据名',
        a.table_name AS '表名',
        a.index_name AS '索引1',
        b.INDEX_NAME AS '索引2',
        a.COLUMN_NAME AS '重复列名'
        FROM STATISTICS a JOIN STATISTICS b ON a.TABLE_SCHEMA=
        b.TABLE_SCHEMA AND a.TABLE_NAME=b.table_name
        AND a.SEQ_IN_INDEX=b.SEQ_IN_INDEX AND a.COLUMN_NAME=
        b.COLUMN_NAME WHERE a.SEQ_IN_INDEX=1 AND a.INDEX_NAME<>b.INDEX_NAME；
        pt-duplicate-key-checker工具
数据库结构优化：
    选择合适的数据类型：
        数据类型的选择，重点在于合适二字，如何确定选择的数据类型是否合适？
        1、使用可以存下你的数据的最小的数据类型
        2、使用简单的数据类型。int要比varchar类型在mysql处理上简单
        3、尽可能的使用not null定义字段
        4、尽量少用text类型，费用不可时最好考虑分表
    使用int来存储日期时间，利用FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转换：
        CREATE TABLE test(id AUTO_INCREMENT) NOT NULL,timestr INT,PRIMARY KEY(id);
        INSERT INTO test(timestr) VALUES(UNIX_TIMESTAMP('2014-06-01 13:12:00'));
        SELECT FROM_UNIXTIME(timestr) FROM test;
    使用bigint来存储ip地址，利用INET_ATON(),INET_NTOA()两个函数来进行转换：
    CREATE TABLE sessions(id AUTO_INCREMENT NOT NULL ,ipaddress,BIGINT,PRIMARY KEY(id));
    INSERT INTO sessions(ipaddress) VALUES(INET_ATON('192.168.0.1'));
    SELECT INET_NTOA(ipaddress) FROM sessions;
表的范式化和反范式化：
        范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，
        也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式
        存在以下传递函数依赖关系：

        （商品名称）->(分类)->（分类描述）
        也就是说存在非关键字段“分类描述”对关键字段“商品名称”的传递依赖。
    不符合第三范式要求的表存在下列问题：（对表进行拆分来解决）
    1、数据冗余：（分类、分类描述）对于每一个商品都会进行记录；
    2、数据的插入异常
    3、数据的更新异常
    4、数据的删除异常（2、3、4统称为写的异常）
        反范式化是指为了查询效率的考虑把原本符合第三范式的表适当增加冗余，以达到优化查询效率的目的，反范式化是一种以空间换时间的操作。


表的垂直拆分
    所谓垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度
    问题。通常垂直拆分可以按以下原则进行
    1、把不常用的字段单独存放到一个表中
    2、把大字段独立存放在一个表中
    3、把经常一起使用的字段放到一起
表的水平拆分：
    表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完整一致的。
    常用的水平拆分方法：
    1、对id进行hash运算，如果要拆分成5个表则使用mod()取出0-4个值
    2、针对不同的hashID把数据存放到不同的表中。
    挑战：
    1、跨分区表进行数据查询
    2、统计及后台报表操作
系统配置优化：
    操作系统配置优化
        数据库是基于操作系统的，目前大多数MySql都是安装在Linux系统之上，
        所以对于操作系统的一些参数配置也会影响到MysqL的性能，下面就列出一些常用的系统配置：
        网络方面的配置，要修改/etc/sysctl.conf文件
        #增加tcp支持的队列数
        net.ipv4.tcp_max_syn_backlog = 65535
        #减小断开连接时资源回收
        net.ipv4.tcp_max_tw_buckets=8000
        net.ipv4.tcp_tw_reuse=1
        net.ipv4.tcp_tw_recyle=1
        net.ipv4.tcp_fin_timeout=10
        打开文件数的限制，可以使用ulimit -a查看目录的各位限制，可以修改/etc/security/limits.conf文件，增加以下内容以修改打开文件数量
        的限制
        *soft nofile 65535
        *hard nofile 65535除此之外最好在MySql服务器上关闭iptables,selinux等防火墙软件。
    MySql配置文件
        MySql可以通过启动时指定配置参数和使用配置文件两种方法进行配置，在大数情况下
        配置文件位于/etc/my.cnf或是/etc/mysql/my.cnf在windowa系统配置文件可以是
        位于C:windows/my.ini文件，MySql查找配置文件的顺序可以通过以下方法获得：
        $ /usr/sbin/mysqld --version --help | grep -A 1 'Default options'
        注意：如果存在多个位置存放配置文件，则后面会覆盖前面的
        常用参数说明：
        1、innodb_buffer_pool_size
            非常重要的一个参数，用于配置innodb的缓冲池如果数据库中只有innodb表，
            则推荐配置量为总内存的75%
            SELECT ENGINE,ROUND(SUM(data_length+index_length)/1024/1024,1) AS "Total MB",
            FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in ("information_schema","performance_schema") GROUP BY ENGINE;
            Innodb_buffer_pool_size >= Total MB
        2、innodb_buffer_pool_instances
            MySql5.5中新增加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池。
        3、innodb_log_buffer_size
            innodb log 缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大。
        4、innodb_flush_log_at_trx_commit
            关键参数，对innodb的IO效率影响很大。默认值为1，可以取0（提交不刷新，每隔一秒变更刷新到磁盘）,1（提交变更刷新到磁盘）,2（提交刷新到缓冲区，每隔一秒刷新到磁盘）三个值，一般建议设为2.但如果数据安全要求比较高则使用默认值1
        5、innodb_read_io_threads/innodb_write_io_threads
            以上两个参数决定了innodb读写的io进程数，默认为4   
        6、innodb_file_per_table
        关键参数，控制innodb每一个表使用独立的表空间，默认为OFF，也就是所有表都会
        建立在共享表空间中
        7、innodb_stats_on_metadata
        决定利了Mysql在设么情况下会刷新innodb表的统计信息（一般设为OFF）
    第三方配置向导：
        https://tools.percona.com/wizard

硬件优化：
    如何选择CPU：
        思考：是选择单核更快的CPU还是选择核数更多的CPU？
            1、MySql有一些工作只能使用到单核CPU
                Replicate.SQL
            2、MySql对CPU核数的支持并不是越多越快
                MySql5.5使用的服务器不要超过32核
    DISK IO优化
        常用RAID级别简介
            RAID0:也称为条带，就是把多个磁盘连接成一个硬盘使用，这个级别IO最好
            RAID1：也称为镜像，要祈求至少有两个磁盘，每组磁盘存储的数据相同。
            RAID5:也是把多个（最少3个）硬盘合并成1个逻辑盘使用，数据读写时
            会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。当
            RAID5的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据
            RAID1+0：就是RAID1和RAID0的结合。同时具备两个级别的优点，一般建议数据库使用这个级别
        SNA和NAT是否适合数据库
            1、常用于高可用解决方案
            2、顺序读写效率很高，但是随机读写不如人意
            3、数据库suijiduxie比率很高