数据库SQL优化
2023-01-08 09:26:19 4 举报AI智能生成
数据库SQL优化
作者其他创作
大纲/内容
慢 SQL 语句
常见原因
数据过多
无索引
索引失效
全模糊查
is not null
!=或<>
字符串不加单引号
or连接查询
没有充分利用到索引
sql写法待优化
关联查询有太多的join(设计缺陷或不得已的需求)
全模糊查询
...
数据库表结果设计不合理
数据库配置参数调优不够好
慢sql分析
explain执行计划-分析sql的索引使用情况
用途
表的读取顺序(id)
数据读取操作的操作类型(select type)
那些索引可以使用(possible keys)
那些索引被实际使用(key)
表之间的引用(ref)
每张表有多少行被物理查询(rows)
数据读取操作的操作类型(select type)
那些索引可以使用(possible keys)
那些索引被实际使用(key)
表之间的引用(ref)
每张表有多少行被物理查询(rows)
分析
11个信息列
id：选择标识符；在一个大的查询中每一个查询关键字都对应一个id
select_type：表示查询的类型
table：输出结果集的表
partitions：匹配的分区；提供有关表分区的信息
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较；当索引列等值查询时，与索引列进行等值匹配的对象信息
rows：扫描出的行数(估算的行数)
filtered：按表条件过滤的行百分比；某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：执行情况的描述和说明；如果Extra为空值代表SQL语句不用优化，性能还可以
select_type：表示查询的类型
table：输出结果集的表
partitions：匹配的分区；提供有关表分区的信息
type：表示表的连接类型
possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较；当索引列等值查询时，与索引列进行等值匹配的对象信息
rows：扫描出的行数(估算的行数)
filtered：按表条件过滤的行百分比；某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：执行情况的描述和说明；如果Extra为空值代表SQL语句不用优化，性能还可以
重点列
id
选择标识符；在一个大的查询中每一个查询关键字都对应一个id
type
表示表的连接类型
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：只检索给定范围的行，使用一个索引来选择行，范围查询时出现
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
range：只检索给定范围的行，使用一个索引来选择行，范围查询时出现
possible_keys
能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
Key
实际决定使用的索引，必然包含在possible_keys中
rows
估算出结果集行数
filtered
指返回结果的行占需要读到的行的百分比
经常会遇到：filtered列值总是100%，这是因为：filtered值只对index和all的扫描有效，其它场合，通常rows值就等于估算的结果集大小
1、 id相同时，执行顺序由上至下
2、如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3、id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
2、如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3、id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
优化方法
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查询
select in 会被改写为exists，造成查询缓慢，可改成关联join查询
where条件中（比如在IN后面值的列表），将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数
当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新
当只要一行数据时使用 LIMIT 1
推荐使用group by， 因为distinct会导致全表扫描
join时 尽量将条件内嵌，先过滤后join
使用索引
建立复合索引时
走索引不一定都快，区分度不够的索引可用考虑用复合索引，把区分度最高的字段放在最左侧
explain 中的 type 至少要达到 range，要求为 ref
表结构优化
优化表存储结构，降低IO 操作次数，提升性能
适当使用冗余字段
尽量少用text，非用不可最好分表
应尽量避免在 where 子句中对字段进行 NULL值判断，因为NULL 极大影响整个索引的效率，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值
数据类型选择
数字类型：非万不得已不要使用DOUBLE
字符类型：定长字段，建议使用 CHAR 类型（char查询快，但是耗存储空间，可用于用户名、密码等长度变化不大的字段）;
不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度
不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度
时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME 类型的一半；
对于只需要精确到某一天的数据类型，建议使用DATE类型
对于只需要精确到某一天的数据类型，建议使用DATE类型
字符编码——使用合适的字符集
数据库架构优化
负载均衡
高可用性负载均衡集群，具备读写分离，一般只对读进行负载均衡
读写分离
对数据库读和写的操作分开对应不同的数据库服务器，这样能有效地减轻数据库压力，也能减轻io压力
分库分表
垂直拆分
水平拆分
参数配置优化
硬件三件套（CPU、内存、磁盘）
其他优化
适当使用视图加速查询
特别是要被多次执行的查询，有助于避免多重排序操作
算法优化，尽量避免使用游标
封装存储过程（不太建议）
索引
原则
最左前缀匹配
分类
主键索引
唯一索引
单值索引
复合索引/组合索引
全文索引
索引使用建议
那些情况下可以建立索引
主键自动建立唯一索引
频繁作为查询字段应该建立索引
查询中与其他表关联的字段,外键建立索引
在高并发的情况下使用组合索引
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
那些情况下不要建立索引
表记录太少
where 条件用不到字段不需要创建索引
过滤性不好的不适合建立索引
经常增删改的字段
数据重复且分布均匀的表字段，因为一般只为最经常查询和最经常排序的数据列建立索引
典型优化案例分析
LIMIT 分页查询
SQL例子
SELECT *
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
ORDER BY create_time
LIMIT 1000, 10;
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
ORDER BY create_time
LIMIT 1000, 10;
方案
在 type, name, create_time 字段上加组合索引
在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的
SELECT *
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time limit 10;
FROM operation
WHERE type = 'SQLStats'
AND name = 'SlowLog'
AND create_time > '2017-03-16 14:00:00'
ORDER BY create_time limit 10;
limit 与order by一起用
如果order by的列有相同的值时，mysql会随机选取这些行，为了保证每次都返回的顺序一致可以额外增加一个排序字段（比如：id），用两个字段来尽可能减少重复的概率
混合排序
EXISTS 子句
提前缩小范围
0 条评论
下一页
