mysql索引原理是什么(你真的完全搞懂了Mysql索引原理吗)

目录

为什么要有索引

索引原理

磁盘IO与预读

索引的数据结构

b 树的查找过程

b 树性质

mysql的索引分类

索引的两大类型Hash与btree

添加索引,必须遵循原则

慢查询优化的基本步骤


欢迎关注笔者,优质文章都在这里等你。

一、为什么要有索引

索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。

一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重。说起加速查询,就不得不提到索引了。

二、索引的原理一 索引原理

索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典,查火车车次,飞机航班等

如上图,是一颗b 树,关于b 树的定义可以参见B 树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

b 树的查找过程

如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。

真实的情况是,3层的b 树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

b 树性质

1.索引字段要尽量的小:通过上面的分析,我们知道IO次数取决于b 数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m 1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。

这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b 树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

2.索引的最左匹配特性(即从左往右匹配):当b 树的数据项是复合的数据结构,比如(name,age,sex)的时候,b 数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b 树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;

但当(20,F)这样的没有name的数据来的时候,b 树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b 树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

四、Mysql索引管理一、功能
  • 索引的功能就是加速查找。
  • mysql中的primary key,unique,联合唯一也都是索引,这些索引除了加速查找以外,还有约束的功能。
二、MySQL的索引分类
  • 普通索引index :加速查找
  • 唯一索引
  • 主键索引:primary key :加速查找 约束(不为空且唯一)
  • 唯一索引:unique:加速查找 约束 (唯一)
  • 联合索引
  • -primary key(id,name):联合主键索引
  • -unique(id,name):联合唯一索引
  • -index(id,name):联合普通索引
  • 全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。
  • 空间索引spatial :了解就好,几乎不用
三、 索引的两大类型hash与btree
  1. 我们可以在创建上述索引的时候,为其指定索引类型,分两类
  2. hash类型的索引:查询单条快,范围查询慢
  3. btree类型的索引:b 树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
  4. 不同的存储引擎支持的索引类型也不一样
  5. InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
  6. MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
  7. Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
  8. NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
  9. Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
四 添加索引,必须遵循原则

1.最左前缀匹配原则,非常重要的原则,

create index ix_name_email on s1(name,email,)

- 最左前缀匹配:必须按照从左到右的顺序匹配

select * from s1 where name='egon'; #可以

select * from s1 where name='egon' and email='asdf'; #可以

select * from s1 where email='alex@oldboy.com'; #不可以

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,

比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,

d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),

表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、

性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,

这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b 树中存的都是数据表中的字段值,

但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。

所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

count(1)或count(列) 代替 count(*)

- 创建表时尽量时 char 代替 varchar

- 表的字段顺序固定长度的字段优先

- 组合索引代替多个单列索引(经常使用多个条件查询时)

- 尽量使用短索引

- 使用连接(JOIN)来代替子查询(Sub-Queries)

- 连表时注意条件类型需一致

- 索引散列值(重复少)不适合建索引,例:性别不适合

mysql索引原理是什么(你真的完全搞懂了Mysql索引原理吗)(1)

五、慢查询优化的基本步骤
  1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
  2. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
  3. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  4. order by limit 形式的sql语句让排序的表优先查
  5. 了解业务方使用场景
  6. 加索引时参照建索引的几大原则
  7. 观察结果,不符合预期继续从0分析

文章来源:https://www.cnblogs.com/bypp/p/7755307.html


您的转发 关注就是对笔者最大的支持,欢迎关注。

对大厂架构设计,BAT等厂家面试题解读,编程语言理论或者互联网圈逸闻趣事这些感兴趣,欢迎关注笔者,没有错,干货文章都在这里。

mysql索引原理是什么(你真的完全搞懂了Mysql索引原理吗)(2)

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页