SQL优化技巧有哪些

本文主要讲解“SQL优化有哪些技巧”，简单明了，易学易懂。现在，请跟随边肖的思路，一起学习学习《SQL优化有哪些技巧》！

一、索引优化

索引的数据结构为B树，B树的查询性能相对较高，因此建立索引可以提高SQL的查询性能。

1、建立普通索引

为经常出现在where关键字之后的表字段建立相应的索引。

2、建立复合索引

如果有几个字段经常出现在where关键字之后，就可以建立相应的复合索引。需要注意的是，有一点是可以优化的：先把出现最多的字段分开。

例如，现在我们有两个字段A和B经常同时出现在where关键字之后：

选择* fromtwherea=和b=2；\*Q1*\也有很多单独使用字段A作为查询条件的SQL:

选择* from where=2；\*Q2*\此时，我们可以构建一个综合指数(a，b)。因为不仅Q1可以使用综合指数，Q2也可以使用综合指数。

3、最左前缀匹配原则

如果我们使用复合索引，我们应该尽量遵循最左边的前缀匹配原则。MySQL将始终向右匹配，直到遇到一个范围查询(，between，like)，然后停止匹配。

假设此时我们有一个SQL:

选择* from where=1 and b=2 and C3 and=4；那么我们应该建立一个综合指数：指数(a，b，d，c)而不是指数(a，b，c，d)。因为字段C是范围查询，所以MySQL在遇到范围查询时会停止索引匹配。

我们还注意到，A、B、D在SQL中的位置可以随意调整，优化器会找到对应的复合索引。

还需要注意的是，最左边的前缀匹配原则不仅是复合索引最左边的N个字段；它也可以是单列(字符串类型)索引最左边的m个字符。

比如我们常说的like关键字，尽量不要用全模糊查询，因为它不需要索引；

所以建议使用右边的模糊查询：从t中选择*其中名字像‘Li %’(查询所有姓李的同学的信息)。

4、索引下推

很多时候，我们也可以通过下推复合索引的索引来优化SQL。

例如，此时，我们有一个复合索引：index(姓名、年龄)，然后我们有一个如下所示的SQL:

选择* fromuserwherenamelike ' sheet % '和age=10 and sex=' m '；根据复合索引最左边的前缀匹配原则，MySQL在匹配复合索引名称(姓名、年龄)时停止匹配。那么接下来的流程就是根据主键返回表判断年龄和性别的条件是否同时满足，如果满足则返回给客户端。

但由于索引下推的优化，名称匹配时不会立即返回表中；但首先判断综合指数指标中的年龄(姓名、年龄)是否符合条件；如果满足条件，回到表中再判断是否满足性，否则会被过滤掉。

然后，通过MySQL 5.6引入的索引下推优化，可以减少返回表的数量。

5、覆盖索引

很多时候，我们也可以覆盖索引来优化SQL。

案例1: SQL只查询主键作为返回值。

主键索引(聚集索引)的叶节点是整行数据，而普通索引(辅助索引)的叶节点是主键的值。

因此，当我们的SQL只查询主键值时，可以直接获取对应叶节点的内容，避免返回表中。

情况SQL的查询字段在索引中。

复合索引：如果我们有一个复合索引(姓名、年龄)和一个SQL，如下所示：

select name,age from t where name like '张%';

由于是字段 name 是右模糊查询所以可以走复合索引，然后匹配到 name 时，不需要回表，因为 SQL 只是查询字段 name 和  age，所以直接返回索引值就 ok 了。

6、普通索引

尽量 使用普通索引 而不是唯一索引。

首先，普通索引和唯一索引的查询性能其实不会相差很多;当然了，前提是要查询的记录都在同一个数据页中，否则普通索引的性能会慢很多。

但是，普通索引的更新操作性能比唯一索引更好;其实很简单，因为普通索引能利用 change buffer  来做更新操作;而唯一索引因为要判断更新的值是否是唯一的，所以每次都需要将磁盘中的数据读取到 buffer pool 中。

7、前缀索引

我们要学会巧妙的使用 前缀索引，避免索引值过大。

例如有一个字段是 addr varchar(255)，但是如果一整个建立索引 [ index(addr) ]，会很浪费磁盘空间，所以会选择建立前缀索引 [  index(addr(64)) ]。

建立前缀索引，一定要关注字段的区分度。例如像身份证号码这种字段的区分度很低，只要出生地一样，前面好多个字符都是一样的;这样的话，最不理想时，可能会扫描全表。

前缀索引避免不了回表，即无法使用覆盖索引这个优化点，因为索引值只是字段的前 n 个字符，需要回表才能判断查询值是否和字段值是一致的。

怎么解决?

倒序存储：像身份证这种，后面的几位区分度就非常的高了;我们可以这么查询：

select field_list from t where id_card = reverse('input_id_card_string');

2. 增加 hash 字段并为 hash 字段添加索引。

8、干净的索引列

索引列不能参与计算，要保持索引列“干净”。

假设我们给表 student 的字段 birthday 建立了普通索引。

下面的 SQL 语句不能利用到索引来提升执行效率：

select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';

我们应该改成下面这样：

select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');

9、扩展索引

我们应该尽量 扩展索引，而不是新增索引，一个表最好不要超过 5 个索引;一个表的索引越多，会导致更新操作更加耗费性能。

二、SQL 优化

1、Order By 优化

1.order by 后面的字段尽量是带索引的，这样能避免使用 sort_buffer 进行排序。

• 假如有一条 SQL，根据生日查询所有学生的信息：select * from student order by birthday desc;

• 那么为了提升 SQL 的查询性能，我们可以为 birthday 字段建立索引：

CREATE INDEX index_birthday ON student(birthday);

2.select 后面不要带上不必要的字段，因为如果单行长度太长导致查询数据太多，MySQL 会利用 rowid  排序来代替全字段排序，这样会导致多了回表的操作。

如果我们只是查询学生的姓名、年龄和生日，千万不要写 select *;

而是只查询需要的字段：select name, age, birthday。

2、Join 优化

1. 鸿蒙官方战略合作共建——HarmonyOS技术社区

2. 在使用 join 的时候，应该让小表做驱动表。小表：总数据量最小的表

3. 使用 join 语句，最好保证能利用被驱动表的索引，不然只能使用 BNL(Block Nested-Loop Join)算法，还不如不用。

4. 启用 BKA(Batched Key Access) 算法，使得 NLJ 算法也能利用上  join_buffer，被驱动表可以批量查询到符合条件的值，然后可以利用 MMR(Multi-Range Read) 的顺序读盘特性来提升回表效率。

5. 如果一定要用 join，而且被驱动表没有索引可以使用，那么我们可以利用临时表(create temporary table  xx(...)engine=innodb;)来让 BNL 算法转为 BKA 算法，从而提升查询性能。

6. join_buffer 是一个无序数组，所以每次判断都需要遍历整个 join_buffer。我们可以在业务端实现 hash join 来提升 SQL  的执行速度。

3、Group By 优化

1. 鸿蒙官方战略合作共建——HarmonyOS技术社区

2. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null。

3. 尽量让 group by 过程用上表的索引，不但不需要临时表，还不需要额外的排序。

4. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表。

5. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

4、OR 优化

在 Innodb 引擎下 or 关键字无法使用组合索引。

假设现在关于订单表有一条 SQL ：

select id，product_name from orders where mobile = '12345678900' or user_id = 6;

一般我们为了提升上面 SQL 的查询效率，会想着为字段 mobile 和 user_id 建立一个复合索引  index(mobile,user_id);

可是我们使用 explain 可以发现执行计划里面并没有提示到使用复合索引，所以 or 关键字无法命中 mobile + user_id  的组合索引。

那么我们可以分别为两个字段建立普通索引，然后采用 union 关键字，如下所示：

(select id，product_name from orders where mobile = '12345678900') union (select id，product_name from orders where user_id = 6);

此时 mobile 和 user_id 字段都有索引，查询才最高效。

5、IN 优化

in 关键字适合主表大子表小，exist 关键字适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了，可以尝试改为 join  查询。

假设我们现在有一条 SQL ，要查询 VIP 用户的所有订单数据：

select id from orders where user_id in (select id from user where level = 'VIP');

我们可以发现不会有任何关于索引的优化，所以我们可以采用 join查询，如下所示：

select o.id from orders o join user u on o.user_id = u.id and u.level = 'VIP';

此时被驱动表应该是 user，那么可以利用到 user 表的主键索引，即可以使用 BKA 算法来提升 join 查询的性能。

6、Like 优化

like 用于模糊查询，但是如果是全模糊查询，将不能命中对应字段的索引。

假设现在关于学生表有一条 SQL：

SELECT name,age,birthday FROM student WHERE name like '%张%';

使用 explain 可以发现执行计划提示查询未命中索引。

因为本来需求就是查询姓张的所有同学信息，所以没必要使用全模糊查询，使用右模糊查询即可。

换成下面的写法：

SELECT name,age,birthday FROM student WHERE name like '张%';

但是产品经理一定要前后模糊匹配呢?全文索引 FULLTEXT 可以尝试一下，但是 MySQL 的全文索引不支持中文查询的。

所以说 Elasticsearch 才是终极武器!

三、数据表设计优化

1、数据类型：应该选择更简单或者占用空间更小的类型。

• 整型选择：可以根据长度选择 tinyint、smallint、medium_int，而不是直接使用 int。

• 字符串选择：能确定字符串长度的，尽量使用 char 类型，而不是变长的 varchar 类型。

• 浮点型选择：精度要求比较高的使用 decimal 而不是 double;也可以考虑使用 BIGINT 来保存，小数位保存可以使用乘以整百来解决。

• 日期选择：尽量使用 timestamp 而不是 datetime。

2、避免空值：

NULL 值依然会占用空间，并且会使索引更新更加复杂，更新 NULL 时容易发生索引分裂的现象。

可以使用有意义的值来代替 NULL 值，例如 “none” 字符串等等。

3、超长字符串：

一般超长字符串，varchar 难以存储，我们一般会使用 text 类型。

但是 text 类型的字段尽量避免放在主表中，而是抽出来在子表里，用业务主键关联。

感谢各位的阅读，以上就是“SQL优化技巧有哪些”的内容了，经过本文的学习后，相信大家对SQL优化技巧有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！