⑧ mysql索引及优化

1 索引的基础操作

1.1 索引类型

1.按照物理存储方式分为：聚簇索引与非聚簇索引；
2.按照功能分为：普通索引、主键索引、唯一索引、全文索引；
3.按照索引作用的字段个数分为：单一索引和联合索引；

1.2 不同功能索引概述

1.普通索引：可以作用在任何字段，不限制类型，为了提高查询效率的索引；
2.主键索引：特殊的唯一索引，在unique+not NULL，最多只能有一个；
3.唯一性索引：使用unique关键字定义的索引，索引值必须是唯一的，但允许有NULL值；
4.全文索引：一般不会用

1.3 创建索引

SHOW INDEX FROM table1 #查看表的索引结构

# 1.CREATE TABLE时创建索引
CREATE TABLE table1 (
	id INT PRIMARY KEY auto_increment,  #创建主键索引
	emp_num VARCHAR ( 20 ) UNIQUE,			#创建唯一性索引
	dep_id INT,	
-- 定义表时声明索引				
INDEX idx_dep_id( dep_id ) 						#创建联合索引
)

#2.表创建成功了,再增加索引
ALTER TABLE table1 ADD INDEX idx_emp_id(emp_id)		#创建单列普通索引
ALTER TABLE table1 ADD INDEX idx_emp_num_dep_id(emp_num,dep_id)	#创建多列联合索引
ALTER TABLE table1 ADD UNIQUE INDEX idx_emp_num(emp_num)		#创建唯一性索引

CREATE INDEX idx_emp_id ON table1(emp_id)		#通过CREATE INDEX .. ON 的当时创建

1.4 删除索引

#1.通过 ALTER TABLE table_name DROP INDEX index_name 方式删除
-- ALTER TABLE table1 DROP INDEX idx_amp_dep

#2.通过drop INDEX 删除索引
-- DROP INDEX index_name ON table_name

1.5 索引的设计原则

适合创建索引的情况：

1 字段的数值有唯一性的限制

​ 如果某个字段是唯一的，则直接可以创建唯一性索引；比如学号、ID等；

2 频繁作为WHERE查询条件的字段

​ 频繁使用某个字段进行where条件查询，则考虑建索引；

-- 1.数据量80W
SELECT * FROM student_info st WHERE st.course_id=759
-- 添加索引前：800ms
ALTER TABLE student_info ADD INDEX idx_course_id(course_id)
-- 添加索引后：520ms

3 经常进行GROUP BY 和 ORDER BY的列

1.如果分组和排序一起使用可以建立联合索引，先group by再进行order by;
2.如果待排序的列有很多个可以建立联合索引

-- 数据量80W
SELECT course_id FROM student_info st GROUP BY st.course_id
-- 添加索引前：800ms
ALTER TABLE student_info ADD INDEX idx_course_id(course_id)
-- 添加索引后：550ms

4 UPDATE/DELETE的WHERE条件列

UPDATE student_info st SET st.created_time = CURRENT_TIMESTAMP WHERE st.course_id = 7536
-- 添加索引前：1200s
-- 为UPDATE的where字段添加索引
ALTER TABLE student_info ADD INDEX idx_course_id(course_id)
-- 添加索引后：390ms