⑧ 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
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值