步步递进SQL调优的基本操作

2021-12-13  本文已影响0人  技术架构小白白

前言

本文平铺直叙，步步深入，先讲解数据库相关理论知识，再讲到索引，详细介绍explain关键字，最后通过实战教程，讲解sql调优的基本逻辑以及常见的索引失效场景。本文提到的数据库均为Mysql。

1、什么是数据库

作为软件系统开发者，几乎天天都会跟数据库打交道，不知道大家是否有认真思考过，到底什么是数据库。在生活中，保存粮食的仓库我们叫粮库，保存汽油的仓库叫粮库，那么以此类推，保存数据的仓库就叫做仓库。电脑上我们把照片放到同一个文件夹下，那么这个文件夹就是一个照片数据库；把文档资料放到一个文件夹，那么这个文件夹也是一个数据库。

在计算机编程中，数据库的定义和生活中有一定的区别。同样是数据的集合这没有变，但是多了一些条件限定，每一种类型数据集合里面的数据都有固定的内容结构。

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。（摘抄自百度百科）

面试题：如果让你开发一个数据库，该如何进行设计？

2、数据库设计三大范式

2.1、第一范式

数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。
比如用户地址信息，如果仅仅作为属性展示，无相关业务可以仅用一个地址字段进行保存，但如果涉及用户归属省市区查询统计等，则需要再拆分。

2.2、第二范式

确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如订单表（订单号、数量、商品名称、商品介绍、购买人id）把订单和商品详细信息都放在了同一张表中，应该拆分为两个表，商品信息保存在商品表中，订单表只存放订单信息。

2.3、第三范式

表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能依赖于主键）。
示例同范式2，如果已经有了订单表和商品表，那订单表设计字段就只需要包含商品id，不需要额外的商品名称、介绍等字段。
范式2与范式3的本质区别：在于有没有分出两张表。范式2是说一张表中包含了多种不同实体的属性，那么必须要分成多张表，比如分成了订单和商品表。范式3是要求已经分好了多张表的话，一张表中只能有另一张表的ID，而不能有其他任何信息（其他任何信息，一律用主键在另一张表中查询）。

必须先满足范式1才能满足范式2，必须同时满足范式1和2才能满足范式3。

3、什么是索引

索引本质是一种可以高效获取数据的数据结构。
数据库维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

4、索引分类

主键索引：也简称主键，它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长，不允许有空值。
普通（单值）索引：即一个索引只包含单个列，一个表可以有多个普通索引。
唯一索引：索引列的值必须唯一，但允许有空值。
组合（复合）索引：即一个索引包含多个列。
全文索引：主要用来查找文本中的关键字。

聚簇索引：也叫聚集索引，它实际上并不是一种单独的索引类型，而是一种数据存储方式，将数据存储与索引放到了一块，找到索引也就找到了数据。一个表仅有一个聚簇索引。聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
非聚簇索引：也叫辅助索引、二级索引，它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

Innodb的主键是聚簇索引、MyISAM都是非聚簇索引。

5、索引结构

二叉树：每个节点最多有两个子节点，如果是插入有顺序的数据，可能蜕化成链表。
平衡二叉树：基于二叉树容易蜕化成链表的缺点，进行优化，任意节点的左右子树高度差绝对值不超过1 。
红黑树：属于平衡二叉树的一种，引入了红黑色节点。

B树：与二叉树的区别是多叉，又叫多路自平衡查找树。

B+树：B树的升级版，B+树非叶子节点不存储数据，所有数据均保存在叶子节点中，每个叶子节点指向相邻的叶子节点，便于区间查找。

面试题：用InnoDB引擎使用B+树作为Mysql的索引结构有什么好处。
目的是减少磁盘IO次数，加快查询效率，具体体现在下面两点：

6、explain关键字

explain信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

6.1、id

SELECT的查询序列号，表示语句的执行顺序。

6.2、select_type

查询类型，主要用于区别普通查询、联合查询、子查询等复杂的查询

6.3、table

标识SQL操作属于哪张表。显示这一步所访问数据库中表名称，有时不是真实的表名字，可能是别名，也可能是第几步执行的结果简称。

6.4、type

标识对表的访问方式，常用的类型有（从上到下，性能从差到好）：

6.5、possible_keys

表示可能应用在这张表中的索引，一个或者多个，但不一定被查询实际使用。

6.6、key

表示实际使用到的索引，必然包含在possible_keys中。
如果为NULL，则没有建立索引或者索引失效。查询中若使用了覆盖索引（查询的数据列只用从索引中就能够取得，不必再读取数据化，换句话说：查询列要被所建的索引覆盖），则该索引仅出现在key列表。

6.7、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。不损失精确性的情况下，长度越短越好

常用计算说明（utf8编码格式）
varchar(10)且字段允许NULL，key_len = 10*3+2+1
varchar(10) 且字段不允许NULL，key_len = 10*3+2
int字段允许NULL，key_len = 4+1
int字段不允许NULL，key_len = 4
+2表示可变长字段，+1表示可以为空。

6.8、ref

表示哪些列被使用了，如果可能的话，最好是一个常数（const），哪些列或常量被用于查找索引列上的值。


6.9、rows

根据表的统计信息和索引的选用情况，大概估算所需要查询的行数。

6.10、Extra

表示不适合在其他列显示，但是也非常重要的额外信息，挑选几个常见结果说明如下。

需特别注意以下几点：

7、最左前缀原则（最左匹配原则）

可以对某一列建立索引，还可以对多列建立一个复合索引，对复合索引存在一个最左前缀匹配原则的概念。

8、SQL优化实战

建表语句：
CREATE TABLE `user001` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `idcard` varchar(18) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

插入测试数据：
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('张三', 15, '18200000000', 'idcard1');
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('李四', 27, '15900000000', 'idcard2');
INSERT INTO `user001`(`name`, `age`, `mobile`, `idcard`) VALUES ('王五', 29, '13712345678', 'idcard3');

创建复合索引：
alter table `user001` add index idx_nameAgeMobile(`name`, `age`, `mobile`);

1)、未使用任何的where条件。

explain SELECT * from user001;

2)、使用到where条件，但未使用索引字段。

explain SELECT * from user001 where idcard= 'idcard1';

3)、使用到索引字段。

explain select * from user001 where id = 1;
explain select * from user001 where name = '张三';
explain select * from user001 where id = 1 and name = '张三';
explain select * from user001 where name = '张三' and age = 15;
explain select * from user001 where name = '张三' and mobile = '18200000000';
explain select * from user001 where name = '张三' and idcard = 'idcard1';
explain select * from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select * from user001 where mobile = '18200000000' and age = 15 and name = '张三';
explain select * from user001 where name = '张三' and age > 15;
explain select * from user001 where name = '张三' and age > 15 and mobile = '18200000000';
explain select * from user001 where name = '张三' and age > 15 and idcard = 'idcard1';
explain select * from user001 where name = '张三' and age = 15 and mobile like '18%';

3)、使用到索引字段，但未遵循最左前缀匹配原则。

explain select * from user001 where age = 15;
explain select * from user001 where mobile= '15900000000';
explain select * from user001 where age = 15 and mobile= '15900000000';

4)、使用到索引字段，但在该字段上进行了操作（计算、函数、自动或者手动类型转换）。

explain select * from user001 where name = '张三';
explain select * from user001 where left(name,2) = '张三';
explain select * from user001 where left(name,2) = '张三';
explain select * from user001 where name = '张三' and (age + 1) > 15;
explain select * from user001 where name = 2000;
explain select * from user001 where name = '2000';

5)、尽量使用覆盖索引。

explain select * from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select name,age,mobile from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select name,age,mobile from user001 where name = '张三' and age > 15 and mobile = '18200000000';
explain select name from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select name,mobile from user001 where name = '张三' and age = 15 and mobile = '18200000000';
explain select age,mobile from user001 where name = '张三' and age = 15 and mobile = '18200000000';

6)、is null、is not null无法使用索引。

explain select * from user001 where name is null;
explain select * from user001 where name is not null;

7)、like以通配符开头（%abc..)会使索引失效。

explain select * from user001 where name like '%张三%';
explain select * from user001 where name like '%张三';
explain select * from user001 where name like '张三%';
explain select * from user001 where name like '张三%' and age = 15;
explain select * from user001 where name like '%张三' and age = 15;
explain select * from user001 where name like '张%三%' and age = 15;

面试题：如何解决like '%xx%'导致索引失效？

使用覆盖索引
explain select name from user001 where name like '%张三%';

explain select id from user001 where name like '%张三%';
explain select id,name from user001 where name like '%张三%';

8)、order by。

explain select * from user001 where name = '张三' and mobile = '18200000000' order by age;
explain select * from user001 where name = '张三' order by age;
explain select * from user001 where name = '张三' order by mobile;
explain select * from user001 where name = '张三' order by idcard;
explain select * from user001 where name = '张三' order by age,mobile;
explain select * from user001 where name = '张三' order by mobile,age;
explain select * from user001 where name = '张三' and age = 15 order by age,mobile;
explain select * from user001 where name = '张三' and age = 15 order by mobile,age;

order by关键字优化（主要是避免文件排序）

explain select * from user001 where name = '张三' and age > 10 order by age;
explain select * from user001 where name = '张三' and age > 10 order by age,mobile;
explain select * from user001 where name = '张三' and age > 10 order by mobile;
explain select * from user001 where name = '张三' and age > 10 order by mobile,age;

假设复合索引为a_b_c(a , b , c)
1、order by能使用索引最左前缀
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc
2、如果where使用索引的最左前缀定义为常量，则order by能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c
3、不能使用索引进行排序
order by a asc,b desc,c desc //排序不一样
where d = const order by b,c //丢失a索引
where a = const order by c //丢失b索引
where a = const order by a,d //d不是索引的一部分
where a = in (...) order b,c //对于排序来说，多个相等条件也是范围查询

9)、group by。分组前基本上都需要先排序，可能会有临时表产生

explain select * from user001 where name = '张三' group by age,mobile;
explain select * from user001 where name = '张三' group by mobile,age;

group by关键字优化

10)、查询结果数量过大，占据了大表30%以上

explain select * from health_record where institutionId like '01%';
explain select * from health_record where institutionId like '0101%';

总结：

9、索引失效场景

面试题：如何解决like '%xx%'导致索引失效？------覆盖索引

10、SQL优化步骤

上一篇 下一篇

猜你喜欢

热点阅读