面试的时候肯定会问这一个问题,mysql为什么会选择b+树作为索引呢?而不选择其他索引,例如b树?hash?
下面说的磁盘IO是指数据从硬盘加载到内存中的操作
我们可以简单的理解为 聚簇索引就是主键索引,非聚簇索引就是普通索引
本质的区别是
聚簇索引 的叶子节点存储的是整行数据
innodb是通过主键来实现聚簇索引的,如果没有主键的话,那么他就会选择一个唯一非空的索引来实现,如果再没有的话,他就会隐式生成一个主键来实现聚簇索引
非聚簇索引存储的是索引值和主键值
创建主键索引
ALTER TABLE test add PRIMARY KEY (id)
创建唯一索引
ALTER TABLE test add UNIQUE idx_id_card(id_card)
创建普通索引
ALTER TABLE test add INDEX idx_name(name)
创建联合索引
ALTER TABLE test add INDEX idx_age_name(age,name)
修改索引名称 :先删除再添加
删除索引 (两种方式)
ALTER TABLE test DROP INDEX idx_id_card
DROP INDEX idx_id_card on test
--删除主键索引
DROP PRIMARY key on test
ALTER TABLE test DROP PRIMARY key
查看表中索引
SHOW INDEX FROM test
分析索引
EXPLAIN SELECT * from test WHERE name = "xhJaver"
我们先给name字段添加一个索引,索引名字叫做idx_name
ALTER TABLE test add INDEX idx_name(name)
查看test表中的索引
SHOW INDEX FROM test
其中的属性
select * from test where name = "xhJaver"
假如说我们name字段建立了索引,然后当我们运行这一句sql语句的时候,因为建立的是普通索引,所以我们的b+树的叶子节点存储的数据是id,我们会找到name是xhJaver的这条记录的id,再根据这个id,去主键索引的那棵b+树去查询,查询到叶子节点时即查询出这条记录,可见这个过程中,我们从一棵树跑到了另一棵树继续查,这样就叫做“回表查询”,那有没有办法只查一棵树就可以查询出结果呢?
办法当然是有的啦,那就是覆盖索引,我们注意到,刚才这个sql语句时查询出来了所有元素,假如说我们这样写的话
select address from test where name = "xhJaver"
假如说我们建立的索引是(name,address)那么这个时候(name,address)这棵b+树的叶子节点存储的数据就包括address了,此时就不需要再根据name = "xhJaver"的id去第二棵树查了,这样就避免了回表查询
假如说现在我们写一个这样的sql语句
select * from test where name = "xhJaver" and age =23 and address="京东"
并且我们建立的索引是(name,address,age)这样是会用到(name,address,age)索引的,可是如果要这样写的话
select * from test where name = "xhJaver" and age >23 and address="京东"
这样只会用到(name,age)这两个索引,从左边开始匹配,如果要是遇到范围查询的话,则不继续往右匹配索引
我们用explain语句解析一下下面这条sql语句
EXPLAIN SELECT * from test WHERE name = "xhJaver"
它的属性有
id: 执行的顺序
select_type: 查询的类型
table: 关于哪张表的
partitions: 分区相关(还没搞懂呜呜呜)
type:访问类型
性能由好至坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说,好的sql查询至少达到range级别,最好能达到ref
possible_keys:查询的字段上有索引的话,就会显示出来,
key : 具体用到的索引,若用到了覆盖索引,则possible_keys为null,只会显示在key中
key_len:索引中使用的字节数,最大可能长度,并非实际长度,key_len是根据表定义计算而得的,不是通过表内检索出的
ref: 表示使用索引的是哪一个字段
rows:大致估算出所需要读取的行数
filtered:显示了通过条件过滤出的行数的百分比估计值。
Extra:
1. EXPLAIN SELECT (select student.id from student WHERE student.`name`="xhJaver") FROM teacher
2. EXPLAIN SELECT * FROM teacher where teacher.id = (select student.id from student WHERE student.`name`="xhJaver")
我们写几个sql语句实际分析下
1.sql1 SELECT后面有子查询
2.sql2 where后面有子查询
我们就拿后面这个图来实战分析一下,挑几个重要的属性说一下
select_type:
table: 这条sql查询用到的表
type: 访问类型
possible_key: 代表可能用到的索引,但是不一定会用到
key: 代表用到的索引, 用到了idx_name,PRIMARY索引
ref: 这一列显示了在key列记录的索引中,表查找值所用到的列或常量, 常见的有:const,字段名
extra:
select student.id from student WHERE student.`name`="xhJaver"
name字段有索引,查询的是id,b+树叶子节点存的数据就是id,所以不需要回表查询了,用到了覆盖索引
但是用覆盖索引就可以解决 like左模糊查询走不到索引的情况 如果只select索引字段,或者select索引字段和主键,也会走索引的。