MySQL查询语句优化

在项目中经常和MySQL数据库打交道,写过各种各样的SQL,也遇到过各种问题,针对遇到的各种场景,记录一些解决方案,主要是MySQL索引问题。
Mysql语句的优化主要是在索引上下功夫,尽量使用索引,但是也要控制索引的长度和数量,尽管索引能加快查询速度,但是也会拖慢插入、更新速度。
至于索引无法解决的,那就分库,分表,读写分离,上集群吧

  1. 如果表中某个字段长度比较大,比如文章内容,上千个字符的,那么列表查询时尽量过滤掉,需要用到这个字段时再去查询
  2. where后面有多个条件时,mysql只会用一个索引(和条件的前后顺序无关),所以组合索引这时才有优势
  3. 使用explain查看时,如果有possible_keys,但是却不使用索引,就可以强制使用索引,from table_name force index (index_name) from的表名后面,但是不一定有用
  4. 不要在索引列上进行函数运算
  5. Hash索引(只有Memory引擎支持,使用最多的是InnoDB引擎的B-Tree索引)只支持等值比较,例如使用=,IN( )和<= >=。对于WHERE price>100并不能加速查询。
  6. 如果mysql使用了缓存,就不一定会使用索引,用explain查看extra列,看是否使用了缓存
  7. 在innodb表上做count(*)查询是非常慢的,因为要对所有的结果进行扫描,特别是数据有几百万的时候,count查询是很慢的。解决办法就是:如果结果只有几千条,尽量采用前段分页,避免count查询
  8. 针对时间维度的报告,一定要把天和小时拆分成两个字段,否则对于时间维度的报告,如果对该字段使用了函数将无法使用到索引
  9. 列类型永远越短越好,建议:布尔/枚举:tinyint,日期与时间戳:timestamp或int,char/text/blob: 尽量用符合实际长度的varchar(n),小数及货币:移位转为int 或 decimal,IP地址:int。
  10. 慢查询日志一定要有
  11. DATE_FORMAT()函数在百万级数据查询时,对查询耗时是有秒级的影响,尽量避免
  12. not in 用不上索引,需要改成left join
  13. 创建表时,字符串类型一定要检查是否有COLLATE标识,如果两个表中字段标识不一致,会到时无法使用索引
  14. group by的字段中如果有text容易报out of sort memory,需要修改成varchar类型
JouyPub wechat
欢迎订阅「K叔区块链」 - 专注于区块链技术学习