理解mysql 底层原理
mysql 关系数据库的一种，开源免费，支持百万级的存储性能、性能稳定、社区活跃
鉴于 本人半路出家，对于一些原理的东西都不怎么了解，正好 无所事事的情况下，翻博客，吸取知识，然后 再总结出自己的一套理论。
mysql 常用存储引擎
innodb
InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 idb 是数据文件。
InnoDb中存在 表锁与行锁
InnoDB 支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读；而在 Oracle 数据库中，只支持串行化级别和读已提交这两种级别，其中默认的为读已提交级别 。
— InnoDB 中的 B+Tree
InnoDB 是以 ID 为索引的数据存储。采用 InnoDB 引擎的数据存储文件有两个，一个定义文件，一个是数据文
件。
InnoDB 通过 B+Tree 结构对 ID 建索引，然后在叶子节点中存储记录。
若建索引的字段不是主键 ID，则对该字段建索引，然后在叶子节点中存储的是该记录的主键，然后通过主键索引找到对应的记录。
myisam
Myisam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义文件，.MYD 是数据文件，.MYI 是索引文件。Myisam 只支持表锁，且不支持事务。Myisam 由于有单独的索引文件，在读取数据方面的性能很高 。
Myisam 中的 B+Tree：
Myisam 引擎也是采用的 B+Tree 结构来作为索引结构。由于 Myisam 中的索引和数据分别存放在不同的文件，所以在索引树中的叶子节点中存的数据是该索引对应的数据记录的地址，由于数据与索引不在一起，所以 Myisam 是非聚簇索引。
InnoDB 和 Myisam 都是用 B+Tree 来存储数据的，目前常用的Mysql引擎是InnoDB，原因在于它支持行级锁、支持事务。
MySQL 的数据、索引存储结构
介绍Mysql数据库数据的存储方式以及Mysql的索引村粗结构
A、访盘请求完成过程
硬盘在逻辑上被划分为磁道、柱面以及扇区。
1）确定磁盘地址（柱面号，磁头号，扇区号），内存地址（源 / 目）：当需要从磁盘读取数据的时候，系统会将数据的逻辑地址传递给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。
2）为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点：
A. 首先必须找到柱面，即磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间。
B. 然后目标扇区旋转到磁头下，即磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。
3）即一次访盘请求（读 / 写）完成过程由三个动作组成：
A. 寻道（时间）：磁头移动定位到指定磁道。
B. 旋转延迟（时间）：等待指定扇区从磁头下旋转经过。
C. 数据传输（时间）：数据在磁盘与内存之间的实际传输。
B、磁盘的读写原理
系统将文件存储到磁盘上时，按柱面、磁头、扇区的方式进行，即最先是第 1 磁道的第一磁头下的所有扇区，然后是同一柱面的下一个磁头……
一个柱面存储满后就推进到下一个柱面，直到把文件内容全部写入磁盘。系统也以相同的顺序读出数据，读出数据时通过告诉磁盘控制器要读出扇区所在柱面号、磁头号和扇区号（物理地址的三个组成部分）进行。
C、减少 I/O 的预读原理
磁盘读取文件，效率太低，因此需要减少访问磁盘IO的频率。
磁盘预读原理：
磁盘往往不是严格地按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
这样做的理论依据是计算机科学中著名的局部性原理：
- 当一个数据被用到时，其附近的数据一般来说也会被马上使用。
- 程序运行期间所需要的数据通常比较集中。
- 由于磁盘顺序读取的效率很高（不需要寻道时间，只需要很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 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/2k取整后>=1，即令n/2k=1， 可得k=log2n,（是以2为底，n的对数），所以时间复杂度可以表示O()=O(logn)
红黑树：
一种特殊的二叉查找树，红黑树的应用比较广泛，主要是用它来存储有序的数据，它的时间复杂度是O(lgn)，效率非常之高。
B树与B+树的底层:
B树也称B-树,它是一颗平衡多路查找树。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B-tree算法减少定位记录时所经历的中间过程，从而加快存取速度。
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关系，加索引不起作用。
复合索引和多个单列索引的效率比较：
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。
复合索引与单索引索引，到底孰优孰劣？结论如下：
对于具有2个用and连接条件的语句，且2个列之间的关联度较低的情况下，复合索引有一定优势。
对于具有2个用and连接条件的语句，且2个列之间的关联度较高的情况下，复合索引有很大优势。
对于具有2个用or连接条件的语句，单索引有一定优势，因为这种情况下复合索引将会导致全表扫描，而前者可以用到index merge的优化。
索引的建立的注意事项？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、根据当前查询字段返回对应的数据值4、mysql的索引分为聚簇索引和非聚簇索引
mysql中，不同的存储引擎对索引的实现方式不同，大致说下MyISAM（非聚簇索引）和InnoDB（聚簇索引）两种存储引擎。
聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。
非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。因此采用innoDB存储引擎的数据库，表一定要设置自增列作为主键，这样才能提高数据查询以及插入的效率。
聚簇索引不但在检索上可以大大滴提高效率，在数据读取上也一样。比如：需要查询f~t的所有单词。
一个使用MyISAM的主索引，一个使用InnoDB的聚簇索引。两种索引的B+Tree检索时间一样，但读取时却有了差异。
因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。
不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。
（3）Mysql调优
1、创建索引（恰当的添加索引）
如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
所以要建在合适的地方，合适的对象上。经常操作 / 比较 / 判断的字段应该建索引。2、适当的用复合索引代替单索引
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。3、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。4、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。5、like语句操作 （不鼓励模糊查询，会全表扫描）
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。6、不使用NOT IN和操作
NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替。7、开启查询缓存。
避免某些 SQL 函数直接在 SQL 语句中使用，从而导致 Mysql 缓存失效。
query_cache_type【0(OFF)1(ON)2(DEMAND)】来控制缓存的开关. 数据修改会带来缓存失效。8、表的设计
垂直分割表，使得固定表与变长表分割，从而降低表的复杂度和字段的数目。9、读写分离方案
海量数据的存储及访问，通过对数据库进行读写分离，来提升数据的处理能力, 数据库的写操作都集中到一个数据库上，而一些读的操作呢，可以分解到其它数据库上。
优点：得数据库的处理压力分解到多个数据库上，从而大大提升数据处理能力
缺点：付出数据复制的成本。10、缓存技术
搭建redis或者memcache做为缓存层，提高数据库读取速度。11、Mysql limit 分页机制和优化实例
300W数据，select XXX from tableA limit 1000000,10; 会导致mysql将1000000之前的所有数据全部扫描一次，大量浪费了时间。
解决办法：（1） 查询字段，加索引，可以建立与主键的复合索引
（2）limit最大的问题在于要扫描前面不必要的数据，所以可以先对主键的条件做设定，然后记录住主键的位置再取行。
select * from p2p_20131230 where main_id > 1000000 order by main_id limit 10;12、增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。（4）数据库的事务，四个性质说一下，分别有什么用，怎么实现的
ACID。
1、原子性；
操作要么全部发生，否则全部不发生。2、一致性；
在事务开始前和结束后，数据库的完整性约束没有被破坏3、隔离性；
隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。4、持久性（事务结束后，对数据库的更改不会回滚）
事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。事务的实现原理：
隔离性：通过锁来实现，讲解数据库的隔离性，就必须提到其脏读、不可重复读、幻读的概念，具体会在下面讲解到！！
原子性和持久性：通过redo log 来实现
一致性：通过undo来实现
Undo原理：（备份旧数据）
在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。Redo原理：（保存最新数据）
和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。（5）MySQL的并发控制与加锁分析
1、MVCC的设计目的是什么，怎么使用版本号判断数据的可见性
MVCC是一种多版本并发控制机制。锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。
人们一般把基于锁的并发控制机制称成为悲观机制，而把MVCC机制称为乐观机制。这是因为锁机制是一种预防性的，读会阻塞写，写也会阻塞读，当锁定粒度较大，时间较长时并发性能就不会太好；而MVCC是一种后验性的，读不阻塞写，写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，从而大大提升了并发性能。
MVCC的一种简单实现是基于CAS（Compare-and-swap）思想的有条件更新（Conditional Update）。普通的update参数只包含了一个keyValueSet’，Conditional Update在此基础上加上了一组更新条件conditionSet { … data[keyx]=valuex, … }，即只有在D满足更新条件的情况下才将数据更新为keyValueSet’；否则，返回错误信息。
2、隔离级别
在SQL的标准中，定义了四种隔离级别。每一种级别都规定了，在一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。低级别的隔离可以执行更高级别的并发，性能好，但是会出现脏读和幻读的现象。如果不考虑隔离性，事务存在3种并发访问问题 ：脏读（dirty read）：两个事务，一个事务读取到了另一个事务未提交的数据，这便是脏读。
不可重复读：一个事务中两次读取的数据的内容不一致幻读（phantom read）：两个事务，事务A与事务B，事务A在自己执行的过程中，执行了两次相同查询，第一次查询事务B未提交，第二次查询事务B已提交，从而造成两次查询结果不一样，这个其实被称为不可重复读；如果事务B是一个会影响查询结果的insert操作，则好像新多出来的行像幻觉一样，因此被称为幻读。其他事务的提交会影响在同一个事务中的重复查询结果。（一个事务中两次读取的数据的数量不一致 。因为另一个事务是insert操作）
下面简单描述一下SQL中定义的四种标准隔离级别：
READ UNCOMMITTED (未提交读) ：隔离级别：0. 哪个问题都不能解决
原理：
事务A和事务B，事务B可以读取事务A未提交的记录。会出现脏读，因为事务A可能会回滚操作，导致数据发生变化。
READ COMMITTED (提交读) ：隔离级别：1. 可以解决脏读 。
原理：
事务中只能看到已提交的修改，提交读这种隔离级别保证了读到的任何数据都是提交的数据，避免了脏读，但是不保证事务重新读的时候能读到相同的数据，因为在每次数据读完之后其他事务可以修改刚才读到的数据。
REPEATABLE READ (可重复读) ：隔离级别：2. 可以解决脏读和不可重复读，实现不幻读，需要加锁
原理：
在InnoDB中是这样的：RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，因此不存在幻读现象。但是标准的RR只能保证在同一事务中多次读取同样记录的结果是一致的，而无法解决幻读问题。InnoDB的幻读解决是依靠MVCC的实现机制做到的。Mysql默认的隔离级别是RR。
InnoDB的幻读解决是依靠MVCC的实现机制： （增加系统版本号，每次事务操作，会比较系统版本号）
InnoDB为每行记录添加了一个版本号（系统版本号），每当修改数据时，版本号加一。在读取事务开始时，系统会给事务一个当前版本号，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。
例如：
此时books表中有5条数据，版本号为1
事务A，系统版本号2：select * from books；因为1<=2所以此时会读取5条数据。
事务B，系统版本号3：insert into books ...，插入一条数据，新插入的数据版本号为3，而其他的数据的版本号仍然是2，插入完成之后commit，事务结束。
事务A，系统版本号2：再次select * from books；只能读取<=2的数据，事务B新插入的那条数据版本号为3，因此读不出来，解决了幻读的问题。
SERIALIZABLE （可串行化）：隔离级别：3.
原理：
该隔离级别会在读取的每一行数据上都加上锁，退化为基于锁的并发控制，即LBCC。可以解决脏读不可重复读和幻读—相当于锁表需要注意的是，MVCC只在RC和RR两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容。
3、死锁（需要继续补充）
死锁是指两个或者多个事务在同一资源上相互作用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。
死锁的关键在于：两个(或以上)的Session加锁的顺序不一致（6）慢查询
1、MySQL慢查询开启，语句分析
一、第一步.开启mysql慢查询
方式一:修改配置文件
在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）
方式二:通过MySQL数据库开启慢查询:
二、查看慢查询的数量
show global status like '%slow%';三、分析慢查询日志
直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句例如：执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000 得到如下结果：
显示结果分析：
table | type | possible_keys | key |key_len | ref | rows | ExtraEXPLAIN列的解释：
table 显示这一行的数据是关于哪张表的
type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
