共计 4732 个字符，预计需要花费 12 分钟才能阅读完成。
自动写代码机器人，免费开通
这篇文章给大家分享的是有关 mysql 可以靠索引，而我只能靠打工 …. 的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考。一起跟随丸趣 TV 小编过来看看吧。
一、 索引数据结构
面试的时候肯定会问这一个问题，mysql 为什么会选择 b + 树作为索引呢？而不选择其他索引，例如 b 树？hash？
下面说的磁盘 IO 是指数据从硬盘加载到内存中的操作
hash 索引的话，不支持范围查询，因为 hash 就是一个键对应一个值的，没办法范围查询
二叉树的话，它的特点就是左子树小于根节点小于右子树，如果根节点取值有问题的话，有可能会退化成链表，就是树不分叉了，树一直往左或者一直往右，这样就不能折半查找从而减少 IO 次数了，不支持范围查询，要是范围查询的话，每次都要从根部遍历，树也太高了，树越高，IO 操作越频繁，浪费资源
平衡二叉树的话，它就没有了二叉树的这种退化成链表的缺点，因为他左右子节点最多相差 1 层，可是他也不支持范围查找这一点和二叉树的问题一样
b 树的话，和二叉树比起来树是很矮胖，IO 操作减少了，是个多叉树，它每个节点都存了对应的行数据，可是如果这一行的数据的列不断的增加，那么这一页存储的节点就会变少，因为所占的空间不断的变大，树也会越来越高，增加 IO 操作次数，同时是也不支持范围查找。要是相同大小的空间可以存很多的节点数据的话就更好了，所以就有了下面的 b + 树
b+ 树 它非叶子节点只存索引的数据，不存整行数据，但是叶子节点是冗余的，冗余了非叶子节点，叶子节点还都用双向链表链接起来，这样有助于顺序查找，b+ 树和 b 树比起来，更加矮胖，磁盘 IO 次数更少
二、mysql 中索引类型 聚簇索引与非聚簇索引
我们可以简单的理解为
聚簇索引就是主键索引，非聚簇索引就是普通索引
本质的区别是
聚簇索引的叶子节点存储的是整行数据
innodb 是通过主键来实现聚簇索引的，如果没有主键的话，那么他就会选择一个唯一非空的索引来实现，如果再没有的话，他就会隐式生成一个主键来实现聚簇索引
非聚簇索引存储的是索引值和主键值
普通索引一张表中可以有多个普通索引，随便一个字段都可以建立的索引，我们平常建立的索引大部分都是普通索引
联合索引好几个字段联合起来建立的索引
唯一索引业务中唯一的字段适合建立唯一索引，一个表中可以有多个唯一索引
主键索引和唯一索引一样，主键索引也是唯一的，不同的就是，一个表只能有一个主键索引
三、关于索引的 sql
创建主键索引
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_cardDROP 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 复制代码 其中的属性
table: 表名
Non_unique: 能重复的话为 1，不能重复的话为 0，我们主键的那里是 0，而 name 那里是 1，因为 name 可以重复，而主键不能重复
Key_name: 索引名称
Seq_in_index: 索引中列的顺序
Column_name: 列名称
Collation: 列以什么方式存储的，A 升序，null 无序
Cardinality: 数目越大，则使用该索引的可能性越大
Sub_part: 如果列只是部分的编入索引，则被编入索引的字符数目，如果整列被编入索引，则为 null
Packed：关键字是否被压缩，null 表示没有被压缩
Null: 如果该列含有 null, 则为 yes, 如果没有 null, 则为 no
Index_type: 索引数据结构
Comment: 多种评注
四、回表查询
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 分析索引语句
我们用 explain 语句解析一下下面这条 sql 语句
EXPLAIN SELECT * from test WHERE name = xhJaver 复制代码 它的属性有
id: 执行的顺序
id 相同时，顺序从上到下执行 id 不同时，id 大的先执行
select_type: 查询的类型
primary: 最外层的查询被标记为 primarysimple: 简单查询，没有关联其他表，就一张表 subquery: 在 where 或者 select 中的子查询 derived: 衍生虚拟表 例如 from(子查询) t, 这个子查询的结果就被放在虚拟表 t 中
table: 关于哪张表的
partitions: 分区相关（还没搞懂呜呜呜）
type: 访问类型
性能由好至坏依次是
system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL 一般来说，好的 sql 查询至少达到 range 级别，最好能达到 ref
system: 表中只有一行数据
const: 常量查询 通常用于比较主键等于一个常量，用索引查询一次就查到了
eq_ref: 唯一性索引，每个索引对应一条数据，例如主键索引
ref: 非唯一索引，每个索引有可能对应多行数据，例如普通索引
range: 范围查询，用到了，，in,between 等查询
index：全表扫描，但是是遍历整棵索引树
all: 全表扫描，没有用到索引
possible_keys：查询的字段上有索引的话，就会显示出来，
key : 具体用到的索引，若用到了覆盖索引，则 possible_keys 为 null, 只会显示在 key 中
key_len: 索引中使用的字节数，最大可能长度，并非实际长度，key_len 是根据表定义计算而得的，不是通过表内检索出的
ref: 表示使用索引的是哪一个字段
rows: 大致估算出所需要读取的行数
filtered: 显示了通过条件过滤出的行数的百分比估计值。
Extra:
Using filesort： mysql 无法利用索引完成的排序被称为文件排序
Using temporary： 使用临时表存储了下中间结果，mysql 对查询结果排序时是使用了临时表，常见于 order by 和 group by
Using index：使用了覆盖索引，查询内容在索引内
如果出现了 Using where，表示对查询出来的数据进行了过滤如果没有出现 Using where，表示对查询出来的数据没有进行过滤
只有 Using where 查询内容不在索引内，且对查出来的数据进行了过滤
1. EXPLAIN SELECT (select student.id from student WHERE student.`name`= xhJaver) FROM teacher2. EXPLAIN SELECT * FROM teacher where teacher.id = (select student.id from student WHERE student.`name`= xhJaver)
复制代码 我们写几个 sql 语句实际分析下
1.SELECT 后面 
2.where 后面 
我们就拿后面这个图来实战分析一下, 挑几个重要的属性说一下
select_type：
我们最外层的查询是 from teacher 所以 table 为 teacher 的那个表的 select_type 就是 primary
select/where 后面的括号中的查询语句中的表是 student，所以 table 为 student 的那个表的 select_type 就是 subquery
table: 这条 sql 查询用到的表
type: 访问类型
第一行 const : teacher.id = 巴拉巴拉巴拉（这个是常数）主键和常数比较时，这个表最多有一个匹配数据，只读取一次
第二行 ref：代表用到了普通索引，就是这个索引 name 和 xhJaver 匹配, 可能匹配到很多相同的值
possible_key: 代表可能用到的索引，但是不一定会用到
key: 代表用到的索引，用到了 idx_name，PRIMARY 索引
ref: 这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const，字段名
extra：
using index: 一般是使用了覆盖索引，看我们这个 sql 语句，
select student.id from student WHERE student.`name`= xhJaver 复制代码 name 字段有索引，查询的是 id，b+ 树叶子节点存的数据就是 id，所以不需要回表查询了，用到了覆盖索引
八、索引失效原因
遇到范围查询（, ,like,beetwon），右边的索引列会失效
索引字段不能有函数操作或者不能是表达式的一部分
索引字段隐式类型转换
索引字段类型是 string，我们传进来个 int
使用时 or,is null ,is not null , !=, , like %xxx 索引会失效
但是用覆盖索引就可以解决 like 左模糊查询走不到索引的情况
如果只 select 索引字段，或者 select 索引字段和主键，也会走索引的。
感谢各位的阅读！关于 mysql 可以靠索引，而我只能靠打工 …. 就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到吧！
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合！
