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;
