mysql--数据库优化

慢查询日志：

show variables like 'slow_query_log';//查询慢查询日志

set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log';//慢查询日志存储文件

set global log_queries_not_using_indexes=on;//记录没有索引的

set global long_query_time=1;//限制超过多少秒记录

show variables like 'slow_query_log';//查看慢查日志开关状态

show variables like '%log%';//查看日志设置信息

show variables like 'long_query_time';//查看sql语句最长运行设置时间

show variables like 'slow%';//查看日志存储文件位置

慢查日志包含内容：

执行SQL的主机信息 #User@Host:root[root] @ localhost []

SQL的执行信息 #Query_time:0.0001【执行时间】 Lock_time:0.0001【发送时间】 Rows_sent:0【发送行数】 Rows_examined:0【扫描行数】

SQL执行时间  # timestamp=1402389328【时间戳】;

SQL的内容   select CONCAT('storage engine:',@@storage_engine) as INFO;

慢查日志分析工具：

1、mysqldumpslow   -h[帮助（ 使用命令）]【mysql安装后自带】

使用mysqldumpslow,mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log ｜ more;//查看前三条日志

2、pt-query-digest  -help[查看使用命令]

pt-query-digest -t 3 /home/mysql/data/mysql-slow.log ｜ more;//查看前三条日志

如何通过慢查日志发现有问题的sql

1、查询总次数多且每次查询占用时间长的SQL

    通常为pt-query-digest分析的前几个查询

2、IO大的SQL

    注意pt-query-digest分析中的Rows examine项

3、未命中索引的SQL

    注意pt-query-digest分析中Rows examine和Rows Send对比

如何分析SQL查询

使用exolain查询SQL的执行计划

     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上，而不是GROUP BY上

索引优化

如何选择合适的列建立索引？

    1、在where从句，group by从句，order by从句，on从句中出现的列

    2、索引字段越小越好

    3、离散度大的列放到联合索引的前面

#查找重复及冗余索引【在information_schema中执行】

SELECT A.TABLE_SCHEMA AS  '数据库名'

    ,a.table_name as '表名'

    ,a.index_name as '索引1'

    ,b.index_name as '索引2'

    ,a.column_name as '重复列名'

FROM STRTISTICS 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 and a.column_

name=b.column_name WHERE a.SEQ_IN_INDEX=1 and a.index_name <>b.index_name;

也可以使用pt-duplicate-key-checker工具检查重复及冗余索引

pt-duplicate-key-checker \
-u root \
-p '密码' \
-h 127.0.0.1

删除不用的索引

通过慢查日志配合pt-index-usage工具进行索引使用情况分析

pt-index-usage \
-u root -p '密码' \
mysql-slow.log