MySql索引知识点整理（一）

（一）概述

1.索引的作用：相当于数据的目录，用于提高查询速度

2.索引的原理、如何起作用的：所谓索引就是为特定的mysql字段（一个或多个）进行一些特定的算法排序，常用的排序算法有二叉树的算法和哈希算法

 

（二）索引分类

INDEX普通索引

施加的字段值允许重复

UNIQUE唯一索引

施加的字段不可重复，但可以有NULL值。

PRIMARY KEY主键索引

施加的字段不允许出现相同的值，且不能为NULL值，一个表只能有一个primary_key索引。设置主键约束时自动添加

fulltext index 全文索引

上述三种索引都是针对列的值发挥作用，但全文索引，可以针对值中的某个单词，比如一篇文章中的某个词，只有myisam以及英文支持，并且效率让人不敢恭维，很少会使用，可暂时忽略

*组合索引：在多个字段的组合上增加索引，索引类型为上述4中方式

 

（三）索引的增删改查（CRUD）

索引也是数据库中的一种数据库对象，对其的CRUD通用使用DDL语句。

1.创建索引

1）在建表时同时创建，比如：

CREATE TABLE `test1` ( `id` smallint(5) UNSIGNED AUTO_INCREMENT NOT NULL, -- 注意，下面创建了主键索引，这里就不用创建了 `username` varchar(64) NOT NULL COMMENT '用户名', `nickname` varchar(50) NOT NULL COMMENT '昵称/姓名', `intro` text, PRIMARY KEY (`id`), UNIQUE KEY `unique1` (`username`), -- 索引名称，可要可不要，不要就是和列名一样 KEY `index1` (`nickname`), FULLTEXT KEY `intro` (`intro`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='后台用户表';

 

2）修改表结构时创建（针对表已经存在的情况）。语法：

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)

比如：

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名，可要可不要；如果不要，当前的索引名就是该字段名。 ALTER TABLE `table_name` ADD UNIQUE (`column_list`) ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

 

3）直接使用create语句单独创建索引并施加到表上

*注意：此种方式只可对表增加普通索引或UNIQUE索引

CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)

 

2.查询某个表所有的索引

show index from 表名;

 

 

3.删除索引

1）方式1：直接用drop语句删

DROP INDEX `index_name` ON `talbe_name`

2）方式2：修改表结构的时候删 ALTER TABLE `table_name` DROP INDEX `index_name`

 

4.修改索引：删了重建

 

5.组合索引（是一种使用索引的技巧，不属于标准的索引类型）

由于增加了匹配条件的维度，所以通常会比单字段索引更能提升效率

ALTER TABLE `myIndex` ADD INDEX 组合索引名 (字段1,字段2...);

注意字段顺序，mysql 组合索引遵循 “最左前缀”匹配规则。简单的理解就是只从最左面的开始组合。并不是只要包含索引施加的所有字段的组合查询都会用到该组合索引

比如：创建了一个name，city,age的组合索引name_city_age(vc_Name(10),vc_City,i_Age)

下面几句sql可以用上组合索引：

SELECT * FROM myIndex WHREE vc_Name=”erquan” AND vc_City=”郑州”

SELECT * FROM myIndex WHREE vc_Name=”erquan”

下面几句sql查询时组合索引将失效：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”郑州”

SELECT * FROM myIndex WHREE vc_City=”郑州”

即：name_city_age(vc_Name(10),vc_City,i_Age) 是左到右进行索引，如果第一个查询字段不是vc_name也就是没有左前索引Mysql不执行索引查询

（四）设置索引所遵循的一些法则（面试容易问）

1. 为维度高的列创建索引。

• 维度指的是数据列中不重复值出现的个数，这个数量越高，维度就越高。
• 如某个字段在8行数据中的值分别为a,b ,c,d,a,b,c,d这个表的维度为4。
• 性别这样的低纬度列不适合创建索引

1. 对 where,on,group by,order by 中出现的列使用索引。
2. 对数量较小的数据列使用索引会使索引文件更小，节省空间和性能。
3. 根据实际需求可以使用组合索引，一方面减少文件索引大小，另一方面在使用时速度要优于多个单列索引。注意组合索引的前缀匹配。
4. null值太多的列不适合建索引。复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
5. 在索引列上进行运算（比如使用函数）将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017

7. not in和<>操作会导致索引失效

8.like语句对索引使用的影响：

like “aaa%”可以使用索引

like “%aaa%” 不会使用索引

9.不要过多创建索引，除了增加额外的磁盘空间外，对于DML操作的速度影响很大，因为其每增删改一次就得从新建立索引。

（五）使用explain(执行计划)观察索引是否生效

EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

使用方法：在select语句前加上explain就可以了，比如:

explain select * from blog where false;

 

例1：没有查询条件，返回所有字段--全表扫描没有使用索引

 

例2：没有查询条件，返回设置了索引的单个字段，使用到了索引（这里实际上启用了启用了覆盖索引）

 

 

例3：没有查询条件，返回设置了组合索引的两个字段，使用到了组合索引（这里实际上启用了启用了覆盖索引）

 

 

例4：没有查询条件，返回字段列表中包含了组合索引以外的字段（无论此字段上是否施加索引）,均不能用到索引，全表扫描

 

 

例5：以主键字段为查询条件,无论返回所有字段还是部分字段,均使用到了主键索引

 

 

例6：以施加索引的单个字段为精确查询条件,可以使用到索引

 

例7：以施加组合索引的多个字段为精确查询条件，可以使用到索引

 

但是如果组合索引最左边的字段没有出现在查询条件中，即使有其他字段出现，索引也无效

 

例8：关联条件中使用or操作，索引无效

 

例9：使用in操作符以主键字段作为查询条件，使用到索引

 

例10：使用not in操作符以主键字段作为查询条件，未使用索引

 

 

例11：使用like模糊查询，左右两端都有通配符，索引无效

 

例12：使用like模糊查询，只有左端有通配符，索引无效

 

例13：使用like模糊查询，只有右端有通配符，索引有效

 

 

（六）小结

不要盲目的创建索引，只为查询频繁且增删改很少的列创建索引，创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新。

但是，在很多应用中，查询的语句远远大于DML的语句，甚至可以占到80%~90%，所以也不要太在意，只是在大数据导入时，可以先删除索引，再批量插入数据，最后再添加索引。