9. 区分度高(散列性高)的列适合作为索引

列的基数: 指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

可以使用公式 select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

拓展:联合索引把区分度高(散列性高)的列放在前面。

测试

# ⑨区分度高(散列性高)的列适合作为索引
# 略

10.使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率

测试

# ⑩使用最频繁的列放到联合索引的左侧
SELECT *
FROM student_info
WHERE student_id=10013 AND course_id=100;
# 频繁放左边

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

测试

# 补充：在多个字段都要创建索引的情况下，联合索引优于单值索引
# 略

测试代码：04-索引的设计原则.sql

测试代码

04-索引的设计原则

# 04-索引的设计原则
#1.数据的准备
CREATE DATABASE atguigudb1;
USE atguigudb1;
#第1步：创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `course` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_id` INT NOT NULL ,
`course_name` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#第2步：创建模拟数据必需的存储函数
# 函数报错 1418
SELECT @@log_bin_trust_function_creators;
SET GLOBAL log_bin_trust_function_creators=1;
#函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
# 第3步：创建插入模拟数据的存储过程
# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES
(rand_num(10000,10100),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
# 第4步：调用存储过程
CALL insert_course(100);
CALL insert_stu(1000000);
#测试
SELECT COUNT(*) FROM course;    #100
SELECT COUNT(*) FROM student_info;  #1000000
#2.哪些情况适合创建索引
#① 字段的数值有唯一性的限制
#② 频繁作为 WHERE 查询条件的字段
#查看当前stduent_info表中的索引
SHOW INDEX FROM student_info;
#student_id字段上没有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #276ms
#给student_id字段添加索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
#student_id字段上有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #43ms
#将作为where查询条件的字段student_id设为索引后查询效率提高了
#③ 经常 GROUP BY 和 ORDER BY 的列
#student_id字段上有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #41ms
#删除idx_sid索引
DROP INDEX idx_sid ON student_info;
#student_id字段上没有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #866ms
#再测试：
SHOW INDEX FROM student_info;
#添加单列索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);
# 报错1055 sql_mode  性能分析explain 只使用idx_sid索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #5.212s
#解决1055
#修改sql_mode
SELECT @@sql_mode;
SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
#添加联合索引
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id,create_time DESC);
# 性能分析explain 使用联合索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #0.257s
#再进一步：
ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC,student_id);
#两个联合索引，删除一个
DROP INDEX idx_sid_cre_time ON student_info;
#possible_keys: idx_sid,idx_cre_time_sid key:idx_sid
#使用了idx_sid索引 没有使用idx_cre_time_sid
EXPLAIN SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #3.790s
#④ UPDATE、DELETE 的 WHERE 条件列
#查看，没有关于name的索引
SHOW INDEX FROM student_info;
UPDATE student_info SET student_id = 10002 
WHERE NAME = '462eed7ac6e791292a79';  #0.633s
#添加索引
ALTER TABLE student_info
ADD INDEX idx_name(NAME);
UPDATE student_info SET student_id = 10001 
WHERE NAME = '462eed7ac6e791292a79'; #0.001s
# ⑤ DISTINCT 字段需要创建索引
# 略
# ⑥ 多表 JOIN 连接操作时，创建索引注意事项
#首先，`连接表的数量尽量不要超过 3 张`，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
#其次，`对 WHERE 条件创建索引`，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
#最后，`对用于连接的字段创建索引`，并且该字段在多张表中的`类型必须一致`。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
#有索引idx_name
SHOW INDEX FROM student_info;
SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.001s
DROP INDEX idx_name ON student_info;
SELECT s.course_id, NAME, s.student_id, c.course_name 
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.227s
# ⑦使用列的类型小的创建索引
# 略
# ⑧使用字符串前缀创建索引
# 略
# ⑨区分度高(散列性高)的列适合作为索引
# 略
# ⑩使用最频繁的列放到联合索引的左侧
SELECT *
FROM student_info
WHERE student_id=10013 AND course_id=100;
# 频繁放左边
# 补充：在多个字段都要创建索引的情况下，联合索引优于单值索引
# 略

3.3 限制索引的数目

在实际工作中也需要注意平衡，索引的数目不是越多越好。要限制每张表上的索引数目，建议单张表索引数量不超过6个,原因：

1.每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就人。

2.索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。

3.优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有很多个

索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能

3.4 哪些情况不适合创建索引

1. 在where中使用不到的字段，不要设置索引

WHERE条件(包括GROUP BY、ORDER BY)里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。举个例子:

SELECT course_id, student_id,create_time FROM student_info
WHERE student_id = 41251;

因为是按照student_id来进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在SELECT字段中。

2. 数据量小的表最好不要使用索引

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

举例：创建表1：

CREATE TABLE t_without_index(
a INT PRIMARY KEY AUTO_INCREMENT,
b INT
);

提供存储过程1：

#创建存储过程
DELIMITER //
CREATE PROCEDURE t_wout_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 900
DO
INSERT INTO t_without_index(b) SELECT RAND()*10000;
SET i = i + 1;
END WHILE;
COMMIT;
END //
DELIMITER ;
#调用
CALL t_wout_insert();

创建表2：

CREATE TABLE t_with_index(
a INT PRIMARY KEY AUTO_INCREMENT,
b INT,
INDEX idx_b(b)
);

创建存储过程2：

#创建存储过程
DELIMITER //
CREATE PROCEDURE t_with_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 900
DO
INSERT INTO t_with_index(b) SELECT RAND()*10000;
SET i = i + 1;
END WHILE;
COMMIT;
END //
DELIMITER ;
#调用
CALL t_with_insert();

查询对比：

select * from t_without_index where b = 9879;
/*
+------+------+
| a | b |
+------+------+
| 1242 | 9879 |
+------+------+
*/
select * from t_with_index where b = 9879;
/*
+-----+------+
| a | b |
+-----+------+
| 112 | 9879 |
+-----+------+
*/

你能看到运行结果相同，但是在数据量不大的情况下，索引就发挥不出作用了

结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的

#### 3. 有大量重复数据的列上不要建立索引
在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的“`性别`”字段上只有“男”与"女"两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会`严重降低数据更新速度`。
举例1：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。
举例2：假设有一个学生表，学生总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之 1。学生表student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1，0 代表女性，1 代表男性。
```sql
CREATE TABLE student_gender(
student_id INT(11) NOT NULL,
student_name VARCHAR(50) NOT NULL,
student_gender TINYINT(1) NOT NULL,
PRIMARY KEY(student_id)
)ENGINE = INNODB;

如果要筛选出这个学生表中的男性，可以使用：

SELECT * FROM student_gender WHERE student_gender = 1

运行结果（10 条数据，运行时间 0.696s ）：

你能看到在未创建索引的情况下，运行的效率并不高。如果针对student_gender字段创建索引呢?

SELECT * FROM student_gender WHERE student_gender = 1

同样是10条数据，运行结果相同，时间却缩短到了0.052s，大幅提升了查询的效率。

其实通过这两个实验也能看出来，索引的价值是帮助快速定位。如果想要定位的数据有很多，那么索引就失去了它的使用价值，比如通常情况下的性别字段。

结论：当数据重复度大，比如高于 10% 的时候，也不需要对这个字段使用索引

4. 避免对经常更新的表创建过多的索引

第一层含义︰频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义:避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

5. 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

6. 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

7. 不要定义冗余或重复的索引

有时候有意或者无意的就对同一个列创建了多个索引，比如: index(a,b,c)相当于index(a)、index(a,b),indexla,b,c)。

① 冗余索引

举例：建表语句如下

CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
KEY idx_name (name(10))
);

我们知道，通过idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。
② 重复索引

另一种情况，我们可能会对某个列重复建立索引，比方说这样：

CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);

可以看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

3.5 小结

索引是一把双刃剑，可提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。
选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能拘泥于上面的准则，大家要在以后的学习和工作中进行不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。

最后

2022/8/3 15:16

p128~p133

Markdown 38156 字数 2175 行数

HTML 35467 字数 1275 段落

第08章索引的创建与设计原则【2.索引及调优篇】【MySQL高级】4

9. 区分度高(散列性高)的列适合作为索引

10.使用最频繁的列放到联合索引的左侧

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

测试代码：04-索引的设计原则.sql

3.3 限制索引的数目

3.4 哪些情况不适合创建索引

1. 在where中使用不到的字段，不要设置索引

2. 数据量小的表最好不要使用索引

4. 避免对经常更新的表创建过多的索引

5. 不建议用无序的值作为索引

6. 删除不再使用或者很少使用的索引

7. 不要定义冗余或重复的索引

3.5 小结

最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

第08章 索引的创建与设计原则【2.索引及调优篇】【MySQL高级】4

9. 区分度高(散列性高)的列适合作为索引

10.使用最频繁的列放到联合索引的左侧

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

测试代码：04-索引的设计原则.sql

3.3 限制索引的数目

3.4 哪些情况不适合创建索引

1. 在where中使用不到的字段，不要设置索引

2. 数据量小的表最好不要使用索引

4. 避免对经常更新的表创建过多的索引

5. 不建议用无序的值作为索引

6. 删除不再使用或者很少使用的索引

7. 不要定义冗余或重复的索引

3.5 小结

最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

第08章索引的创建与设计原则【2.索引及调优篇】【MySQL高级】4