MySQL索引的创建及使用

目录

一、索引基础

二、适用索引的场景

三、创建索引

四、删除与查看索引

五、使用索引

       六、高效的索引策略

---

一、索引基础

• 索引介绍

数据库索引就像查汉语字典时的拼音、部首

索引是一张保存了主键与索引字段的有序表，并指向实体表的记录

索引分单列索引和组合索引，单列索引——一个索引包含一个列；组合索引——一个索引包含多个列

一个表的多个单列索引≠组合索引

• 优点

提高查询速度

• 缺点

会占用磁盘空间的索引文件

数据操作语句（增INSERT、删DELETE、改UPDATE）处理时间会更长，即降低表的更新速度

 

二、适用索引的场景

1. 数据量超过300的表应有索引

2. 经常与其它表进行连接的表，在连接字段上应建立索引

 

三、创建索引

语法：[UNIQUE|FULLTEXT] INDEX|KEY [index_Name](column_Name[(length)] [ASC|DESC]) [USING 索引方法]

INDEX和KEY作用是一样的

单列索引只有一个column_Name；组合索引有多个column_Name，逗号隔开，使用最左匹配原则

索引方法默认为B+TREE

最左匹配原则：cloumn_Name为(a,b,c)时，WHERE中可查询 (a) 或 (a,b) 或 (a,b,c) 或 (a,c)

1.普通索引

是最基本的索引，没有任何限制，索引值可出现多次

• 创建表时创建索引

语法：INDEX [index_Name] (column_Name)

CREATE TABLE student
(Sno char(5),
Sname varchar(20) not null,
INDEX st_name (Sname));

st_name为索引名字，若用户不指定，则MySQL会自动指定索引名字

第4行可替换为
KEY st_name (Sname)
INDEX (Sname)
KEY (Sname)

• 创建表后创建索引

语法：CREATE INDEX index_Name ON table_Name(column_Name);

• 修改表时创建索引

语法：ALTER TABLE table_Name ADD INDEX [index_Name] (column_Name);

 

2.唯一索引

唯一索引就是唯一键，唯一索引列的值必须唯一，可以为NULL，且可以有多个NULL

如果是组合索引，则列值的组合必须唯一

• 创建表时创建索引

语法：UNIQUE [index_Name] (column_Name)

• 创建表后创建索引

语法：CREATE UNIQUE INDEX [index_Name] ON table_Name(column_Name);

• 修改表时创建索引

语法：ALTER TABLE table_Name ADD UNIQUE [index_Name] (column_Name);

 

3.主键索引

主键索引不需要手动添加，创建主键时就自动创建了主键索引，所以一个表只能有一个主键索引

索引值必须唯一，且不能为NULL

语法：将UNIQUE INDEX改为PRIMARY KEY

 

4.全文索引

在搜索引擎中使用，MySQL不支持中文全文索引，通过sphinx来做中文全文索引

语法：将UNIQUE改为FULLTEXT

 

四、删除与查看索引

1. 删除索引

语法：DROP INDEX [index_Name] ON table_Name;

使用ALTER

语法：ALTER TABLE table_Name DROP INDEX index_Name;

          ALTER TABLE table_Name DROP PRIMARY KEY;

2.显示索引信息

SHOW INDEX  列出表中索引信息

\G  格式化输出信息

语法：SHOW INDEX FROM table_Name [\G];

3.查询是否用了索引

语法：EXPLAIN 查询语句 \G;

explain输出信息的含义

key：经过优化器评估最终使用的索引（查询未使用索引这条就是NULL）

Extra：额外的信息说明

 

/****Extra的四种可能****/

（1）using index：表示覆盖索引即可满足查询要求，无需再回表，因而效率较高

（2）using index;using where：表示首先存储引擎通过索引检索将检索结果返回（仍然不需要回表），然后在Server层再通过where语句对检索结果进行过滤。该过滤不需回表，因而效率也很高

（3）using where：表示MySQL将对存储引擎层提取的结果进行过滤，过滤条件字段无索引。using where本身和是否使用索引无关

（4）using index condition：是MySQL 5.6中引入的一种新特性，仅适用于二级索引，一般发生在查询字段无法被二级索引覆盖的场景，该场景下往往需要回表。通过ICP，可以减少存储引擎返回的行记录，从而减少了IO操作

想了解更多的可以看看https://blog.csdn.net/Saintyyu/article/details/99694649

 

/*use index*/
explain
select Sname
from student
where Sname='Li Zhang' and Sage=19 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ref
possible_keys: st_info
          key: st_info
      key_len: 82
          ref: const
         rows: 1
     filtered: 20.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

/*don't use index*/
explain
select *
from student
where Ssex=1 and Sage=19 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

 

五、使用索引

/*create table*/
create table student
(Sno char(5),
Sname varchar(20) not null,
Ssex char(2),
Sage tinyint,
Sdept varchar(10),
primary key (Sno));

/*insert data*/

/*create index*/
create index st_name on student(Sname,Ssex,Sage);

/*show index*/
show index from student \G;

/*use index*/
select *
from student
where Sname='he Liu' and Ssex=0;

/*drop index*/
drop index st_name on student;

 

六、高效的索引策略

1.哈希索引与前缀索引

有时需要索引很长的字符列，这会增加索引的存储空间并降低索引的效率，可以用哈希索引或前缀索引来使索引更高效

• 哈希索引

hash索引只是hash值顺序排列，跟表数据没有关系，无法应用于order by

hash索引只能用于比较查询=或IN，其他查询范围无效，本质还是因为不存储数据

• 前缀索引

前缀索引是选择字符列的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率

MySQL无法使用前缀索引做ORDER BY、GROUP BY和使用前缀索引做覆盖扫

2.选择合适的索引列顺序

3.聚集索引与非聚集索引

• 聚集索引

 

• 非聚集索引

与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，行数据只是通过保存在B-Tree中的索引对应的指针来指向行数据，如：上面在（Sname,Ssex,Sage）上建立的索引就是非聚集索引。

4.覆盖查询

如果一个索引中包含所有要查询的字段的值，就称之为覆盖索引，覆盖索引可以极大地提高访问性能

如：select Sname,Sage from student where Sname='he Liu';

要查询的字段（Sname,Sage）都包含在组合索引的索引列（Sname,Ssex,Sage）中

 

---

参考资料

1.MySQL 索引

2.mysql索引的新手入门详解

3.MySQL索引的创建与使用

4.MySql索引详细介绍及正确使用方法

5.MySQL中explain字段意义及extra详解

6.MySQL中Explain的Extra字段值Using index和Using index;Using where和Using where以及Using index condition的区别