MySQL数据库学习日志(六):索引和视图
索引
腾讯云数据库负责人林晓斌说过:“我们面试 MySQL 同事时只考察两点,索引和锁”。
(一)索引概述
- MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
- 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
- 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
(二)优缺点
1. 优点
类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO(读和写)成本。
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
2. 缺点
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
(三)分类
1. 按照底层实现的方式
主要有Hash索引和B+索引。(两种查找方式数据结构均有介绍)
Hash索引 O(1)
B+索引
2. 按照功能划分
主要介绍普通索引、唯一索引和组合索引(多列索引)。
普通索引
(1)特点
普通索引使用没有限制,允许在所有数据类型上创建,可以有重复值和空值。
一个索引只包含单个列,一个表可以有多个单列索引。
(2)创建普通索引
1)建表时创建索引
语法
INDEX 索引名 (列名)
示例
create table student(
id int primary key,
name varchar(20),
age int,
index name_indx(name)
)
测试是否创建成功
show index from student
2)修改表时创建索引
语法
ALTER TABLE 表名 ADD INDEX 索引名(列名)
示例
create table student2(
id int primary key,
name varchar(20),
age int
)
ALTER TABLE student2 ADD INDEX name_indx(name)
测试是否创建成功
show index from student2
3)直接创建
语法
CREATE INDEX 索引名 ON 表名(列名)
示例
create table student3(
id int primary key,
name varchar(20),
age int
)
CREATE INDEX name_indx ON student3(name)
测试是否创建成功
show index from student3
唯一索引
(1)特点
与普通索引类似,不同的