mysql index 理解 By backtrack

mysql 官方手册

• https://dev.mysql.com/doc/refman/5.7/en
• http://mysql.taobao.org/monthly/2019/06/

一、关键字

1. 聚簇索引 （ClusteredIndex）
2. 普通索引 （SecondIndex）
3. 回表查询
4. 索引覆盖
5. 分页查询

二、数据构建

create table user(
id int(10) auto_increment,
name varchar(30),
age tinyint(4),
primary key (id),
index idx_age (age)
)engine=innodb charset=utf8mb4;

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

三、索引信息

聚簇索引 -> 主键 (id)
普通索引 -> 字段 （age）

四、索引验证

# not use index -> all
select * from user;
explain select * from user;

# use index -> idx_age
select id from user;
explain select id from user;

# use index index -> idx_age
select id,age from user;
explain select id,age from user;

# not use index -> second index => index => name
select id,age,name from user;
explain select id,age,name from user;
# page query -> not use index => Using filesort
select id,age,name from user order by age limit 100,2

五、索引处理SQL

删除索引 -> drop index idx_age on user;
创建索引 -> create index idx_age_name on user(`age`,`name`);

再次验证
		# use index index -> idx_age_name
		select id,age,name from user;
		explain select id,age,name from user;

		index idx_age_name

还原

删除索引 -> 	drop index idx_age_name on user;
创建索引 -> 	create index idx_age on user(`age`);

验证
		# not use index -> second index => index => name
		select id,age,name from user;
		explain select id,age,name from user;

explain 返回结果含义

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

msyql 数据库 表物理存储信息

1. 查看存储路径

   show global variables like "%datadir%";
   -> /usr/local/mysql/data
   	- ib_logfile0
   	- ib_logfile1
   	-> databaseName
   		- db.opt  
   		- user.frm  
   		- user.ibd 
   		- ibdata1

   db.opt
   ->	default-character-set=utf8
   	default-collation=utf8_general_ci

六、遗留问题

1. mysql 物理存储文件的格式信息
2. B+ 树实现原理 （https://zhuanlan.zhihu.com/p/27700617）
3. explain sql 语句后 key_len 值的计算原理 ->已解决 （https://www.cnblogs.com/xuanzhi201111/p/4554769.html）

七、参考

• https://zhuanlan.zhihu.com/p/109303259