SQL优化

weixin_43766298

已于 2022-05-26 18:09:27 修改

阅读量223

点赞数

分类专栏： JAVA后端面试文章标签： sql mysql 数据库

于 2021-04-11 17:06:59 首次发布

本文链接：https://blog.csdn.net/weixin_43766298/article/details/115602269

版权

JAVA后端面试专栏收录该内容

38 篇文章 0 订阅

订阅专栏

SQL优化

1.完整SQL语法和SQL执行顺序

Mysql查询完整语法

SELECT DISTINCT [column_list]
FROM [table_list]
JOIN(INNER/LEFT/RIGHT) [table] ON [condition]  
WHERE    [condition]
GROUP BY [column_name]
HAVING   [condition]
ORDER BY [column_name] [ASC/DESC]
LIMIT    [num1,num2]

Mysql查询语句执行顺序

1.  FROM      对多个表进行求笛卡儿积形成1个虚表。
2.  JOIN  ON  把符合ON条件的连接表数据连接到虚表。    
3.  WHERE     分组前筛选,对整个虚表进行筛选。
4.  GROUP BY  分组
5.  聚合函数   AVG/MAX/MIN 
6.  HAVING    分组后筛选
7.  SELECT    查询
8.  DISTINCT  去重
9.  ORDER BY  排序
10. LIMIT     分页

2.避免不走索引的场景

尽量避免在字段开头模糊查询，会导致数据库放弃索引进行全表扫描
某些列的值可以采用固定开头，例如对象编码：OC-20201224 000001 编码规则
不使用1 而使用固定位数的 000001 是为了模糊查询给定精确编码如 OC-20201224 1
却查询到了 OC-20201224 11 或者 OC-20201224 111.

已知user表的username是有索引的
SELECT * FROM user WHERE username LIKE '%TEST%';

1.采用后模糊可以使用到索引
SELECT * FROM user WHERE username LIKE 'TEST%';

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

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

1.如果是连续数值，可以用between代替
SELECT * FROM user WHERE id BETWEEN 2 AND 4;

2.如果是子查询，可以用exists关键字代替
-- 不走索引
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
-- 走索引
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

使用union / union all代替or,使用or会导致数据库放弃索引进行全表扫描
union 会对数据进行去重处理,因此union all效率是高于union 的,能保证数据不重复尽量使用union all

--不走索引
SELECT * FROM user WHERE username = 'zs' or username = 'ls';
--走索引
SELECT * FROM user WHERE username = 'zs';
UNION ALL
SELECT * FROM user WHERE username = 'ls';

尽量避免进行NULL值判断,会导致数据库放弃索引进行全表扫描

SELECT * FROM user WHERE username IS NULL;

1.可以给字段添加默认值 '',这样就可以使用到索引了
SELECT * FROM user WHERE username = '';

大数据量的时候, 避免使用where 1=1的条件,一般为了方便动态拼装where条件,一旦只有这一个条件成立会导致全表扫描
优化: 进行SQL拼接的时候,有条件就进行查询,无条件就不查询

SELECT * FROM user WHERE 1=1;

where条件仅包含复合索引的非最左侧列,应该遵循Mysql复合索引的最左匹配原则

已知user表存在复合索引: id_name_age
SELECT * FROM user WHERE name = 'zs' AND age = 12;

只有order by 字段出现在where条件中时, 才会利用该字段的索引而避免排序,更准确的说, order by 中的字段在执行计划中利用了索引时, 不用排序操作

SELECT * FROM user WHERE id < 5000 ORDER BY id DESC;

尽量避免在where条件中等号的左侧进行表达式或函数操作，会导致数据库引擎放弃索引进行全表查询

SELECT * FROM t2 WHERE score/10 = 9
优化方式：
SELECT * FROM t2 WHERE score = 10*9

3.其它优化

1.使用Join替代返回字段中的子查询

--查询很慢
SELECT a.id,(SELECT b.typeName 
             FROM tb_type b WHERE b.id = a.typeId) aType  
FROM tb_a a 

--优化手段
SELECT a.id,b.typeName  
FROM tb_a a 
JOIN tb_type b ON b.id = a.typeId

2.表中建立索引，优先考虑where / group by / on 使用的字段
3.避免出现select *, 查询返回业务需要的字段,避免不必要内存消耗

SELECT * FROM user;
替换成
SELECT username,age FROM user;

4.调整Where后面条件的连接顺序，应将过滤数据多的条件往前放，最快速度缩小结果集

MySQL采用从左往右顺序解析where子句

5. 遵循小表驱动大表原则（Mysql），让小表成为驱动表
- 当使用left join时，左表是驱动表，右表是被驱动表
- 当使用right join时，右表时驱动表，左表是被驱动表
- 当使用inner join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表

4.sql优化神器 explain

explain

explain（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息，对日常生产过程中调优十分有用。
- id: select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。
- type: 主要取值及其表示sql的好坏程度（由好到差排序）：
  system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。
- key: 实际被使用的索引列。
- ref: 关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。
- Extra: 额外信息。使用优先级Using index> Using filesort > Using temporary。

5.数据结构的各种树

二叉树： 每个节点最多只能有两颗子树，且有左右次序之分。
二叉搜索树： 是一种特殊的二叉树。对节点的值进行了有序的排列，加速了数据的定位。
- 如果当前一个节点的左子树不为空，那么左子树上所有节点的值都小于当前节点的值。
- 如果当前一个节点的右子树不为空，那么右子树上所有节点的值都大于当前节点的值。
二叉平衡树AVL： 为了解决二叉搜索树删除节点后退化成线性链表的问题而出现的数据结构。
- 在二叉搜索树的基础上，要求每个节点的左子树和右子树的高度差最多为1。
- 插入或这删除节点的时候，会发生左旋和右旋操作，使得这颗树的左右再次保持一定的平衡。
B树： 为了解决AVL树随着数据量的增大，树的高度不断增加，查询效率降低而出现的数据结构。
- 每个节点都存储了key和data。key存储的是一条的记录的键，data存储的一条记录除key之外的内容。
  * B树允许一个节点包含多个key，随着数据量增大，它能降低树的总高度，提高查询效率。
B+树： 为了解决B树范围查询的效率低下问题（需要进行层级别的递归遍历）而出现的数据结构。
- B+树的非叶子节点仅具有索引的作用，非叶子节点只存储key，不存储data。
- B+树的所有叶子节点构成一个key有序的双向链表，同时存储data，这样就大大加速了范围查询效率。
- 由于data都在一个线性结构上，因此遍历速度将会大大提升。

6.索引相关

6.1 聚集索引（主键索引）

将数据和索引放在一起，索引结构的叶子节点保存了 行数据
聚集索引默认是主键索引，一个表最多只能有一个聚集索引。

6.2 非聚集索引（辅助索引）

将数据和索引分开存储。索引结构的叶子节点只包含索引列值 和 数据行的主键值。
主要包含了 单字段索引 和 联合索引。
一般查找流程（回表）： 先通过属性索引找到对应的主键值 ==> 再根据主键值去主键索引中找到需要的数据行

表   ===> user(id,name) 
索引 ===> index_demo(name)

index_demo索引叶子节点只包含了name列的值和主键id的值。  

如果需要查询全部数据，需要先在辅助索引中拿主键id值 ，再去主键索引中查询数据行。

6.3 聚集索引和非聚集索引的区别

聚集索引：可以在索引结构中直接查询到需要返回的任何数据。
非聚集索引：可以在索引结构中查询到记录对应的主键值，再根据主键值去主键索引中查找需要的数据。

6.4 覆盖索引

需要查询的字段值都可以在辅助索引中拿到。
sql查询只要在辅助索引当中就可以查询到需要的数据，而不需要在辅助索引中拿到主键值，再去主键索引里去查询数据行。

  --创建联合索引
  create index index_bu on user(age, userName)
  
  -- age字段用到联合索引index_bu
  -- 查询需要返回的userName字段刚好在联合索引index_bu中
  -- 这样就可以直接在辅助索引当中查询到数据并返回
  select userName from user where age = 10

6.5 索引的劣势

当给一个字段建立索引时，字段中的数据就会被复制一份出来，用于生成索引结构。
因此，给表添加索引，会增加表的体积，占用磁盘空间。

6.6 索引的适用场景

主键自动建立唯一索引和非空约束
频繁作为查询条件的字段
查询中和其它表进行关联的字段
查询中排序的字段
查询中分组的字段

6.7 不适合索引的场景

经常增删改的表
频繁更新的字段，每次更新不仅会更新记录，还要更新索引，加重了IO负担

7.JDBC

7.1 JDBC使用占位符的好处

1.可对SQL进行预编译（编译后SQL结构固定），防止SQL注入
2.绑定变量，这样可以减少SQL的硬解析，避免每一次都将进行解析并生成执行计划，从而提高执行效率
3.增加SQL语句的可读性

8. 联合索引的分析

#给表tb_item_cat添加复合索引
alter table tb_item_cat  add INDEX(parent_id,name,type_id);

#设 parent_id --> 1 ,name --> 2, type_id -- > 3,下面进行排列组合

#复合索引有效 - 1
EXPLAIN  SELECT * FROM tb_item_cat a WHERE a.parent_id = 1;

#复合索引有效 - 1 2 3
EXPLAIN  SELECT * FROM tb_item_cat a WHERE a.parent_id = 1 and a.`name`='电子书刊' AND a.type_id = 35;

#复合索引有效 - 1 2
EXPLAIN  SELECT * FROM tb_item_cat a WHERE a.parent_id = 1 and a.`name`='电子书刊';

#复合索引无效	- 1 3
EXPLAIN  SELECT * FROM tb_item_cat a WHERE a.parent_id = 1  AND a.type_id = 35;

#复合索引无效	- 2 3
EXPLAIN  SELECT * FROM tb_item_cat a WHERE  a.`name`='电子书刊' AND a.type_id = 35;
	
#结论 复合索引符合最左侧原则

9. SQL执行次数分析

通过该命令可以查看当前数据库下的增删改查的使用次数,来采取对应的优化处理. value值就是执行的次数.

show global status like 'com_______';

variable_name	Value
Com_backup	336
Com_binlog	0
Com_commit	1385559
Com_delete	128504
Com_insert	39742483
Com_repair	0
Com_revoke	0
Com_select	316716995
Com_signal	0
Com_update	48330286
Com_xa_end	0

10. SQL优化~慢查询日志分析

慢查询日志会记录超出自己设置的时间还没有执行完毕的sql.
默认情况下，Mysql数据库并不启动慢查询日志，
需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

SELECT VERSION(); 10.4.6-MariaDB-log

--开启慢查询日志: 
set global slow_query_log=1;

--设置慢查询的超时时间(以秒为单位):
set global long_query_time=2;

--查询慢查询日志相关信息
show variables like '%slow_query_log%';

variable_name	Value
slow_query_log	ON
slow_query_log_file	/apps/logs/mysql/slow3306.log

注意: 使用set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf

weixin_43766298

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL优化

SQL优化篇1.完整SQL语法和SQL执行顺序1.完整SQL语法和SQL执行顺序Mysql查询完整语法SELECT DISTINCT [column_list]FROM [table_list]JOIN(INNER/LEFT/RIGHT) [table] ON [condition] WHERE [condition]GROUP BY [column_name]HAVING [condition]ORDER BY [column_name] [ASC/DESC]LIMIT
复制链接

扫一扫