Mysql SQL查询优化

Mysql SQL查询优化

概述

一般在使用软件系统时，随着时间的积累，MYSQL数据库中的数据随之也增大，单表达到百万级别，如果不优化SQL查询，则响应速度会成为用户的恶梦，降底用户使用率。

一、索引

索引优化方面， 一般从以下几个维度思考

1. SQL加索引吗？
2. 索引是否真的生效？
3. 索引建立是否合理？

1.1SQL没加索引
开发人员在开发的时候，容易疏忽而忘记给SQL增加索引，所以在使用SQL时，就随便查看下explain计划。
如

EXPLAIN SELECT * FROM TABLE WHERE XXX=XXX`

如果表忘记增加索引，可以通过

alter table xxxx add index idx_name(column name)

一般就是：SQL的where条件的字段，或者是order by 、group by后面的字段需需要添加索引

2.1索引不生效
有时候，即使你添加了索引，但是索引会失效的，如下列

• 隐式的类型转换
• 查询条件包含OR ,可能导致索引失效
• like 通配符
• 查询条件不满足联合索引的最左匹配原则
• 在索引列上使用MYSQL函数
• 对索引进行列运算（+，-，*，/）
• 索引字段上使用（！= 或者< >）
• 索引字段上使用is null , is not null
• 左右连接，关联字段编码格式不一致
• 优化器选错索引
  3.1索引设计不合理
  索引不是越多越好，需要合理设计，如：
• 删除冗余和重复索引
• 索引一般不超过5个
• 索引不适合建在大量重复数据字段上，如性别
• 适当使用覆盖索引
• 如果需要使用force index强制走某个索引，那就需要思考你的索引设计是否真的合理了

二、优化SQL

索引优化，其实就是SQL还有很多其它优化的空间。

• select具体字段而不是select *
• 多用limit
• 尽量用union all 替换union
• 优化group by
• 优化order by
• 小表驱动大表
• 字段类型使用合理
• 优化limit分页
• exist & in的合理使用
• join关联表不宜过多
• in 不要过多

三、深度分页问题

以前分析几个接口耗时长的问题，最终结论都是深度分而问题。那深度分页为什么慢，如下

select id ,name from account where create_date > '2000-01-01' limit 100000,10

而limit 100000,10意味，需要扫描100010行，丢弃掉前100000行，最后返回10行。即使create_date，也会回表很多次。

一般可以通过标签记录法和延迟关联法来优化深分页问题
3.1 标签记录法
如上一次记录100000，则sql 可修改

select id ,name from account where id >100000 limit 10;

后面不管多少页，性能都不会差。命中了id主键索引。但是也有局限性：需要一种类似连续自增的字段。
3.2 延迟关联法来
延迟关联法，就是将条件转移到主键索引上，从而减少回表。优化后的SQL如下：

select id ,name from account b INNER JOIN (SELECT id FROM account  WHERE create_date > '2020-09-19' limit 100000, 10) AS c on c.id= b.id;

优化思路就是 ，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。
特别在使用SQL时，多表关联left join table on xxx ,先将有效果的DATA 满足条件之后，再去left join ，SQL效率会提升不少。

四、海量数据查询，考虑使用NO SQL

  面对海量数据查询的时候，一般SQL 都是跟深分页问题有关的。从而发现，**使用标签记录法和延迟关联法，效果不是很明显 ，原因是要统计和模糊搜索，并且统计的数据是真的大**。
  通过部门讨论，可以将数据同步到Elasticsearch，然后这些模糊搜索需求，都走Elasticsearch去查询了，再或者一定要用关系型数据库存储的话，就可以分库分表。但是有时候，我们也可以使用NoSQL，如Elasticsearch、Hbase等