性能优化之MySQL优化

1 性能优化方向

硬件->系统配置->数据库表结构->SQL及索引

2 实验数据集

使用MySQL提供的sakila数据库

3 使用慢查询日志监控

开启慢查询日志

show variables like 'slow_query_log';
set global slow_query_log_file='/mysql/sql_log/mysql-slow.log'
set global log_queries_not_using_indexes=on;
set global slow_query_log=on;
set global long_query_time=1;

慢查询包含的内容：SQL执行的主机信息；SQL的执行信息；SQL的执行信息；SQL的内容

慢查日志的分析工具：

4 ubuntu启动慢启动日志

注意：mysql为自动安装

mkdir /var/lib/mysql/sql_log;
touch /var/lib/mysql/sql_log/mysql-slow.log;
chmod 777 /var/lib/mysql/sql_log;
chown mysql:root /var/lib/mysql/sql_log/mysql-slow.log;//注意：没有这句话，mysql无法写入慢日志

5慢查询日志分析工具pt-query-digest

软件安装

wget percona.com/get/percona-toolkit.tar.gz

tar -zxvf percona-toolkit-2.2.13.tar.gz 

cd percona-toolkit-2.2.13

perl Makefile.PL

make && make install

日志分析

pt-query-digest ubuntu-slow.log

6 如何通过慢查询发现问题SQL

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

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

2 IO大的SQL

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

3 未命中索引的SQL

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

7 Count()和Max()的优化

1) 优化MAX()给指定列加索引
2) 优化Count()

8 子查询的优化

通常情况下。需要把子查询优化为join查询，但在优化时要注意关联键是否一对多的关系，要注意重复数据。

9 group by

一定要在子查询中增加过滤条件，而不是在外层增加

 explain select actor.first_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id\G;  
//使用了临时表，会产生了大量的IO。
//explain可知，Using temporary; Using filesort

 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)\G;
 //虽然行数增加了，但是采用了索引，减少了io

10 limit优化

limit常用于分页处理，时常会伴随着order by从句使用，因此大多时候会使用filesorts这样会造成大量的IO问题
优化步骤1：使用索引列或者主键进行order by操作

select film_id,description from sakila.film order by title limit 50,5 \G

优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤

select film_id,description from sakila.film order by film_id limit 50,5 

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

1）在where从句，group by从句，order by从句，on从句中出现的列
2）索引字段越小越好
3）离散度大的列放在联合索引的前面

select * from payment where staff_id=2 and customer_id=584;
/*由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)*/

12 索引的维护及优化

使用优化工具去除冗余索引

 pt-duplicate-key-checker -u root -p '123456' -h 127.0.0.1

 pt-duplicate-key-checker --host=ip --user='username' --pwassword='' --databases=db

# ########################################################################
# sakila.payment                                                          
# ########################################################################

# idx_fk_customer_id is a left-prefix of test
# Key definitions:
#   KEY `idx_fk_customer_id` (`customer_id`),
#   KEY `test` (`customer_id`,`staff_id`),
# Column types:
#     `customer_id` smallint(5) unsigned not null
#     `staff_id` tinyint(3) unsigned not null
# To remove this duplicate index, execute:
ALTER TABLE `sakila`.`payment` DROP INDEX `idx_fk_customer_id`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   32172
# Total Duplicate Indexes  1
# Total Indexes            100
root@ubuntu:/var/lib/mysql# pt-duplicate-key-checker -u root -p '123456' -h 127.0.0.1
Skipping #mysql50#percona-toolkit-3.0.4... at /usr/local/bin/pt-duplicate-key-checker line 3457.
# ########################################################################
# sakila.payment                                                          
# ########################################################################

# idx_fk_customer_id is a left-prefix of test
# Key definitions:
#   KEY `idx_fk_customer_id` (`customer_id`),
#   KEY `test` (`customer_id`,`staff_id`),
# Column types:
#     `customer_id` smallint(5) unsigned not null
#     `staff_id` tinyint(3) unsigned not null
# To remove this duplicate index, execute:
ALTER TABLE `sakila`.`payment` DROP INDEX `idx_fk_customer_id`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   32172
# Total Duplicate Indexes  1
# Total Indexes            100

13 表的垂直拆分

垂直拆分的原则：
1）把不常用的字段单独存放在一个表中
2）把大字段独立存放在一个表中
3）把经常一起使用的字段放在一起

14 数据库配置优化

如果用于配置innoDB的缓冲池只有InnoDB表。则推荐配置量为总内存的75%