MySQL深入学习 --- 索引的创建和删除，索引设计原则，索引失效场景，查询优化，索引下推ICP

往期：

• MySQL深入学习 — mysql逻辑架构，SQL的执行流程，数据库缓冲池
• MySQL深入学习 — MySQL存储引擎，InnoDB、MyISAM索引的数据结构

六、索引的创建和删除

索引的分类：

• 从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引
• 按照物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引
• 按照 作用字段个数进行划分，分成单列索引和联合索引

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

• 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 等索引；

6.1 索引的创建

1.创建表的时候创建

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

• UNIQUE 唯一索引、FULLTEXT 全文索引、SPATIAL 空间索引
• INDEX 与 KEY 是同义词，两者作用相同
• index_name指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名
• col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择，可以选多个列组成联合索引
• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
• ASC或 DESC指定升序或者降序的索引值存储

CREATE TABLE book(
    book_id INT,
    book_name VARCHAR(100),
    year_publication YEAR,
    INDEX(year_publication) 			#普通索引，也是单列索引
    INDEX multi_idx(book_id,book_name) 	#多列索引
    UNIQUE INDEX uk_idx_id(book_id)		#唯一索引
);

CREATE TABLE student (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    student_no VARCHAR(200),
    student_name VARCHAR(200),
    PRIMARY KEY(id)						#主键索引
);

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`)	#全文索引
) ENGINE=MyISAM DEFAULT CHARSET=utf8;	
#不同于like方式查询
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;
#全文索引可以用match+against方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

CREATE TABLE test5(
    geo GEOMETRY NOT NULL,
    SPATIAL INDEX spa_idx_geo(geo)		#空间索引
) ENGINE=MyISAM;

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

2.在已存在的表上创建

1.使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

2.使用CREATE INDEX创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

6.2 索引的删除

1.使用ALTER TABLE删除

ALTER TABLE table_name DROP INDEX index_name;

2.使用DROP INDEX删除

DROP INDEX index_name ON table_name;

七、索引设计原则

7.1 哪些情况适合索引

1. 字段的数值有唯一性的限制
   • 唯一性索引的值是唯一的，可以更快通过索引确定某条记录
2. 频繁作为WHERE 查询条件的字段
   • 某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了
3. 经常 GROUP BY 和 ORDER BY 的列
   • 排序操作会浪费很多时间，如果为其建立索引，可以有效的避免排序操作，相当于事先用索引为其排序
4. UPDATE、DELETE 的 WHERE 条件列
   • 在UPDATE或DELETE时，首先要用WHERE查询出来，如果添加了索引，找到这个要修改或删除的记录的效率就会提高
   • 但是后续的更新操作，更新的字段是非索引字段效率反而会高，因为非索引字段不需要维护索引
   • 所以，WHERE条件加索引，尽量不要修改索引字段
5. DISTINCT字段需要创建索引
   • 如果需要去重，对该字段创建索引也会提