MySQL_索引

索引的数据结构

B+Tree

B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

B树：
非叶子节点和叶子节点都会存储数据。
B+树：
所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

特点

• 遍历全表的速度特别快，不需要查询了子节点之后，再返回父节点进行查询
• 范围查询的时候，只要找到第一个节点就能很快找到后面的节点，也是不需要再返回父节点进行查询

Hash（对象、值→数字）重复

特点
A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
B. 无法利用索引完成排序操作
C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引
哈希碰撞：值不同，哈希值相同

为什么InnoDB存储引擎选择使用B+tree索引结构?
A. 相对于二叉树，层级更少，搜索效率高；
B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储
的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
C. 相对Hash索引，B+tree支持范围匹配及排序操作；

索引失效的场景和如何避免索引失效

• 不要在索引列上进行运算操作， 索引将失效。

-- 当根据phone字段进行等值匹配查询时, 索引生效。
explain select * from tb_user where phone = '17799990015';
-- 当根据phone字段进行函数运算操作之后，索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';

• 字符串类型字段使用时，不加引号，索引将失效。
  接下来，我们通过两组示例，来看看对于字符串类型的字段，加单引号与不加单引号的区别：

explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= 0;

explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015;

• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';

• 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;
-- 由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
-- 然后，我们可以对age字段建立索引。
create index idx_user_age on tb_user(age);

• 如果MySQL评估使用索引比全表更慢，则不使用索引。

select * from tb_user where phone >= '17799990005';
select * from tb_user where phone >= '17799990015';

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

is null 、is not null是否走索引，得具体情况具体分析，并不是固定的

• 批量插入数据

联合索引

最左匹配原则

建立联合索引时会遵循最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配
例如：

为user表中的name、address、phone列添加联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);
所以，下面的三个SQL语句都可以命中索引
SELECT * FROM user WHERE address = ‘北京’ AND phone = ‘12345’ AND name = ‘张三’;

SELECT * FROM user WHERE name = ‘张三’ AND address = ‘北京’;

SELECT * FROM user WHERE name = ‘张三’;

这三条SQL语句在检索时分别会使用以下索引进行数据匹配 (name,address,phone) (name,address) (name)
索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动的调整where条件中的顺序
如果联合索引中最左边的列不在查询条件中，则不会命中索引
SELECT * FROM user WHERE address = ‘北京’ ;

数据库优化

避免回表

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？

使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）

如果在一个场景下，select id,name,sex from user where name =‘zhangsan’;这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。
这就是一个典型的使用覆盖索引的优化策略减少回表的情况。

联合索引的使用

联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。

联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那建议这种情况下使用联合索引。
联合索引的使用：

考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

补充 索引

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让
索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建
立索引，这样可以大大节约索引空间，从而提高索引效率。
语法

create index idx_xxxx on table_name(column(n)) ; 

执行计划 explain

建立连接→查看缓存（取消了）→词法解析 语法解析—解析树→查询优化器—根据解析树生成执行计划→执行引擎→存储引擎

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

字段	含义
type	表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等