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

THE END
喜欢就支持一下吧
点赞10分享