MySQL索引15问

MySQL索引15问

聊聊：索引的优缺点是什么？

优点：

聚簇索引：

• 顺序读写
• 范围快速查找
• 范围查找自带顺序

非聚簇索引：

• 条件查询避免全表扫描scan
• x序分组后，范围，排序，分组查询返回id行，排序分组后，再回表查询完整的数据，有可能利用顺序读写
• 覆盖索引不需要回表操作

索引的代价

• 空间上的代价

每建立一个索引都要为他建一个B+树，每一个树的每个节点都是一个数据页，一个页默认会占用16kb的空间

• 时间上代价

  每次对数据的增删改查，同时会修改各个B+树的索引

使用索引一定能提升效率吗？

不一定

• 少量数据全表扫描也很快，可以直接获取到全量数据
• 唯一索引会影响插入速度，建议使用
• 索引过多会影响更新，插入，删除数据速度

如果是大段文本内容，如何创建索引

B树和B+树都可以作为索引的数据结构，在MySQL中采用的是B+树

• 第一种方式是分表存储，然后创建索引
• 第二种是使用ES为大文件创建索引

CRUD时聚簇索引与非聚簇索引的区别是什么？

• 聚簇索引插入新值时比采用非聚簇索引插入新值得速度慢很多，因为插入要保证主键不能重复
• 聚簇索引范围，排序查找效率高，因为是有序的
• 非聚簇索引访问需要两次索引查找，第一次查询主键值，第二次根据主键值找行数据

非聚簇索引为什么不存数据地址值而存主键？

因为聚簇索引中有时会引发分页操作、重排操作数据有可能会移动

什么是回表操作？

第一次 取回id，然后根据id拿到完整的数据 （绝大多数情况非聚簇索引都是回表操作）

id age name sex
age -> index
select * from user where age >20 ; 

select * from user where age >20 ; 

什么是覆盖索引？

id age name sex
age -> index
select * from user where age >20 ; 

第⼀次 取回id，第二次（回表）根据id拿到完整数据

age,name -> index
select age from user where age >20 and name like"张%" ;

覆盖索引不会回表查询，查询效率也是比较高的

非聚簇索引一定回表查询吗？

不一定，只要B+树中包含的字段（创建索引的字段），覆盖想要select的字段，就不用回表查询

为什么回表查询？直接存数据不好吗？

控制非聚簇索引的大小

如果把一个InnoDB表的主键删除掉，是不是没有主键就不会进行回表操作

不是，InnoDB会生成rowid辅助回表查询

什么是联合索引，组合索引，复合索引？

联合索引是对表上的多个列进行索引

复合索引创建时字段顺序不一样，使用效果一样吗？

可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。

比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照 c2 列进行排序。
• 在记录的 c2 列相同的情况下，采用 c3 列进行排序
• B+树叶子节点处的记录由 c2列、c3列和主键c1列组成
• 本质上也是二级索引

什么是唯一索引

• 随表一起创建索引：

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 idx_name (customer_name), -- 普通索引：既不是主键，列值也不需要唯⼀，单纯的为了提高查询速度而创建。
KEY idx_no_name (customer_no,customer_name) -- 复合索引：即⼀个索引包含多个列。
)

• 单独创建索引

CREATE TABLE customer1 (
    id INT UNSIGNED, 
    customer_no VARCHAR(200), 
    customer_name VARCHAR(200)
); 

ALTER TABLE customer1 ADD PRIMARY KEY customer1(id); -- 主键索引
CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯⼀索引
CREATE INDEX idx_name ON customer1(customer_name); -- 普通索引
CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 复合索引

什么时候使用唯一索引？

业务需求唯一字段的时候，一般不考虑性能问题

【强制】业务上具有唯⼀特性的字段，即使是多个字段的组合，也必须建成唯⼀索引。

说明：不要以为唯⼀索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；

另外，即使在应用层做了非常完善的校验控制，只要没有唯⼀索引，根据墨菲定律，必 然有脏数据产生。

什么时候适合创建索引，什么时候不适合创建索引？

适合创建索引

• 频繁作为where条件语句查询字段
• 关联字段需要建立索引
• 排序字段可以建立索引
• 分组字段可以建立索引
• 统计字段可以建立索引

不合适创建索引

• 频繁更新的字段不适合建立索引
• where、分组、排序中用不到的字段不必要建立索引
• 可以确定表数据非常少不需要建立索引
• 参与mysql函数计算的列不合适键索引

创建索引时避免有如下极端误解:

• 宁滥毋缺，认为是一个查询就需要建一个索引
• 宁缺毋滥，认为索引会消耗空间、严重拖慢更新和新增速度
• 抵制唯一索引。认为业务的唯一性一律需要在应用层通过先查后插方式解决

有哪些情况会导致索引实现

• 计算、函数导致索引失效
• Like%，—开头索引失效
• 不等于索引失效
• is not null 失效和 is null
• 类型转换导致索引失效

一个表有多个索引时，能否手动选择使用哪个索引？

不可以，只能用过Mysql优化器自己选择

如何查看一个表的索引？

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;
SELECT * FROM information_schema.OPTIMIZER_TRACE; 
set session optimizer_trace="enabled=off"; 

多个索引优先级怎么匹配

1. 主键匹配
2. 全值匹配
3. 最左前缀匹配
4. 范围匹配
5. 索引扫描
6. 全表扫描

⼀般性建议

• 对于单键索引，尽量选择过滤性更好的索引（例如：⼿机号，邮件，身份证）
• 在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 选择组合索引时，尽量包含where中更多字段的索引
• 组合索引出现范围查询时，尽量把这个字段放在索引次序的最后⾯
• 尽量避免造成索引失效的情况

使用Order By时能否通过索引排序？

没有过滤条件，不走索引

索引排序的内部流程是什么？

select name,id from user where name like '%明' order by name;
select name,id，age from user where name like '%明' 

关键配置：

• sort_buffer 可供排序的内存缓冲区大小
• max_length_for_sort_data 单行所以字段总和限制，超过这个大小启动双路排序

什么是双路排序和单路排序？

单路排序：一次取出所以的字段进行排序，内存不够用的时候使用磁盘

双路排序：取出排序字段进行排序，排序完成之后在通过回表查询所有需要的其他字段

单路快，双路慢

但是用单路有问题

在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出,

单路排序 优化策略

• 增加sort_buffer_size参数设置
• 增大max_length_for_sort_data参数的设置
• 减少select后面的查询字段，禁止使用select *

提高order by的速度

1. Order by 时select * 是一个大忌
2. 尝试提高sort_buffer_size
3. 尝试提高max_length_for_sort_data

group by分组和order by在索引使用上有什么区别

两者使用索引原则几乎一样，唯一区别：

• group by 先排序再分组，遵照索引键的最佳左前缀法则
• group by没有过滤条件，也可以用上索引。Order by 必须用过滤条件才能使用上索引

如果表中有字段为null，⼜被经常查询该不该给这个字段创建索引？

应该创建索引，使用的时候尽量使用is null判断。

IS NOT NULL 失效 和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL; 
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

**注意：**当数据库中的数据的索引列的 NULL值达到比较高的比例的时候 ，即使在IS NOT NULL 的情

况下 MySQL的查询优化器会选择使用索引， 此时type的值是range（范围查询）

-- 将 id>20000 的数据的 name 值改为 NULL
UPDATE emp SET `namè = NULL WHERE ìd` > 20000; 
-- 执行查询分析，可以发现 IS NOT NULL 使用了索引
-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效，由查询优化器的算法决定
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL

有字段为null索引是否会失效？

不⼀定会失效，每⼀条sql具体有没有使用索引 可以通过trace追踪⼀下

最好还是给上默认值