Mysql 优化方案

SQL优化基础

SQL优化简介

SQL优化在提升系统性能中是：（成本最低 && 优化效果最明显）的途径。

优化成本：硬件>系统配置>数据库表结构>SQL及索引。
优化效果：硬件<系统配置<数据库表结构<SQL及索引。

SQL层优化遵从原则

减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
返回更少的数据：只返回需要的字段和数据分页处理减少磁盘io及网络io
减少交互次数：批量DML操作，函数存储等减少数据连接次数
减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源

SQL优化核心

最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询

SQL语句-语法顺序

1. SELECT
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

SQL语句-执行顺序

顺序	语法	描述
1.	`FROM`	表名选取表，将多个表数据通过笛卡尔积变成一个表。
2.	`ON`	筛选条件对笛卡尔积的虚表进行筛选
3.	`JOIN`	join表：<join, left join, right join...> 指定join，用于添加数据到on之后的虚表中；例如left join会将左表的剩余数据添加到虚表中
4.	`WHERE`	where条件对上述虚表进行筛选
5.	`GROUP BY`	分组条件 <SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
6.	`HAVING`	分组筛选对分组后的结果进行聚合筛选
7.	`SELECT`	返回数据列表返回的单列必须在group by子句中，聚合函数除外
8.	`DISTINCT`	数据除重
9.	`ORDER BY`	排序条件
10.	`LIMIT`	行数限制

SQL优化策略

EXPLAIN

善用 EXPLAIN 查看SQL执行计划

下面来个简单的示例，以下五列是要重点关注的数据

type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式
key_len列，索引长度
rows列，扫描行数。该值是个预估值
extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary

建表优化

建立索引时

优先考虑where、order by使用到的字段。

尽量使用数值类型

int 代替 varchar

如性别：1-男；2-女；3-其它

引擎在处理数值类型时查询时比较一次

引擎在处理字符类型时查询时将逐个比较字符串中每一个字符

尽量使用变长类型

varchar/nvarchar 代替 char/nchar
首先变长字段存储空间小，可以节省存储空间

其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

若是存储的时 NULL
varchar这样的变长字段， null 不占用空间;
char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的。

增删改语句优化

使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

大批量插入数据

方法一：

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);

推荐使用方法二，原因如下：

在特定场景可以减少对DB连接次数
SQL语句较短，可以减少网络传输的IO。
减少SQL语句解析的操作，MySQL没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；

适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

事务- 占用的undo数据块；
事务在redo log中记录的数据块；
性能方面减少锁竞争。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

避免重复查询更新的数据

针对业务中经常出现的更新行同时又希望获得改行信息的需求，MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法，在MySQL中可以通过变量实现。
例如，更新一行记录的时间戳，同时希望查询当前记录中存放的时间戳是什么，简单方法实现：

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;

前后二者都需要两次网络来回，但使用变量避免了再次访问数据表，特别是当t1表数据量较大时，后者比前者快很多。

查询索引优化

对于联合索引来说，要遵守最左前缀法则

举列来说索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name，school都无法使用这个索引。
所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面

尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

LIKE '%name'或者LIKE '%name%'：这种查询会导致索引失效而进行全表扫描。

SELECT * FROM t WHERE username LIKE '%mjtabu%';
SELECT * FROM t WHERE username LIKE '%mjtabu';

优化方式：尽量在字段后面使用模糊查询

SELECT * FROM t WHERE username LIKE 'mjtabu%';

如果需求是要在前面使用模糊查询，推荐使用FullText全文索引，用match against 检索。
创建全文索引的sql语法是：

ALTER TABLE `table_name` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

使用全文索引的sql语句是：

select id,fnum,fdst from table_name 
where match(user_name) against('mjtabu' in boolean mode);

注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别

尽量避免使用in 和not in，会导致引擎走全表扫描

SELECT * FROM t WHERE id IN (2,3);

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3;

若是子查询，可以用exists代替。如下：

-- 不走索引
select * from t_mjtabu1 where id in (select id from t_mjtabu2);

-- 走索引
select * from t_mjtabu1 where exists (select * from t_mjtabu2 where id = t_mjtabu1.id);

尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描

select * from t_mjtabu1 where id = 1 or id = 3;

优化方式：可以用union代替or。如下：

select * from t_mjtabu1 where id = 1
union 
select * from t_mjtabu1 where id = 3

尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t_mjtabu1 WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t_mjtabu1 WHERE score = 0

尽量避免在where条件中对字段进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到运算符右侧。如下：

-- 全表扫描
SELECT * FROM t_mjtabu1 WHERE score*2 = 100；
-- 走索引
SELECT * FROM t_mjtabu1 WHERE score = 100/2；

当数据量大时，避免使用where 1=1的条件

SELECT * FROM t_mjtabu1 WHERE 1 = 1

优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。
如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

-- 全表扫描
select * from t_mjtabu1 where score != 1
-- 走索引
select * from t_mjtabu1 where score > 1 
union 
select * from t_mjtabu1 where score < 1;

where条件仅包含联合索引非前置列

MySQL联合索引最左匹配原则
联合索引由多个字段组合构成：column1、column2、column3；
SQL语句没有包含索引最左列：column1，则不会走联合索引。

-- 全表扫描
select * from t_mjtabu1 where column2=1 and column3=2

隐式类型转换造成不使用索引

索引列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

-- 全表扫描
select * from t_mjtabu1 where user_name = 567;
-- 走索引
select * from t_mjtabu1 where user_name = '567';

order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 全表扫描
select * from t_mjtabu1 order by score;
-- 走索引
select * from t_mjtabu1 where score > 0 order by score;

对于上面的语句，数据库的处理顺序是：

第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

查询其它优化

分段查询

一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

使用合理的分页方式以提高分页的效率

select id,name from table_name limit 866613, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。

sql可以采用如下的写法：

select id,name from table_name where id> 866612 limit 20

避免出现select *

select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数，将指定列名以取代select *。

当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型

使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。
这样就可以减少解析的时间并减少那些由列名歧义引起的语法错误。

调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

多表关联查询时，小表在前，大表在后

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

例如：表1有50条数据，表2有30亿条数据；如果全表扫描表2，结果可想而知。

避免出现不确定结果的函数

特定针对主从复制这类业务场景。
由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

用where字句替换HAVING字句

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

join语句优化

LEFT JOIN A表为驱动表
INNER JOIN MySQL会自动找出那个数据少的表作用驱动表
RIGHT JOIN B表为驱动表

尽量使用inner join，避免使用left join 或者 right join
参与联合查询的表至少为2张表，一般都存在大小之分。
如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表;
但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表;
但是right join在驱动表的选择上遵循的是右边驱动右边的原则，即right join右边的表名为驱动表。

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

例子：假设要将不存在table2表col_111的数据取出来，可以用下面这个查询完成：

SELECT col1 FROM table1 WHERE col_111 NOT in (SELECT col_111 FROM table2 )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当table2表中对 col_111 建有索引的话，性能将会更好，查询如下：

SELECT col1 FROM table1 
LEFT JOIN table2 table1.col_111=table2.col_111 
WHERE table2.col_111 IS NULL

连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

union语句优化

union all	合并
union	合并并去重

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
UNION ALL 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效：

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
UNION 
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

group by语句优化

默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 GROUP BY col1，col2，....;
查询的方法如同在查询中指定 ORDER BY col1，col2，...;

因此，如果有分组查询GROUP BY，但你并不想对分组的值进行排序，可以指定 ORDER BY NULL禁止排序。

例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

posted @ 2021-01-27 09:47 踏步阅读(837) 评论(0) 收藏举报

刷新页面返回顶部