【MySQL学习笔记】（十）使用索引的操作

数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速地查找所需信息，
而无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找
到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引
是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针
清单。索引的作用如下。
(1)通过创建唯一索引，可以保证数据记录的唯一 性。
(2)可以大大加快数据的检索速度。
(3)可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
(4)在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组
和排序的时间。
(5)使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。
索引分为聚集索引和非聚集索引。聚集索引是指索引表中索引项的顺序与表中记录的物理
顺序一致的索引。

注意：MySQL中不同的存储引擎使用不用的索引类型，如使用B+tree，Hash和Full-text等进行高效的查询，本篇文章只关心其语法上的操作，不过多讨论内部实现细节。

创建索引

创建主键索引

• 第一种方式

-- 在创建表的时候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar(30));
1
2
3

• 第二种方式

-- 在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30), primary key(id));
1
2
3

• 第三种方式

create table user3(id int, name varchar(30));

-- 创建表以后再添加主键

alter table user3 add primary key(id);
1
2
3
4
5

查询一下是否存在索引

mysql> show index from user1\G;
*************************** 1. row ***************************
        Table: user1 //表名
   Non_unique: 0 //唯一索引
     Key_name: PRIMARY // 主键索引
 Seq_in_index: 1
  Column_name: id //索引在哪列
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE //这里是以二叉树形式的索引，具体内部形式是B+树，这里不做过多介绍
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

主键索引的特点：

• 一个表中，最多有一个主键索引，当然可以使用复合主键
• 主键索引的效率高（主键不可重复）
• 创建主键索引的列，它的值不能为null，且不能重复
• 主键索引的列基本上是int

创建唯一键索引

• 第一种方式
  – 在表定义时，在某列后直接指定unique唯一属性。

create table user4(id int, name varchar(30) unique);
1

• 第二种方式

-- 创建表时，在表的后面指定某列或某几列为unique

create table user5(id int, name varchar(30), unique(name));
1
2
3

• 第三种方式

--创建一张表后再添加索引
alter table user4 add unique(id);
1
2

唯一索引的特点：

• 一个表中，可以有多个唯一索引
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定not null，等价于主键索引

普通索引的创建

• 第一种方式

create table user8(id int primary key,
 name varchar(20),
 email varchar(30),
 index(name) --在表的定义最后，指定某列为索引
);
1
2
3
4
5

• 第二种方式

create table user9(id int primary key, name varchar(20), email 
varchar(30));

alter table user9 add index(name); --创建完表以后指定某列为普通索引
1
2
3
4

• 第三种方式

create table user10(id int primary key, name varchar(20), email 

varchar(30));

-- 创建一个索引名为 idx_name 的索引 

create index idx_name on user10(name);
1
2
3
4
5
6
7

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

查询索引

• 第一种方法： show keys from 表名
• 第二种方法: show index from 表名;
• 第三种方法（信息比较简略的情况）： desc 表名；

删除索引

• 第一种方法-删除主键索引： alter table 表名 drop primary key;

• 第二种方法-其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys
  from 表名中的 Key_name 字段

mysql> alter table user10 drop index idx_name;

• 第三种方法方法： drop index 索引名 on 表名

索引创建原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合作创建索引
• 不会出现在where子句中的字段不该创建索引