MySQL索引大解析

• 生活家

1、理解索引本质

索引是一个存储 每行数据的磁盘地址 的数据结构：提高查询和更新数据库表的速度
全表扫描 and 走索引检索
这个数据结构里面放的健值对（一般key就是咱们添加索引的字段，值就是对应的磁盘地址）

索引类型：

normal：普通的索引，没啥特殊要求
unique：唯一索引，字段的值不能重复
特殊的唯一索引：primary key主键，而且不能为null
full text： 文本类型的字段，匹配某个字段like，可以创建全文索引，char，varchar，text
这时候不需要用like了，却而代之：match（content） against（‘关键字’ in natural language mode）
索引到底用哪种数据结构存储呢？BTREE HASH

2、掌握索引底层数据结构

查找： 二分查找：每次就把范围缩小了一半
有序数组：查询 比较会 比较快=<> 更新数据就慢
单链表：更新很快 频繁修改的数据 查找效率也不够高

BST：Binary Search Tree 二叉查找树二叉树
左子树节点<父节点 —右子树节点>父节点
能实现比较快的插入修改同时支持快速的查询，投影到水平轴就是一个左到右依次变大的线性：查找效率跟树的深度有关：时间复杂度最坏会变成O（n），此时变成一个线性的单链表

然后为了改善这个不足：平衡二叉树（左右子树的深度差不能大于1）
AVL Tree/Balance Binary Search Tree
树上每个点存储：键值 数据磁盘地址 子节点引用

思考：
这个数据结构是放在磁盘上，会很大不会放到内存
I/O操作，innodb当中最小单位page一页就是16k（16384byte），所以这样浪费大量空间
每个节点如果存储这么少的内容的话，浪费大量空间，访问一次节点，一次磁盘io

1、一个节点上存储更多数据
2、二叉变成多叉，degree路数 度：这样树的深度越来越小，从高瘦变成矮胖
多路平衡查找树Balance Tree B树

1、节点（磁盘块）拥有的子树数量称为度degree
每个节点存储的健值对（索引对应行个数）1行就是一个健值对，健值对是N-1的话
度就是N：这个节点下面有的子树个数
2、如何实现一个节点存储多个数据，还保持平衡？
Max Degree=3: 节点的数据数量大于2时候就会开始分裂，合并
建议不要去更新主键索引!

B+树加强版的BTree
节点拥有的子树数量（度degree）等于节点的存储数据量（索引关键字个数）

思考：
在叶子上存储数据！假设一条记录1KB，一个叶子节点存储16条数据，关键字数量16，于是度也是16；
bigint 8byte + 子节点引用指针6byte一个单元14byte
16384/14=1170，1170117016=2千万条数据，树深度3、只需三次IO
另外：相邻叶子节点增加了一个指针，如果是范围查询呢？
避免每次回到父节点重新遍历查询！只需要沿着指针进行查询！
和B树的对比:
1、B树能解决问题的，B+都能解决
2、扫库，扫表能力更强
只需遍历叶子节点，不要遍历整个树节点
3、磁盘读写能力更强
只需3次IO，访问更多关键字
4、排序能力更强
叶子节点有指向下一个相邻叶子节点的指针，范围查询，形成有序链表
5、效率更稳定
IO次数固定3次，数据只放叶子节点，不管查询啥样数据

索引方式： Hash 和 B Tree
hash：O（1），索引字段值得到hashcode，无序的；所以不支持范围插叙，只能根据对应的key获取value
对应字段有重复值，或者产生了hash碰撞，降低查询效率！
innodb里面都是基于B树的索引方式数据结构，准确是用的B+树

3、索引在不同存储引擎的实现方式

常用的存储引擎，myisam和innodb
在数据库安装的根目录下会有跟数据库名对应的文件名，打开文件会发现

3.1 对于myisam有三个文件：

(1) 表名.frm: 存放表结构的，每个表每个存储引擎都会有的
(2) 表名.MYD：D:Data 数据
(3) 表名.MYI：I: Index 索引 （主键索引 辅助索引）都是在叶子节点上找到对应Data的磁盘地址然后去.MYD文件里面取数据。

3.2 对于InnoDB只有一个文件 表名.ibd

（1）主键索引：索引和数据放在一起（数据即索引 索引即数据？）主键索引只有一个，父节点存放的键值和子节点引用；数据就放在叶子结点上
聚集索引：在叶子节点上，如果索引的键值顺序（逻辑顺序）跟叶子节点上存储数据的顺序一致。
（2）普通索引（辅助索引）：在叶子节点上存储的是（存储辅助索引键值和主键值）。需要遍历两颗B+树
（3）问题 主键索引上才有数据，如果这个表没有指定主键索引 只有普通索引怎么办 甚至没有索引咋办（数据放哪儿）？
1.primary key = 聚集索引
2.unique key not null ：在没有1的情况下，找到一个不包含空值的唯一索引作为聚集索引
3.啥也没有的话：在InnoDB当中隐藏的为每行数据实现了ROWID作为它的聚集索引
一张表不可能没有聚集索引（InnoDB）

4、索引的创建和使用

问题：是不是把表的所有字段都建立索引 那样查询变得很快了？

(1) 列的离散度

离散度公式：count(distinct(column_name)):count(*)<=1 去重以后的行数除以总行数
离散度小意思就是重复度高！查询的时候需要扫描的行数就越多！这时候数据库优化器会放弃使用索引（约等于全表扫描）！有一个阈值。

(2) 联合索引最左匹配

再多个字段上同时建立联合索引！并且字段是有顺序的（按照建立的顺序来匹配 不会跳过 最左的字段 直接匹配查询后面的字段）
alter table user add index comidx_name_phone (name, phone);
(A)explain select * from user where name=’tingting’ and phone=’12345678910’;
可以用到联合索引
(B)explain select * from user where phone=’12345678910’ and name=’tingting’ ;
由于优化器optimizer,会对我们的SQL语句进行优化，会变成跟A等价
( C)explain select * from user where name=’tingting’;
当然可以用到name刚好最左
(D)explain select * from user where phone=’12345678910’;
不能用到联合索引
怎么创建联合索引？
根据业务常用的查询字段
例如idx(a,b,c): 实际上就是建立了 idx(a) and idx(a,b) and idx(a,b,c)三个索引
所以查询条件：b, c,bc,ac这些都不会用到联合索引！

(3) 覆盖索引

什么是回表？
多扫描一颗B+树的过程就叫做 回表：会带来额外的性能的消耗
什么是覆盖索引？
在建立了主键以外的辅助索引的时候，刚好这个辅助索引的字段也是我们查询需要的字段，这个时候就不需要去主键的B+树的叶子结点上去数据了，此时就叫覆盖索引！
例如：alter table user add index comidx_name_phone (name, phone);
select name, phone from user where name=’tingting’ and phone=’12345678910’;
select name, phone from user where name=’tingting’;
select name, phone from user where phone=’12345678910’;这是一个特例
在explain之后出现Using index就代表使覆盖索引了
所以 这也是一个不要使用select * ，要查询那个就写出来

(4) 创建索引：

  1. 在用于where order by join的（on）字段上创建索引

  2. 合理创建索引的个数 不是越多越好

  3. 列的离散度比较高适合建立索引，低的优化器会放弃建立索引

  4. 频繁更新的字段不要作为索引或者主键（最好跟业务无关的id建立主键）

  5. 建立联合索引，而不是修改单列索引

  6. 过长的字段怎么建立索引？建议建立前缀索引（截取一个长度），有一个存储空间和区分度（离散度），把握前缀长度和离散度的关系：
    代码用例：
    – 计算出完整字符串的选择性(图4)
    SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;
    – 计算各个前缀的选择性(图5)，然后找出选择性与图4相近的
    SELECT
    COUNT(DISTINCT LEFT(CITY,3)/COUNT( * )) PREF3,
    COUNT(DISTINCT LEFT(CITY,4)/COUNT( * )) PREF4,
    COUNT(DISTINCT LEFT(CITY,5)/COUNT( * )) PREF5,
    COUNT(DISTINCT LEFT(CITY,6)/COUNT( * )) PREF6,
    COUNT(DISTINCT LEFT(CITY,7)/COUNT( * )) PREF7,
    FROM city_demo;
    —建立前缀索引
    ALTER TABLE city_demo ADD INDEX idx_city (city(7)) USING BTREE ;
    – 或者这个也行
    ALTER TABLE city_demo ADD KEY idx_city (city(7))
    – 又或者直接用Navicat可视化操作也行

  7. 为什么不建议使用无序的字段（UUID,hashCode, idCard）作为主键索引？这些字段无法排序，存储的时候是乱序的，

5、什么时候用不到索引

1.索引上使用函数（replace/substr/concat/sum count avg）,表达式
不要再索引的字段上做任何逻辑计算
2.字符串不加引号
字符类型的字段你不加单引号 会出现隐式的转换而用不到索引
3.like条件前面加上%
字符的比较肯定是从左往右开始匹配的，前面加上%肯定没法比较；
索引下推？Index Condition Pushing Down 特殊情况当存储引擎可以用到！
4.负向查询能用到索引吗？<> != not in
实际上不是这样的！不一定的 有时候能用到

这些都是基本的规则 到底能不能用到是由optimizer优化器决定的,判断标准
Cost Based Optimizer (CBO)：IO CPU的成本，这样跟很多因素有关
Rule Base Optimizer (RBO): 基于规则的优化器 老版本的Oracle

附录1：
Oracle采坑笔记：不能使用now（）， 数据库名要用双引号括起来，结尾不能使用分号，SQL语句格式一定要正确 最好手敲，主键递增另外建立
什么字段标识符无效，都用双引号括起来！
触发器; 另外注意into:new.这里，“：”前后都不要有空格。
详细连接
一定要注意空格 不要空格尽量不要有 也不要换行！
CREATE sequence police_test_seq increment by 1 start with 1 cache 20;

CREATE or REPLACE TRIGGER police_test_trigger
BEFORE INSERT on “police_test” for each row
BEGIN
select police_test_seq.nextval into:new.“id” from dual;
end;

附录2：
Java8stream学习笔记
默认情况下有序集合 迭代器 Stream.sorted生成的流都是有序流，有序流在处理完成之后会回复到原有的顺序，但是unordered()方法可以解除有序流的顺序限制，更好的发挥并行处理的性能优势，例如distinct将保存任意一个唯一元素而不是靠前的一个，limit将保存任意N个元素 而不是前N个。

  1. 生成流的方法：
    a. Collection接口的stream()或parallelStream()方法
    b. 静态的Stream.of(),Stream.empty()方法
    c. Arrays.stream(array,from,to)
    d. 等等
  2. 流的使用
    list.stream() Arrays.stream() Stream.of(“str”, “str1”, “str2”)
    try(Stream lines=Files.lines(Paths.get(“filePath”), Charset.defaultCharset())){}catch(IOException e){}
  3. 筛选filter
    filter函数接收一个Lambda作为参数筛选出返回的布尔类型是true的类型的元素
    例如：List result=list.stream().filter(Person::isStudent).collect(toList);
  4. 去重distinct
    去掉重复的元素
  5. 截取limit
    list.stream().limit(3).collect(toList);截取前三个元素 也可以是随机的（unordered()）
  6. 跳过skip
    用法同limit
  7. 映射map
    list.stream().map(Person::getName) 对流中的每一个元素执行一个函数，也就是执行map里面的Lambda表达式，最后将结果放入一个新的流当中。

本文地址：https://blog.csdn.net/blackxc/article/details/107151361

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐