10.索引
MySQL中的索引
索引是什么
索引是对数据库表中的一列或者多列值进行排下序的一种结构。
使用索引可以快速访问数据库表中的特定信息。
索引是一种特殊的文件,它们包含数据表中所有记录的位置信息。
更通俗的说,数据库索引好比是一本书前的目录,能加快数据查询的速度。
MySQL索引的建立对MySQL高效运行是很重要的,索引可以大大提高MySQL的检索速度。
索引的优缺点
优点
- 创建唯一索引,可以保证数据库标的每一行数据的唯一性
- 可以加快数据检索的速度
- 可以加快表之间的连接
- 可以减少查询中分组和排序的时间
缺点
- 创建和维护索引浪费时间,数据量越多需要的时间越长
- 索引占用物理空间,数据量越大,占用的物理空间越大
- 降低增删改的效率,因为每次增删改都需要动态维护
什么时候需要使用索引
- 频繁作为查询条件的字段应该创建索引
- 查询中排序的字段应创建索引
- 需要统计或者分组的字段
什么时候不需要使用索引
- 频繁更新的字段
- where条件里用不到的字段
- 数据量少
- 经常使用“增删改”的字段
- 数据重复且分布平均的字段,例如性别字段
MySQL中的索引类型
索引的类型
- 普通索引:最基本的索引,没有任何限制
- 唯一索引:索引列值必须唯一,允许空值
- 主键索引:特殊的索引,唯一的标识一条记录,不为空。一般用PRIMARY KEY约束,一般在用主键约束时,就添加了主键索引
- 联合索引:在多个字段建立索引,能加速查询速度
普通索引
是最基本的索引。
操作:
- 查询索引:
SHOW INDEX FROM table_name;
- 直接创建索引:
CREATE INDEX index_name ON table_name(column_name(length));
- 修改表时添加索引:
ALTER TABLE table_name ADD INDEX index_name(column_name(length));
- 创建表时添加索引:
CREATE TABLE table_name(last_name VARCHAR(5) INDEX index_name(length));
- 删除索引:
DROP INDEX index_name ON table_name;
唯一索引
创建唯一约束时会创建唯一索引。
唯一索引的索引列的值必须唯一,但允许有空值。
操作:
- 创建:
CREATE UNIQUE INDEX index_name ON table_name(column_name(length));
- 修改表时添加:
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name(length));
- 创建表时指定:
CREATE TABLE table_name(last_name VARCHAR(5) UNQIUE INDEX index_name(length));
主键索引
主键索引时特殊的唯一索引,一张表只能有一个主键索引。
主键索引的索引列的值必须唯一,且不允许有空值。
操作:
- 创建:
ALTER TABLE table_name ADD PRIMARY KEY(column_name);
- 创建表时指定:
CREATE TABLE table_name(stu_id int PRIMARY KEY NOT NULL);
组合索引
组合索引是指多个字段创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用(最左前缀原则)。
最左前缀原则:就是最左优先。
例如:
使用了表中的name,address,salary创建组合索引,那么想要组合索引生效,只能使用
name,address,salary 或者 name,address 或者name
操作:
- 添加组合索引:
ALTER TABLE table_name ADD INDEX index_name(column_name1(length),column_name2(length));
- 创建表时指定:
CREATE TABLE table_name()
使用索引
针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用索引。
# 创建表和索引,插入数据
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
create index idx_emp_no on salaries(emp_no);
INSERT INTO salaries VALUES(10005,78228,'1989-09-12','1990-09-12');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
# 使用普通索引
EXPLAIN SELECT * FROM salaries WHERE emp_no = 10005;
# 使用强制索引
SELECT *
FROM salaries FORCE INDEX(idx_emp_no)
WHERE emp_no = 10005;