一、解决什么问题？
前提： 两个数据库（开发和测试环境）拥有三张完全一样的表（索引、字段一样），其中每个表的数据在50万左右。
操作：在两个数据库上执行一条sql语句（该sql语句针对三张表进行了inner join 查询）
问题：在其中一个数据库执行sql语句查询只需要176ms，而在另一个数据库执行sql确需要18s
mysql1.png
二、分析过程
- 通过explain分析sql发现相同的sql语句在不同的数据库上使用了不同的驱动表，mysql的内部优化使执行顺序发生了变化,其中执行较快的使用了pi作为驱动表，执行较慢的使用了zo作为驱动表，并且使用了不同的索引
- 既然如此进一步分析mysql的驱动规则：
- 使用left join时 左表是驱动表
- 使用right join时 右表是驱动表
- 使用inner join时 小表驱动大表（在被驱动表建立索引，才能使用到索引）
驱动表可以直接排序，而非驱动表的排序需要对循环查询的合并结果进行排序
- 既然发现了是驱动表以及索引以及排序导致的问题，目前通过强制驱动表(straight_join)、强制索引、或者修改排序使用驱动表排序解决问题，但是mysql为什么会被优化成现在的样子仍然是一个迷......
--- 强制索引
select *
from table_1 force index ()
inner join table_2;
- 通过执行analyze table解决问题
- analyze用来修复表的统计信息包括索引
mysql的optimizer（优化元件）在优化SQL语句时，首先需要收集一些相关信息，其中就包括表的cardinality（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了
- Optimize table 是减少存储碎片的
重新利用未使用的空间，并整理数据文件的碎片，Optimize在执行时会锁表
三、总结
- 经常更新的数据库应该定期执行analyze和Optimize
- 一些数据库创建索引的经验
- 索引实际上也是一张表，保存了主键和索引字段，并指向实体表的记录
- 索引提高了数据检索的效率，但同时会降低更新表的速度
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询外键关系建立索引
- 查询排序、分组字段建立索引
- 表记录太少、数据重复、频繁更新字段不适合创建索引
- 唯一性太差的字段不适合创建索引（状态）
- 不会出现在where中的条件不适合创建索引
- 尽量使用数据量小的创建索引（varchar(10)创建比varhchar(100)好）
- 删除不再使用的索引
- 经常和其他表连接的表，在连接字段上应该创建索引
- 模糊匹配不能使用索引如 '%%', 'X%'可以使用索引
- mysql的索引根据查询的内容使用不同的索引，有时候可能会导致索引使用不上，针对多表链接查询建议先根据条件查询出id，在通过in 来查询出所需要的内容
- 最近使用mysql查询特别是几十张表join的时候往往莫名其妙其中一张表的索引就使用不上了，然后查询就超级慢，去掉这一张表就很快了（通过14的方式解决了这个问题，但是仍然很懵逼为什么使用不上索引）
- mysql的优化只能说：路漫漫其修远兮，吾将上线而求索！
网友评论