本文以Mysql数据库为例,总结数据库优化方法。
一、数据库优化四个层面
Mysql数据库优化,可以从以下四个层面优化:硬件、系统配置、数据库表结构、sql语句及索引。
优化效果:SQL语句及索引>数据库表结构>系统配置>硬件
优化成本:SQL语句及索引<数据库表结构<系统配置<硬件
二、sql语句层面优化五个原则
-
减少数据访问:设置合理的字段类型,启用压缩,通过索引访问等减少磁盘IO。
-
返回更少的数据:只返回需要的字段和数据分页处理,减少磁盘IO及网络IO。
-
减少交互次数:批量DML(Data Manipulation Language,即增删改查)操作,函数储存等减少数据连接次数。
-
减少服务器CPU开销:尽量减少数据排序操作以及全表查询,减少CPU内存占用。
-
利用更多资源:使用表分区,增加并行操作,最大限度利用CPU资源。
可总结为以下三点:
- 最大化利用索引
- 尽可能避免全表扫表
- 减少无效数据查询
三、SQL的语法顺序及执行顺序
SELECT语句,语法顺序如下:
SELECT
DISTINCT <select_list>
FROM <left_table>
JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
SELECT语句,执行顺序如下:
FROM
<表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...>
<join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>
四、查询条件优化
- 对于复杂的查询,可使用中间临时表暂存数据
-
拆分复杂SQL为多个小SQL,避免大事务
简单的SQL容易使用到Mysql的QUERY CACHE;减少锁表时间特别是MylSAM 存储引擎的表;可使用多核CPU。
-
优化 group by 语句
默认情况下,MySQL 会对 GROUP BY 分组的所有值进行排序,如 “GROUP BY col1,col2,…;” 查询的方法如同在查询中指定 “ORDER BY col1,col2,…;” 。
如果显式包括一个包含相同的列的 ORDER BY 子句,MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。
因此,如果查询包括 GROUP BY 但你并不想对分组的值进行排序,你可以指定 ORDER BY NULL 禁止排序。
例如:
SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;
-
优化 join 语句
利用join语句代替子查询(in),减少Mysql在内存中创建临时表,提升效率;若on链接的字段为索引,则性能更好。
-
优化 union 查询
除非缺失需要消除重复行,否则建议使用union all。因为如果没有all 关键词,mysql会给临时表加上distinct选项,进行唯一性校验。
-
使用 truncate 代替 delete
若确认删除全表,可使用truncate,不会记录可恢复的信息,且数据不能被恢复。
-
使用合理的分页方式以提高分页效率
使用合理的分页方式以提高分页效率 针对展现等分页需求,合适的分页方式能够提高分页的效率。
案例1:
select * from t where thread_id = 10000 and deleted = 0
order by gmt_create asc limit 0, 15;
上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引 IO+索引全部记录结果对应的表数据 IO。
因此,该种写法越翻到后面执行效率越差,时间越长,尤其表数据量很大的时候。
适用场景:当中间结果集很小(10000 行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用。
案例2:
select t.* from (select id from t where thread_id = 10000 and deleted = 0
order by gmt_create asc limit 0, 15) a, t
where a.id = t.id;
上述例子必须满足 t 表主键是 id 列,且有覆盖索引 secondary key:(thread_id, deleted, gmt_create )。
通过先根据过滤条件利用覆盖索引取出主键 id 进行排序,再进行 join 操作取出其他字段。
数据访问开销=索引 IO+索引分页后结果(例子中是 15 行)对应的表数据 IO。因此,该写法每次翻页消耗的资源和时间都基本相同,就像翻第一页一样。
适用场景:当查询和排序字段(即 where 子句和 order by 子句涉及的字段)有对应覆盖索引时,且中间结果集很大的情况时适用。
五、SELECT语句其他优化
- 避免出现select※
-
避免出现不确定结果的函数
针对主从赋值这类业务场景,由于原理上从库复制的是主库执行的语句,使用如 now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应数据不一致。
另外不确定值的函数,产生的sql语句无法利用query cache。
Mysql 主从复制 作用和原理
Mysql 查询缓存(Query Cache)详细介绍
-
多表关联查询时,小表在前,大表在后
在mysql中,执行from后的表关联查询是从左往右的(与Oracle相反),第一张表会涉及全表扫描。
所以应将小表放在前面,先扫小表,以提高效率。
-
使用表的别名
当在sql中连接多个表时,将表别名前缀在每个列名上,以减少解析时间并避免列名歧义导致的语法错误。
-
调整where语句中链接顺序
mysql采用从左往右,自上而下的顺序解析where语句,因此应将过滤数据多的条件往前放。
六、增删改DML语句优化
-
大批量插入数据
若同时执行大量插入操作,建议使用多个值INSERT语句(方法二),可提升效率的原因有三个:
①减少sql语句解析的操作;
②特定场景可减少对DB链接次数;
③SQL语句较短,可以减少网络传输的IO.
方法一:
insert into T values(1,2);
insert into T values(1,3);
insert into T values(1,4);
方法二:
Insert into T values(1,2),(1,3),(1,4);
-
适当使用commit
适当使用commit可释放事务占用的资源以减少消耗,commit后能释放的资源如下:
①事务占用的undo数据块;
②事务在redo log中记录的数据块;
③释放事务,减少锁争用影响性能。
-
避免重复查询表中更新的数据
针对业务中常出现的更新行的同时又希望获取改行信息的需求,MySQL不支持PostgreSQL那样的update returning语法,在MySQL中可通过变量实现。
如以下方法中,前后均需两次网络来回,但通过变量避免再次访问数据表,当t1表体量较大时可明显提升效率:
简单方法实现:
Update t1 set time=now() where col1=1;
Select time from t1 where id =1;
使用变量,可以重写为以下方式:
Update t1 set time=now () where col1=1 and @now: = now ();
Select @now;
-
查询优先还是更新优先(insert、delete、update)
MySQL 还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。
我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确保查询效率还是确保更新的效率,决定是查询优先还是更新优先。
MySQL默认的调度策略总结如下:
①写入操作优先于读取操作。
②对某张数据表的写入操作某一时刻只能发生一次,写入请求按他们到达的次序来处理。
③对某张数据表的多个读取操作可以同时进行。
七、避免不走索引的场景
-
尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描
如果需求是要在前面使用模糊查询:
①使用 MySQL 内置函数 INSTR(str,substr)来匹配,作用类似于 Java 中的 indexOf(),查询字符串出现的角标位置。
②使用 FullText 全文索引,用 match against 检索。
③数据量较大的情况,建议引用 ElasticSearch、Solr,亿级数据量检索速度秒级。
④当表数据量较少(几千条儿那种),别整花里胡哨的,直接用 like ‘%xx%’。
SELECT * FROM t WHERE username LIKE '%陈%'
优化方式:尽量在字段后面使用模糊查询。
SELECT * FROM t WHERE username LIKE '陈%'
-
尽量避免使用in 和 not in,会导致引擎走全表扫描
如下:
SELECT * FROM t WHERE id IN (2,3)
优化方式:如果是连续数值,可以用 between 代替。
SELECT * FROM t WHERE id BETWEEN 2 AND 3
如果是子查询,可以用 exists 代替。
如下:
-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);
-
尽量避免使用or,会导致数据库引擎放弃索引进行全表扫描
如下:
SELECT * FROM t WHERE id = 1 OR id = 3
优化方式:可以用 union 代替 or。
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3
-
尽量避免进行null值判断,会导致数据库引擎放弃索引进行全表扫描
如下:
SELECT * FROM t WHERE score IS NULL
优化方式:可以给字段添加默认值 0,对 0 值进行判断。
SELECT * FROM t WHERE score = 0
-
尽量避免在where条件中等号左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描
可以将表达式、函数操作移动到等号右侧,如下:
-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9
-
当数据量大时,避免使用where 1=1 条件
通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:
SELECT username, age, sex FROM T WHERE 1=1
优化方式:用代码拼装 SQL 时进行判断,没 where 条件就去掉 where,有 where 条件就加 and。
-
查询条件不能用 <> 或 !=
使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。
如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。
-
where 条件仅包含符合索引非前置列
如下:复合(联合)索引包含 key_part1,key_part2,key_part3 三列,但 SQL 语句没有包含索引前置列"key_part1",按照 MySQL 联合索引的最左匹配原则,不会走联合索引。
select col1 from table where key_part2=1 and key_part3=2
-
隐式类型转换造成不使用索引
如下 SQL 语句由于索引对列类型为 varchar,但给定的值为数值,涉及隐式类型转换,造成不能正确走索引。
select col1 from table where col_varchar=123;
-
order by 条件要与 where 中条件一致,否则 order by 不会利用索引进行排序
数据库的处理顺序是:
①根据 where 条件和统计信息生成执行计划,得到数据。
②将得到的数据排序。当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看 order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则重新进行排序操作。
③返回排序后的数据。
当 order by 中的字段出现在 where 条件中时,才会利用索引而不再二次排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。
这个结论不仅对 order by 有效,对其他需要排序的操作也有效。比如 group by 、union 、distinct 等。
如下:
-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;
-
正确使用 hint 优化语句
MySQL 中可以使用 hint 指定优化器在执行时选择或忽略特定的索引。
一般而言,处于版本变更带来的表结构索引变化,更建议避免使用 hint,而是通过 Analyze table 多收集统计信息。
但在特定场合下,指定 hint 可以排除其他索引干扰而指定更优的执行计划:
①USE INDEX 在你查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可以让 MySQL 不再考虑其他可用的索引。
例子: SELECT col1 FROM table USE INDEX (mod_time, name)…
②IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引,可以使用 IGNORE INDEX 作为 Hint。
例子: SELECT col1 FROM table IGNORE INDEX (priority) …
③FORCE INDEX 为强制 MySQL 使用一个特定的索引,可在查询中使用FORCE INDEX 作为 Hint。
例子: SELECT col1 FROM table FORCE INDEX (mod_time) …
在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。
如果我们知道如何选择索引,可以使用 FORCE INDEX 强制查询使用指定的索引,如下:
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;
MySQL的特殊关键字提示:hint
八、建表优化
- 表中建立索引,优先考虑where、group by、order by使用到的字段
-
尽量使用数字型字段(如性别,男1,女0),若只含数值信息的字段尽量不要设计为字符型,这回降低查询和连接的性能
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
-
用varchar/nvarchar 代替 char/nchar
不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL 也包含在内),都是占用 100 个字符的空间的,如果是 varchar 这样的变长字段, null 不占用空间。
搞懂这些SQL优化技巧