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%