MySql 如何实现的索引机制:
MySQL 中索引分三类: B+ 树索引、 Hash 索引、全文索引
InnoDB 索引 与 MyISAM 索引实现的区别是什么:
- MyISAM 索引文件和数据文件是分离的,使用B+树实现,主键索引和辅助索引实现一致,索引文件仅保护记录所在页的指针(物理位置),通过这些地址来读取页,进而读取被索引的行。
- InnoDB 的辅助索引 (非聚簇索引,非主键索引) data 域存储相应记录主键的值而不是地址
- InnoDB 的数据文件本身就是主索引文件
一个表中如果没有创建索引,那么还会创建 B+树吗:
如果有主键则会根据主键创建B+树,如果没有主键则以隐式rowId来创建B+树。
B+ 树实现原理:
假设有个表 index_demo, 表中有 2个 INT类型的列,1个CHAR(1)类型的列,c1 列为主键
CREATE TABLE index_demo(c1 INT, c2 INT, c3 CHAR(1), PRIMARY KEY(c1));
index_demo 表的简化的行格式示意图如下:
- record_type: 表示记录的类型,0是普通记录、2是最小记录、3是最大记录、1是B+树非叶子节点记录
- next_record: 表示下一条记录的相对位置,我们用箭头表明下一条记录
- 各个列的值: 这里只记录在 index_demo 表中的三个列,分别是 c1、c2 和 c3
- 其他信息:除了上述3种信息以外的所有信息,包含其他隐藏列的值以及记录的额外信息
InnoDB 的主键索引方案:
InnoDB 的非主键索引(c2)方案:
聚簇索引与非聚簇索引 B+ 树实现有什么区别:
聚簇索引是主键作为索引的值,有且仅能只有一个。除了聚簇索引之外创建的一切索引,都称为非聚簇索引
索引类型 | 特点 | 优点 | 缺点 | 限制 |
聚簇索引 |
|
|
|
|
非聚簇索引(c2)的不同之处:
- 页内的记录 是按照从 c2 列的大小顺序排成一个单向链表
- 页与页之间 也是根据页中记录的 c2 列的大小顺序排成一个双向链表
- 非叶子节点存储的是记录的 c2列 + 页号
- 叶子节点存储的并不是完整的用户记录,而只是 c2列 + 主键 这两列的值
B+ 树中可以存放多少条索引记录:
- 真实环境中一个页存放的记录数据是非常大的(默认 16KB),假设指针与键值忽略不计(或看作 10个字节),数据占 1KB 的空间
- 如果 B+ 树只有1层,也就是1个用于存放用户记录的节点,最多能存放16条记录
- 如果B+ 树有2层,最多能存放 1600 * 16 = 25600 条记录
- 如果B+ 树有3层,最多能存放 1600 * 1600 * 16 = 40960000 条记录
- 如果存储千万级别的数据,只需要三层就够了
- B+ 树的非叶子节点不存储用户记录,只存储目录记录,相对于 B 树每个节点可以存储更多的记录,树的高度会更矮胖, I/O 次数也会更少
自适应哈希索引:
是 InnoDB 引擎的一个特殊功能,当它注意到某些索引值被使用的非常频繁时,会在内存种基于 BTree 所有之上再创建一个哈希索引,这就让BTree索引也具有哈希索引的一些优点。这一过程完全是内部行为,用户无法控制或配置。
SHOW ENGINE INNODB STATUS \G;
自增主键和字符串主键的区别:
- 自增主键能够维持底层数据顺序写入
- 读取可以由 B+ 树的二分查找定位
- 支持返回查找,范围数据自带顺序
自增ID,删除添加和删除重启后添加区别:
- 删除添加:会延续删除之前最大的ID 开始递增
- 删除重启后: 会从最大的 id 开始递增
索引的优缺点:
索引类型 | 优点 | |
聚簇(主键)索引 |
| 空间上代价: 每建立一个索引都要为它建立一颗 B+ 树,每一颗 B+树的每一个节点都是一个数据页,一个页默认会占用 16KB 的存储空间,一颗很大的 B+ 树由许多数据页组成,那就是很大的一片存储空间 时间上的代价: 每次对表中数据进行 增、删、改 操作时,都需要去修改各个 B+ 树索引。并且可能对节点和记录排序造成破坏,所以存储引擎需要额外的时间进行一些记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序 |
非聚簇索引 |
|
大段文本内容,如果创建(优化)索引:
- 大段内容进行分页, 再访问第二页内容时,进行关联查询
- 分表存储然后创建索引(前面精确匹配,后面模糊匹配)
- 使用 ES 为大文本创建索引,返回ID,然后再做数据库定位
CRUD 时聚簇索引与非聚簇索引的区别:
- 聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多,因为插入要保证主键不重复(提前检查,非聚簇索引设置唯一同样效果)
- 聚簇索引范围,排序查找效率搞,因为是有序的
- 非聚簇索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据
非聚簇索引为什么不存数据地址而存储主键:
因为聚簇索引有时会引发分页操作、重排操作数据有可能会移动
回表操作和覆盖索引:
比如 字段有 id age name sex, 以 age 建立索引
执行 select * from user where age > 20; 非聚簇索引需要执行找到大于 20 的主键ID,然后回表查询具体数据
select age from user where age > 20; 想要的数据在B+树中,则不需要回表操作,并且查询效率很高(尽量不查询非必要字段),即覆盖索引。
为什么要回表查询:
- 为了控制非聚簇索引的大小
- 减少由于数据修改,造成的 I/O 次数
联合索引、组合索引、复合索引:
create index idx_c2_c3 on user (c2, c3)
为c2 和 c3 列创建联合索引,先拿 c2 进行排序,然后c3 进行排序。
全职匹配:where 条件要全职匹配索引创建的顺序,不可颠倒
最左前缀:可以拿 c2 (第一个索引) 进行查询
唯一索引:
- 随表一起创建
CREATE TABLE customer(
id INT UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id), -- 主键索引:列设定为主键后会自动建立索引,唯一且不能为空
UNIQUE INDEX uk_no (customer_no), -- 唯一索引: 索引列值必须唯一,允许有NULL值,且NULL可能会出现多次。
KEY index_name(customer_name), -- 普通索引:既不是主键,列值也不需要唯一,单纯的为了提高查询速度而创建
KEY index_no_name(customer_no, customer_name) -- 复合索引: 即一个索引包含了多个列
);
- 单独创建索引
CREATE TABLE customer(
id INT UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
);
ALTER TABLE customer ADD PRIMARY KEY customer(id); -- 主键索引
CREATE UNIQUE INDEX uk_no ON customer(customer_no); -- 唯一索引
CREATE INDEX idx_name ON customer(customer_name); -- 普通索引
CREATE INDEX idx_no_name ON customer(customer_no, customer_name); -- 复合索引
唯一索引是否影响性能:
- 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
- 不要以为索引影响力额 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
适合创建索引:
- 频繁作为where 条件语句查询字段
- 关联字段需要建立索引
- 排序字段可以建立索引
- 分组字段可以建立索引(因为分组前提是排序)
- 统计字段可以建立索引(count(), max())
不适合创建索引:
- 频繁更新的字段不适合建立索引
- where, 分组,排序中用不到的字段不必要建立索引
- 可以确定表数据非常少不需要建立索引
- 参与 mysql 函数计算的列不适合建立做引
索引下推:
是否开启索引下推 | 效果 |
未开启索引下推 |
|
开启索引下推 |
|
哪些情况会导致索引失效:
- 计算、函数导致索引失效
-- 显示查询分析
EXPLAIN SELECT * FROM emp WHERE emp.name LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; -- 索引失效
- LIKE 以%, _ 开头导致失效
SELECT * FROM emp WHERE NAME LIKE '%ab%'; -- 索引失效
- 不等于(!= 或 <>) 索引失效
SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'; -- 索引失效
- IS NOT NULL 失效 可能导致索引失效(NULL 的值多的时候,可能会走索引 type = range)
SELECT * FROM emp WHERE emp.name IS NOT NULL; -- 索引失效
- 类型转换导致索引失效
SELECT * FROM emp WHERE name = 123;
- 符合索引最左列没有使用到
SELECT * FROM emp WHERE name = "abc"; -- age, name 为组合索引
为什么 LIKE 以 % 开头索引会失效:
有字段 id, name, age。 name 创建索引
seLect * from user where name like '%名'; -- type = all 索引失效
select name from user where name like '%名'; -- type = index, 覆盖索引
没有高效使用索引是因为字符串索引会逐个转换accii码, 生成 B+ 树时按首个字符串顺序排序,类似复合索引未用左列字段失效一样,跳过开头部分也就无法使用生成的 B+ 树了。
如何查看一个表的索引:
show index from t_emp;
explain select * from t_emp where id = 1; -- 显示可能会用到的索引以及最终使用的索引
能否查看索引选择逻辑,使用 optimizer_trace:
set session optimizer_trace = "enabled=on", end_markers_in_json=on; -- 转换 json 格式输出
select * from t_emp where deptid = 1; -- 执行SQL
SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 优化器选择, cost 会计算每个索引所花费的情况
set session optimizer_trace = "enabled=off";
多个索引优先级是如何匹配的:
- 主键(唯一索引)匹配
- 全值匹配(单值匹配)
- 最左前缀匹配
- 范围匹配
- 索引扫描
- 全表扫描
索引使用一般性建议:
- 对于单件索引,尽量选择过滤性更好的索引(例如:手机号、邮件、身份证)
- 在选择组合索引的时候,过滤性好的字段在索引字段顺序中,位置越靠前越好
- 选择组合索引时,尽量包含where 中更多字段的索引
- 组合索引出现范围查询时,尽量把这个字段放在索引次序的最后面
- 尽量避免造成索引失效的情况
使用 Order By 时能否通过索引排序:
没有过滤条件不走索引
通过索引排序内部流程:
- sort_buffer 可提供排序的内存缓冲区大小
- max_length_for_sort_data 单行所有字段总和限制, 超过这个大小启动双路排序
- 通过索引过滤筛选条件,将有价值的数据进行排序
- 判断索引内容是否覆盖 select 字段:
- 如果覆盖索引, select 的字段和排序都在索引上,那么就在内存中进行排序,排序后输出结果
- 如果索引没有覆盖查询字段,接下来计算 select 的字段是否超过max_length_for_sort_data 限制,如果超过,启动双路排序,否则使用单路
双路排序和单路排序:
- 单路排序:一次取出所有字段进行排序,内存不够用的时候会使用排序
- 双路排序:取出排序字段进行排序,排序完成后再次回表查询所需要的其他字段
group by 分组和 order by 在索引使用上的区别:
group by 使用索引的原则几乎跟 order by 一致,唯一区别:
- group by 先排序再分组,遵照索引建的最佳左前缀法则
- group by 没有过滤条件,也可以用上索引。 order by 必须有过滤条件才能使用上索引