Mysql索引详述

一、什么是索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。索引就好比是书的目录，可以让我们更快速查找到需要的信息

二、为什么要使用索引

索引是数据库性能调优技术的基础，常用于实现数据的快速检索。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

2.1顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。看似很简单，但是当数据量非常庞大的时候效率会非常低下。

2.2索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。 使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

三、索引的优缺点

3.1优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

3.2缺点

• 创建索引和维护索引要耗费时间。
•  当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，就好比书中的章节增加或者删除，我们需要对目录进行更新一样。
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。

四、索引的分类

MySQL 的索引有两种分类方式：逻辑分类和物理分类。 按照逻辑分类，索引可分为：

• 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
• 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
• 全文索引：让搜索关键词更高效的一种索引。

按照物理分类，索引可分为：

• 聚集索引：一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引，如果还是没有的话，就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引，因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）；
• 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。
   

五、创建索引

可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。

语法格式：

 CREATE INDEX <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

这里的<长度>是指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。

六、查看索引

可以使用 SHOW INDEX 语句查看表中创建的索引。

语法格式：

SHOW INDEX FROM <表名> [ FROM <数据库名>]

七、修改和删除索引

不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。

在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

语法格式：

DROP INDEX <索引名> ON <表名>

注意：

如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

八、最左前缀原则

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

举例：

CREATE INDEX info_index ON userinfo(name,power)

按照上文的说明，mysql会对字段name也建立索引

• mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

九、索引在什么情况下不会被使用

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用。

首先创建一个存储过程用来批量插入数据

CREATE PROCEDURE proc_info()
BEGIN
 DECLARE num int DEFAULT 0;
 WHILE num < 20000 DO
	INSERT INTO info (name) VALUES(CONCAT("人员",num));
	SET num = num +1;
END WHILE;
END;

调用该存储过程

CALL proc_info()

创建索引

CREATE INDEX  index_name ON info(name)

查询索引

SHOW INDEX FROM info FROM demo

9.1查询条件中有OR关键字

这里先把index_name这个索引删除

查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引。如果 OR 前后有一个条件的列不是索引，那么查询中将不使用索引。

然后再添加index_name索引

9.2 like查询

当模糊查询不以%开始，索引正常

以%开头时

这个问题只能通过覆盖索引来解决；那什么是覆盖索引呢？

覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

比如我创建一个复合索引

CREATE INDEX index_name_cno ON userinfo(name,cno)

 

 

上文中查询的字段都在索引中，那此时模糊查询仍以%开头

我这里使用的Mysql版本为

 

9.3 字符串不加单引号

这里是针对字符串类型的数字，如果没加单引号，Mysql底层会进行隐式转换，导致索引失效

9.4 不要使用select *

这是为了避免通过索引回表查询

• using filesort：性能低，需要额外一次查询、排序，一般出现在where后的条件与order by 的条件不一致时。
• using temporary：性能低，用到了临时表，一般出现在where后的条件与group by 的条件不一致时。
• using idnex：性能提升，索引覆盖，不读取源文件而从索引文件中获取，无需回表查询
• using where：既需要从索引文件中查，也需要回原表查

9.5mysql估计使用全表扫描要比使用索引快,则不使用索引

 

9.6 IN走索引，NOT IN不走索引