乐趣区

关于java:5分钟快速掌握阿里内部MySQL性能优化的核心技术

jiezi

前言

BATJTMD 等大厂的面试难度越来越高，但无论从大厂还是到小公司，始终未变的一个重点就是对 SQL 优化教训的考查。一提到数据库，先“说一说你对 SQL 优化的见解吧？”。

SQL 优化曾经成为掂量程序猿优良与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，如果是你，在这个问题上能吊打面试官还是会被吊打呢？

有敌人疑难到，SQL 优化真的有这么重要么？如下图所示，SQL 优化在晋升零碎性能中是：（老本最低 && 优化成果最显著）的路径。如果你的团队在 SQL 优化这方面搞得很优良，对你们整个大型零碎可用性方面无疑是一个质的逾越，真的能让你们老板省下不止几沓子钱。

  • 优化老本：硬件 > 系统配置 > 数据库表构造 >SQL 及索引。
  • 优化成果：硬件 < 系统配置 < 数据库表构造 <SQL 及索引。
String result = "嗯，不错，";
 
if ("SQL 优化教训足") {if ("相熟事务锁") {if ("并发场景解决 666") {if ("会打王者光荣") {result += "今天入职"}
        }
    }
} else {result += "先回去等音讯吧";} 
 
Logger.info("面试官：" + result);

别看了，下面这是一道送命题。

好了咱们言归正传，首先，对于 MySQL 层优化我个别听从五个准则：

  1. 缩小数据拜访：设置正当的字段类型，启用压缩，通过索引拜访等缩小磁盘 IO
  2. 返回更少的数据：只返回须要的字段和数据分页解决 缩小磁盘 io 及网络 io
  3. 缩小交互次数：批量 DML 操作，函数存储等缩小数据连贯次数
  4. 缩小服务器 CPU 开销：尽量减少数据库排序操作以及全表查问，缩小 cpu 内存占用
  5. 利用更多资源：应用表分区，能够减少并行操作，更大限度利用 cpu 资源

总结到 SQL 优化中，就三点:

  • 最大化利用索引；
  • 尽可能防止全表扫描；
  • 缩小有效数据的查问；

了解 SQL 优化原理，首先要搞清楚 SQL 执行程序：

SELECT 语句 – 语法程序：

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>

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 优化策略实用于数据量较大的场景下，如果数据量较小，没必要以此为准，免得画龙点睛。

一、防止不走索引的场景

1. 尽量避免在字段结尾含糊查问，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '% 陈 %'

优化形式：尽量在字段前面应用含糊查问。如下：

SELECT * FROM t WHERE username LIKE '陈 %'

如果需要是要在后面应用含糊查问，

  • 应用 MySQL 内置函数 INSTR(str,substr) 来匹配，作用相似于 java 中的 indexOf()，查问字符串呈现的角标地位
  • 应用 FullText 全文索引，用 match against 检索
  • 数据量较大的状况，倡议援用 ElasticSearch、solr，亿级数据量检索速度秒级
  • 当表数据量较少（几千条儿那种），别整花里胡哨的，间接用 like ‘%xx%’。

2. 尽量避免应用 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);

3. 尽量避免应用 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

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

SELECT * FROM t WHERE score IS NULL

优化形式：能够给字段增加默认值 0，对 0 值进行判断。如下：

SELECT * FROM t WHERE score = 0

5. 尽量避免在 where 条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

能够将表达式、函数操作挪动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量大时，防止应用 where 1= 1 的条件。通常为了不便拼装查问条件，咱们会默认应用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1

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

7. 查问条件不能用 <> 或者 !=

应用索引列作为条件进行查问时，须要防止应用 <> 或者!= 等判断条件。如的确业务须要，应用到不等于符号，须要在从新评估索引建设，防止在此字段上建设索引，改由查问条件中其余索引字段代替。

8.where 条件仅蕴含复合索引非前置列

如下：复合（联结）索引蕴含 key_part1，key_part2，key_part3 三列，但 SQL 语句没有蕴含索引前置列 ”key_part1″，依照 MySQL 联结索引的最左匹配准则，不会走联结索引。

select col1 from table where key_part2=1 and key_part3=2

9. 隐式类型转换造成不应用索引

如下 SQL 语句因为索引对列类型为 varchar，但给定的值为数值，波及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;

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

-- 不走 age 索引
SELECT * FROM t order by age;
 
-- 走 age 索引
SELECT * FROM t where age > 0 order by age;

对于下面的语句，数据库的解决程序是：

  • 第一步：依据 where 条件和统计信息生成执行打算，失去数据。
  • 第二步：将失去的数据排序。当执行解决数据（order by）时，数据库会先查看第一步的执行打算，看 order by 的字段是否在执行打算中利用了索引。如果是，则能够利用索引程序而间接获得曾经排好序的数据。如果不是，则从新进行排序操作。
  • 第三步：返回排序后的数据。

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

这个论断不仅对 order by 无效，对其余须要排序的操作也无效。比方 group by、union、distinct 等。

11. 正确应用 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;

二、SELECT 语句其余优化

1. 避免出现 select*

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

应用 select * 取出全部列，会让优化器无奈实现索引笼罩扫描这类优化，会影响优化器对执行打算的抉择，也会减少网络带宽耗费，更会带来额定的 I /O, 内存和 CPU 耗费。

倡议提出业务理论须要的列数，将指定列名以取代 select *。

2. 避免出现不确定后果的函数

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

3. 多表关联查问时，小表在前，大表在后。

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

例如：表 1 有 50 条数据，表 2 有 30 亿条数据；如果全表扫描表 2，你品，那就先去吃个饭再说吧是吧。

4. 应用表的别名

当在 SQL 语句中连贯多个表时，请应用表的别名并把别名前缀于每个列名上。这样就能够缩小解析的工夫并缩小哪些友列名歧义引起的语法错误。

5. 用 where 字句替换 HAVING 字句

防止应用 HAVING 字句，因为 HAVING 只会在检索出所有记录之后才对后果集进行过滤，而 where 则是在聚合前刷选记录，如果能通过 where 字句限度记录的数目，那就能缩小这方面的开销。HAVING 中的条件个别用于聚合函数的过滤，除此之外，应该将条件写在 where 字句中。

where 和 having 的区别：where 前面不能应用组函数

6. 调整 Where 字句中的连贯程序

MySQL 采纳从左往右，自上而下的程序解析 where 子句。依据这个原理，应将过滤数据多的条件往前放，最快速度放大后果集。

三、增删改 DML 语句优化

1. 大批量插入数据

如果同时执行大量的插入，倡议应用多个值的 INSERT 语句(办法二)。这比应用离开 INSERT 语句快（办法一），个别状况下批量插入效率有几倍的差异。

办法一：

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

抉择后一种办法的起因有三。

  • 缩小 SQL 语句解析的操作，MySQL 没有相似 Oracle 的 share pool，采纳办法二，只须要解析一次就能进行数据的插入操作；
  • 在特定场景能够缩小对 DB 连贯次数
  • SQL 语句较短，能够缩小网络传输的 IO。

2. 适当应用 commit

适当应用 commit 能够开释事务占用的资源而缩小耗费，commit 后能开释的资源如下：

  • 事务占用的 undo 数据块；
  • 事务在 redo log 中记录的数据块；
  • 开释事务施加的，缩小锁争用影响性能。特地是在须要应用 delete 删除大量数据的时候，必须合成删除量并定期 commit。

3. 防止反复查问更新的数据

针对业务中经常出现的更新行同时又心愿取得改行信息的需要，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 表数据量较大时，后者比前者快很多。

4. 查问优先还是更新（insert、update、delete）优先

MySQL 还容许扭转语句调度的优先级，它能够使来自多个客户端的查问更好地合作，这样单个客户端就不会因为锁定而期待很长时间。扭转优先级还能够确保特定类型的查问被解决得更快。咱们首先应该确定利用的类型，判断利用是以查问为主还是以更新为主的，是确保查问效率还是确保更新的效率，决定是查问优先还是更新优先。

上面咱们提到的扭转调度策略的办法次要是针对只存在表锁的存储引擎，比方 MyISAM、MEMROY、MERGE，对于 Innodb 存储引擎，语句的执行是由取得行锁的程序决定的。MySQL 的默认的调度策略可用总结如下：

1）写入操作优先于读取操作。

2）对某张数据表的写入操作某一时刻只能产生一次，写入申请依照它们达到的秩序来解决。

3）对某张数据表的多个读取操作能够同时地进行。MySQL 提供了几个语句调节符，容许你批改它的调度策略：

  • LOW_PRIORITY 关键字利用于 DELETE、INSERT、LOAD DATA、REPLACE 和 UPDATE；
  • HIGH_PRIORITY 关键字利用于 SELECT 和 INSERT 语句；
  • DELAYED 关键字利用于 INSERT 和 REPLACE 语句。

如果写入操作是一个 LOW_PRIORITY（低优先级）申请，那么零碎就不会认为它的优先级高于读取操作。在这种状况下，如果写入者在期待的时候，第二个读取者达到了，那么就容许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才容许写入者开始操作。这种调度批改可能存在 LOW_PRIORITY 写入操作永远被阻塞的状况。

SELECT 查问的 HIGH_PRIORITY（高优先级）关键字也相似。它容许 SELECT 插入正在期待的写入操作之前，即便在失常状况下写入操作的优先级更高。另外一种影响是，高优先级的 SELECT 在失常的 SELECT 语句之前执行，因为这些语句会被写入操作阻塞。如果心愿所有反对 LOW_PRIORITY 选项的语句都默认地依照低优先级来解决，那么 请应用 –low-priority-updates 选项来启动服务器。通过应用 INSERTHIGH_PRIORITY 来把 INSERT 语句进步到失常的写入优先级，能够打消该选项对单个 INSERT 语句的影响。

四、查问条件优化

1. 对于简单的查问，能够应用两头长期表 暂存数据

2. 优化 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 ;

3. 优化 join 语句

MySQL 中能够通过子查问来应用 SELECT 语句来创立一个单列的查问后果，而后把这个后果作为过滤条件用在另一个查问中。应用子查问能够一次性的实现很多逻辑上须要多个步骤能力实现的 SQL 操作，同时也能够防止事务或者表锁死，并且写起来也很容易。然而，有些状况下，子查问能够被更有效率的连贯(JOIN).. 代替。

例子：假如要将所有没有订单记录的用户取出来，能够用上面这个查问实现：

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

如果应用连贯(JOIN).. 来实现这个查问工作，速度将会有所晋升。尤其是当 salesinfo 表中对 CustomerID 建有索引的话，性能将会更好，查问如下：

SELECT col1 FROM customerinfo 
   LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
      WHERE salesinfo.CustomerID IS NULL

连贯(JOIN).. 之所以更有效率一些，是因为 MySQL 不须要在内存中创立长期表来实现这个逻辑上的须要两个步骤的查问工作。

4. 优化 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';

5. 拆分简单 SQL 为多个小 SQL，防止大事务

  • 简略的 SQL 容易应用到 MySQL 的 QUERY CACHE；
  • 缩小锁表工夫特地是应用 MyISAM 存储引擎的表；
  • 能够应用多核 CPU。

6. 应用 truncate 代替 delete

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

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

7. 应用正当的分页形式以进步分页效率

应用正当的分页形式以进步分页效率 针对展示等分页需要，适合的分页形式可能进步分页的效率。

案例 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 子句波及的字段）有对应笼罩索引时，且两头后果集很大的状况时实用。

五、建表优化

1. 在表中建设索引，优先思考 where、order by 应用到的字段。

2. 尽量应用数字型字段（如性别，男：1 女：2），若只含数值信息的字段尽量不要设计为字符型，这会升高查问和连贯的性能，并会减少存储开销。

这是因为引擎在解决查问和连贯时会 一一比拟字符串中每一个字符，而对于数字型而言只须要比拟一次就够了。

3. 查问数据量大的表 会造成查问迟缓。次要的起因是扫描行数过多。这个时候能够通过程序，分段分页进行查问，循环遍历，将后果合并解决进行展现。要查问 100000 到 100050 的数据，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
   FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4. 用 varchar/nvarchar 代替 char/nchar

尽可能的应用 varchar/nvarchar 代替 char/nchar，因为首先变长字段存储空间小，能够节俭存储空间，其次对于查问来说，在一个绝对较小的字段内搜寻效率显然要高些。

不要认为 NULL 不须要空间，比方：char(100) 型，在字段建设时，空间就固定了，不论是否插入值（NULL 也蕴含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段，null 不占用空间。

写在最初

欢送大家关注我的公众号【惊涛骇浪如码】，海量 Java 相干文章，学习材料都会在外面更新，整顿的材料也会放在外面。

感觉写的还不错的就点个赞，加个关注呗！点关注，不迷路，继续更新！！！

退出移动版