MySQL-数据库索引

13.1.1 什么是索引

• 在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效。
• 如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。拿汉语字典的目录页（索引）打比方，可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
• 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

13.1.2 常见索引分类

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

注意

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

​

11.1.3 主键索引 (PRIMARY KEY)

基本特点

• 主键是一种唯一性索引,每个表只能有一个主键,用于标识数据表中的某一条记录。
• 一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。

1) 创建db_mysql2 数据库

create database db_mysql2 character set utf8;

2) 创建 user表

-- 创建数据表
create table USER(
 cid int,
 dname varchar(20),
 age int
);

3)基本语法格式

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

CREATE TABLE 表名( 
    -- 添加主键 (主键是唯一性索引,不能为null,不能重复,)
    字段名 类型 PRIMARY KEY, 
); 

• 修改表结构 添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 )

4) 为user表添加主键索引

-- 主键索引
alter table user add primary key(cid);

结构信息

​

11.1.4 唯一索引(UNIQUE)

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

注意事项

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

1) 语法格式

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

CREATE TABLE 表名( 
    列名 类型(长度), 
      -- 添加唯一索引
    UNIQUE [索引名称] (列名)
);

• 在已有的表上创建索引

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

修改表结构添加索引

ALTER TABLE 表名 ADD UNIQUE ( 列名 )

2) 为 age字段添加唯一索引

create unique index ind_age on USER(age);

​

3) 向表中插入数据

-- 插入数据
insert into user values(1, 'curry', 10);

11.1.5 普通索引 (INDEX)

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

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

1) 语法格式

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

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

• 修改表结构添加索引

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

2) 给 dname字段添加索引

# 给dname字段添加索引
alter table user add index dname_indx(dname);

​

11.1.6 删除索引

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

1)语法格式

ALTER  TABLE  table_name  DROP  INDEX index_name;

2) 删除 user 表中名为 dname_indx 的普通索引。

alter table user drop index dname_indx;

11.1.7 索引的优缺点总结

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

• 索引的优点

  1. 大大的提高查询速度
  2. 可以显著的减少查询中分组和排序的时间。

• 索引的缺点

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

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