ToB企服应用市场:ToB评测及商务社交产业平台

标题: 8-2 MySQL 索引的设计原则(超具体阐明讲解) [打印本页]

---

作者: 宝塔山    时间: 6 天前
标题: 8-2 MySQL 索引的设计原则(超具体阐明讲解)
8-2 MySQL 索引的设计原则(超具体阐明讲解)

@
目录

• 8-2 MySQL 索引的设计原则(超具体阐明讲解)
  
• 1. 测试数据准备
  
• 2. 哪些环境适合创建索引
  
  
  
  • 2.1 字段的数值有唯一性的限定
    
  • 2.2 频仍作为 WHERE 查询条件的字段
    
  • 2.3 经常 GROUP BY 和 ORDER BY 的列
    
  • 2.4 UPDATE、DELETE 的 中的WHERE 条件列添加索引
    
  • 2.5 对于经常 DISTINCT 字段需要创建索引
    
  • 2.6 多表 JOIN 连接操纵时，创建索引留意事项
    
  • 2.7 利用列的类型小的创建索引
    
  • 2.8 利用字符串前缀创建索引
    
  • 2.9 区分度高(散列性高)的列适相助为索引
    
  • 2.10 利用最频仍的列放到联合索引的左侧——索引最左侧匹配
    
  
  
• 3. 哪些环境不适合创建索引
  
  
  
  • 3.1 在 where 筛选条件当中利用不到的字段，不要设置索引
    
  • 3.2 数据量小的表最好不要利用索引
    
  • 3.3 有大量重复数据的列上不要建立索引
    
  • 3.4 避免对经常更新的表创建过多的索引
    
  • 3.5 不发起用无序的值作为索引
    
  • 3.6 删除不再利用大概很少利用的索引
    
  • 3.7 不要界说冗余或重复的索引
    
  
  
• 4. 最后：
  

1. 测试数据准备

第1步：创建数据库、创建表

1. CREATE DATABASE dbtest3;
3. USE dbtest3;
6. #1.创建学生表和课程表
7. CREATE TABLE `student_info` (
8. `id` INT(11) NOT NULL AUTO_INCREMENT,
9. `student_id` INT NOT NULL ,
10. `name` VARCHAR(20) DEFAULT NULL,
11. `course_id` INT NOT NULL ,
12. `class_id` INT(11) DEFAULT NULL,
13. `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`)
14. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
17. CREATE TABLE `course` (
18. `id` INT(11) NOT NULL AUTO_INCREMENT,
19. `course_id` INT NOT NULL ,
20. `course_name` VARCHAR(40) DEFAULT NULL,
21. PRIMARY KEY (`id`)
22. ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

复制代码

第2步：创建模拟数据必需的存储函数

阐明： 创建函数，假如报错：

1. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

复制代码

由于开启过慢查询日志bin-log, 我们就必须为我们的 function 指定一个参数。
主从复制，主机会将写操纵记录在bin-log日志中。从机读取bin-log日志，实行语句来同步数据。如果使 用函数来操纵数据，会导致从机和主键操纵时间差别等。所以，默认环境下，mysql不开启创建函数设 置。

• 查看mysql是否允许创建函数：
  

1. SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

复制代码

• 命令开启：允许创建函数设置：
  

1. set global log_bin_trust_function_creators=1;    # 不加global只是当前窗口有效。

复制代码

• mysqld重启，上述参数又会消失。永世方法：
  
  
  
  • windows下：my.ini[mysqld]加上：
    
  1. log_bin_trust_function_creators=1 # 1 表示真-开启，0 表示假-关闭

  复制代码
  
  
  • linux下：/etc/my.cnf 下my.cnf[mysqld]加上：
    
  1. log_bin_trust_function_creators=1 # 1 表示真-开启，0 表示假-关闭

  复制代码

1. #函数1：创建随机产生字符串函数
2. DELIMITER //
3. CREATE FUNCTION rand_string(n INT)
4.      RETURNS VARCHAR(255) #该函数会返回一个字符串
5. BEGIN
6.      DECLARE chars_str VARCHAR(100) DEFAULT
7.     'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
8.      DECLARE return_str VARCHAR(255) DEFAULT '';
9.      DECLARE i INT DEFAULT 0;
10.      WHILE i < n DO
11.          SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
12.          SET i = i + 1;
13.      END WHILE;
14.      RETURN return_str;
15. END //
16. DELIMITER ;

复制代码

1. #函数2：创建随机数函数
2. DELIMITER //
3. CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
4. BEGIN
5. DECLARE i INT DEFAULT 0;
6. SET i = FLOOR(from_num +RAND() * (to_num - from_num+1));
7. RETURN i;
8. END //
9. DELIMITER;

复制代码

第3步：创建插入模拟数据的存储过程

1. # 存储过程1：创建插入课程表存储过程
2. DELIMITER //
3. CREATE PROCEDURE insert_course( max_num INT )
4. BEGIN
5. DECLARE i INT DEFAULT 0;
6.   SET autocommit = 0; #设置手动提交事务
7.   REPEAT #循环
8.   SET i = i + 1; #赋值
9.   INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6));
10.   UNTIL i = max_num
11.   END REPEAT;
12.   COMMIT; #提交事务
13. END //
14. DELIMITER;

复制代码

1. # 存储过程2：创建插入学生信息表存储过程
2. DELIMITER //
3. CREATE PROCEDURE insert_stu( max_num INT )
4. BEGIN
5. DECLARE i INT DEFAULT 0;
6. SET autocommit = 0; #设置手动提交事务
7.   REPEAT # 循环
8.   SET i = i + 1; # 赋值
9.   INSERT INTO student_info (course_id,class_id,student_id,`NAME`) VALUES
10.   (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
11.   UNTIL i = max_num
12.   END REPEAT;
13.   COMMIT;  # 提交事务
14. END //
15. DELIMITER ;

复制代码

第4步：调用存储过程

1. CALL insert_course(100);
3. CALL insert_stu(1000000);

复制代码

第5步：查看是否含有这么多数据，数据是否插入成功

1. SELECT COUNT(*) FROM course;
3. SELECT COUNT(*) FROM student_info;

复制代码

2. 哪些环境适合创建索引

2.1 字段的数值有唯一性的限定

索引自己可以起到束缚的作用，比如：唯一索引，主键索引都是可以起到唯一性束缚的，因此在我们的数据表中，如果某个字段是唯一性的  ，就可以直接创建唯一性索引 ，大概主键索引。这样可以更快速地通过该索引来确定某条记录。
比方：门生表中学号 是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个门生的信息，如果利用姓名 的话，可能存在同名现象，从而减低查询速率。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
阐明：不要以为唯一索引影响了 insert 速率，这个速率消耗可以忽略，但进步查找速率是明显s的。

2.2 频仍作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被利用到，那么就需要给这个字段创建索引了。尤其是在 数据量大的环境下，创建平凡索引就可以大幅提拔数据查询的效率。
比如 student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。
没有给 student_id 字段添加索引，实行的速率是：

1. SELECT course_id,class_id,`name`,create_time,student_id
2. FROM student_info
3. WHERE student_id = 123110;

复制代码

1. # 给 student_id 字段添加上索引之后
2. ALTER TABLE student_info
3. ADD INDEX ids_sid(student_id);

复制代码

2.3 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们利用 GROUP BY 对数据进行分组查询，大概 利用 ORDER BY 对数据进行排序的时候，就需要 对分组大概排序的字段进行索引 。如果待排序的列有多 个，那么可以在这些列上建立 组合索引 。

1. # 经常 GROUP BY 和 ORDER BY 的列
3. # student_id 字段上有索引的
4. SELECT student_id, COUNT(*) AS num
5. FROM student_info
6. GROUP BY student_id LIMIT 100;

复制代码

1. # 删除 idx_sid 索引
2. DROP INDEX ids_sid ON student_info;
4. # student_id 字段上没有索引的
5. SELECT student_id, COUNT(*) AS num
6. FROM student_info
7. GROUP BY student_id LIMIT 100;

复制代码

如果同时有 GROUP By 和 ORDER BY 的环境：比如我们按照 student_id 进行分组，同时按照创建时间降序的方式进行排序，这时我们就需要同时进行 GROUP BY 和 ORDER BY，那么是不是需要单独创建 student_id 的索引和 create_time 的索引呢？
当我们对 student_id 和 create_time 分别创建索引，实行下面的SQL查询

1. # 添加单例索引
2. ALTER TABLE student_info
3. ADD INDEX idx_sid(student_id);
5. ALTER TABLE student_info
6. ADD INDEX idx_cre_time(create_time);
10. SELECT student_id, COUNT(*) AS num
11. FROM student_info
12. GROUP BY student_id
13. ORDER BY create_time DESC
14. LIMIT 100; # 5.212s

复制代码

当我们添加对 student_id 和 create_time 组合创建联合索引，实行下面的SQL查询

1. # 添加为 student_id 和 create_time 组合创建联合索引
2. ALTER TABLE student_info
3. ADD INDEX idx_sid_cre_time(student_id,create_time DESC);
5. SHOW INDEX FROM student_info;

复制代码

再次实行该SQL语句：

2.4 UPDATE、DELETE 的 中的WHERE 条件列添加索引

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操纵，如果对 WHERE 字段创建了索引，就 能大幅提拔效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或 删除。 如果进行更新的时候，更新的字段是非索引字段，提拔的效率会更明显，这是因为非索引字段更 新不需要对索引进行维护。

1. # update, delete 的 where 条件列添加索引
2. SHOW INDEX FROM student_info;
4. UPDATE student_info SET student_id = 10002
5. WHERE NAME = 'jfiodasjfoj';

复制代码

为 name 添加上索引

1. # 添加索引
2. ALTER TABLE student_info
3. ADD INDEX idx_name(`name`);
6. UPDATE student_info SET student_id = 10002
7. WHERE NAME = 'jfiodasjfoj';

复制代码

2.5 对于经常 DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，利用 DISTINCT，那么对这个字段创建索引，也会提拔查询效率。 比如，我们想要查询课程表中差别的 student_id 都有哪些，如果我们没有对 student_id 创建索引，实行 SQL 语句：

1. SELECT DISTINCT(student_id) FROM `student_info`;

复制代码

运行结果（600637 条记录，运行时间 0.683s ）：
如果我们对 student_id 创建索引，再实行 SQL 语句：

1. SELECT DISTINCT(student_id) FROM `student_info`;

复制代码

运行结果（600637 条记录，运行时间  0.010s ）：
你能看到 SQL 查询效率有了提拔，同时表现出来的 student_id 还是按照 递增的顺序 进行展示的。这是因 为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
2.6 多表 JOIN 连接操纵时，创建索引留意事项

• 首先， 连接表的数量尽量不要超过  3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。
  
• 其次， 对  WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的环境下， 没有 WHERE 条件过滤是非常可怕的。
  
• 最后， 对用于连接的字段创建索引 ，而且该字段在多张表中的 类型必须同等 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
  

举个例子，如果我们只对 student_id 和 name 创建索引，实行 SQL 语句：

1. SELECT s.course_id, NAME, s.student_id, c.course_name
2. FROM student_info s JOIN course c
3. ON s.course_id = c.course_id
4. WHERE NAME = '462eed7ac6e791292a79';

复制代码

1. DROP INDEX idx_name ON student_info;SELECT s.course_id, NAME, s.student_id, c.course_name
2. FROM student_info s JOIN course c
3. ON s.course_id = c.course_id
4. WHERE NAME = '462eed7ac6e791292a79';

复制代码

2.7 利用列的类型小的创建索引

利用列小的类型，创建的索引占用的磁盘空间就比较小一些，因为MySQL8 是将索引和数据都是存放再一起的。
我们这里所说的类型巨细 指的就是该类型表示的数据范围的巨细。
我们在界说表布局的时候要显式的指定列的类型，以整数类型为例，有TINYINT，MEDIUMINT，INT，BIGINT 等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的环境下，尽量让索引列利用较小的类型，比如我们能利用 INT 就不要利用 BIGINT ，能利用 MEDIUMINT 就不要利用 INT 。
2.8 利用字符串前缀创建索引

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

• 创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
  

1. create table shop(address varchar(120) not null);
2. alter table shop add index(address(12));

复制代码

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字 段的散列度(选择性)会降低。 怎么计算差别的长度的选择性呢？
先看一下字段在全部数据中的选择度：

1. select count(distinct address) / count(*) from shop;

复制代码

通过差别长度去计算，与全表的选择性对比：
公式：

1. count(distinct left(列名, 索引长度))/count(*);

复制代码

比方：

1. select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
2. count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
3. count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
4. count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
5. from shop;

复制代码

引申另一个问题：索引列前缀对排序的影响
拓展：Alibaba《Java开辟手册》

• 【 逼迫 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本 区分度决定索引长度。
  
• 阐明：索引的长度与区分度是一对抵牾体，一样平常对字符串类型数据，长度为 20 的索引，区分度会 高达 90% 以上 ，可以利用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
  

2.9 区分度高(散列性高)的列适相助为索引

列的基数 指的是某一列中不重复数据的个数，比方说某个列包含值2,5,8,2,5,8,2,5,8 ，虽然有9 条记录，但该列的基数却是3。也就是说，在记录行数肯定的环境下，列的基数越大，该列中的值越分散；列的基数越小，该列中值越集中 。这个列的基数指标非常紧张，直接影响我们是否能够有用的利用索引。最好为列的基数大的列建立索引，为基数太小的列建立索引效果可能不好。
可以利用公式：select count(distinct a) / count(*) from t1 计算区分度，越接近 1 越好，一样平常超过 33% 就算是比较高效的索引了。
拓展：联合索引把区分度高(散列性高)的列放在前面。
2.10 利用最频仍的列放到联合索引的左侧——索引最左侧匹配

索引最左侧匹配的原则，索引会优先判定最左侧的字段是否，建立的索引，建立了索引就会走索引，如果左侧的字段没有走索引，就算后面的字段有索引，也不会走索引的。
3. 哪些环境不适合创建索引

3.1 在 where 筛选条件当中利用不到的字段，不要设置索引

你都不对该字段，进行筛选过滤，那么索引你没有意义，因为你索引也是会增加磁盘空间巨细的。
3.2 数据量小的表最好不要利用索引

举例：创建表1：

1. CREATE TABLE t_without_index(
2. a INT PRIMARY KEY AUTO_INCREMENT, b INT
3. );

复制代码

提供存储过程1：

1. #创建存储过程
2. DELIMITER //
3. CREATE PROCEDURE t_wout_insert()
4. BEGIN
5.     DECLARE i INT DEFAULT 1;
6.     WHILE i <= 900
7.     DO
8.          INSERT INTO t_without_index(b) SELECT RAND()*10000;
9.          SET i = i + 1;
10.     END WHILE;
11.     COMMIT;
12. END //
13. DELIMITER ;
14. #调用
15. CALL t_wout_insert();

复制代码

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

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

3.3 有大量重复数据的列上不要建立索引

举例1：要在 100 万行数据中查找此中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先 访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不利用索引可能还要大。
举例2：假设有一个门生表，门生总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之 1。 门生表 student_gender 布局如下。此中数据表中的 student_gender 字段取值为 0 或 1，0 代表女性，1 代 表男性。

1. CREATE TABLE t_with_index(
2. a INT PRIMARY KEY AUTO_INCREMENT, b INT,
3. INDEX idx_b(b) );

复制代码

如果我们要筛选出这个门生表中的男性，可以利用：

1. #创建存储过程
2. DELIMITER //
3. CREATE PROCEDURE t_with_insert()
4. BEGIN
5.    DECLARE i INT DEFAULT 1;
6.    WHILE i <= 900
7.    DO
8.         INSERT INTO t_with_index(b) SELECT RAND()*10000;
9.         SET i = i + 1;
10.    END WHILE;
11.    COMMIT;
12. END //
13. DELIMITER ;
14. #调用
15. CALL t_with_insert();

复制代码

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

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

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

因为你不停更新表的同时，索引也是在同步更新的，索引更新是会斲丧大量的时间。
3.5 不发起用无序的值作为索引

比方身份证、UUID(在索引比较时需要转为ASCII，而且插入时可能造成页分裂)、MD5、HASH、无序长字 符串等。
3.6 删除不再利用大概很少利用的索引

3.7 不要界说冗余或重复的索引

冗余索引
举例：建表语句如下

1. mysql> select * from t_without_index where b = 9879;
2. +------+------+
3. |a   |b   |
4. +------+------+
5. | 1242 | 9879 |
6. +------+------+
7. 1 row in set (0.00 sec)
8. mysql> select * from t_with_index where b = 9879;
9. +-----+------+
10. |a |b   |
11. +-----+------+
12. | 112 | 9879 |
13. +-----+------+
14. 1 row in set (0.00 sec)

复制代码

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一 个专门针对 name 列的索引就算是一个 冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有 什么好处.。
重复索引：
另一种环境，我们可能会对某个列 重复建立索引 ，比方说这样：

1. CREATE TABLE student_gender(
2.     student_id INT(11) NOT NULL,
3.     student_name VARCHAR(50) NOT NULL,
4.     student_gender TINYINT(1) NOT NULL,
5.     PRIMARY KEY(student_id)
6. )ENGINE = INNODB;

复制代码

我们看到，col1 既是主键、又给它界说为一个唯一索引，还给它界说了一个平凡索引，可是主键自己就 会天生聚簇索引，所以界说的唯一索引和平凡索引是重复的，这种环境要避免。
4. 最后：

“在这个最后的篇章中，我要表达我对每一位读者的感激之情。你们的关注和回复是我创作的动力源泉，我从你们身上吸取了无尽的灵感与勇气。我会将你们的鼓励留在心底，继承在其他的范畴奋斗。感谢你们，我们总会在某个时候再次相遇。”

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4