Mysql索引 · 多云平台/运维手册

MySQL 索引
索引的类型
什么时候考虑给字段添加索引？(满足什么条件)
索引的优点
索引的缺点
什么情况下不创建索引
底层工作原理

MySQL 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引的类型

1、普通索引
这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。
创建方式：
直接创建索引（给字段索引）

CREATE INDEX index_name ON table(column(length))

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	INDEX index_name (title(length))
)

删除索引

DROP INDEX index_name ON table

2、唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。

创建唯一索引(给字段添加索引)

CREATE UNIQUE INDEX index_name ON table(column(length))

修改表结构

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

创建表时同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	UNIQUE indexName (title(length))
);

3、组合索引
平时用的SQL查询
语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–title,time
–title

为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。
简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：
使用到上面的索引

SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE utitle='测试';

未使用到上面的索引

SELECT * FROM article WHREE time=1234567890;

以上就是常用的三种索引类型及其创建使用的方法

什么时候考虑给字段添加索引？(满足什么条件)

数据量庞大。（根据客户的需求，根据线上的环境）
该字段很少的DML操作。（因为字段进行修改操作，索引也需要修改维护）
该字段经常出现在where子句中。（经常根据哪一个字段查询）

注意：主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高，尽量根据主键查询。

索引的优点

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2.建立索引可以大大提高检索的数据,以及减少表的检索行数
3.在表连接的连接条件可以加速表与表直接的相连
4.在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
5.建立索引,在查询中使用索引可以提高性能

索引的缺点

1.在创建索引和维护索引会耗费时间,随着数据量的增加而增加
2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

什么情况下不创建索引

1.查询中很少使用到的列不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2.很少数据的列也不应该建立索引,比如一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3.定义为text和image和bit数据类型的列不应该增加索引,
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

底层工作原理

通过B tree 缩小扫描范围，底层索引进行了排序，索引携带数据在表中的物理地址，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址关联到表中的数据，效率是最高的。

mysql> select ename from emp where ename = 'SMITH';
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.00 sec)

通过索引转换为

mysql> select ename from emp where 物理地址 = 03x;