MySQL索引

索引

概述

索引是帮助MySQL高效获取数据的数据结构(有序)

没有索引查询是这样的: 全表扫描

结构

B-Tree

B+Tree

• 绿色数据只起到索引数据的作用 , 不存储数据
• 叶子节点串成链表 , 是数据存储部分.
• 
• 与B-Tree区别 : 所有的数据都会出现在叶子节点
• 叶子节点形成一个单向链表

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

Hash

通过hash算法, 将键值映射为新的值 = >映射到对应的槽位上=> 然后存储在Hash表中

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

3). 存储引擎支持 在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

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

分类

聚集索引, 只记录行数据

二级索引

挂的是对应的ID
二级索引先比较首字母字典序, 然后类似于二叉搜索树的方式, 找到Arm, 然后找到Arm对应的ID, 然后通过聚集索引 , 找到对应的行数据

思考题

根据id查询效率高, 因为根据name查询还需要再回表查询一次

语法

table_name索引关联的表名

SQL性能分析

主要优化查询语句

根据具体情况来优化

慢查询日志:

• profile详情

explain执行计划

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
执行计划各字段的含义:
id: 表示操作表的顺序, 如果id相同, 从上往下执行, 不同, 越大越先执行
多对多, 需要一张关联表

执行顺序 - > s > sc > c : 原因: s 和 c表直接没有关系, 是通过中间表来联系起来的

id值越大越先被执行

**type: 表示连接类型，性能由好到差的连接类型为NULL、system、const、 eq_ref、ref、range、 index、all 。 **
使用主键或者唯一索引,则为const , 非唯一性索引,ref
优化的时候尽量往前优化
possible_key 显示可能应用在这张表上的索引，一个或多个。
key 实际使用的索引，如果为NULL，则没有使用索引。
key_len 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长 度，在不损失精确性的前提下， 长度越短越好 。
filtered 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

索引使用

验证索引对查询效率提升

主键默认有主键索引,所以通过id查, 一般性能都很高
创建索引的过程会构建b+树的结构, 通过创建索引, 来提升其他字段的性能(提升的效率很大)

索引的使用规则

索引失效

• 最左前缀法则

查询从索引的左列开始, 如果跳过了某一列, 索引将部分失效(后面的字段索引失效)

• 范围查询

• 列运算

在索引列上进行列运算失效

• 字符串不加引号, 索引也将会失效
• 模糊查询失效

• or连接

如果or连接一侧有索引 一侧 没有索引, 那么两个字段的索引将都会失效

• 数据分布影响

当需要查询的数据满足绝大部分, 就可能不走索引,直接全表扫描
如果很稀少, 夸张一点, 10086个人中查询一个人 , 就需要用索引来提高查询效率

SQL提示

use index是一个建议, 建议数据库用哪个索引
force是强制数据库用哪个索引

覆盖索引

思考:
如果通过查找username, 但没有password字段, 需要进行回表查询效率降低, 效率提升的办法就是将username和 password建立一个联合索引.
注意最左匹配原则
面试官:谈谈你对mysql联合索引的认识?

前缀索引

为什么使用前缀索引?
当字段过于庞大, 创建字段索引, 会出现浪费大量磁盘IO的情况

解决
前缀索引理解

单列索引&联合索引

单列索引:


联合索引


对于phone和name的顺序也是要考虑的

设计原则

总结