在项目中经常和MySQL数据库打交道，写过各种各样的SQL，也遇到过各种问题，针对遇到的各种场景，记录一些解决方案，主要是MySQL索引问题。
Mysql语句的优化主要是在索引上下功夫，尽量使用索引，但是也要控制索引的长度和数量，尽管索引能加快查询速度，但是也会拖慢插入、更新速度。
至于索引无法解决的，那就分库，分表，读写分离，上集群吧
- 如果表中某个字段长度比较大，比如文章内容，上千个字符的，那么列表查询时尽量过滤掉，需要用到这个字段时再去查询
- where后面有多个条件时，mysql只会用一个索引（和条件的前后顺序无关），所以组合索引这时才有优势
- 使用explain查看时，如果有possible_keys，但是却不使用索引，就可以强制使用索引，from table_name force index (index_name) from的表名后面，但是不一定有用
- 不要在索引列上进行函数运算
- Hash索引(只有Memory引擎支持，使用最多的是InnoDB引擎的B-Tree索引)只支持等值比较，例如使用=，IN( )和<= >＝。对于WHERE price>100并不能加速查询。
- 如果mysql使用了缓存，就不一定会使用索引，用explain查看extra列，看是否使用了缓存
- 在innodb表上做count(*)查询是非常慢的，因为要对所有的结果进行扫描，特别是数据有几百万的时候，count查询是很慢的。解决办法就是：如果结果只有几千条，尽量采用前段分页，避免count查询
- 针对时间维度的报告，一定要把天和小时拆分成两个字段，否则对于时间维度的报告，如果对该字段使用了函数将无法使用到索引
- 列类型永远越短越好，建议：布尔/枚举：tinyint，日期与时间戳：timestamp或int，char/text/blob: 尽量用符合实际长度的varchar（n），小数及货币：移位转为int 或 decimal，IP地址：int。
- 慢查询日志一定要有
- DATE_FORMAT()函数在百万级数据查询时，对查询耗时是有秒级的影响，尽量避免
- not in 用不上索引，需要改成left join
- 创建表时，字符串类型一定要检查是否有COLLATE标识，如果两个表中字段标识不一致，会到时无法使用索引
网友评论