mysql怎么看b树 mysql用b+树
mysql采用哪些索引,B树索引解释下
事实上,在MySQL数据库中,诸多存储引擎使用的是B+树,即便其名字看上去是BTREE。
创新互联于2013年开始,先为无棣等服务建站,无棣等地企业,进行企业商务咨询服务。为无棣企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
4.1 innodb的索引机制
先以innodb存储引擎为例,说明innodb引擎是如何利用B+树建立索引的
首先创建一张表:zodiac,并插入一些数据
对于innodb来说,只有一个数据文件,这个数据文件本身就是用B+树形式组织,B+树每个节点的关键字就是表的主键,因此innode的数据文件本身就是主索引文件,如下图所示,主索引中的叶子页(leaf page)包含了数据记录,但非叶子节点只包含了主键,术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起,因此这种索引被称为聚簇索引,或聚集索引。
这种索引方式,可以提高数据访问的速度,因为索引和数据是保存在同一棵B树之中,从聚簇索引中获取数据通常比在非聚簇索引中要来得快。
所以可以说,innodb的数据文件是依靠主键组织起来的,这也就是为什么innodb引擎下创建的表,必须指定主键的原因,如果没有显式指定主键,innodb引擎仍然会对该表隐式地定义一个主键作为聚簇索引。
同样innodb的辅助索引,如下图所示,假设这些字符是按照生肖的顺序排列的(其实我也不知道具体怎么实现,不要在意这些细节,就是举个例子),其叶子节点中也包含了记录的主键,因此innodb引擎在查询辅助索引的时候会查询两次,首先通过辅助索引得到主键值,然后再查询主索引,略微有点啰嗦
Mysql InnoDB b+树的高度
为什么Mysql考虑使用B+树,而不是B树,其实我们可以先了解下B树和B+树的特点来看下。
※ 树的每个结点都会存储数据
※ 单次查询不一定要遍历到树的根部,平均查询时间会比较快
※ 非叶子节点不存储数据,只存储(冗余)索引,索引包含主键和指针
※ 叶子节点才真正存储数据
※ 每个叶子节点互相链表相连,保证了范围查询的时效性(页之间用双向链表连接,数据间用单项链表链接)
InnoDB最小存储单位是页,叶子节点和非叶子节点最小单位都是页,页大小Mysql 默认设定16384字节,约为16KB。
我们假设主键ID为bigint类型,长度为8字节,而指针大小在InnoDB源码中设置为6字节,这样一共14字节
我们一个页中能存放多少这样的索引元素,其实就代表有多少指针,即16384/14=1170;
高度为2的B+树能存放1170×16=18720
高度为3的B+树能存放1170×1170×16 = 21902400
InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。
在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。
MYSQL使用基础、进阶分享
MySQL是一个关系型数据库管理系统,由瑞典MySQL AB公司开发,属于Oracle旗下产品,是最流行的关系型数据库管理系统之一。
端口是3306。
表很多时,使用linux脚本,需要根据需要修改一下:
和创建一样,可以加上 if exists
可两篇文章:
如:
用于在已有的表中添加、删除或修改列。
添加 ADD
或
默认是添加到最后,但可以指定位置。 FIRST :添加最前
AFTER 字段名 :添加指定字段之后
例子:
删除 DROP
修改 MODIFY 主要修改原列的类型或约束条件 同样可以用 FIRST 和 AFTER 字段名 ,代表的是修改到哪里。
修改字段名 CHANGE
可以把表2的数据复制到表1中,但 不能复制约束性条件 。
单行
多行,注意 只有一个VALUES :
不写 (行1, 行2...) 这一部分的话,默认一一对应
除了以上方法外,还可以用SET为每一行附上相应的值。
假如没有筛选的话,就给全部都修改了。可以用 WHERE 筛选。
假如 没有筛选的话,就给全部删除了 。相当于清空。
清空
先把表删除,然后再建一个。与 DELETE FROM 相比, TRUNCATE 的效率更快,因为 DELETE FROM 是把记录逐条删除的。
查询执行的顺序
FROM -- WHERE -- SELECT -- GROUP BY -- HAVING -- ORDER BY -- LIMIT
注意
当数据很大,上百万的时候,使用LIMIT ... OFFSET ..的方式进行分页十分浪费资源且耗时长。最好是结合WHERE使用,如:
REGEXP 使用正则表达进行匹配。 查询时,需要搭配WHERE或HAVING使用 。
两个表之间有交集且要用到两个表的数据时,可以使用内连接查询。
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。
用法:
RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。 把LEFT JOIN的表1、表2调换顺序,就是REGHT JOIN 。
FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行. 相当于结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
但 MySQL中不支持 FULL OUTER JOIN 。
即SELECT嵌套。
IN 一个查询结果作为另一个查询的条件。 如:
EXISTS 用于判断查询子句是否有记录,如果有一条或多条记录存在返回 True,否则返回 False。True时执行。 如:
索引的本质是一种排好序的数据结构。利用索引可以提高查询速度。
常见的索引有:
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。 外键的使用条件:
外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作。
对已有的两个表增加外键 比如:主表为A,子表为B,外键为aid,外键约束名字为a_fk_b
为子表添加一个字段,当做外键
为子表添加外键约束条件
假如删除记录报错: [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...)
这是因为MySQL中设置了foreign key关联,造成无法更新或删除数据。可以通过设置 FOREIGN_KEY_CHECKS 变量来避免这种情况。 第一步:禁用外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=0; 第二步:删除数据 第三步:启动外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=1; 查看当前FOREIGN_KEY_CHECKS的值,可用如下命令: SELECT @@FOREIGN_KEY_CHECKS;
使用 UNION 来组合两个查询,如果第一个查询返回 M 行,第二个查询返回 N 行,那么组合查询的结果一般为 M+N 行。
每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行,如果需要 保留 相同行,使用 UNION ALL 。
只能包含一个 ORDER BY 子句,并且必须位于语句的最后 。
内置函数很多, 见: MySQL 函数
我们一般使用 START TRANSACTION 或 BEGIN 开启事务, COMMIT 提交事务中的命令, SAVEPOINT : 相当于设置一个还原点, ROLLBACK TO : 回滚到某个还原点下
一般的使用格式如下:
开启事务时, 默认加锁
根据类型可分为共享锁(SHARED LOCK)和排他锁(EXCLUSIVE LOCK)或者叫读锁(READ LOCK)和写锁(WRITE LOCK)。
根据粒度划分又分表锁和行锁。表锁由数据库服务器实现,行锁由存储引擎实现。
除此之外,我们可以显示加锁
加锁时, 如果没有索引,会锁表,如果加了索引,就会锁行
InnoDB默认支持行锁,获取锁是分步的,并不是一次性获取所有的锁,因此在锁竞争的时候就会出现死锁的情况
解决方法:
即ACID特性:
由于并发事务会引发上面这些问题, 我们可以设置事务的隔离级别解决上面的问题.
MySQL的默认隔离级别(可重复读)
查看当前会话隔离级别
方式1
方式2
设置隔离级别
主从集群的示意图如下:
主要涉及三个线程: binlog 线程、 I/O 线程和 SQL 线程。
同步流程:
由于MySQL主从集群只会从主节点同步到从节点, 不会反过来同步, 所以需要读写分离
读写分离需要在业务层面实现 , 写数据只能在主节点上完成, 而读数据可以在主节点或从节点上完成
索引是帮助MySQL高效获取数据的排好序的数据结构
MySQL的索引有
推荐两个在线工具:
简单来说, B树是在红黑树(一个平衡二叉树)的基础上将一个节点存放多个值, 实现的, 降低了树的高度, 每个节点都存放索引及对应数据指针, 同一层的节点是递增的
而B+树在B树的基础上进行优化, 非叶子节点存放 子节点的开始的索引, 叶子节点存放索引和数据的指针, 且叶子节点之间有双向的指针
如下示意图:
不同的引擎, 主键索引存放的数据也不一样, 比如常见的 MyISAM 和 InnoDB
MyISAM 的B+树叶子节点存放表数据的指针, InnoDB 的B+树叶子节点存放处主键外的数据
其他的:
即多个列组成一个索引, 语法:
由于联合索引的B+树的结构, 根据列建立, 所以我们的查找条件也要根据索引列的顺序( where column1=x, column2=y,columnN... ), 否则会全表扫描
如果你对列进行了 (+,-,*,/,!) , 那么都将不会走索引。
OR 引起的索引失效
OR 导致索引是在特定情况下的,并不是所有的 OR 都是使索引失效,如果OR连接的是 同 一个字段,那么索引 不会失效 , 反之索引失效 。
这个我相信大家都明白,模糊搜索如果你前缀也进行模糊搜索,那么不会走索引。
这两种用法,也将使索引失效。另 IN 会走索引,但是当IN的取值范围较大时会导致索引失效,走全表扫描, 见: MySQL中使用IN会不会走索引
不走索引。
走索引。
所以设计表的时候, 建议不可为空, 而是将默认值设置为 "" ( NOT NULL DEFAULT "" )
mysql innodb 索引到底是b+树还是b树?
先从数据结构的角度来答。
题主应该知道B-树和B+树最重要的一个区别就是B+树只有叶节点存放数据,其余节点用来索引,而B-树是每个索引节点都会有Data域。
这就决定了B+树更适合用来存储外部数据,也就是所谓的磁盘数据。
从Mysql(Inoodb)的角度来看,B+树是用来充当索引的,一般来说索引非常大,尤其是关系性数据库这种数据量大的索引能达到亿级别,所以为了减少内存的占用,索引也会被存储在磁盘上。
那么Mysql如何衡量查询效率呢?磁盘IO次数,B-树(B类树)的特定就是每层节点数目非常多,层数很少,目的就是为了就少磁盘IO次数,当查询数据的时候,最好的情况就是很快找到目标索引,然后读取数据,使用B+树就能很好的完成这个目的,但是B-树的每个节点都有data域(指针),这无疑增大了节点大小,说白了增加了磁盘IO次数(磁盘IO一次读出的数据量大小是固定的,单个数据变大,每次读出的就少,IO次数增多,一次IO多耗时啊!),而B+树除了叶子节点其它节点并不存储数据,节点小,磁盘IO次数就少。这是优点之一。
另一个优点是什么,B+树所有的Data域在叶子节点,一般来说都会进行一个优化,就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据,这样就能进行区间访问啦。
至于MongoDB为什么使用B-树而不是B+树,可以从它的设计角度来考虑,它并不是传统的关系性数据库,而是以Json格式作为存储的nosql,目的就是高性能,高可用,易扩展。首先它摆脱了关系模型,上面所述的优点2需求就没那么强烈了,其次Mysql由于使用B+树,数据都在叶节点上,每次查询都需要访问到叶节点,而MongoDB使用B-树,所有节点都有Data域,只要找到指定索引就可以进行访问,无疑单次查询平均快于Mysql(但侧面来看Mysql至少平均查询耗时差不多)。
总体来说,Mysql选用B+树和MongoDB选用B-树还是以自己的需求来选择的。
文章题目:mysql怎么看b树 mysql用b+树
分享地址:http://scyanting.com/article/hiccdj.html