MySQL优化（总结）

数据库优化的目的

避免出现页面访问错误

•   由于数据库连接timeout产生页面5xx错误
  

•   由于慢查询造成页面无法加载
  

•   由于阻塞造成数据无法提交
  

增加数据库的稳定性

•   很多数据库问题都是由于低效的查询引起的
  

优化用户体验

•   流畅页面的访问速度
  

•   良好的网站功能体验
  

SQL语句优化

使用MySQL慢查日志对有效率问题的SQL进行监控

• 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
  
  
  
  
  

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

1. 查询次数多且每次查询占用时间长的SQL
   通常为pt-query-digest分析的前几个查询
2. IO大的SQL
   注意pt-query-digest分析中Rows examine 项
3. 未命中索引的SQL
   注意pt-query-digest分析中Rows examine 和Rows Send的对比

如何分析SQL查询

COUNT()和MAX()的优化方法

• 给pyment_date建立索引
• create index idx_payment payment(pay_date)
  
  

子查询的优化

• select t.id from t where t.id in (select t1.tid from t1);
  等同于
• select distinct t.id from t join t1 on t.id=t1.id;
  
• select distinct title,release_year,length from film join film_actor on film.film_id=film_actor.film_id join actor on film_actor.actor_id=actor.actor_id where actor.first_name=‘sandra’;

group by的优化

limit查询的优化

索引优化

选择合适的列建立索引

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)好？
  

•   	由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)
  

•   	如何查询离散度？
  

•   	select count(distinct customer),count(distinct staff_id) from payment;
  

##### 如何查找重复及冗余索引

• 在information_schema库下进行
  

数据库结构优化

• from_unixtime(),int类型的时间戳转成时间格式
• unix_timestamp(),时间格式转成时间戳
  
  
  
  
  
  
  
• 将title和description拆分
  
  
  
• 前台使用拆分后的表，后台使用汇总表

数据库系统配置优化

第三方配置工具使用

服务器硬件优化