索引的创建与设计原则

索引的创建与设计原则

1. 索引的声明与使用

1.1 索引的分类

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一性索引、主键索引、全文索引。

1. 普通索引 (Normal Index)：
   普通索引是最基本的索引类型，它可以加快对数据的查询速度，但允许数据表中出现重复的索引键值。普通索引在查询条件中使用时，数据库会利用索引快速定位满足条件的数据行，从而加快查询操作。但是，由于允许重复索引键值，普通索引在唯一性方面没有限制。

2. 唯一索引 (Unique Index)：
   唯一索引与普通索引类似，也可以加快查询速度，但它要求索引列的值在整个表中是唯一的，不允许出现重复的索引键值。唯一索引可以用来强制保持数据的唯一性，常用于约束某列的值不能重复。

3. 主键索引 (Primary Key Index)：
   主键索引是一种特殊的唯一索引，用于标识表中的唯一记录。每张表只能有一个主键索引，并且主键索引通常会与表的主键列关联。主键索引的作用是为了快速定位表中的唯一记录，通常用于数据的唯一标识和数据表之间的关联。

4. 全文索引 (Full-Text Index)：
   全文索引是一种特殊类型的索引，主要用于在文本类型的数据中进行全文本搜索。它允许在文本字段中进行关键字搜索，并且支持复杂的文本匹配和模糊查询。全文索引常用于处理大段文本的搜索，如文章、评论等。

按照作用字段个数进行划分，分成单列索引和联合索引。

1. 单列索引 (Single-Column Index)： 单列索引是指在单个字段上创建的索引，它只包含一个列的值。单列索引是最简单的索引类型，用于加快对单个字段的查询速度。通过在单列上创建索引，数据库系统可以快速定位满足查询条件的数据行，从而提高查询性能。对于经常用于搜索和筛选的字段，特别是数据的唯一标识或经常用于连接查询的字段，单列索引特别有用。

2. 联合索引 (Composite Index 或 Multi-Column Index)： 联合索引是指在多个字段上创建的索引，它包含多个列的值。通过在多个字段上创建联合索引，可以优化复合查询的性能。联合索引可以让数据库系统在执行查询时更快地定位满足多个条件的数据行，避免多次扫描整个数据表。联合索引的创建顺序也很重要，查询时应该按照创建索引时的字段顺序来使用，以确保索引的有效使用。联合索引在适当的场景下可以大幅提高复合查询的效率。

按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。

1. 聚簇索引 (Clustered Index)： 聚簇索引是一种特殊的索引类型，它对表中的数据行进行了物理上的重新排序，使得数据行的物理存储顺序与索引的排序顺序一致。每个表只能有一个聚簇索引，一般情况下，主键索引就是聚簇索引。由于数据行的物理排序与索引排序相同，所以通过聚簇索引进行范围查询或排序查询时，数据库可以直接顺序读取磁盘上的连续数据块，从而加快查询速度。但是，由于聚簇索引会导致数据行的物理重新排序，当插入新数据时，可能需要对数据进行移动和调整，因此对于频繁进行插入和删除操作的表，聚簇索引的维护成本可能较高。
2. 非聚簇索引 (Non-clustered Index)： 非聚簇索引是普通的索引类型，它并不影响数据行的物理存储顺序。每个表可以有多个非聚簇索引，它们可以用于加快对单个或多个列的查询速度。非聚簇索引会创建一个独立的索引结构，其中包含索引列的值和指向相应数据行的指针。通过非聚簇索引进行查询时，数据库首先根据索引定位到数据行的位置，然后再通过指针找到相应的数据行。非聚簇索引适合用于频繁进行搜索和筛选的列，以及经常用于连接查询的列。

1.2 创建索引

隐式创建索引：在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关索引。

1. 普通索引 (Normal Index)：
   假设有一个名为products的表，我们希望在product_name字段上创建普通索引。

CREATE INDEX idx_product_name ON products (product_name);

2. 唯一性索引 (Unique Index)：
   假设有一个名为users的表，我们希望在email字段上创建唯一性索引，以确保email的值在表中是唯一的。

CREATE UNIQUE INDEX idx_email ON users (email);

3. 主键索引 (Primary Key Index)：
   假设有一个名为orders的表，我们希望在order_id字段上创建主键索引，以标识每个订单的唯一性。

ALTER TABLE orders ADD PRIMARY KEY (order_id);

或者在创建表的时候直接指定主键：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    -- other columns...
);

4. 全文索引 (Full-Text Index)：
   假设有一个名为articles的表，其中有一个content字段包含文章内容，我们希望在content字段上创建全文索引，以支持全文搜索。

CREATE FULLTEXT INDEX idx_content ON articles (content);

使用全文索引后，在进行全文搜索查询时，通常不需要使用LIKE操作符。全文索引提供了更高效、更灵活的全文本搜索功能，比简单的LIKE操作更快速和高效。

使用全文索引时，通常需要使用MySQL提供的全文搜索函数来进行查询，主要有两个常用的函数：MATCH() 和 AGAINST()。

语法如下：

sqlCopy code
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST ('search_keyword');

在上述查询中，MATCH(column_name)用于指定要进行全文搜索的列，AGAINST ('search_keyword')表示要搜索的关键字。

全文索引的搜索功能比简单的LIKE操作更强大，可以支持：

1. 自然语言搜索（Natural Language Search）：可以进行全文本的自然语言搜索，理解搜索词之间的关系，如词的顺序、近义词等。
2. 布尔搜索（Boolean Search）：可以使用布尔运算符（AND、OR、NOT）进行更复杂的搜索组合。
3. 词形还原（Stemming）：可以根据搜索词的词根形式查找相关单词。
4. 停用词（Stopwords）：自动忽略常见的无意义词，如“the”、“and”等，从而提高搜索效率。

由于全文索引使用了更高效的搜索算法和数据结构，所以对于大量文本数据的搜索，使用全文索引比使用LIKE操作更快捷和高效。然而，要使用全文索引功能，需要确保数据库表使用了支持全文索引的存储引擎，如InnoDB或MyISAM，并且对相应的列创建了全文索引。

1.3 删除索引

在MySQL中，有两种主要的方式可以删除索引：通过DROP INDEX语句或通过ALTER TABLE语句。

方式一：使用 DROP INDEX 语句删除索引

DROP INDEX index_name ON table_name;

在上述语句中，index_name 是要删除的索引的名称，table_name 是索引所在的表名。通过执行这条语句，可以从指定的表中删除指定的索引。

方式二：使用 ALTER TABLE 语句删除索引

ALTER TABLE table_name DROP INDEX index_name;

在上述语句中，table_name 是索引所在的表名，index_name 是要删除的索引的名称。通过执行这条语句，也可以从指定的表中删除指定的索引。

无论使用哪种方式，删除索引都要谨慎操作，确保删除的是不再需要的索引，并且不会对查询性能造成负面影响。建议在删除索引之前进行性能测试和分析，确保删除的索引是冗余的、不必要的或不再使用的。在生产环境中进行索引删除操作时，最好在非高峰期进行，并备份好数据，以防操作失误导致数据丢失。

2. MySQL 8 索引新特性

MySQL 8.0引入了一些新的索引相关的特性和改进，以下是其中一些主要的新特性：

1. Invisible Indexes（隐藏索引）：
   MySQL 8.0引入了“隐藏索引”的概念，允许用户创建隐藏索引，这样这些索引在查询中将不被使用，但是仍然保留在表中。隐藏索引主要用于在不影响现有应用程序的情况下，进行索引的测试和评估，以确定是否需要保留该索引。

2. Descending Indexes（降序索引）：
   MySQL 8.0开始支持降序索引，允许在创建索引时指定某个列的降序排序。降序索引可以在某些特定查询场景中提供性能优势，例如倒序排序或使用降序排列的范围查询。

3. Prefix Indexes（前缀索引）：
   MySQL 8.0增加了对前缀索引的优化支持，可以在创建索引时指定某个列的前缀长度，从而减少索引的存储空间，并提高索引查询性能。

4. Grouping and Sorting with Indexes（索引分组和排序）：
   MySQL 8.0引入了新的索引算法，允许在索引上进行GROUP BY和ORDER BY操作，从而在某些场景下避免使用临时表和文件排序，提高了查询性能。

5. Persistent Optimizer Statistics（持久化优化器统计信息）：
   MySQL 8.0改进了优化器统计信息的处理，允许将优化器统计信息持久化存储在表中，这样即使服务器重启，也能保留之前的统计信息，减少了服务器启动后重新收集统计信息的开销。

6. Histograms（直方图）：
   MySQL 8.0引入了直方图，用于对列的数据分布进行更细粒度的统计。直方图可以帮助优化器更好地选择合适的索引和执行计划，提高查询性能。

这些新的索引特性和改进使得MySQL 8.0在索引的设计和使用方面更加灵活和高效，提供了更多优化查询性能的选项。使用这些新特性，可以更好地满足不同场景下的索引需求，提高数据库的性能和响应能力。

3. 索引的设计原则

3.1 数据准备

CREATE DATABASE atguigudb1;

USE atguigudb1;

#1.创建学生表和课程表
CREATE TABLE `student_info` (
 `id` INT(11) 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;


#函数1：创建随机产生字符串函数

SELECT @@log_bin_trust_function_creators;

SET GLOBAL log_bin_trust_function_creators = 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 ;


# 存储过程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 ;

#调用存储过程：
CALL insert_course(100);

SELECT COUNT(*) FROM course;

CALL insert_stu(1000000);

SELECT COUNT(*) FROM student_info;

3.2 哪些情况适合创建索引

1. 字段数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，就可能存在重名现象，从而降低查询速度。

Alibaba开发手册：业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2. 频繁作为 WHERE 查询条件的字段

某个字段SELECT语句的WHERE条件中经常被用到，那么就要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升查询效率。

例如对指定学号查询：

SHOW INDEX FROM student_info;

# 有索引：(execution: 2 ms, fetching: 58 ms)
# 没有索引：execution: 238 ms, fetching: 16 ms
SELECT course_id, class_id, NAME, create_time, student_id
FROM student_info
WHERE student_id = 123110;

#给student_id字段添加索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
# 删除索引
DROP INDEX idx_sid ON student_info;

3. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立 组合索引。
比如，按照 student id 对学生选修的课程进行分组，显示不同的 student id 和课程数量，显示 100 个即可如果我们不对 student_id 创建索引，执行下面的 SQL 语句:
SELECT student_id, count(*) as num FROM student_info group by student_id limit 100;

#有索引：(execution: 3 ms, fetching: 44 ms)
#没有索引：(execution: 731 ms, fetching: 18 ms)
SELECT student_id, count(*) as num
FROM student_info
GROUP BY student_id
limit 100;

联合索引：

# 单列索引
ALTER TABLE student_info
    ADD INDEX idx_sid (student_id);

DROP INDEX idx_sid ON student_info;

ALTER TABLE student_info
    ADD INDEX idx_cre_time (create_time);

DROP INDEX idx_cre_time ON student_info;
# 联合索引
ALTER TABLE student_info
    ADD INDEX idx_sid_cre_time (student_id, create_time DESC);

DROP INDEX idx_sid_cre_time ON student_info;
# 联合索引2
ALTER TABLE student_info
    ADD INDEX idx_sid_cre_time2 (create_time DESC, student_id);

DROP INDEX idx_sid_cre_time2 ON student_info;
# 单列索引：(execution: 1 s 235 ms, fetching: 7 ms)
# 联合索引：(execution: 688 ms, fetching: 12 ms)
# 联合索引2：(execution: 1 s 546 ms, fetching: 18 ms)
SELECT student_id, count(*) as num
FROM student_info
GROUP BY student_id, create_time
ORDER BY create_time DESC
limit 100;

4. UPDATE、DELETE的WHERE条件列

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

#添加索引
ALTER TABLE student_info
ADD INDEX idx_name(NAME);

DROP INDEX idx_name ON student_info;

# 没有索引：686 ms
# 有索引：3 ms
UPDATE student_info SET student_id = 10001
WHERE NAME = '462eed7ac6e791292a79';

5. DISTINCT字段需要创建索引

对某个字段需要去重使用DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6. 多表JOIN连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE 条件过滤是非常可怕的

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 course_id 在 student info 表和course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar类型，否则字符串通过隐私转换为int类型，索引就会失效了。

SHOW INDEX FROM student_info;

ALTER TABLE student_info
ADD INDEX idx_name(NAME);

DROP INDEX idx_name ON student_info;

# 没有索引：(execution: 218 ms, fetching: 8 ms)
# 有索引：(execution: 3 ms, fetching: 60 ms)
SELECT c.course_id, name, s.student_id, course_name
FROM student_info s
         JOIN course c on s.course_id = c.course_id
WHERE name = 'aCOjEu';

7. 使用列的类型占用小的创建索引

我们这里所说的 类型大小 指的就是该类型表示的数据范围的大小。

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TINYINT、MEDIUMINT、INT、BIGINT 等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT 就不要使用 BIGINT，能使用 MEDIUMINT就不要使用 INT 。这是因为:

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的 主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

8. 使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:

B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
如果B+树索引中索引列存储的字符串很长，那在做字符串 比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫 前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

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

create table shop(address varchar(120) not null);

alter table shop add index(address(12));

截取多少字符串？取的少重复内容太多，取的多索引空间又会大。

字段在全部数据中的选择度：

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

通过不同长度去计算，与全表的选择性对比：

公式：count(distinct left(列名, 索引长度))/count(*) 越接近1越好

例如：截取前不同长度字符的选择度

select count(distinct left(address, 10) / count(*) as sub10,
count(distinct left(address, 15) / count(*) as sub10,
count(distinct left(address, 20) / count(*) as sub10,
count(distinct left(address, 25) / count(*) as sub10
from shop;
#0.0805, 0.0956, 0.0991, 0.1005

引申另一个问题：索引前缀对排序的影响

如果使用了索引前缀，比如只把前12个字符放到了二级索引中，就无法对前12个字符相同，后面字符不同的记录进行排序，也就是使用索引前缀的方式无法支持使用索引排序，只能使用文件排序。

拓展：Alibaba开发手册

【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度区分是一对矛盾体，一般对字符串数据类型，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

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

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

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

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

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

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

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

3.3 限制索引数目

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

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

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

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

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

1.在WHERE中使用不到的字段不要建立索引

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

如果表记录太少，比如少于1000个，不需要创建索引，是否创建索引对查询效率影响不大。

3. 有大量重复数据的列不要建立索引

比如“性别”字段只有“男/女”两个不同值，因此无需建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

比如100万条数据查找男生数据，50万行左右，一旦创建了索引，需要先访问50万次索引，然后再访问50万次数据表，这样加起来的开销比不使用索引还要大。

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

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

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

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

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

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

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

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

冗余索引

有时有意或者无意的对同一个列创建了多个索引，比如index(a,b,c)相当于index(a)、index(a,b)。因为单列索引可以通过联合索引体现。

重复索引 对同一个列创建了多个索引