[[数据库]] MySQL的索引 MADPEACH

Chapter6 MySQL的索引

---

一、索引种类

#索引：并不是在表中查，而是在文件中查，后映射到对应表中的位置，创建索引会占用硬盘位置；无索引：从前到后依次查找。
#索引种类（以何种格式存储）
1.hash索引：找单值快，找范围慢
	创建一个索引表，把列值转化为哈希值。哈希表中的存放顺序与原表中并不一致。（缺点，若针对语句查找范围id>3的行，用哈希索引查找并不会比原表快）
			name       哈希值       数据地址
			huge1       998          xxxx
			huge2       997          xxxx
			huge3       996          xxxx
	
2.B-tree索引：找范围快，找单值慢
	二叉查找树。常用，默认的方法。对于engine=innodb用的就是这种存储方式。

---

二、创建索引

1.主键索引    #特点：加速查找+不能重复+不能为空
	a.建表时创建
		详情见Chapter2
	b.后期创建
		alter table 表名 add primary key(列名)

2.普通索引    #特点：加速查找
	a.建表时创建
		create table xxx(
			index ixname(name)
		)
	b.后期创建
		create index 索引名称 on 表名(列名)
		drop index 索引名称 on 表名
	
3.唯一索引    #特点：加速查找+不能重复
	a.建表时创建
		create table xxx(
			unique index ixname(name)
		)	
	b.后期创建
		create unique index 索引名称 on 表名(列名)
	    drop unique index 索引名称 on 表名
	
	#上述3种索引均用在单列上，下面的联合索引则用在多列上
	
4.联合索引=组合索引
		a.联合主键索引
		
		b.联合唯一索引
			create unique index 索引名称 on 表名(列名,列名)
			drop unique index 索引名称 on 表名
			
		c.联合普通索引
			create index ix_name_email on userinfo(name,email)

#组合索引涉及到最左前缀匹配问题
	假如有一句话create index ix_name_email on userinfo(name,email)
	#满足左，左中，左中右均会走索引
	select * from userinfo where name='huge';
	select * from userinfo where name='huge' and email='sadds';
	#不使用最左列，则不会走索引
	select * from userinfo where email='[e-mail]';

---

三、索引使用方式及相关名词

#以下两个名词不是真是的索引种类，而是索引的某种使用方式

1.覆盖索引
	在索引文件中直接获取数据。
	select name from userinfo where name="  ".
	即在name列索引文件中查name，只在索引文件中查，不涉及回原表

2.索引合并
	把多个单列索引合并使用
	select * from userinfo where name='  ' and id=;

3.组合索引vs索引合并
	一般来说，效率：组合索引>索引合并
	但要看哪句SQL比较常用，再确定索引方案

	组合索引  -(name,email)
		select * from userinfo where name='huge' and email='asdf'; #走索引
		select * from userinfo where name='huge';  #不走索引

	索引合并  -name   -email
		select * from userinfo where name='huge';#走索引
		select * from userinfo where email='asdf';#走索引
		select * from userinfo where name='huge' and email='asdf'; #效率低于组合索引

四、命中索引

应为频繁查找的列创建索引，为了命中索引，要注意以下几点
1.避免使用like '%xx'
2.避免使用函数
3.类型不一致
4.order by
5.组合索引最左前缀

---

五、预估语句的执行时间

1.预估方式
	想要预估select * from userinfo where name='huge'这句话的执行时间，可以进行如下指令：    
 	explain select * from userinfo where name='huge'
 	#该操作可以看到该语句执行时的type，走的是all（全表扫描）还是const（走索引）
 	#一般来说，评价指标从好到坏（速度从慢到快）如下
 	all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

2.DBA的慢日志工作：目的-优化SQL

本文地址：https://blog.csdn.net/MadPeach/article/details/108950744