Mysql索引

前言

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

如果把数据库中的某一张看成一本书，那么索引就像是书的目录，可以通过目录快速查找书中指定内容的位置，对于数据库表来说，可以通过索引快速查找表中的数据。

索引的概念

索引是对数据库表中一列或多列的值进行排序的一种数据结构.实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。使用索引可快速访问数据库表中的特定信息.

索引的原理

索引一般以文件形式存在磁盘中（也可以存于内存中），存储的索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全量搜索，也就是进行全局扫描，有多少条数据就要进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。而建立索引之后，会将建立索引的KEY值放在一个n叉树上（BTree）。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表，每次以索引进行条件查询时，会去树上根据key值直接进行搜索。

索引的优点

建立索引的目的是加快对表中记录的查找或排序！

① 建立索引的列可以保证行的唯一性，生成唯一的rowId

② 建立索引可以有效缩短数据的检索时间

③ 建立索引可以加快表与表之间的连接

④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

索引的缺点

① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大

② 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）

③ 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

索引的使用场景

数据库中表的数据量较大的情况下，对于查询响应时间不能满足业务需求，可以合理的使用索引提升查询效率。

索引的分类

① 普通索引（单列索引）

② 复合索引（组合索引）

③ 唯一索引

④ 主键索引

⑤ 全文索引

索引的创建

unique|fulltext为可选参数，分别表示唯一索引、全文索引

index和key为同义词，两者作用相同，用来指定创建索引

col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择

index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值

length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度

asc或desc指定升序或降序的索引值存储

① 普通索引（单列索引）

直接创建索引 CREATE INDEX index_name ON table_name(col_name); 修改表结构的方式添加索引 ALTER TABLE table_name ADD INDEX index_name(col_name); 创建表的时候同时创建索引 CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` varchar(255) NOT NULL , `content` varchar(255) NULL , `time` varchar(20) NULL DEFAULT NULL , PRIMARY KEY (`id`), INDEX index_name (title(255)) ) 删除索引 DROP INDEX index_name ON table_name; 或者 alter table `表名` drop index 索引名;

② 复合索引（组合索引）

复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。 （1）创建一个复合索引

create index index_name on table_name(col_name1,col_name2,...);

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

alter table table_name add index index_name(col_name,col_name2,...);

③ 唯一索引

唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）。

（1）创建唯一索引

# 创建单个索引 CREATE UNIQUE INDEX index_name ON table_name(col_name); # 创建多个索引 CREATE UNIQUE INDEX index_name on table_name(col_name,...);

（2）修改表结构

# 单个 ALTER TABLE table_name ADD UNIQUE index index_name(col_name); # 多个 ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);

（3）创建表的时候直接指定索引

CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` varchar(255) NOT NULL , `content` varchar(255) NULL , `time` varchar(20) NULL DEFAULT NULL , PRIMARY KEY (`id`), UNIQUE index_name_unique(title) )

④ 主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引： （1）主键索引(创建表时添加)

CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` varchar(255) NOT NULL , `content` varchar(255) NULL , `time` varchar(20) NULL DEFAULT NULL , PRIMARY KEY (`id`) )

（2）主键索引(创建表后添加)

alter table tbl_name add primary key(col_name);

CREATE TABLE `order` ( `orderId` varchar(36) NOT NULL, `productId` varchar(36) NOT NULL , `time` varchar(20) NULL DEFAULT NULL ) alter table `order` add primary key(`orderId`);

⑤ 全文索引

在一般情况下，模糊查询都是通过 like 的方式进行查询。但是，对于海量数据，这并不是一个好办法，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情,所以 like 进行模糊匹配性能很差。

这种情况下，需要考虑使用全文搜索的方式进行优化。全文搜索在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引在 MySQL 5.6 版本之后支持 InnoDB，而之前的版本只支持 MyISAM 表。

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。目前只有char、varchar，text 列上可以创建全文索引。

小技巧： 在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

（1）创建表的适合添加全文索引

CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` varchar(255) NOT NULL , `content` text NOT NULL , `time` varchar(20) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (content) )

（2）修改表结构添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name)

（3）直接创建索引

CREATE FULLTEXT INDEX index_fulltext_content ON table_name(col_name)

注意： 默认 MySQL 不支持中文全文检索！

MySQL 全文搜索只是一个临时方案，对于全文搜索场景，更专业的做法是使用全文搜索引擎，例如 ElasticSearch (ES)或 Solr。

索引的查询和删除

#查看: show indexes from `表名`; #或 show keys from `表名`; #删除 alter table `表名` drop index 索引名;

索引失效的情况

随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表）统计信息失效 需要重新搜集统计信息索引本身失效 需要重建索引

1.or语句前后没有同时使用索引。 2.复合索引未用左列字段; 3.like以%开头; 4.需要类型转换; 5.where中索引列有运算; 6.where中索引列使用了函数; 7.如果mysql觉得全表扫描更快时（数据少）;

总结

MySQL改善查询性能改善的最好方式，就是通过数据库中合理地使用索引！

一般当数据量较大的时候，遇到sql查询性能问题，首先想到的应该是查询的sql时候使用了索引，如果使用了索引性能还是提高不大，就要检查索引是否使用正确，索引是否在sql查询中生效了！

如果索引生效了，并且索引的使用也是合理的，最后sql性能还是不高，那就考虑重新优化sql语句！