MySQL 索引

1 什么是索引

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令 MySQL 的查询和

运行更加高效。

如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是

一个人力三轮车。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目

录（索引）快速查找到需要的字

2 常见索引分类

索引名称	说明
主键索引（primary key）	主键是一种唯一性索引,每个表只能有一个主键, 用于标识数据表中的每一条记录
唯一索引（unique）	唯一索引指的是索引列的所有`值都只能出现一次,` 必须唯一.
普通索引（index）	最常见的索引,作用就是加快对数据的`访问速度`

MySql 将一个表的索引都保存在同一个索引文件中, 如果对中数据进行增删改操作,MySql 都会自动的更

新索引.

2.1 主键索引 (PRIMARY KEY)

特点: 主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录。

一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。

创建一个只有字段的数据库表 demo01

CREATE TABLE demo01(

	did INT,

	dname VARCHAR(20),

	hobby VARCHAR(30)

);

创建主键索引

语法格式

创建表的时候直接添加主键索引 (最常用)

CREATE TABLE 表名(

	-- 添加主键 (主键是唯一性索引,不能为null,不能重复,)

	字段名 类型 PRIMARY KEY,

);

修改表结构添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

为 demo1 表添加主键索引

ALTER TABLE demo01 ADD PRIMARY KEY ( did )

2.2 唯一索引(UNIQUE)

特点: 索引列的所有值都只能出现一次, 必须唯一.

唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

创建唯一索引

语法格式

创建表的时候直接添加主键索引

CREATE TABLE 表名(

	列名 类型(长度),

	-- 添加唯一索引

	UNIQUE [索引名称] (列名)

);

使用 create 语句创建: 在已有的表上创建索引

create unique index 索引名 on 表名(列名(长度))

修改表结构添加索引

ALTER TABLE 表名 ADD UNIQUE ( 列名 )

为 hobby 字段添加唯一索引

create unique index ind_hobby on demo01(hobby)

测试

向表中插入数据

INSERT INTO demo01 VALUES(1,'张三','DBJ');

# 报错Duplicate entry 'DBJ' for key 'hobby'

# 唯一索引保证了数据的唯一性,索引的效率也提升了

INSERT INTO demo01 VALUES(2,'李四','DBJ');

3 普通索引 (INDEX)

普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。

因此，应该只为那些最经常出现在查询条件（WHERE column=）或排序条件（ORDERBY column）中的数据列创建

索引。

创建普通索引

语法格式

使用 create index 语句创建: 在已有的表上创建索引

create index 索引名 on 表名(列名[长度])

修改表结构添加索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名)

给 dname 字段添加索引

# 给dname字段添加索引

alter table demo01 add index dname_indx(dname);

4 删除索引

由于索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应该及时删除不再使用的索

引

语法格式

ALTER TABLE table_name DROP INDEX index_name;

删除 demo01 表中名为 dname_indx 的普通索引

ALTER TABLE demo01 DROP INDEX dname_indx;

3 索引性能测试

1 导入数据表

下载测试 sql

找到软件文件夹下的测试索引.sql 文件, 点击执行

运行出现以下问题

Err 1153 - Got a packet bigger than 'max_allowed_packet' bytes

由于数据量较大，mysql 会对单表数据量较大的 SQL 做限制，10w 条数据的字符串超出了 max_allowed_packet

的允许范围。

解决办法

找到：max_allowed_packet 可以ctrl+f 搜索修改默认值为419430400 大概是400M

查看我们是否修改成功

下面步入正题

查询 test_index 表中的总记录数 ·表中有 500万条数据

SELECT COUNT(*) FROM test_index;

测试

在没有添加索引的情况下, 使用 dname 字段进行查询

#未添加索引,进行分组查询

SELECT * FROM test_index GROUP BY dname;

为dname字段添加普通索引

注意: 一般我们都是在创建表的时候就确定需要添加索引的字段

#添加索引

ALTER TABLE test_index ADD INDEX dname_indx(dname);

4 索引的优缺点总结

添加索引首先应考虑在 where 及 order by 涉及的列上建立索引。

索引的优点
大大的提高查询速度

可以显著的减少查询中分组和排序的时间。

索引的缺点
创建索引和维护索引需要时间，而且数据量越大时间越长

当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护

速度

巴特西

简单使用 MySQL 索引

MySQL 索引

1 什么是索引

2 常见索引分类

2.1 主键索引 (PRIMARY KEY)

创建主键索引

2.2 唯一索引(UNIQUE)

创建唯一索引

测试

3 普通索引 (INDEX)

创建普通索引

4 删除索引

3 索引性能测试

1 导入数据表

4 索引的优缺点总结

最新文章

热门文章