尽量减少访问磁盘io的次数是数据库设计和优化的重要原则之一；
每个索引在innodb里都是一颗B+树，一张表由多棵B+树组成，B+树很好的配合了磁盘的读写性能。并且索引文件和数据文件都是存储在磁盘上的。那么这些数据的存储格式是怎样的呢？
之前写过一篇
认识索引中了解了索引的使用，此篇再对原理进行一些补充学习记录。
1、数据逻辑存储结构
这里需要了解一下Innodb存储引擎的逻辑存储结构，其结构从大到小依次划分为：
表空间，
段(Segment)，
区也称为
簇(Extent)，
页(Page)也称为块
，行(Row)。其结构图如下：
1.1、结构数据与内存存储的关系
-
Innodb中一个 簇中有64个连续的页，大小为： 1M；
-
一个 页page的大小为：16K；InnoDB 存储引擎磁盘管理的最小单位，可以通过 innodb_page_size 设置；
-
如果一 行的数据大小为1kb，那么一页就可以存储16行数据记录；如果一个页面用完，就会产生一个新的页，一个簇用完就会分配一个新的簇；如果数据不是连续，往一个已经写满的页中继续写入数据，就会导致 页分裂；数据过少，删除的时候就会导致 页合并；
-
磁盘扇区：512Byte。（数据库访问一次磁盘大约为10ms）
-
一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个 表空间最多存储 64TB 的数据。
tips：在
文件系统中也有页的概念，和内存打交道，最小的单位也是
page,文件系统的页大小为：4K
磁盘寻址过程：每次访问磁盘的一个块时，磁臂就需移动到正确的磁道上（这段时间为寻址时间），然后盘片就需旋转到正确的扇区上（这叫旋转时延），这套动作需要时间，所以说顺序写比随机写性能高，大部分情况db的最大瓶颈在io；
问题: 表中设置主键 (not null primary key auto_increment
)的好处：
为什么不推荐使用订单号作为主键？而推荐使用自增的id做主键？
-
顺序插入（有业务逻辑的字段插入非有序，成本高)，追加操作，效率高，不用挪数据；
-
因为B+树是通过合并和分页来保证树的高度和平衡的，所以如果顺序递增的节点或者是不经常更新增加删除的节点就会 避免页分裂和合并，而分裂和合并这个操作是极其消耗性能的。
-
二级索引的叶子结点占用的空间小，节约空间；(例如：int和字符串的身份证号，显然int节约内存)
-
对于顺序递增的节点的优势：
-
顺序递增的索引添加新的索引节点的时候会顺序添加到当前节点索引的后续位置，一页写满自动开辟新页，形成一个紧凑的索引结构；
-
插入的效率高，不会增加很多开销在索引维护上；
-
-
对于随机无序的主键ID就会导致：
-
随机读写；
-
页分裂；
-
2、索引结构的选型
|
索引的结构的选型？
|
尽量减少访问资源的次数是数据库设计的重要原则之一；
|
|
数组
|
优点：
(1). 查询效率高O(1)；
(2). 空间连续可以利用cpu缓存加速；
缺点：插入和删除效率低下0(N)；
适用场景：静态存储引擎[不需要修改的数据]；
|
|
二叉查找树（本质链表）
|
缺点：
(1). 高度大，增加磁盘io次数;
(2). 单边倾斜；查找效率退化为：O(N)；
|
|
哈希表
|
数据结构：数组+链表的方式；
优点：等值查询的速度比较快O(1)；
缺点：
(1). 因为无序，不能做区间查询比较慢，时间复杂度O(N)；
(2). hash冲突造成性能下降；
适用场景：membercache，redis和noslq等一些存储引擎；
|
|
平衡二叉树
|
缺点：
(1). 一个结点只存放一个数据，浪费空间，导致高度太高，磁盘io次数多；
(2). 随机写
(3). 通过旋转来保持平衡，性能低下；
优点：查找效率高：O(logN)
|
|
多路平衡二叉树 -B树-(balance-Tree)
|
优点：
(1). 一个节点保存多个数据来降低树的高度；
(2). 通过分裂和合并来保证树的平衡；
(3). 查找效率：O(logN)
缺点：不能满足顺序查找；
|
|
B+树
|
优点：
(1). 磁盘io少：更矮胖，高度低；
(2). 排序能力强：叶子节点形成链表，支持范围查询；
(3). 性能稳定；数据放在叶子结点上：O(logN)；
(4). 扫库扫表能力强：顺序扫描叶子结点就OK；
缺点：通过分页和合并来保证树的平衡；
|
2.1、为啥选用B+树呢？
举个栗子:假设一条记录是 1K，一个叶子节点(一页)可以存储 16 条记录。非叶子节点可以存储多少个指针?
分析：假设索引字段是 bigint 类型，长度为 8 字节。数据库中页的大小为：16kb=16384byte，指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。
非叶子节点(一页)可以存储 16384/14=1170 个这样的 单元(键值+指针)，代表有 1170 个指针。
树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170*1170*16=21902400=2190w。
在查找数据时一次页的查找代表一次 IO，也就是说，
一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。
由此可见B+树突出的磁盘io性能，所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。
附加重要特性：范围查询
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数 据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
检索方式
：
它是根据左闭右开的
区间 [ )来检索数据
。
时刻注意：
数据库的设计原则之一就是尽量减少磁盘io，这也是sql优化的top1原则。
2.2、索引字段的选择：离散度
-
字段离散度低：扫描的行数就多，所以索引应该建立在离散度大的字段上；
-
字段离散度的计算：count(distinct（count(field) )):distinct(*)
极限思维：离散度极低，所有的数据都一样，索引首先是全表扫描整颗B+树，然后索引文件还浪费了存储空间；
3、有关sql的优化总结
索引的优点
-
(1). 减少访问磁盘的io次数，提高检索速度；
-
(2). 索引有序，避免外排序和使用临时表等；
-
(3). 可以将随机io变为顺序io；
3.1、常见优化的方向
mysql的性能的优化，遵照木桶原理，应该
从下往上，不重不漏，每一个环节都要考虑：
第一：
表结构设计、索引的设计和sql的层面优化；
-
参考 用好explain，它约等于优化器干的事情，让我们清楚sql的执行过程；
-
但毕竟优化器的优化有限，如果sql本身写的很烂，它也不可能优化的很好；
常见优化方案：
-
(1).磁盘io：减少数据访问（减少磁盘访问）
-
参考上节： 索引技巧的使用；
-
-
(2).内存：返回更少数据（减少网络传输或磁盘访问）
-
数据分页处理、只返回需要的字段；【 limit 1和limit 10000000,5 语句的优化】
-
-
(3).网络：减少交互次数（减少网络传输）
-
批量查询，只返回查询必要字段，避免select *；
-
-
(4).cpu：减少服务器CPU开销（减少CPU及内存开销）
-
合理使用排序，order by排序参考、减少比较操作、大量复杂运算在客户端处理；
-
第二：数据库的系统架构优化
-
主从使读写分离；
-
引入mq，消峰填谷，使请求排队异步处理减少瞬时压力；
-
使用本地缓存或者分布式redis缓存，减少数据库的连接；
-
分库分表；
第三：
配置优化：
-
连接数的优化，连接池中连接数的配置；
-
因为每一个连接都要开辟出一个新的线程来处理，线程多服务端的压力就大，线程上下文的切换，内存的消耗，通常线程池配置如下：
-
核心线程数配置： Ncpu核心 + 1；
-
最大线程数配置：2 * 核心线程；
-
第四：业务应用方案的优化。除来以上技术本身的优化，其实业务优化也很重要，根据业务特点。
这个其实很重要，业务的优化有时候会比技术优化本身带来的效益更多，而不是死磕技术。
-
预售分流： 提前双11，提前618活动：比如双十一的预售：双十一前一周就已经有商品预售了，不让流量集中在一天，减少数据库的压力；
-
分时分段：例如高并发的业务如秒杀，可以分时分段的进行，减少数据库的压力：
-
限流：令牌机制等；
4、结束
sql的优化核心：
减少数据库的磁盘io次数
。
乃至于整个系统的优化核心最终都是为了保证磁盘io次数尽量少；
而索引占着绝对的地位。
OK----壮心未与年俱老，死去犹能作鬼雄。
水滴石穿，积少成多。学习笔记，内容简单，用于复习，梳理巩固。
##参考资料，
《Innodb存储引擎》
《MySql实战详解》
扫一扫
3125
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
