基于Mysql数据库原理特性以及底层机制的深⼊研究理解
Mysql作为关系型数据库的⼀种，它的开源免费特性以及⽀持百万级存储性能，备受互联⽹公司的喜爱，我个⼈在做研究⽣的项⽬以及去互联⽹公司实习的期间，⼤部分接触的也都是基于Mysql作为底层数据的存储，CRUD⽤的⽐较多，稍微复杂⼀点就是多条查询，各种内外连接以及group by操作，对于Mysql数据库原理特性以及底层机制的研究不够深⼊，因此，本篇⽂章主要是研究Mysql原理特性。（⽂章中截图来⾃于⽹上的好多博客）
（1）Mysql常⽤的引擎
1. InnoDB
InnoDB 的存储⽂件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义⽂件，⽽ idb 是数据⽂件。
InnoDB 中存在表锁和⾏锁，不过⾏锁是在命中索引的情况下才会起作⽤。
InnoDB ⽀持事务，且⽀持四种隔离级别（读未提交、读已提交、可重复读、串⾏化），默认的为可重复读；⽽在 Oracle 数据库中，只⽀持串⾏化级别和读已提交这两种级别，其中默认的为读已提交级别。
InnoDB 中的 B+Tree
InnoDB 是以 ID 为索引的数据存储。采⽤ InnoDB 引擎的数据存储⽂件有两个，⼀个定义⽂件，⼀个是数据⽂件。
InnoDB 通过 B+Tree 结构对 ID 建索引，然后在叶⼦节点中存储记录。
若建索引的字段不是主键 ID，则对该字段建索引，然后在叶⼦节点中存储的是该记录的主键，然后通过主键索引到对应的记录。
2. Myisam
Myisam 的存储⽂件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义⽂件，.MYD 是数据⽂件，.MYI 是索引⽂件。Myisam 只⽀持表锁，且不⽀持事务。Myisam 由于有单独的索引⽂件，在读取数据⽅⾯的性能很⾼ 。
Myisam 中的 B+Tree：
Myisam 引擎也是采⽤的 B+Tree 结构来作为索引结构。由于 Myisam 中的索引和数据分别存放在不同的⽂件，所以在索引树中的叶⼦节点中存的数据是该索引对应的数据记录的地址，由于数据与索引不在⼀起，所以 Myisam 是⾮聚簇索引。
InnoDB 和 Myisam 都是⽤ B+Tree 来存储数据的，⽬前常⽤的Mysql引擎是InnoDB，原因在于它⽀持⾏级锁、⽀持事务。
（2）MySQL 的数据、索引存储结构
说到Mysql的数据、索引存储结构，就得先介绍Mysql数据库数据的存储⽅式以及Mysql的索引了。
1、Mysql数据库数据存储的原理
数据库数据都是以磁盘⽂件的⽅式存储到系统当中的，存储⽅式如下：信息存储在硬盘⾥，硬盘是由很多的盘⽚组成，通过盘⽚表⾯的磁性物质来存储数据。
A、访盘请求完成过程
硬盘在逻辑上被划分为磁道、柱⾯以及扇区。
1）确定磁盘地址（柱⾯号，磁头号，扇区号），内存地址（源 / ⽬）：当需要从磁盘读取数据的时候，系统会将数据的逻辑地址传递个磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。
2）为了读取这个扇区的数据，需要将磁头放到这个扇区上⽅，为了实现这⼀点：
A. ⾸先必须到柱⾯，即磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间。
B. 然后⽬标扇区旋转到磁头下，即磁盘旋转将⽬标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。
3）即⼀次访盘请求（读 / 写）完成过程由三个动作组成：
A. 寻道（时间）：磁头移动定位到指定磁道。
B. 旋转延迟（时间）：等待指定扇区从磁头下旋转经过。
C. 数据传输（时间）：数据在磁盘与内存之间的实际传输。
B、磁盘的读写原理
系统将⽂件存储到磁盘上时，按柱⾯、磁头、扇区的⽅式进⾏，即最先是第 1 磁道的第⼀磁头下的所有扇区，然后是同⼀柱⾯的下⼀个磁头……
⼀个柱⾯存储满后就推进到下⼀个柱⾯，直到把⽂件内容全部写⼊磁盘。系统也以相同的顺序读出数
据，读出数据时通过告诉磁盘控制器要读出扇区所在柱⾯号、磁头号和扇区号（物理地址的三个组成部分）进⾏。
C、减少 I/O 的预读原理
磁盘读取⽂件，效率太低，因此需要减少访问磁盘IO的频率。
磁盘预读原理：
磁盘往往不是严格地按需读取，⽽是每次都会预读，即使只需要⼀个字节，磁盘也会从这个位置开始，顺序向后读取⼀定长度的数据放⼊内存。
这样做的理论依据是计算机科学中著名的局部性原理：
1. 当⼀个数据被⽤到时，其附近的数据⼀般来说也会被马上使⽤。
2. 程序运⾏期间所需要的数据通常⽐较集中。
3. 由于磁盘顺序读取的效率很⾼（不需要寻道时间，只需要很少的旋转时间），因此对于具有局部性的程序来说，预读可以提⾼ I/O 效
率。
预读的长度⼀般为页（Page）的整数倍。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储分割为连续的⼤⼩相等的块。
每个存储块称为⼀页（在许多操作系统中，页的⼤⼩通常为 4k），主存和磁盘以页为单位交换数据，当程序要读取的数据不在主存中时，会触发⼀个缺页异常。
此时系统会向磁盘发出读盘信息，磁盘会到数据的起始位置并向后连续读取⼀页或⼏页的数据载⼊内存中，然后异常返回，程序继续运⾏。
2、Mysql索引
索引是⼀种⽤来实现 MySQL ⾼效获取数据的数据结构。我们通常所说的在某个字段上建索引，意思就是让 MySQL 对该字段以索引这种数据结构来存储，然后查的时候就有对应的查算法。
建⽴索引的原因：
为了查的优化，特别是当数据很庞⼤的时候，采⽤特殊的查算法，可以实现数据的⾼效快速查询。
特殊查算法：
Mysql数据库索引采⽤的数据结构是B tree和B+ tree。
为什么要采⽤B tree和B+ tree？
⼀般的查算法有顺序查、折半查、快速查等，但是每种查算法都只能应⽤于特定的数据结构之上，例如顺序查依赖于顺序结构，折半查通过⼆叉查树或红⿊树实现⼆分搜索。这样的索引数据结构还是会对数据库的数据结构有要求，⽽且对磁盘IO的操作依旧很频繁。因此采⽤了B树和B+ 树.
注：⼆分查算法的时间复杂度计算：
⽐如：总共有n个元素，每次查的区间⼤⼩就是n，n/2，n/4，…，n/2^k（接下来操作元素的剩余个数），其中k就是循环的次数。 由于n/2^k取整后>=1，即令n/2^k=1， 可得k=log2n,（是以2为底，n的对数），所以时间复杂度可以表⽰O()=O(logn)
红⿊树：
⼀种特殊的⼆叉查树，红⿊树的应⽤⽐较⼴泛，主要是⽤它来存储有序的数据，它的时间复杂度是O(lgn)，效率⾮常之⾼。
B树与B+树的底层:
B树也称B-树，它是⼀颗平衡多路查树。⼀般来说，索引本⾝也很⼤，不可能全部存储在内存中，因此索引往往以索引⽂件的形式存储的磁盘上。这样的话，索引查过程中就要产⽣磁盘I/O消耗。B-tree算法减少定位记录时所经历的中间过程，从⽽加快存取速度。
B树和B+树的原理在下⼀章节单独讲解。
3、索引相关问题
1、索引的创建
创建索引：
在执⾏CREATE TABLE语句时可以创建索引，也可以单独⽤CREATE INDEX或ALTER TABLE来为表增加索引。
普通索引、UNIQUE索引或PRIMARY KEY索引区别：
如果不允许重复值，则使⽤UNIQUE索引或PRIMARY KEY索引，否则⽤普通索引，另外PRIMARY KEY索引是主键索引，⼀张表只有⼀个字段是PRIMARY KEY索引。
单字段索引创建：
1．ALTER TABLE
ALTER TABLE⽤来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
2．CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
单字段索引删除：
DROP INDEX index_name ON talbe_name
复合索引创建：
ALTER TABLE myIndex ADD INDEX name_city_age (Name(10),City,Age);    为什么是Name(10)？  ⼀般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引⽂件的⼤⼩，提⾼ INSERT 的更新速度。
Mysql复合索引符合最左原则：
对于复合索引:Mysql从左到右的使⽤索引中的字段，⼀个查询可以只使⽤索引中的⼀部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以⽀持a | a,b| a,b,c 3种组合进⾏查，但不⽀持 b,c进⾏查 .当最左侧字段是常量引⽤时，索引就⼗分有效。因此我们在创建复合索引时应该将最常⽤作限制条件的列放在最左边，依次递减。
2、哪些字段适合作为索引？
注明：有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引;    设计索引的⼀个重要原则就是能⽤窄索引不⽤宽索引，因为窄索引往往⽐组合索引更有效;
何时⽤单字段索引：
何时是⽤复合索引：
根据where条件建索引是极其重要的⼀个原则，复合索引的⼏个字段是否经常同时以AND⽅式出现在Where⼦句中？单字段
查询是否极少甚⾄没有？如果是，则可以建⽴复合索引；否则考虑单字段索引；如果where条件中是OR关系，加索引不起作⽤。
mysql删除重复的数据保留一条复合索引和多个单列索引的效率⽐较：
⽐如有⼀条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使⽤⼀个索引，所以虽然
这样已经相对不做索引时全
表扫描提⾼了很多效率，但是如果在area、age两列上创建复合索引的话将带来更⾼的效率。如果我们创建了(area, age,salary)的复合索
引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。
复合索引与单索引索引，到底孰优孰劣？结论如下：
1. 对于具有2个⽤and连接条件的语句，且2个列之间的关联度较低的情况下，复合索引有⼀定优势。
2. 对于具有2个⽤and连接条件的语句，且2个列之间的关联度较⾼的情况下，复合索引有很⼤优势。
3. 对于具有2个⽤or连接条件的语句，单索引有⼀定优势，因为这种情况下复合索引将会导致全表扫描，⽽前者可以⽤到index merge
的优化。
4.
索引的建⽴的注意事项？
1、频繁进⾏数据操作（insert、update、delete）的表，不要建⽴太多的索引；
2、删除⽆⽤的索引，避免对执⾏计划造成负⾯影响；
以上是⼀些普遍的建⽴索引时的判断依据。⼀⾔以蔽之，索引的建⽴必须慎重，对每个索引的必要性都应该经过仔细分析，要有建⽴的依据。因为太多的索引与不充
3、Mysql索引命中规则
最左匹配原则
1、先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。
2、等值的条件去命中索引最左边的⼀个字段，然后依次从左往右命中，范围的放在最后。
⼀条sql语句要执⾏完成需要经历什么样的过程
当⼀条sql语句提交给mysql数据库进⾏查询的时候需要经历以下⼏步
1、先在where解析这⼀步把当前的查询语句中的查询条件分解成每⼀个独⽴的条件单元
2、mysql会⾃动将sql拆分重组
3、然后where条件会在B-tree index这部分进⾏索引匹配，如果命中索引，就会定位到指定的table records位置。如果没有
命中，则只能采⽤全部扫描的⽅式
4、根据当前查询字段返回对应的数据值

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。