优化建议
数据库中最大的性能瓶颈就是磁盘io，主要体现在读写前寻找磁道的过程中；另一个影响性能因素是内存，innodb在内存中开辟了一个Buffer_Pool缓冲池，然后把数据页和索引页都放在内存缓冲池中读写，影响缓冲池的参数是innodb_buffer_pool_size，如果仅有innodb存储引擎的数据库服务器上，可以设置为60%的内存
对于同一份数据，当我们使用不同的方式去寻找其中的内容时，所需要读取的数据量可能是天壤之别，所消耗的资源相差也很大
join语句优化
使用小表驱动大表(小结果集驱动大结果集)，减少join语句的nestedLoop的循环总数
如 select * from admin left join log on log.admin_id = admin.id where log.admin_id > 10
就应该改为
select * from (select * from admin where id > 10) t1 left join log on log.admin_id = t1.id
使得左表尽可能的小
优先优化内层循环，内层循环是执行次数最多的
保证join语句中被驱动表上的join条件已被索引
尽量用join代替子查询(MySQL5.6及以上忽略该建议，优化提升的不多了)
MySQL5.6之前很多子查询会采用
DEPENDENT SUBQUERY（相关子查询）的执行方式，也就是外层查询每执行一行，子查询就会重新执行一次，会导致大量重复的查询操作；部分子查询会生成临时表来存储中间结果，创建和维护临时表会带来额外的开销，包括磁盘 I/O 和内存使用。MySQL 5.6 引入了子查询物化特性，MySQL 会将子查询的结果物化到一个临时表中，这个临时表只会被创建一次，然后外层查询可以直接从这个临时表中获取数据，而不需要每次都重新执行子查询
join buffer的大小对整个join语句的消耗起到关键的作用
减少排序
排序会消耗较多 CPU 资源，所以减少排序可以在缓存命中率高、IO 能力足够的场景下会影响 SQL 的响应时间
减少排序的方法
- 通过利用索引来排序的方式进行优化
- 减少参与排序的记录条数
- 非必要不对数据进行排序
减少or的使用
当 where 子句中存在多个条件以“或”并存的时候，MySQL 的优化器并没有很好的解决其执行计划优化问题，再加上MySQL 特有的 SQL 与 Storage 分层架构方式，造成了其性能比较低下，使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果
防止出现索引失效的情况
如以下情况应尽量避免
- 计算
- 使用not，<>，!=
- 使用IS NULL和IS NOT NULL
- 数据类型转换
- 对索引字段使用函数或者表达式
- 索引字段中有null
group by优化
group by中where高于having，能写在where中的限定条件就不要去having中限定
union优化
对于union查询，如果不需要去除重复行，建议使用union all，因为union会进行去重，给临时表加上distinct，导致对整个临时表的数据做唯一性检查
优化存储引擎配置
MyISAM存储引擎
减少碎片
删除和修改表数据之后可能会存在一些碎片，可以使用analyze table [tablename]来监测和重组表的关键字分布情况、optimize table [tablename]来恢复被删除的块和重组表。怎么判断该表是否要进行优化呢？可以通过show table status from [database] like '[tablename]';查看data_free字段，这个就表示碎片的内存
我用的版本是5.7，在执行
optimize table [tablename]报的是Table does not support optimize, doing recreate + analyze instead，这个可以在启动mysql的时候指定–skip-new来解决。也可以用ALTER TABLE [tablename] ENGINE='InnoDB';来代替optimize table [tablename]
优化配置
对于MyISAM存储引擎主要优化key cache来有效的使用内存。在查询MyISAM表时会先查询key cache，如果cache中存在索引，直接在cache中检索而不用在磁盘中检索了
可以通过show status like 'key%'命令和show VARIABLES like 'key%'查看状态和系统变量
使用多个Key Cache
除了使用默认的Key Cache，还允许一个或多个表的索引加载到自定义的特殊缓存中，来减少对默认Key Cache的争用。
创建二级Key Cache
SET GLOBAL ad_cache.key_buffer_size=128*1024;
使用
select @@global.ad_cache.key_buffer_size;来查询所设置的二级Key Cache
将表索引写入该缓存中
CACHE INDEX ad_log IN ad_cache;
如果想要删除或者刷新该key cache，可以将其key_buffer_size设为0
SET GLOBAL ad_cache.key_buffer_size=0;
Innodb存储引擎
可以查看文章 监控Innodb
v1.3.10