MySQL索引

1.索引的介绍

索引的建立可以大大提高MySQL的检索速度。索引就类似汉语字典中的目录，可以帮助我们快速查询某个字的位置。

优缺点

• 优点：索引可以大大减小服务器需要扫描的数据量，从而大大加快数据的检索速度。
• 缺点：索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间。
• 缺点：过多的索引会大大提高了查询速度，但同时却会降低表的更新速度（增，删，改）。因为MySQL不仅要保存数据，还要保存一下索引文件。

应该被作为索引的列

• 为WHERE子句中的常用的列上面创建索引。若常用列有许多重复值则不必创建索引。
• 为排序（order by）的列上创建索引，加快排序查询时间。

不应该被作为索引的列

• 避免对经常更新的表进行过多的索引
• 在查询条件中很少使用的列不应该创建索引。
• 若表中某个列包含许多重复值(例如性别)，为它建立索引就没有太大的实际效果。
• 数据量小的表最好不要使用索引。
• 参与计算的列字段不适合建索引

索引什么情况下会失效？

• like %aaa% 不会使用索引而like aaa% 可以使用索引。
• 不在索引列上做任何操作，例如计算、函数、类型转换，这将导致索引失效。
• 少用or，用它来连接时会索引失效。
• MySQL在使用不等于（!=或者<>）的时候，索引会失效导致全表扫描。

2.mysql索引的分类

mysql中的索引可以根据不同的方面进行分类。

• 按字段个数分类：单列索引、组合索引。
• 按字段特性分类：普通索引、唯一索引、主键索引、全文索引，空间索引。
• 按物理存储分类：聚集索引、非聚集索引。
• 按数据结构分类：B+tree索引、Hash索引、Full-text索引。

1 按字段个数分类

• 单列索引：一个索引只包含一个字段。
• 组合索引：一个索引包含两个或两个以上的字段。

组合索引的最左前缀原则

• 组合索引中字段的先后顺序会与where子句中的字段先后顺序进行匹配，匹配上的where子句就能使用索引。
• 按从左到右的顺序依次进行匹配。直到遇到范围查询（>,<,between,like）就停止匹配。
• 例如组合索引index_name(a,b,c)，只会匹配上a、a,b、a,b,c 三种类型的查询。
• 注意若子句中abc三个字段都存在，无论什么顺序。where子句会将其优化为a,b,c查询。
• 注意若子句中的条件通过>,<,between,like连接。则不会触发索引

>例如：某个索引包含三个字段（姓名，年龄，性别）。即aaa_index(name,age,gender)

select * from table where name = '小明'
select * from table where name = '小明' and age = 12
select * from table where name = '小明' and age = 12 and gender = '男'
select * from table where name = '小明' and gender = '男'

只有上面这三个语句能够使用到索引aaa_index。其余的查询语句无法使用到索引aaa_index。
第四个语句只能使用到name字段索引，不能使用到gender字段索引。

>原因解释：

组合索引index_name(a,b,c)，只会走a、a,b、a,b,c 三种类型的查询。a,c顺序只走a字段索引，不会走c字段索引。

2 按字段特性分类

普通索引（NORMAL）：最基本的索引，没有任何限制。

唯一索引（UNIQUE）：为某个字段创建唯一约束时，会自动创建唯一索引。

• 为某字段添加唯一索引后，索引列的值必须唯一，但允许有空值。出现多个空值不会发生重复冲突。
• 若是唯一组合索引，则该索引的列值的组合必须唯一。

主键索引：每张表只能有一个主键索引

• 特殊的唯一索引。不允许有空值。
• 若表中只有一个主键，mysql会自动为主键建立主键索引。
• 若表中主键有多个。则mysql会为多个主键建立主键组合索引。

全文索引（FULLTEXT）：用于在定义全文索引的列上支持值的全文查找，允许插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的大字段列上创建。

空间索引（SPATIAL）：用于对空间数据类型的字段建立的索引，空间索引只能在存储引擎为MyISAM的表中创建。

3 按物理存储分类

聚集索引

• 将数据与索引放到一块存储，索引结构的叶子节点就保存了行数据，必须有且只有一个。
• 表记录的排列顺序和索引的排列顺序一致。

非聚集索引：

• 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个。
• 表记录的排列顺序和索引的排列顺序不一致。

注意
1.默认主键索引就是聚集索引；
2.如果不存在主键，将使用第一个唯一索引作为聚集索引；
3.上述都没有，则innodb会自动生成一个rowid作为隐藏的聚集索引。

4 按数据结构分类

由于索引的数据结构类型和存储引擎有关，每种存储引擎所支持的索引数据结构类型不一定完全相同。

下表是MySQL常见的存储引擎 InnoDB，MyISAM 和 Memory 分别支持的索引类型

存储引擎	InnoDB(默认)	MyISAM	Memory
B+tree索引	yes	yes	yes
Hash索引	no	no	yes
Full-text索引	yes	yes	no

B+tree索引

Hash索引

Full-text索引

3. 索引的创建

用命令行的方式创建，查询，删除索引

命令行索引的创建有三种方式：①：在创建表的时候创建索引 ② 在已存在的表上创建索引 ③ 通过修改表结构的方式创建索引

创建索引的语法
CREATE 索引类型 索引名 ON 表名(字段名,字段名,....);
ALTER TABLE 表名 ADD 索引类型 索引名(字段名,字段名,....);

//直接创建普通索引
CREATE INDEX 索引名 ON 表名(字段名,字段名,....);
//修改表结构的方式添加普通索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名,字段名,....);

//创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名,字段名,....);
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段名,字段名,....);

//主键索引不用主动创建，mysql默认为主键创建主键索引

//创建全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名(字段名,字段名,....);
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段名,字段名,....);

//创建空间索引
CREATE SPATIAL INDEX 索引名 ON 表名(字段名,字段名,....);
ALTER TABLE 表名 ADD SPATIAL INDEX 索引名(字段名,字段名,....);


//删除表的索引
DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名

//查询表中的索引
show index from 表名 [ from 数据库名 ];

用navicat等图形界面工具创建索引

百度

4. EXPLAIN语句

explain语句可以查看sql语句中索引的使用情况

例如： 给TTL字段创建一个索引aaa。
explain select * from pol_law_d where TTL = '111';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0pfKFDSN-1657268957568)(…/blog_img/20220708160945.png)]

概要描述：
id:标识符
select_type:表示查询的类型.(SIMPLE表示简单SELECT)
table:表名
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明