mysql select 性能_Mysql查询性能优化

1、分析慢查询日志

(1)慢查询日志特点与参数

写日志是顺序存储，对空间需求较大。

慢查日志 会记录所有符合条件的sql，包括回滚日志。

show_query_log： 启动停止慢查询日志 可以运行中更改 set global show_query_log=on

通过脚本定时开关

show_query_log_file ：指定慢查日志的存储路径和文件 日志存储和数据存储分开存储。存储在不同的磁盘分区更好

long_query_time： 指定慢查日志sql执行时间的阈值 超过此值的查询会记录到日志中。单位是秒 100微秒 = 0.0001秒。默认10秒。

log_queries_not_using_indexes ： 是否记录未使用索引的sql。即使查询执行的小于long_query_time ，只要没使用索引，就会被记录。会避免暂时数据量小执行可以接受，但是数据量大的时候将变慢的查询。

(2)分析工具

工具：

mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar)

按照哪种排序方式输出结果

c: 查询总次数

t:查询总时间

l:锁时间

r:总数据行

这些参数前边加a指的是平均。

-t :指的是前几条

其他工具

apt-get install percona-toolkit

pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssW0rd slow-mysql.log

2、实时获取性能状态

可以查找Processlist 表。使用如下命令

select id,user,host,DB,command,time,state,info from information_schema.PROCESSLIST where time >=60;

3、mysql解析预处理参数

查询缓存是hash全值匹配。

query_cache_type设置查询缓存是否可用 不使用查询缓存 建议 off

query_cache_size 查询缓存的大小 建议0

4、确定查询处理各个阶段所消耗的时间

(1) 使用profile

set profiling = 1

启用之后会记录每次查询的时间

show profiles

查看第N个查询消耗的总时间的信息

show profile for query N

可以看到Sending data 用时最多

Sending data 通常可以使用索引来优化。

查看第N个cpu的使用信息

(2)使用performance_schema

performance_schema 是 mysql 5.5 引入的存储引擎

启用：

UPDATE `setup_instruments` SET `enabled` = 'YES', `TIMED` = 'YES' WHERE NAME LIKE

'stage%';

UPDATE `setup_consumers` SET `enabled` = 'YES' WHERE NAME LIKE

'events%';

可以分析mysql数据库中每一个线程的性能

查询mysql 性能的sql语句：

SELECT a.`THREAD_ID`, SQL_TEXT, c.EVENT_NAME,(c.TIMER_END-c.TIMER_START)/1000000000 AS DURATION

FROM events_statements_history_long a

JOIN threads b on a.`THREAD_ID`=b.`THREAD_ID`

JOIN events_stages_history_long c ON c.`THREAD_ID`=b.`THREAD_ID` AND c.`EVENT_ID` BETWEEN a.`EVENT_ID` AND a.`END_EVENT_ID`

ORDER BY a.`THREAD_ID`,c.EVENT_ID

5、大表处理

对于大表数据可以分批处理。

大表结构：因为改变表结构会锁表。可以通过下列方式完成大表结构的修改：

建立新表 新表结构就是需要的结构；原表增加触发器；原表数据拷贝到新表；同步完成对老表加一个排它锁；重新命名新表为老表的名字；删除重命名的老表。

使用如下命令即可

pt-online-schema-change --alter=”MODIFY c VARCHAR(150) NOT NULL DEFAULT ‘’ ” --user=root --password=PassWord D=dbname,t=tablename--charset=utf8 --excute