共计 3158 个字符，预计需要花费 8 分钟才能阅读完成。
这篇文章主要介绍“MySQL 索引及优化的知识点有哪些”的相关知识，丸趣 TV 小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL 索引及优化的知识点有哪些”文章能帮助大家解决问题。
索引是什么？
索引是帮助 MySQL 进行高效查询的一种数据结构。好比一本书的目录，能加快查询的速度
索引的结构？
索引可以有 B -Tree 索引，Hash 索引。索引是在存储引擎中实现的
InnoDB / MyISAM 仅支持 B-Tree 索引
Memory/Heap 支持 B -Tree 索引和 Hash 索引
B-Tree
B-Tree 是一种非常适合用于磁盘操作的数据结构。它是一棵多路平衡查找树。其高度一般在 2 -4，其非叶子节点，叶子节点，都会存储数据。其所有的叶子节点，都在同一层。下图是一颗 B -Tree
B+ Tree：B+ 树是在 B -Tree 基础上的一种优化。它和 B 树的主要区别在于：B+ 树的数据全部存储在叶子节点中，且叶子节点被一个链表串了起来。下图是一颗 B + 树
InnoDB 中一个页的大小为 16KB（一个页即 B + 树上的一个节点），若表的主键为 INT，大小为 4 字节，那一个节点也能够存储 4K 个键值，假设指针和键值都占相同大小，那么高度为 3 的 B + 树，第二层有 2048 个节点，第三层的叶子节点数为 2048*2048 = 4194304，一个节点为 16KB，则一共可容纳 67108864KB，即 65536MB，即 64G 的数据。
由于叶子节点是被一个链表串起来的，所以若 order by 索引列，则默认已经是排好序的，所以效率会很高。
MyISAM 索引
MyISAM 的索引和数据是分开存放的。在 MyISAM 的主键索引中，B+ 树叶子节点里，存的是记录的地址，故 MyISAM 通过索引查询，需要经过 2 次 IO
MyISAM 的辅助索引和主键索引一样，唯一的区别是，辅助索引中的 key 可以重复，而主键索引的 key 不能重复
InnoDB 索引
InnoDB 的数据和索引是存放在一起的，又称聚集索引。数据通过主键索引，存放在主键索引 B + 树的叶子节点上。
InnoDB 主键索引，数据已经包含在了叶子节点中，即索引和数据存放在一起，是为聚集索引。
InnoDB 的辅助索引，叶子节点中存的是主键值，而不是地址。走辅助索引，需要检索 2 次。
InnoDB 和 MyISAM 索引的区别：
InnoDB 使用聚集索引，其主键索引叶子节点中直接存储了数据，而其辅助索引中叶子节点存的是主键的值
MyISAM 使用非聚集索引，数据和索引不在同一个文件中，其主键索引中叶子节点上存的是该行记录所在的地址，其辅助索引中叶子节点上存的也是记录所在的地址，只是辅助索引的 key 可以重复，而主键索引的 key 不能重复
问题：
InnoDB 为什么不要使用过长的字段做主键？
过长的主键，会使得辅助索引所占空间变得很大
为什么推荐 InnoDB 使用自增主键？
若使用自增主键，则每次插入新的记录，就会顺序的将新记录添加到当前索引节点的后续位置，一页写满了，才会进行开辟新的一页，这样使得索引结构很紧凑，且每次插入时不需要移动已有数据，非常高效。而如果不使用自增主键，则每次插入新记录时，都要选择一个插入位置，并且可能需要移动数据，使得效率不高，且索引结构不紧凑
为什么要用 B + 树，不用 B 树
索引存在哪儿？
索引本身也比较大，一般会存储在磁盘中，索引和数据可能是分开存放的（MyISAM 的非聚集索引），也可能是一起存放的（InnoDB 的聚集索引）
索引的优缺点?
优点
降低 IO 成本，提高数据查询效率
降低排序成本（被索引的列会自动排序，使用 order by 效率会提高很多）
缺点
索引会额外占据存储空间
索引会降低更新表数据的效率。进行增删改操作时，不仅要保存数据，还要更新对应的索引
索引的分类
单列索引
主键索引
唯一索引
普通索引
组合索引
索引使用
建立索引
CREATE INDEX index_name ON table_name(col_name);
-- 或者
ALTER TABLE table_name ADD INDEX index_name(col_name)删除索引
DROP INDEX index_name ON table_name;需要建立索引的场景
频繁作为查询条件的列，需建索引
多表关联中，关联字段需建索引
查询中排序的字段，需建索引
不适用索引的场景
写多读少的表，不适合建索引
频繁更新的字段，不适合建索引
explain 执行计划
现有一张 user 表，其索引如下所示
其中 name,age,address 三个字段作为一个组合索引
可以使用 explain 对某个 SQL 语句进行性能分析
explain select * from user where name = ampossible_keys
可能用到的索引
key
实际用到的索引
key_len
用于查询的索引的长度
ref
如果是等值查询，这里会会是 const
rows
预计需要扫描的行数（不是精确值）
extra
额外信息，如
using where
表示存储引擎返回的结果，还需要在 SQL Layer 层过滤
using index
表示不需要回表查询，一般在使用了覆盖索引时会是这个值。覆盖索引指的是，select 中的列，全是索引列。不需要回表查询指的是，直接走辅助索引，就能拿到索引列的值，不需要再去主键索引上取记录了
using index condition
MySQL 5.6.x 之后支持 ICP 特性（Index Condition Pushdown），可以把检查条件下推到存储引擎层，不符合条件的记录，直接不读取，而不是像原来一样，先读取出来，再在 SQL Layer 层过滤，这样减少了存储引擎层扫描的行数
using filesort
排序时无法用到索引
type
system : 表中只有 1 行数据，或空表
const : 使用唯一索引或主键索引，且用 where 等值查询，返回记录是 1 行，又叫唯一索引扫描
ref : 针对非唯一索引，使用等值 where 条件，或者最左前缀规则的查询。
下面是满足了最左前缀规则，即对 idx_name_age_add 来说，满足了最左前缀，第一个索引为 name
range：索引范围扫描，常见于，，between，in，like 等查询
注意 like 时，通配符 % 不能放在开头，否则会导致全表扫描
index：没有完全匹配上索引，但不用回表查询的
all: 全表扫描，然后再在 SQL Layer 层过滤符合要求的记录
索引使用规范（索引失效分析）
全值匹配
在索引列上使用等值查询
explain select * from user where name = y and age = 15;
2. 最左前缀
组合索引中，查询条件要从组合索引的最左列开始，如上述 example 中组合索引 idx_name_age_add，是建立在三个列 name，age，address 的，若跳过 name，直接用 age 查询，则会变为全表扫描
explain select * from user where age = 15;
3. 不要在索引列上做计算
4. 范围条件右侧的索引列会失效
看到第一个 SQL 语句，没有用上 addresss 索引
5. 尽量使用覆盖索引
explain select name,age from user where name = y and age = 1;可以避免回表查询
6. 索引字段不要使用不等（!= 或）, 不要判断 null（is null/ is not null）
会导致索引失效，转为全表扫描
7. 索引字段上使用 like 时，不要以 % 开头
8. 索引字段如果是字符串，记得加单引号
9. 索引字段不要用 or
例子总结：
关于“MySQL 索引及优化的知识点有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注丸趣 TV 行业资讯频道，丸趣 TV 小编每天都会为大家更新不同的知识点。
