MySQL索引

1. 什么是索引

• 索引是对数据库表中一列或多列的值进行排序的结构。使用索引可以提高数据库中特定数据的查询速度。
• 索引是一个单独的，存储在硬盘上的数据库结构，它们包含着对数据库表中所有记录的引用指针。
• 索引是在存储引擎中实现的，InnoDB存储引擎只支持 BTREE 索引。

2. 索引的优缺点

优点：

• 通过创建唯一索引，可以保证数据库中每一行数据的唯一性。
• 可以大大加快数据的查询速度。
• 在实现数据的参照完整性方面，可以加速表和表之间的链接。
• 在使用分组和排序查询时，可以显著减少分组和排序的时间。

缺点：

• 创建索引和维护索引要好费时间，随着数据量的增加所耗费的时间也会增加
• 索引需要占用磁盘空间，除了数据表占空间之外，索引也需要占用物理空间，如果有大量的索引，索引文件可能比数据文件更快的达到最大文件尺寸。
• 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

3. 索引的分类

• 普通索引：基本的索引类型，允许在定义索引的列插入重复值和空值。
• 唯一索引：索引列的值必须唯一，允许有空值。主键索引是一种特殊的唯一索引，不允许有空值。
• 单列索引：一个索引只包含单个列，一个表可以有多个单列索引。
• 组合索引：在多个字段上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才有效。组合索引遵循最左前缀集合。组合索引的列，如果有唯一性约束，那么组合的列的值必须唯一。
• 全文索引：支持全文查找。(只有在MyISAM存储引擎才支持)。
• 空间索引：(只有在MyISAM存储引擎才支持)

4. 索引的设计原则

（1） 索引并非越多越好：如果一个表中有大量的索引，不仅占用磁盘空间，而且会影响 INSERT、DELETE、UPDATE操作的性能，因为在进行这些操作的时候，索引也会调整和更新。
（2）避免对经常更新的表进行过多的索引，并且索引列尽可能的少，而对于经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
（3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短。
（4）在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列不要建立索引。如：性别字段不适合建立索引。因为本身字段就只包含 男、女 两个值。
（5）当唯一性是某种数据本身的特征，应该指定唯一索引。使用唯一索引能确保列的完整性，提高查询速度。
（6）在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以建立组合索引。

5. 索引的操作

1. 创建索引

• 建表时创建：

CREATE TABLE book(
	id INT(11) PRIMARY KEY AUTO INCREMENT,
	name VARCHAR(255) NOT NULL,
	year_publication YEAR NOT NULL,
	INDEX(year_publication) // 创建普通索引，可选值有 UNIQUE INDEX KEY
	// 普通索引：INDEX(year_publication)
	// 唯一索引：UNIQUE INDEX UniqueId(id)
	// 单列索引：INDEX SingleIdx(name(20))
	// 组合索引：IDEX MultiIdx(id, name)
	// 全文索引：FULLTEXT INDEX FullTxtIdx(name)
);

• 在已经存在的表上建立索引

1. ALTER TABLE book ADD INDEX BkNameIdx(book_name(30))
2. CREATE INDEX BkNameIdx ON book(book_name)

2. 删除索引

1. ALTER TABLE table_name DROP INDEX index_name
2. DROP INDEX index_name ON table_name

6. 索引优化

1. 何时使用聚集索引或非聚集索引？
   
2. 索引不会包含有NULL值的列
   只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3. 使用短索引
   对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
4. 索引列排序
   MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5. like语句操作
   一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6. 不要在列上进行运算
   例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
   select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。
   总结：MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引

7. QA

主键索引和唯一索引的区别：

• 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
• 在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。