300字范文 > Mysql复习计划（四）- 索引失效和数据库设计规范

Mysql复习计划（四）- 索引失效和数据库设计规范

时间：2024-05-20 02:38:33

一. 索引失效1.1 数据准备1.2 最左匹配原则1.3 计算、函数、类型转换导致索引失效1.4 范围条件右侧的列索引失效1.5 不等于判断造成索引失效1.6 is not null 造成索引失效1.7 以%为开头的模糊查询造成索引失效1.8 or 的前后存在非索引列造成索引失效二. join语句原理2.1 简单嵌套循环连接2.2 索引嵌套循环连接2.3 块嵌套循环连接三. 覆盖索引和索引下推3.1 覆盖索引3.2 索引下推3.2.1 开启和关闭ICP的性能对比3.2.2 ICP使用的注意事项 3.3 补充查询优化策略3.3.1 Exists 和 In 的区分3.3.2 count(*) 和count(字段)效率3.3.3 select * 注意事项3.3.4 limit 1对优化的影响四. 数据库设计规范4.1 数据库主键的设计4.1.1 自增ID回溯问题4.1.2 推荐主键设计 4.2 范式4.2.1 键和相关属性概念4.2.2 各个范式的概念4.2.3 反规范化

一. 索引失效

1.1 数据准备

1.建表和创建相关的存储函数：

# 班级表CREATE TABLE `class` (`id` INT(11) NOT NULL AUTO_INCREMENT,`className` VARCHAR(30) DEFAULT NULL,`address` VARCHAR(40) DEFAULT NULL,`monitor` INT NULL ,PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;# 学生表CREATE TABLE `student` (`id` INT(11) NOT NULL AUTO_INCREMENT,`stuno` INT NOT NULL ,`name` VARCHAR(20) DEFAULT NULL,`age` INT(3) DEFAULT NULL,`classId` INT(11) DEFAULT NULL,PRIMARY KEY (`id`)#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;set global log_bin_trust_function_creators=1;#随机产生字符串DELIMITER //CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1;END WHILE;RETURN return_str;END //DELIMITER ;#用于随机产生多少到多少的编号DELIMITER //CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) BEGINDECLARE i INT DEFAULT 0;SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;RETURN i;END //DELIMITER ;#创建往stu表中插入数据的存储过程DELIMITER //CREATE PROCEDURE insert_stu( START INT , max_num INT ) BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0; #设置手动提交事务REPEAT #循环SET i=i+1; #赋值INSERT INTO student (stuno, name ,age ,classId ) VALUES((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); UNTIL i = max_numEND REPEAT;COMMIT; #提交事务END //DELIMITER ;#执行存储过程，往class表添加随机数据DELIMITER //CREATE PROCEDURE `insert_class`( max_num INT ) BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO class ( classname,address,monitor ) VALUES(rand_string(8),rand_string(10),rand_num(1,100000));UNTIL i = max_numEND REPEAT;COMMIT;END //DELIMITER ;# 删除某张表的某个索引DELIMITER //CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT '';DECLARE _cur CURSOR FOR SELECT index_name FROMinformation_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ;#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ; #若没有数据返回,程序继续,并将变量done设为2OPEN _cur;FETCH _cur INTO _index;WHILE _index<>'' DOSET @str = CONCAT("drop index " , _index , " on " , tablename );PREPARE sql_str FROM @str ;EXECUTE sql_str;DEALLOCATE PREPARE sql_str;SET _index='';FETCH _cur INTO _index;END WHILE;CLOSE _cur;END //DELIMITER ;

2.执行函数，创建数据：

#执行存储过程，往class表添加1万条数据 CALL insert_class(10000);#执行存储过程，往stu表添加50万条数据CALL insert_stu(100000,200000);

1.2 最左匹配原则

案例如下：首先创建个联合索引，注意顺序：age,classId,name

CREATE INDEX idx_age_classid_name ON student(age,classId,name);EXPLAIN SELECT SQL_NO_CACHE * FROM student where age = 30 and classid = 1 and name = 'abcd';EXPLAIN SELECT SQL_NO_CACHE * FROM student where classid = 1 and name = 'abcd';EXPLAIN SELECT SQL_NO_CACHE * FROM student where age = 30 and name = 'abcd';

可以发现：

首先我们联合索引是以age为开头的，根据最左匹配原则，若查询条件不带有age列相关查询，此时索引会失效。对应上图的第二个查询，没有用到任何索引。并且对于的type为ALL，说明进行了全表扫描。我们写的SQL中关于where子句中条件的顺序问题，其实没什么关系，因为优化器会帮助我们优化语句。联合索引为age,classId,name，若不带classId或者name，依旧是可以根据索引查询的。但是此时只会根据最左侧的字段来索引，跳过的字段以及其后续的索引都无法被使用。

1.3 计算、函数、类型转换导致索引失效

案例1：函数导致索引失效

CREATE INDEX idx_name ON student(NAME);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

结果如下：可见后者并没有使用索引，出现了索引失效。

案例2：计算导致索引失效

CREATE INDEX idx_sno ON student(stuno);EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

结果如下：可见后者并没有使用索引，出现了索引失效。

案例3：类型转换导致索引失效

注意，name是字符串列。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

结果如下：可见后者并没有使用索引，出现了索引失效。此时name做了类型转换，从string转为int类型。

1.4 范围条件右侧的列索引失效

测试之前，记得把student表中，将除了刚开始创建的联合索引之外的所有索引都删除。

如图，student表中还剩下一个联合索引

此时我们执行语句

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId = 20 AND student.name = 'abc' ;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId > 20 AND student.name = 'abc' ;

结果如下：

1.5 不等于判断造成索引失效

不等于判断是啥呢？就是!=以及<>

例如：

CREATE INDEX id_name ON student(name);EXPLAIN SELECT SQL_NO_CACHE * from student where name !='abc';

结果如下：

1.6 is not null 造成索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

1.7 以%为开头的模糊查询造成索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name like 'ab%';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name like '%ab%';

1.8 or 的前后存在非索引列造成索引失效

# 去除表的所有索引CALL proc_drop_index('dbtest1','student');# 建立age索引create index idx_age on student(age);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 or classid=100;

结果如下：可见索引失效

但是此时如果我再给classid列添加索引，然后在尝试下看看：

create index idx_classid on student(classid);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 or classid=100;

结果如下：

可见此时用到了索引。说明只要在or的前后存在一个非索引列的时候，索引就会失效。

同时需要大家注意：

不同的字符集之间的比较，会进行转换操作，此时也会造成索引失效。

二. join语句原理

例如一个很简单的join语句：

select * from a left join b on (a.c1 = b.c1) and a.c2 = b.c2;

2.1 简单嵌套循环连接

如图：

假设表A有100条数据，表B有50条数据。那么该算法的实现过程就是：

循环遍历表A的每一条数据。每一次循环，拿着当前表A的遍历到的一行数据，去被驱动表B中进行扫描匹配。此时外表（表A）扫描次数为1次。内表（表B）扫描次数为100次。读取记录的次数：100+100*50。join比较的次数为100*50。

对于内表的扫描次数，可以发现完全决定于外表的数据量。因此我们也常常听别人说过，join语句的使用时，要小表join大表。

由于这种算法的效率非常的低下，因此有了其他两种优化算法。

2.2 索引嵌套循环连接

如图：

本算法主要的思路就是减少内层表数据的匹配次数。因此要求被驱动表上必须要有索引：

通过外层表匹配条件直接和内层表索引进行匹配。避免和内层表的每条记录进行比较。

说白了，就是算法1的基础上增加个索引，避免全表扫描。当然需要大家注意几点：

被驱动表增加索引，效率是非常高的，但是索引如果不是主键索引，就得做一次回表查询。因此最好是使用主键索引进行匹配。

2.3 块嵌套循环连接

根据前面两种算法：

若join的列存在索引，就用索引嵌套算法。若join的列不存在索引，就用简单嵌套算法。效率低下。

从另一个角度来看：

每次访问被驱动表，其中的表记录都会加载到内存中。再从驱动表中取一条记录进行匹配，匹配结束后清除内存。上述的操作循环执行，大大增加了IO的次数，因此为了减少被驱动表的IO次数，就出现了块嵌套循环连接。

块嵌套循环连接：

相比之前的一条一条记录的IO，成了一块一块数据的获取。引入join buffer缓冲区，将驱动表join相关的部分数据列缓存到缓冲区中，然后全表扫描被驱动表。被驱动表的每一条记录一次性和join buffer缓冲区中的所有记录进行匹配。即将多次比较合并成一次。

如图：

小结下就是：

整体的效率比较：索引嵌套> 块嵌套>简单嵌套。永远都用小表作为驱动表，减少外层循环的次数。增大join buffer size的大小，一次缓存的数据越多，那么内表扫描的次数越少。

三. 覆盖索引和索引下推

3.1 覆盖索引

覆盖索引的概念：一个索引包含了满足查询结果的数据就叫做覆盖索引。

# 创建一个age和name的联合索引create index idx_age_name on student(age,name);EXPLAIN SELECT * from student where age <> 20;EXPLAIN SELECT age,name from student where age <> 20;

看看结果是什么：

在第一章节中，我们知道，当使用不等于判断的时候，索引会失效，但是为什么第二个SQL语句，在使用了<>的情况下，依旧用到了索引呢？这里就用到了覆盖索引的概念，做个解释：

第一个SQL语句，是select *操作，即需要获取到所有列的数据。第二个SQL语句，是SELECT age,name操作，只需要获取指定的列。InnoDB引擎中，只有主键的B+树，其叶子节点存储了列的完整信息，而其他的非聚簇索引，是不包含的，因此第一个SQL语句需要进行回表操作。而第二个SQL语句中，已经根据age和name建立了联合索引，而联合索引的本质也是非聚簇索引，是包含对应列的相关值的。并且联合索引包含的信息，正好覆盖到了查询的返回集。此时就不需要进行回表查询，优化器认为此时利用索引查询的效率比不使用索引而去回表查询的效率要高，因此使用了索引。

覆盖索引的优点：

避免Innodb表进行索引的二次查询(回表)。可以把随机IO变成顺序IO加快查询效率。

覆盖索引的缺点：

需要进行维护索引字段的维护，联合索引覆盖面越广，维护成本越大。

3.2 索引下推

索引下推：Index Condition Pushdown(ICP)，是Mysql5.6中的新特性。一种在存储引擎层使用索引过滤数据的一种优化方式。

用一种直观的对比就是：

没有ICP的情况：存储引擎会遍历索引来定位基表中的数据行，并将他们返回给Mysql服务器，由Mysql服务器来评估where子句是否保留行，即进行过滤操作。有ICP的情况：若部分where子句可以仅仅使用索引中的列来评估，那么这部分会交给存储引擎来进行筛选（过滤）。

ICP的作用：

减少存储引擎访问基表的次数。减少Mysql服务器访问存储引擎的次数。

其实，我们使用Explain进行分析的时候，Extra字段中，倘若出现了Using index condition，说明就使用了索引下推。

Using index condition：搜索条件中虽然出现了索引列，但是却不能使用索引，比如使用模糊查询导致索引失效。复习见上文Mysql复习计划（三）- 索引的设计原则和Mysql性能分析

例如SQL语句：

EXPLAIN SELECT * from s1 where key1 > 'z' and key1 like '%a';

再给个案例：

1.首先，将student表中的索引全部删除，然后建立个联合索引：

CREATE INDEX idx_age_classid_name ON student(age,classId,name);# 正常的使用到了联合索引中的全部索引。EXPLAIN SELECT SQL_NO_CACHE * FROM student where age = 30 and classid = 1 and name = 'abcd';# 使用了部分索引的情况，因为classid这个列，使用了<>EXPLAIN SELECT SQL_NO_CACHE * FROM student where age = 30 and classid <> 1 and name = 'abcd';

看下结果：

咱们用通俗点的话来说就是。

首先通过age索引进行查找，找到100条数据。若没有ICP的情况下，由于此时使用了<>运算，索引失效，那么一般会直接进行回表查询，要查100次。但是有ICP的情况下，会在已有的100条数据的基础上，做一次过滤，此时数据可能过滤到10条了，此时再去回表查询。只用查10次。

3.2.1 开启和关闭ICP的性能对比

CREATE table people(id int not NULLAUTO_INCREMENT,zipcode VARCHAR(20) COLLATE utf8_bin DEFAULT null,firstname VARCHAR(20) COLLATE utf8_bin DEFAULT null,lastname VARCHAR(20) COLLATE utf8_bin DEFAULT null,address VARCHAR(50) COLLATE utf8_bin DEFAULT null,PRIMARY KEY(id),key zip_last_first (zipcode,lastname,firstname))ENGINE = INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE = utf8_bin;INSERT into people values (1,'00001','三','张','北京'),(2,'00002','四','李','南京'),(3,'00003','五','王','浙江'),(4,'00004','六','赵','江苏');DELIMITER //CREATE PROCEDURE insert_people(max_num INT)BEGINDECLARE i int default 0;set autocommit = 0;repeatset i = i+1;insert into people(zipcode,firstname,lastname,address) values('00001','七','赵','海南');until i = max_numend repeat;commit;end //DELIMITER;call insert_people(10000);# 打开profilingset profiling = 1;

此时进行测试：第一次调用SQL：

SELECT * from people where zipcode = '00001' and lastname like '%张%';

结果如下：0.035s

第二次调用SQL，关闭索引下推：

SELECT /*+ no_icp (people) */ * from people where zipcode = '00001' and lastname like '%张%';

结果如下：0.134s

当然，数据量越大，这个差别体现的也就越明显，也可以通过这种方式来关闭ICP功能：

# 关闭set optimizer_switch = 'index_condition_pushdown=off';# 开启set optimizer_switch = 'index_condition_pushdown=on';

3.2.2 ICP使用的注意事项

当SQL使用覆盖索引的时候，不支持ICP，因为此时使用ICP的作用不大，并不会减少IO。对于InnoDB表，ICP仅仅适用于二级索引。

3.3 补充查询优化策略

3.3.1 Exists 和 In 的区分

# existsselect * from A where exists (select col from B where B.col = A.col);# inselect * from A where col in (select col from B);

一般来说，选择标准都是小表驱动大表。

当A小于B的时候，用Exists。因为Exists的实现相当于外表循环。

for(i int A){for(j int B){}}

相反，B小于A的时候，用In，因为其实现相当于内表循环：

for(i int B){for(j int A){}}

3.3.2 count(*) 和count(字段)效率

Mysql中统计表的数据其实一般有三种写法：

select count(*);select count(1);select count(具体的某个字段);

count(*)和count(1)本质上没有区别。

倘若在InnoDB表中进行扫描，若采用count(具体字段)的方式来统计行数，尽量采用二级索引，因为主键为聚簇索引，包含的数据信息比较多。对于前两者方式，系统会自动采用占用空间更小的二级索引来进行统计。

3.3.3 select * 注意事项

首先，在表查询的时候，应该明确查询的字段，不要使用*作为查询的字段列表。原因如下：

Mysql在解析的时候，会通过查询数据字典，将*按序转化成所有的列名，会大大增加耗费的时间和资源。无法使用覆盖索引。

3.3.4 limit 1对优化的影响

若某些SQL语句是会全表扫描的，并且能够确定结果集只有一条数据，那么此时加上limit 1的时候，当找到了目标结果，就会停止继续扫描，从而加快查询速度。

四. 数据库设计规范

4.1 数据库主键的设计

首先来说下自增主键ID有哪些问题：

可靠性不高：存在自增ID回溯的问题。安全性不高：对外暴露的接口可以非常容易猜测对应的信息，进行爬虫来获取数据。性能差：自增ID的性能较差，需要在数据库服务器端生成。交互多：当前插入的数据，若需要知道其自增ID是多少，那么需要多一次IO的交互，才能够得知。局部唯一性：最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在分布式系统来说，没台服务器之间无法保证自增ID的唯一性。

4.1.1 自增ID回溯问题

首先，自增ID回溯问题，在Mysql8.0才恢复，因此我们使用Mysql5.7版本来复现。废话不多说，直接上案例：

# 创建新表CREATE TABLE `teacher` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 插入几条数据INSERT into teacher values(1,'a'),(2,'a'),(3,'a')

查看下表：

我们使用命令查看下当前的AUTO_INCREMENT值是多少：AUTO_INCREMENT=4

show create table teacher;# 结果如下CREATE TABLE `teacher` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

此时呢，我们将Id为3的这条数据删除：再次查看AUTO_INCREMENT的值发现还是4

show create table teacher;

此时重启Mysql：

docker restart mysql;

再次查看值发现：AUTO_INCREMENT的值变回了3，这就是自增ID回溯。

CREATE TABLE `teacher` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

4.1.2 推荐主键设计

以淘宝的主键设计为例，其可能为：

订单ID = 时间 + 去重字段 + 用户ID后6位尾号

对于非核心业务，我们可以使用主键自增ID。对于核心业务，主键设计的目标至少是全局唯一并且单调递增。

最简单的主键设计就是UUID。其特点：全局唯一，占用36字节，数据无序，插入性能差。

Mysql的UUID组成结构为：

UUID = 时间+UUID版本(16字节)- 时钟序列(4字节) - MAC地址(12字节)

改造UUID：

将时间高低位互换，例如使用时间戳，就是单调递增的。Mysql8.0就支持这样的UUID存储，同时除去了UUID字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节

例如：MySQL8.0提供的uuid_to_bin函数实现上述功能

SET @uuid = UUID();SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

结果如下：保证全局唯一+单调递增。

4.2 范式

范式（Normal Form简称NF）的概念：在关系型数据库中，关于数据表设计的基本原则、规则就称之为范式。

按照范式的级别，从低到高分别是：

第一范式（1NF）。第二范式（2NF）。第三范式（3NF）。巴斯-科德范式（BCNF）了解即可。第四范式（4NF）了解即可。第五范式（5NF）了解即可。

4.2.1 键和相关属性概念

超键：能够唯一标识元组的属性集。候选键：如果超键不包括多余的属性，那么这个超键就是候选键。主键（主码）：用户可以从候选键中选做一个作为主键。外键：若数据表A中的某个属性集并不是A的主键，而是另外一个数据表B中的主键，那么该属性集就是数据表A的外键。主属性：包含在任一候选键中的属性称之为主属性。非主属性：与主属性相对的概念。

例如存在两张表：

球员表(player) :球员编号 | 姓名 | 身份证号 | 年龄 | 球队编号球队表(team) :球队编号 | 主教练 | 球队所在地

那么这里对应的上述属性就是：

超键 :对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如(球员编号) (球员编号，姓名)(身份证号，年龄)等。候选键 :就是最小的超键，对于球员表来说，候选键就是(球员编号)或者(身份证号)。主键 :我们自己选定，也就是从候选键中选择一个，比如(球员编号)。外键 :球员表中的球队编号。主属性/非主属性 :在球员表中，主属性是(球员编号)(身份证号)，其他的属性(姓名)(年龄)(球队编号)都是非主属性。

4.2.2 各个范式的概念

第一范式：确保数据表中每个字段的值必须具有原子性，即数据表中每个字段的值为不可拆分的最小数据单元。

第二范式：在满足第一范式的基础上，还要满足数据表里的每一条数据记录都是可唯一标识的。同时所有非主键字段都必须完全依赖主键。

第三范式：在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关。即要求数据表中的所有非主键字段不能依赖于其他非主键字段。

巴斯范式：在第三范式的基础上，对第三范式的设计规范要求更强，使得数据库冗余度更小。即只存在一个候选键，或者它的每个候选键都是单属性。此时关系成为了巴斯范式。（因为其相当于扩充的第三范式，因此还并不能称之为第四范式）它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

第四范式：在巴斯范式的基础上，消除非平凡且非函数依赖的多值依赖。

多值依赖概念：

多值依赖：属性之间的一对多关系，记为K→→A。函数依赖：本质上是单值依赖。不能表达属性值之间的一对多关系。平凡的多值依赖：全集U=K+A，一个K可以对应多个A，此时整个表就是一组一对多关系。非平凡的多值依赖：全集U=K+A+B，一个K可以对应多个A或者B。A和B之间相互独立。K→→A，K→→B，整个表有多组一对多关系。

第五范式：满足第四范式的基础上，消除不是由候选键所蕴含的连接依赖。若关系模式R中的每一个连接依赖都由R的候选键所隐含，此时此关系模式叫第五范式。