Mysql的优化思路

日志、执行计划、索引、缓存（查询缓存和索引缓存）、SQL优化、分区、分表、分库、修改数据库的配置

  1. 日志：开启long query和log_queries_not_using_indexes的日志，帮助定位问题。
  2. 执行计划：查看执行计划是否建立和走了正确的索引，以及索引的优化，联合索引引用的长度
  3. 其它手段：海量数据的话，就要结合业务考虑表的存储引擎、缓存查询、分区（水平和垂直分区）、分表、读写分离、集群、读写分离
  4. 修改数据库的配置：比如缓存配置 Buffer Pool的size，通过缓存来提速。

慢查询、非索引查询的日志功能

  1. show variables like '%quer%';
  2. 查看 slow_query_log 是否开启,和超时记录的时间。
  4. set GLOBAL slow_query_log='ON';
  5. set GLOBAL long_query_time=2; #要重新打开连接才会生效
  6. select sleep(3);
  8. show variables like '%log_output%'; #查看输出日志的格式是 log日志 还是表数据
  9. show global status like '%slow%'; 所有慢查询的次数
  11. # 设置表数据存储慢查询
  12. set global log_output='FILE,TABLE';
  13. select * from mysql.slow_log;

Sql优化

  1. SELECT语句务必指明字段名称：
  • 避免了不需要查询字段的IO
  • 有时候可以使用覆盖索引会更快
  1. limit优化
  • 只需要查询一条数据的时候，加上 limit 1，减少不必要的搜索
  • 大表做limit查询的时候，可以用 范围查找时（ID连续），不要用limit查找，例如 between id>100000 limit 5
  • 大表分批读取，使用limit+覆盖索引效果更好
  1. 必要时可以使用force index来强制查询走某个索引
  2. 其它的SQL优化参考索引的优化

查询优化思路

SQL查询慢的两个原因：搜索慢 、查询的结果太大（IO开销大)

  1. 查的结果太大：
  • 数据太多，可以用分页分批查询，不仅可以降低IO的开销，也可以降低java存储的内存开销
  • 字段太多，特别是一些大的字段，只查询自己需要的字段，降低IO开销
  • SELECT 不要跟 号，可以避免 的转义。
  1. 搜索慢的解决思路：
  • 是否正确的使用好的索引（一张表的一次查询只能使用一个索引）
  • SQL是否可以优化，比如大表的limit查询、In查询不如用范围查询来的好，between and。 select *
  • 根据业务场景 分区、分表、分库、读写分离
  • 查询结果缓存：Mysql自带的缓存功能，或者依赖第三方做缓存（Ehcache、Redis等）（在业务低峰期做定时任务加载最新的数据到缓存中）
  • 业务上是否可以妥协，不要对过多的字段做分组、排序
  • 存储引擎的选择，多读少写的场景下可以考虑用Myisam，一级索引快。如果都是精确查询的话，可以考虑用Memory引擎。
  1. 其它辅助
  • explain 查看执行计划
  • 开启Mysql的慢查询日志功能，查看哪些SQL慢了
  • 或者使用Druid、cat的监控功能