友情提示

转载须知: 以下所有文章整理于B站宋红康老师的《MySQL数据库入门到大牛》。MySQL (opens new window)

1. 索引的声明与使用

1.1 索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

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

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

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

1. 普通索引

在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在 任何数据类型 中,其值是否唯一和非空,要由字段本身的完整性约束条件决定。建立索引以后,可以通过索引进行查询。例如,在表student 的字段 name 上建立一个普通索引，查询记录时就可以根据该索引进行查询。

2. 唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引,在创建唯一性索引时,限制该索引的值必须是唯一的,但允许有空值。在一张数据表里 可以有多个唯一索引。

例如，在表 student 的字段 email 中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引，可以更快速地确定某条记录。

3. 主键索引

主键索引就是一种 特殊的唯一性索引,在唯一索引的基础上增加了不为空的约束,也就是 NOT NULL+UNIQUE,一张表里 最多只有一个主键索引。

Why? 这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

4. 单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引,也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个 单列索引。

5. 多列 (组合、联合) 索引

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如,在表中的字段id,name和gender上建立一个多列索引 idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循 最左前缀集合。

6. 全文检索

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。

使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上,查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。 例如，表 student 的字段 information 是 TEXT 类型，该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。

全文索引典型的有两种类型：自然语言的全文索引 和 布尔全文索引。

• 自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里,相关度是基于匹配的关键词的个数,以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。 相反，非常常见的单词将不会被搜索,如果一个词语的在超过50%的记录中都出现了,那么自然语言的搜索将不会搜索这类词语！

MySQL数据库从3.23.23版开始支持全文索引,但MySQL5.6.4以前只有Myisam支持, 5.6.4版本以后innodb才支持,但是官方版本不支持中文分词,需要第三方分词插件。在5.7.6版本, MySQL内置了ngram全文解析器,用来支持亚洲语种的分词。测试或使用全文索引时,要先看一下自己的MySQL版本、存储引擎和数据类型是否支持全文索引。

随着大数据时代的到来,关系型数据库应对全文索引的需求已力不从心,逐渐被solr、ElasticSearch等专门的搜索引擎所替代。

7. 补充：空间索引

使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、 POINT、 LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索,而且索引的字段不能为空值。对于初学者来说,这类索引很少会用到。

小结：不同的存储引擎支持的索引类型也不一样

InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory ：支持 B-tree、Hash 等 索引，不支持 Full-text 索引；

NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive ：不支 持 B-tree、Hash、Full-text 等索引；

1.2 创建索引

MySQL支持多种方法在单个或多个列上创建索引：在创建表的定义语句 CREATE TABLE 中指定索引列，使用 ALTER TABLE 语句在存在的表上创建索引，或者使用 CREATE INDEX 语句在已存在的表上添加索引。

1.2.1 创建表的时候创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

举例：

CREATE TABLE dept(
  dept_id INT PRIMARY KEY AUTO_INCREMENT,  -- 定义主键约束，并自动为dept_id列创建唯一索引
  dept_name VARCHAR(20)
);

CREATE TABLE emp(
  emp_id INT PRIMARY KEY AUTO_INCREMENT,  -- 定义主键约束，并自动为emp_id列创建唯一索引
  emp_name VARCHAR(20) UNIQUE,            -- 定义唯一性约束，为emp_name列自动创建唯一索引
  dept_id INT,
  CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)  -- 定义外键约束，为dept_id列创建索引以维护与dept表的参照完整性
);

但是，如果创建表时显式创建索引的话，基本语法格式如下：

• [UNIQUE | FULLTEXT | SPATIAL]: 这些是可选的索引类型，分别用于创建唯一索引、全文索引和空间索引。
  • UNIQUE 索引保证了索引列的每一行数据都是唯一的。
  • FULLTEXT 索引用于全文搜索。
  • SPATIAL 索引用于地理空间数据的搜索。
• [INDEX | KEY]: INDEX和KEY是同义词，都用于创建索引。您可以使用这两个关键字中的任何一个来指定创建的是一个索引。
• [index_name]: 索引的名称。这是可选的；如果不指定，MySQL默认将使用列名作为索引名。
• col_name 指的是您选择用来建立索引的字段名。您可以选择表中已定义的一个或多个字段来创建索引。如果是多个字段，这通常用于创建复合索引，这种索引基于这些字段的组合来优化查询性能。
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
• [ASC | DESC]: 这是可选的，用于指定索引中数据的存储顺序，升序或降序。

（1）创建普通索引

不指定索引类型默认创建的就是普通索引，在book表中的year_publication字段上建立普通索引，普通索引不要求字段值的唯一性，允许有重复值和NULL值SQL语句如下：

CREATE TABLE book(
  book_id INT,
  book_name VARCHAR(100),
  authors VARCHAR(100),
  info VARCHAR(100),
  comment VARCHAR(100),
  year_publication YEAR,    -- 创建表时为这个字段创建普通索引
  INDEX(year_publication)   -- 指定year_publication字段上的普通索引
);

（2）创建唯一索引

UNIQUE 索引保证了索引列的每一行数据都是唯一的，不可以添加重复数据，但可以添加多个null值

CREATE TABLE test1 (
  id INT NOT NULL,          -- 定义一个名为id的整型列，不允许NULL值
  name VARCHAR(30) NOT NULL, -- 定义一个名为name的字符串列，最大长度为30字符，不允许NULL值
  UNIQUE INDEX uk_idx_id(id) -- 为id列创建一个唯一索引，索引名为uk_idx_id
);

（3）主键索引

• 随表一起建索引：当你创建一个新表时，可以直接在表定义中指定某个列作为主键，这会自动为该列创建一个唯一索引。主键保证了表中每一行数据的唯一性，并且不允许有NULL值。语法：

create table student (
id int(10) primary key,     -- 定义主键约束，自动创建唯一索引
student_no varchar(200),
student_name varchar(200)
);

• 删除主键索引：如果需要删除一个已经存在的主键索引，可以使用ALTER TABLE命令配合DROP PRIMARY KEY操作。这将移除表的主键约束以及相应的唯一索引

alter table student drop primary key;

• 修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

alter table student add primary key (新的主键列);

（4）创建单列索引

单列索引是在数据库表的一个字段上创建的索引，用于加速基于该字段的查询操作。这种索引类型非常适合于那些经常需要作为查询条件的字段。

CREATE TABLE test2(
  id INT NOT NULL,
  name CHAR(50) NULL,
  INDEX single_idx_name(name(20))  -- 为name字段创建一个单列索引，索引名为single_idx_name
);

查看索引信息：创建索引后，可以通过SHOW INDEX FROM命令查看表的索引信息，这有助于验证索引是否按预期创建，并了解其属性。查看test2表索引信息的语法示例：

show index from test2 \g;

（5）创建组合索引

组合索引，也称为复合索引，是在数据库表的两个或多个字段上同时创建的索引。

创建组合索引：在创建表的同时，可以为表中的多个字段创建一个组合索引。这个操作通常用于优化那些涉及多个字段的查询性能，尤其是在这些字段经常一起出现在查询条件中时。组合索引的创建示例语法如下：

CREATE TABLE test3(
  id INT(11) NOT NULL,
  name CHAR(30) NOT NULL,
  age INT(11) NOT NULL,
  info VARCHAR(255),
  INDEX multi_idx(id, name, age)  -- 在id、name和age字段上创建一个组合索引，索引名为multi_idx
);

查看索引信息：创建索引后，可以通过SHOW INDEX FROM命令查看表的索引信息，以验证索引是否按照预期创建，并了解其属性。查看test3表索引信息的语法示例：

show index from test3 \g;

在test3表中，查询id和name字段，使用EXPLAIN语句查看索引的使用情况：

explain select * from test3 where id=1 and name='songhongkang' \g;

可以看到，查询id和name字段时，使用了名称为MultiIdx的索引，如果查询 (name, age) 组合或者单独查询name和age字段，会发现结果中possible_keys和key值为NULL, 并没有使用在t3表中创建的索引进行查询。

（6）创建全文索引

FULLTEXT全文索引可以用于全文检索，并且只为 CHAR 、VARCHAR 和 TEXT 列创建索引。索引总是对整个列进行，不支持局部 (前缀) 索引。

举例1：创建表test4，在表中的info字段上建立全文索引，SQL语句如下：

CREATE TABLE test4(
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR(255),
  FULLTEXT INDEX futxt_idx_info(info)  -- 在info字段上创建全文索引，索引名为futxt_idx_info
) ENGINE=MyISAM;  -- 使用MyISAM引擎，因为早期版本的MySQL中，全文索引仅支持MyISAM

在MySQL5.7及之后版本中可以不指定最后的ENGINE了，因为在此版本中InnoDB支持全文索引。

语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

show index from test4 \g;

由结果可以看到，info字段上已经成功建立了一个名为futxt_idx_info的FULLTEXT索引。

举例2：

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT INDEX(title, body)  -- 同时在title和body字段上创建全文索引
) ENGINE=InnoDB;  -- 从MySQL 5.6开始，InnoDB引擎也支持全文索引

创建了一个给title和body字段添加全文索引的表。

举例3：

CREATE TABLE `papers` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) DEFAULT NULL,
  `content` TEXT,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`content`)  -- 在title和content字段上创建全文索引，索引名为title
) ENGINE=MyISAM DEFAULT CHARSET=utf8;  -- 使用MyISAM引擎，并指定字符集为utf8

全文索引与LIKE查询的区别

LIKE查询：使用LIKE进行文本搜索时，MySQL需要扫描整个表来找到匹配的字符串，这在大型数据集上效率很低，尤其是当匹配模式以通配符开始时（例如'%查询字符串%'）。

SELECT * FROM papers WHERE content LIKE '%查询字符串%';  -- 使用LIKE进行文本搜索

全文索引查询：使用MATCH() AGAINST()进行全文搜索时，MySQL可以利用全文索引快速定位匹配的文本，大大提高搜索效率。全文搜索支持自然语言搜索、布尔搜索等复杂的搜索模式，能够处理词干变化和同义词等语言特性。

SELECT * FROM papers WHERE MATCH(title,content) AGAINST ('查询字符串');  -- 使用全文索引进行搜索

通过使用全文索引和MATCH() AGAINST()查询，可以实现高效且功能强大的文本搜索功能，适用于搜索含有大量文本的数据库记录。这对于开发搜索引擎、内容管理系统等应用尤为重要。

注意事项

1. 使用全文索引前，搞清楚版本支持情况；
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。

（7）创建空间索引

SPATIAL 索引用于地理空间数据的搜索，在空间索引创建中，要求字段类型必须是空间数据类型并且非空 。

举例：创建表test5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

CREATE TABLE test5(
  geo GEOMETRY NOT NULL,                 -- 定义一个名为geo的GEOMETRY类型字段，且不允许NULL值
  SPATIAL INDEX spa_idx_geo(geo)         -- 在geo字段上创建一个空间索引，索引名为spa_idx_geo
) ENGINE=MyISAM;                         -- 指定表的存储引擎为MyISAM

查看索引信息：创建索引后，可以通过SHOW INDEX FROM命令查看表的索引信息，这有助于验证索引是否按预期创建，并了解其属性。查看test5表索引信息的语法示例：

show index from test5 \g;

可以看到，test5表的geo字段上创建了名称为spa_idx_geo的空间索引。注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。

（8）查看索引信息

方式一：使用 show create table 查看表结构：

show create table test1 \g; # 查看指定表创建的表结构

方式二：使用 show index from 表名 查看指定表的所有索引信息：

show index from test1; # 查看指定表的所有索引信息

• 表名（Table）：test1，表示这个索引属于test1表。
• 是否唯一（Non_unique）：0，表示这是一个唯一索引，索引的列值在表中是唯一的。
• 索引名（Key_name）：uk_idx_id，这是索引的名称，uk可能意味着这是一个唯一键。
• 索引中的序列号（Seq_in_index）：1，表示id列是在这个索引中的第一个（可能是唯一的）列。
• 列名（Column_name）：id，这是索引涉及的列名。
• 排序规则（Collation）：A，表示索引中的值是按升序排列的。A代表升序，D代表降序，NULL表示不适用。
• 索引的基数（Cardinality）：0，这是一个估计的值，表示索引中唯一值的大致数量，这个数字用于查询优化。
• 索引的子部分（Sub_part）：NULL，表示索引涵盖了整个列，而不是列的一部分。
• 是否压缩索引（Packed）：NULL，表示这个索引没有被压缩。
• 是否允许为空（Null）：空值表示列不允许包含NULL值。
• 索引类型（Index_type）：BTREE，这表示索引使用的是B+树结构，适用于范围查询和精确匹配查询。
• 注释（Comment）：用于添加有关索引的额外信息。
• 索引注释（Index_comment）：供索引的额外说明。
• 索引是否可见（Visible）：YES，表示这个索引在优化查询时是可见的，即查询优化器在制定计划时会考虑这个索引。
• 表达式索引的表达式（Expression）：NULL，表示这不是基于表达式的索引。

方式三：获取SELECT查询的执行计划。通过执行计划，你可以看到MySQL是如何处理你的SQL语句的，包括它如何使用索引

EXPLAIN SELECT * FROM book;  # 分析查询book表的执行计划

1.2.2 在已经存在的表上创建索引

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

1. 使用ALTER TABLE语句创建索引： ALTER TABLE语句创建索引的基本语法如下：

ALTER TABLE 表名 ADD 索引类型 INDEX 索引名 (列名);
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名); -- 创建唯一索引
ALTER TABLE 表名 ADD PRIMARY KEY (列名); -- 创建主键索引
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名 (列名); -- 创建全文索引
ALTER TABLE 表名 ADD SPATIAL INDEX 索引名 (列名); -- 创建空间索引

2. 使用CREATE INDEX创建索引：CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中， CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE 索引类型 INDEX 索引名 ON 表名 (列名);

1.3 删除索引

1. 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：

ALTER TABLE 表名 DROP INDEX 索引名;

2. 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：

DROP INDEX 索引名 ON 表名;

温馨提示

• 添加AUTO_INCREMENT约束字段的唯一索引不能被删除，因为AUTO_INCREMENT约束的列必须是键列（主键列，唯一键列）

• 在删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

2. MySQL8.0索引新特性

2.1 支持降序索引

降序索引以降序存储键值。虽然在语法上，从 MySQL4版本 开始就已经支持降序索引的语法了，但实际上DESC定义是被忽略的，直到MySQL 8.x版本才开始真正支持降序索引 (仅限于InnoDBc存储引擎)。

MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。 在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能。

举例：分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1，结果如下：

CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));

在MySQL 5.7版本中查看数据表ts1的结构，结果如下:

从结果可以看出，索引仍然是默认的升序

在MySQL 8.0版本中查看数据表ts1的结构，结果如下：

从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。

分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据，执行语句如下：

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
	DECLARE i INT DEFAULT 1;
	-- 初始化一个循环计数器 i，从1开始
	WHILE i < 800
	DO
		-- 循环体内，向表ts1插入随机数
		insert into ts1 select rand()*80000, rand()*80000;
		SET i = i+1; -- 每插入一次数据，计数器 i 自增1
	END WHILE;
	commit; -- 循环结束后，提交事务确保数据插入操作生效
END //
DELIMITER 

-- 调用存储过程，开始执行数据插入
CALL ts_insert();

然后通过EXPLAIN语句分析SQL的执行计划

EXPLAIN SELECT * FROM ts1 ORDER BY a, b DESC LIMIT 5;

在MySQL 5.7版本中查看数据表ts1的执行计划，结果如下:

在MySQL 8.0版本中查看数据表 ts1 的执行计划，结果如下:

从结果可以看出，修改后MySQL 5.7 的执行计划要明显好于MySQL 8.0。

2.2 隐藏索引

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较 大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使 查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引）， 确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除。

同时，如果你想验证某个索引删除之后的 查询性能影响，就可以暂时先隐藏该索引。

注意事项

主键不能被设置为隐藏索引。当表中没有显式主键时，表中第一个唯一非空索引会成为隐式主键，该隐式主键也不能设置为隐藏索引。

在MySQL中，索引默认是可见的，但我们可以通过使用VISIBLE或INVISIBLE关键词来设置索引的可见性。这种设置允许我们在不删除索引的情况下，对其进行隐藏或显示，以此来帮助数据库调优，同时不影响索引的实时更新。下面详细解释每种情况：

1. 创建表时直接创建

创建隐藏索引可以在创建表的时候一并完成，通过在CREATE TABLE语句中加入INVISIBLE关键字，标记某个索引为不可见。示例如下：

CREATE TABLE 表名 (
    列名1 数据类型 [约束条件],
    列名2 数据类型 [约束条件],
    ...
    列名N 数据类型 [约束条件],
    INDEX 索引名称 (列名 [长度]) INVISIBLE
);

这里的INVISIBLE关键字就是用来指明创建的索引是不可见的。

2. 在已经存在的表上创建

如果表已经存在，仍然可以为其添加隐藏索引，通过CREATE INDEX语句实现，并加上INVISIBLE关键字。示例如下：

-- 为已存在的表添加索引，并设置为不可见
CREATE INDEX 索引名称 ON 表名 (列名 [长度]) INVISIBLE;

3. 通过 ALTER TABLE 语句创建隐藏索引

也可以通过ALTER TABLE语句为已存在的表添加索引，并设置为不可见。示例如下：

-- 为已存在的表添加索引，并设置为不可见
ALTER TABLE 表名 ADD INDEX 索引名称 (列名 [(长度)]) INVISIBLE;

4. 切换索引可见状态

对于已存在的索引，可以通过ALTER TABLE语句切换其可见性状态。示例如下：

ALTER TABLE 表名 ALTER INDEX 索引名称 INVISIBLE;  -- 将现有索引设置为不可见状态
ALTER TABLE 表名 ALTER INDEX 索引名称 VISIBLE;   -- 将不可见索引切换为可见状态

通过调整索引的可见性，可以在不影响现有查询的前提下，通过explain测试索引对性能的影响，帮助开发者进行数据库调优。

注意事项

当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

5. 使隐藏索引对查询优化器可见

在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关 (use_invisible_indexes) 来打开某个设置，使隐藏索引对查询优化器可见。如果use_invisible_indexes 设置为off (默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计 划时仍会考虑使用隐藏索引。

（1）在MySQL命令行执行如下命令查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

在输出的结果信息中找到如下属性配置。

use_invisible_indexes=off

此属性配置值为off，说明隐藏索引默认对查询优化器不可见。

（2）使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令：

mysql> set session optimizer_switch="use_invisible_indexes=on";
Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功，再次查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_
intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_co
st_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on
,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on
,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,
use_invisible_indexes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)

此时，在输出结果中可以看到如下属性配置。

use_invisible_indexes=on

use_invisible_indexes属性的值为on，说明此时隐藏索引对查询优化器可见。

（3）使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况。

explain select * from classes where cname = '高一2班';

发现查询优化器开启状态时，即使隐藏了索引的可见性，还是会使用隐藏索引来查询数据。

（4）如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可。

mysql> set session optimizer_switch="use_invisible_indexes=off";
Query OK, 0 rows affected (0.00 sec)

再次查看查询优化器的开关设置。

mysql> select @@optimizer_switch \G

此时，use_invisible_indexes属性的值已经被设置为“off”。

3. 索引的设计原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。 高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑相应准则。

3.1 数据准备

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

-- 创建数据库
CREATE DATABASE atguigudb1;
-- 使用新创建的数据库
USE atguigudb1;

-- 创建学生信息表
CREATE TABLE `student_info` (
  `id` INT(11) NOT NULL AUTO_INCREMENT, -- 主键，自增
  `student_id` INT NOT NULL, -- 学生ID
  `name` VARCHAR(20) DEFAULT NULL, -- 学生姓名
  `course_id` INT NOT NULL, -- 课程ID
  `class_id` INT(11) DEFAULT NULL, -- 班级ID
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- 记录创建时间，自动更新
  PRIMARY KEY (`id`) -- 设置id为主键
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- 使用InnoDB存储引擎，设置默认字符集为utf8

-- 创建课程表
CREATE TABLE `course` (
  `id` INT(11) NOT NULL AUTO_INCREMENT, -- 主键，自增
  `course_id` INT NOT NULL, -- 课程ID
  `course_name` VARCHAR(40) DEFAULT NULL, -- 课程名称
  PRIMARY KEY (`id`) -- 设置id为主键
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; -- 使用InnoDB存储引擎，设置默认字符集为utf8

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

-- 设置命令分隔符，为了让函数内部的;不被误解为语句的结束
DELIMITER //

-- 创建一个名为rand_string的函数，接受一个整数n作为参数
CREATE FUNCTION rand_string(n INT)
	RETURNS VARCHAR(255) -- 定义函数返回值类型为字符串，最大长度255
BEGIN
	DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; -- 定义一个包含大小写字母的字符串
	DECLARE return_str VARCHAR(255) DEFAULT ''; -- 定义一个用于存放最终生成字符串的变量
    DECLARE i INT DEFAULT 0; -- 初始化一个计数器i
    WHILE i < n DO -- 开始一个循环，直到i等于n
    	SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); -- 从chars_str中随机选择一个字符并追加到return_str
    	SET i = i + 1; -- 计数器i自增
    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;
    -- 使用FLOOR和RAND函数生成介于from_num和to_num之间的随机整数
    SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1));
    -- 返回计算得到的随机整数
    RETURN i;
END //
DELIMITER ;

在创建随机产生字符串函数时，可能会报错：

This function has none of DETERMINISTIC......

由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。

在主从复制中，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设置。

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

show variables like 'log_bin_trust_function_creators';

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

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

• 如果mysqld重启，上述设置的参数又会消失。永久生效方法如下：

  • windows下：my.ini[mysqld]加上：

    log_bin_trust_function_creators=1
    

  • linux下：/etc/my.cnf下my.cnf[mysqld]加上：

    log_bin_trust_function_creators=1
    

    添加完成使用systemctl restart mysqld命令重启MySQL服务即可生效

第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);

3.2 哪些情况适合创建索引

3.2.1 字段的数值有唯一性的限制

（1）索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。 （2）例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。

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

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

（2）比如 student_info 数据表（含 100 万条数据），假设我们想要查询 student_id = 123110 的用户信息。

# 查看当前 stduent_info 表中的索引，目前只有主键(id)索引
SHOW INDEX FROM student_info;

# student_id 字段上没有索引
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110;  # 375 ms

# 给 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; # 6 ms

3.2.3 经常 GROUP BY 和 ORDER BY 的列

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

（2）比如，按照 student_id 对学生选修的课程进行分组，显示不同的 student_id 和课程数量，显示 100 个即可。

-- 查询student_id字段和其对应的记录数量，限制结果为前100条，此时student_id字段上存在索引
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id 
LIMIT 100; -- 执行时间为4毫秒，快速返回结果

-- 删除名为idx_sid的索引，该索引之前是基于student_info表的student_id字段创建的
DROP INDEX idx_sid ON student_info;

-- 再次执行相同的查询，此时student_id字段上没有索引
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id 
LIMIT 100;  -- 执行时间为689毫秒，由于缺少索引，查询速度变慢

同样，如果是ORDER BY，也需要对字段创建索引。

（3）如果同时有 GROUP BY 和 ORDER BY 的情况：比如我们按照 student_id 进行分组，同时按照创建时间降序的方式进行排序，这时我们就需要同时进行 GROUP BY 和 ORDER BY，那么是不是需要单独创建 student_id 的索引和 create_time 的索引呢?

• ① 当我们对 student_id 和 create_time 分别创建索引，执行下面的 SQL 查询：

-- 为student_info表添加一个基于student_id的单列索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

-- 为student_info表添加一个基于create_time的单列索引
ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);

-- 查询student_id和其对应的记录数，按create_time降序排列，仅显示前100条记录
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  -- 查询耗时5.265秒

-- 如果SQL执行时出现与sql_mode=only_full_group_by相关的错误，可以通过以下语句查看当前的SQL模式
SELECT @@sql_mode;

-- 设置sql_mode以避免only_full_group_by错误，排除了对全组必须使用的限制
SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION';

• ② 对 student_id 和 create_time 创建联合索引。

-- 在student_info表上添加一个组合索引idx_sid_cre_time，基于student_id和create_time字段，其中create_time为降序
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id, create_time DESC);

-- 查询student_id字段和其对应的记录数量，并根据create_time字段的降序排列，限制结果为前100条
-- 由于添加了组合索引，该查询可以更高效地利用索引进行排序和分组，显著减少查询时间
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC
LIMIT 100;  -- 执行时间为0.257秒，添加组合索引后查询速度明显提升

• ③ 如果我们创建联合索引的顺序为 (create_time, student_id) 呢？运行时间为 3.790 s，因为在进行 SELECT 查询的时候，先进行GROUP BY，再对数据进行 ORDER BY 的操作，所以按照 (student_id, create_time) 这个联合索引的顺序效率是最高的。

-- 在student_info表上添加一个组合索引idx_cre_time_sid，基于create_time降序和student_id字段
ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC, student_id);

-- 删除之前创建的idx_sid_cre_time组合索引
DROP INDEX idx_sid_cre_time ON student_info;

-- 查询student_id字段和其对应的记录数量，并根据create_time字段的降序排列，限制结果为前100条
-- 先前添加的组合索引被删除，现在使用新的组合索引idx_cre_time_sid进行查询
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  -- 执行时间为3.790秒，展示了删除旧索引并添加新组合索引后的查询性能

3.2.4 UPDATE、DELETE 的 WHERE 条件列

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

-- 显示student_info表中的所有索引信息
SHOW INDEX FROM student_info;

-- 更新student_info表中的student_id字段为10002，条件是NAME字段等于'462eed7ac6e791292a79'
-- 这个操作在没有特定索引优化NAME字段查询时可能会比较慢
UPDATE student_info SET student_id = 10002  -- 更新的字段是非索引字段
WHERE NAME = '462eed7ac6e791292a79';  -- 执行时间为0.733秒，表明更新操作可能没有使用有效的索引

-- 在student_info表上为NAME字段添加索引，旨在优化基于NAME字段的查询和更新操作
ALTER TABLE student_info
ADD INDEX idx_name(NAME);

-- 再次更新student_info表中的student_id字段为10001，条件仍然是NAME字段等于'462eed7ac6e791292a79'
-- 由于已经添加了针对NAME字段的索引，这次更新操作应该会更快
UPDATE student_info SET student_id = 10001 
WHERE NAME = '462eed7ac6e791292a79'; -- 执行时间为0.0秒，表明索引优化显著提高了更新操作的性能

3.2.5 DISTINCT 字段需要创建索引

（1）有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

（2）比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（600637 条记录，运行时间 0.683s ）

（3）如果我们对 student_id 创建索引，再执行 SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（600637 条记录，运行时间 0.010s ）

（4）你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照递增的顺序进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。

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

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

（2）对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大，没有 WHERE 条件过滤是非常可怕的。

（3）对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。 比如 course_id 在 student_info 表和 course 表中都为 int(11)类型，而不能一个为 int，另一个为 varchar 类型（如果发生类型转换，则无法用上索引，查询速度会变慢）。

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

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';  --  只对 student_id 添加了索引

上述运行结果（1 条数据，运行时间 0.189s ）

-- 然后对name字段创建索引，测试查询效率
ALTER TABLE student_info ADD INDEX idx_name(name);

这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s 。

3.2.7 使用列的类型小的创建索引

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

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

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

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

3.2.8 使用字符串前缀创建索引

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

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

（2）我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。

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

create table shop(address varchar(120) not null); 
# 创建前缀索引，前缀长度为 12
# 这样做可以节省索引存储空间，提高查询效率
alter table shop add index(address(12));

（4）问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度：

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

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

公式：

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

例如：

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

越接近于1越好，说明越有区分度

（5）引申另一个问题：索引列前缀对排序的影响。

如果使用了索引列前缀，比方说前边只把address列的 前12个字符 放到了二级索引中，下边这个查询可能就有点尴尬了：

SELECT * FROM shop
ORDER BY address
LIMIT 12;

因为二级索引中不包含完整的 address 列信息，所以无法对前12个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序。

（6）拓展：Alibaba《Java开发手册》

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

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

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

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

（2）可以使用以下公式计算区分度，越接近 1 越好，一般超过 33% 就算是比较高效的索引了。

select count(distinct a) / count(*) from t1

（3）拓展：联合索引把区分度高（散列性高）的列放在前面。

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

• 将最频繁使用的列放到联合索引的最左侧，可以减少单独为这些列建立索引的需要，从而减少整体的索引数量。
• 同时由于"最左前缀原则"，可以增加联合索引的使用率。

笔记

最左前缀原则是指在使用联合索引时，数据库查询可以利用索引中最左边的列进行查找，但是如果查询中没有使用索引最左边的列作为第一个查询条件，则该索引就无法被有效利用。

具体来说，假设有一个联合索引是基于(A, B, C)三个列创建的。根据最左前缀原则：

• 如果查询条件是基于列A的，则可以有效利用这个联合索引。
• 如果查询条件是基于列A和列B的组合，则也可以利用这个联合索引。
• 如果查询条件是基于列A、列B和列C的组合，则可以利用这个联合索引。
• 如果查询条件只基于列B或列C，或者是列B和列C的组合，而没有包含列A，则无法利用这个联合索引。
• 如果查询条件是基于列A和列C的组合，只有列A可以利用这个联合索引，数据库无法直接跳过B直接对C进行索引查找，因为索引是按照(A, B, C)的顺序存储和搜索的。

原理

因为联合索引是按照列的顺序存储键值的，查询时如果跳过了最左边的列，数据库就无法确定该从哪个点开始搜索，因为没有更早的键值作为搜索的起点。这就是为什么如果查询不包含最左列，则无法有效利用索引的原因。

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

对于单列索引，数据库可能需要在多个索引之间跳转来满足查询条件，这通常比在一个联合索引中顺序扫描要低效。

3.3 限制索引的数目

在实际工作中,我们需要限制每张表上的索引数量,因为索引的数目不是越多越好.建议单张表索引数不超过6个；原因如下：

• ① 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
• ② 索引会影响 INSERT、DELETE、UPDATE 等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
• ③ 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加 MySQL 优化器生成执行计划时间，降低查询性能。

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

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字段中。

3.4.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();

查询对比：

mysql> select * from t_without_index where b = 9879;
+------+------+
| a | b |
+------+------+
| 1242 | 9879 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t_with_index where b = 9879;
+-----+------+
| a | b |
+-----+------+
| 112 | 9879 |
+-----+------+
1 row in set (0.00 sec)

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

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

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

（1）在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的“性别""字段上只有“男"与“女"两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

（2）举例 1：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。

（3）举例 2：假设有一个学生表，学生总数为 100 万人，男性只有 10 个人，也就是占总人数的 10 万分之一。学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1，0 代表女性，1 代表男性。

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% 的时候，也不需要对这个字段使用索引。

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

• 频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，更新索引也会影响效率。

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

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

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

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

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

3.4.7 不要定义夯余或重复的索引

（1）冗余索引

• ① 有时候有意或者无意的就对同一个列创建了多个索引，比如：index(a,b,c) 相当于 index(a)、index(a,b)、index(a,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),  -- 主键约束，使用id列
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),  -- 联合索引，包括姓名的前10个字符、生日和电话号码
    KEY idx_name (name(10))  -- 对姓名的前10个字符创建的冗余索引，已由上一个联合索引覆盖
);

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

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

CREATE TABLE repeat_index_demo (
    col1 INT PRIMARY KEY,  -- col1作为主键，自动创建聚簇索引
    col2 INT,
    UNIQUE uk_idx_c1 (col1),  -- 对col1创建一个唯一索引，这是不必要的，因为主键已经确保了唯一性
    INDEX idx_c1 (col1)  -- 对col1创建一个普通索引，这也是冗余的，因为主键索引已经足够
);

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

3.5.小结

（1）索引是一把双刃剑，可提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。

（2）选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能拘泥于上面的准则，大家要在以后的学习和工作中进行不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。