快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

【mysql数据库的底层缓存机制】

请联系QQ:1793040 索取软件

索引的过程:

假如一张表有一亿条数据 ,需要查找其中某一条数据,按照常规逻辑, 一条一条的去匹配的话, 最坏的情况下需要匹配一亿次才能得到结果,用大O标记法就是O(n),这是无法接受的,而且这一亿条数据显然不能一次性读入内存供程序使用, 因此, 这一亿次匹配在不经缓存优化的情况下就是一亿次磁盘IO开销。

如果把这张表转换成平衡树结构(一棵非常茂盛和节点非常多的树),假设这棵树有10层,那么只需要10次IO开销就能查找到所需要的数据, 速度以指数级别提升,用大O标记法就是O(log n),n是记录总树,底数是树的分叉数,结果就是树的层次数。换言之,查找次数是以树的分叉数为底,记录总数的对数。用程序来表示就是Math.Log(100000000,10),100000000是记录数,10是树的分叉数(真实环境下分叉数远不止10), 结果就是查找次数,这里的结果从亿降到了个位数。因此,利用索引会使数据库查询有惊人的性能提升。

索引是帮助数据高效查询数据的一种数据结构,它包含一个表中某些列的值以及记录对应的地址,并且把这些值存储在一个数据结构中。

索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销,也就是为什么索引会给查询以外的操作带来副作用的原因。

数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。所以我们要减少IO次数,对于树来说,IO次数就是树的高度,而”矮胖”就是b树的特征之一,树的阶数的大小取决于磁盘页的大小。

B树

B+树

B+树与B树的特点

(1)B树每个节点都存储数据,所有节点组成这棵树。

B+树只有叶子节点存储数据(B+树中有两个头指针:一个指向根节点,另一个指向关键字最小的叶节点),叶子节点包含了这棵树的所有数据,所有的叶子结点使用链表相连,便于区间查找和遍历,所有非叶节点起到索引作用。

(2)B树中叶节点包含的关键字和其他节点包含的关键字是不重复的。

B+树的索引项只包含对应子树的最大关键字和指向该子树的指针,不含有该关键字对应记录的存储地址。

(3)B树中每个节点(非根节点)关键字个数的范围为[m/2(向上取整)-1,m-1](根节点为[1,m-1]),并且具有n个关键字的节点包含(n+1)棵子树。

B+树中每个节点(非根节点)关键字个数的范围为[m/2(向上取整),m](根节点为[1,m]),具有n个关键字的节点包含(n)棵子树。

B+树中查找,无论查找是否成功,每次都是一条从根节点到叶节点的路径。

B+树与B树的区别

叶子结点包含全部关键字以及指向相应记录的指针,而且叶结点中的关键字按大小顺序排列,相邻叶结点用指针连接。

非叶结点仅存储其子树的最大(或最小)关键字,可以看成是索引。

B+树更适合外部存储。由于内结点不存放真正的数据(只是存放其子树的最大或最小的关键字,作为索引),一个结点可以存储更多的关键字,每个结点能索引的范围更大更精确,也意味着B+树单次磁盘IO的信息量大于B树,I/O的次数相对减少。

MySQL是一种关系型数据库,区间访问是常见的一种情况,B+树叶结点增加的链指针,加强了区间访问性,可使用在区间查询的场景;而使用B树则无法进行区间查找。

聚集索引

叶子节点存的是整行数据,直接通过这个聚集索引的键值找到某行。数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。数据行和相邻的键值紧凑地存储在一起,因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚集索引。

非聚集索引

也就是我们常说的普通索引,非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。

索引树结构中各节点的值来自于表中的索引字段, 假如给user表的name字段加上索引 , 那么索引就是由name字段中的值构成,在数据改变时, DBMS需要一直维护索引结构的正确性。

如果给表中多个字段加上索引 , 那么就会出现多个独立的索引结构,每个索引(非聚集索引)互相之间不存在关联。

每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

InnoDB的聚集索引

InnoDB聚集索引的叶子节点存储行记录,因此InnoDB必须要有且只有一个聚集索引

(1)如果表定义了PK(Primary Key,主键),那么PK就是聚集索引。

(2)如果表没有定义PK,则第一个NOT NULL UNIQUE的列就是聚集索引。

(3)否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

这种机制使得基于PK的查询速度非常快,因为直接定位的行记录。

InnoDB的普通索引

InnoDB普通索引的叶子节点存储主键值,MyISAM则是存储的行记录头指针

非聚集索引和聚集索引的区别

(1)通过聚集索引可以查到需要查找的数据

(2)通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据

不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 一般来说,聚集索引(主键)是通往真实数据所在的唯一路径。

回表查询

InnoDB有两大类索引,一类是聚集索引(Clustered Index),一类是普通索引(Secondary Index)。普通索引因为无法直接定位行记录,其查询过程在通常情况下是需要扫描两遍索引树的。

索引下推:like ‘hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度

联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age=1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。

最左前缀:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符

在java系统里写的SQL,都必须符合最左前缀匹配原则,确保你所有的sql都可以使用上这个联合索引,通过索引来查询
create index (shop_id,product_id,gmt_create)
(1)全列匹配
一个sql里,正好where条件里就用了这3个字段,那么就一定可以用到这个联合索引的:
select * from product where shop_id=1 and product_id=1 and gmt_create=’2018-01-01 10:00:00′

(2)最左前缀匹配
这个就是说,如果你的sql里,正好就用到了联合索引最左边的一个或者几个列表,那么也可以用上这个索引,在索引里查找的时候就用最左边的几个列就行了:
select * from product where shop_id=1 and product_id=1

这个是没问题的,可以用上这个索引的

(3)最左前缀匹配了,但是中间某个值没匹配
这个是说,如果你的sql里,就用了联合索引的第一个列和第三个列,那么会按照第一个列值在索引里找,找完以后对结果集扫描一遍根据第三个列来过滤,第三个列是不走索引去搜索的,就是有一个额外的过滤的工作,但是还能用到索引,所以也还好,例如:
select * from product where shop_id=1 and gmt_create=’2018-01-01 10:00:00′
就是先根据shop_id=1在索引里找,找到比如100行记录,然后对这100行记录再次扫描一遍,过滤出来gmt_create=’2018-01-01 10:00:00’的行
这个我们在线上系统经常遇到这种情况,就是根据联合索引的前一两个列按索引查,然后后面跟一堆复杂的条件,还有函数啥的,但是只要对索引查找结果过滤就好了,根据线上实践,单表几百万数据量的时候,性能也还不错的,简单SQL也就几ms,复杂SQL也就几百ms。可以接受的。

(4)没有最左前缀匹配
那就不行了,一定不会用索引,所以这个错误千万别犯
select * from product where product_id=1

这个肯定不行

(5)前缀匹配
这个就是说,如果你不是等值的,比如=,>=,<=的操作,而是like操作,那么必须要是like 'XX%'这种才可以用上索引,比如说
select * from product where shop_id=1 and product_id=1 and gmt_create like ‘2018%’

(6)范围列匹配
如果你是范围查询,比如>=,<=,between操作,你只能是符合最左前缀的规则才可以范围,范围之后的列就不用索引了
select * from product where shop_id>=1 and product_id=1
这里就在联合索引中根据shop_id来查询了

(7)包含函数
如果你对某个列用了函数,比如substring之类的东西,那么那一列不用索引
select * from product where shop_id=1 and 函数(product_id)=2
上面就根据shop_id在联合索引中查询

覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据

覆盖索引

有一种例外可以不使用聚集索引就能查询出所需要的数据, 这种非主流的方法 称之为”覆盖索引”查询, 也就是平时所说的复合索引或者多字段索引查询。 文章上面的内容已经指出, 当为字段建立索引以后, 字段中的内容会被同步到索引之中, 如果为一个索引指定两个字段, 那么这个两个字段的内容都会被同步至索引之中。

案例分析:

先看下面这个SQL语句

//建立索引

create index index_birthday on user_info(birthday);

//查询生日在1996年4月15日出生用户的用户名

select user_name from user_info where birthday=’1996-04-15′

这句SQL语句的执行过程如下

(1)通过非聚集索引index_birthday查找birthday等于1996-04-15的所有记录的主键ID值

(2)通过得到的主键ID值执行聚集索引查找,找到主键ID值对就的真实数据(数据行)存储的位置

(3) 从得到的真实数据中取得user_name字段的值返回, 也就是取得最终的结果

user_info添加双字段的覆盖索引

create index index_birthday_and_user_name on user_info(birthday, user_name);

这句SQL语句的执行过程就会变为

通过非聚集索引index_birthday_and_user_name查找birthday等于1996-04-15的叶节点的内容,然而, 叶节点中除了有user_info表主键ID的值以外, user_name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式, 可以省略不使用覆盖索引查找的后面两个步骤, 大大的提高了查询性能

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040