数据库SQL优化

数据过多

无索引

全模糊查

is not null

!=或<>

字符串不加单引号

or连接查询

索引失效

没有充分利用到索引

关联查询有太多的join(设计缺陷或不得已的需求)

全模糊查询

...

sql写法待优化

数据库表结果设计不合理

数据库配置参数调优不够好

常见原因

表的读取顺序(id)数据读取操作的操作类型(select type)那些索引可以使用(possible keys)那些索引被实际使用(key)表之间的引用(ref)每张表有多少行被物理查询(rows)

用途

id：选择标识符；在一个大的查询中每一个查询关键字都对应一个idselect_type：表示查询的类型table：输出结果集的表partitions：匹配的分区；提供有关表分区的信息type：表示表的连接类型possible_keys：表示查询时，可能使用的索引key：表示实际使用的索引key_len：索引字段的长度ref：列与索引的比较；当索引列等值查询时，与索引列进行等值匹配的对象信息rows：扫描出的行数(估算的行数)filtered：按表条件过滤的行百分比；某个表经过搜索条件过滤后剩余记录条数的百分比Extra：执行情况的描述和说明；如果Extra为空值代表SQL语句不用优化，性能还可以

11个信息列

选择标识符；在一个大的查询中每一个查询关键字都对应一个id

id

表示表的连接类型

null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL（从左到右，性能从高到底）

ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件range：只检索给定范围的行，使用一个索引来选择行，范围查询时出现

type

能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

possible_keys

实际决定使用的索引，必然包含在possible_keys中

Key

估算出结果集行数

rows

指返回结果的行占需要读到的行的百分比

1、 id相同时，执行顺序由上至下2、如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行3、id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

经常会遇到：filtered列值总是100%，这是因为：filtered值只对index和all的扫描有效，其它场合，通常rows值就等于估算的结果集大小

filtered

重点列

分析

explain执行计划-分析sql的索引使用情况

慢sql分析

执行顺序

数据量很大，分页查询

避免在 where 子句中使用 or 来连接条件， 否则将导致引擎放弃使用索引而进行全表扫描， 可以使用union all 或union合并查询

避免在 where 子句中对字段进行表达式操作、函数操作

关于 in 和 exist，如果查询的俩个表大小一致则性能差别可忽略，如果子查询表大用 exist，否则使用 in，比如：select num from a where num in(select num from b).用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)select in 会被改写为exists，造成查询缓慢，可改成关联join查询

where条件中（比如在IN后面值的列表），将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新

当只要一行数据时使用 LIMIT 1

推荐使用group by， 因为distinct会导致全表扫描

join时 尽量将条件内嵌，先过滤后join

SQL语句优化

建立复合索引时

走索引不一定都快，区分度不够的索引可用考虑用复合索引，把区分度最高的字段放在最左侧

explain 中的 type 至少要达到 range，要求为 ref

使用索引

优化表存储结构，降低IO 操作次数，提升性能

适当使用冗余字段

尽量少用text，非用不可最好分表

应尽量避免在 where 子句中对字段进行 NULL值判断，因为NULL 极大影响整个索引的效率，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值

数字类型：非万不得已不要使用DOUBLE

字符类型：定长字段，建议使用 CHAR 类型（char查询快，但是耗存储空间，可用于用户名、密码等长度变化不大的字段）;不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度

时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME 类型的一半；对于只需要精确到某一天的数据类型，建议使用DATE类型

数据类型选择

字符编码——使用合适的字符集

表结构优化

高可用性负载均衡集群，具备读写分离，一般只对读进行负载均衡

负载均衡

对数据库读和写的操作分开对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻io压力

读写分离

垂直拆分

水平拆分

分库分表

参数配置优化

硬件三件套（CPU、内存、磁盘）

数据库架构优化

特别是要被多次执行的查询，有助于避免多重排序操作

适当使用视图加速查询

算法优化，尽量避免使用游标

封装存储过程（不太建议）

其他优化

优化方法

慢 SQL 语句

最左前缀匹配

原则

主键索引

唯一索引

单值索引

复合索引/组合索引

全文索引

分类

主键自动建立唯一索引

频繁作为查询字段应该建立索引

在高并发的情况下使用组合索引

查询中统计或者分组字段

那些情况下可以建立索引

表记录太少

where 条件用不到字段不需要创建索引

过滤性不好的不适合建立索引

经常增删改的字段

数据重复且分布均匀的表字段，因为一般只为最经常查询和最经常排序的数据列建立索引

那些情况下不要建立索引

索引使用建议

索引

SQL例子

SELECT   *FROM     operationWHERE    type = 'SQLStats'AND      name = 'SlowLog'AND      create_time > '2017-03-16 14:00:00'ORDER BY create_time limit 10;

在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的

方案

LIMIT 分页查询

如果order by的列有相同的值时，mysql会随机选取这些行，为了保证每次都返回的顺序一致可以额外增加一个排序字段（比如：id），用两个字段来尽可能减少重复的概率

limit 与order by一起用

混合排序

EXISTS 子句

提前缩小范围

典型优化案例分析

数据库SQL优化