mysql-Innodb解析

一.计算机不同介质操作速度

相对于CPU和内存操作， 我们可以看到磁盘的操作延时明显要大得多， 一次磁盘搜索的延时需要10ms。 假入我们某一个业务操作进行了大量磁盘读写， 那可以预料到这个服务的性能肯定是非常差的， 那么到底是什么原因导致磁盘读写速度这么慢呢？ 对于机械磁盘来说（这里先抛开SSD）， 它的速度主要依赖转动磁盘和移动磁头的时间， 这是一种机械运动； 而CPU和内存（RAM）主要是依赖电信号， 这两者肯定不是在同一数量级的。 

磁盘io远远慢于内存操作和cpu操作，记住这个知识点。

二.mysql操作简析

1.行数据深度剖析

HAR(M)列的存储格式

对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。 另外有一点还需要注意，变长字符集的CHAR(M)类型的列要求至少占用M个字节，而VARCHAR(M)却没有这个要求。比方说对于使用utf8字符集的CHAR(10)的列来说，该列存储的数据字节长度的范围是10～30个字节，即使我们向该列中存储一个空字符串也会占用10个字节。

行溢出数据

VARCHAR(M)最多能存储的数据

MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。可以不严谨的认为，mysql一行记录占用的存储空间不能超过65535个字节。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

1. 真实数据

2. 真实数据占用字节的长度

3. NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间

假设varchar_size_demo只有一个VARCHAR类型的字段，那么该字段最大占用的65532个字节。因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节。如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据。如果该列是ascii字符集，对应的最大字符数最大为65532；如果是utf8字符集，则对应的最大字符数为21844。记录中的数据太多产生的溢出。

2.数据查询操作

通过Page Directory在一个数据页中查找指定主键值的记录的过程分为两步：

1. 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。

2. 通过记录的next_record属性遍历该槽所在的组中的各个记录。

3.数据插入操作

当提交一个事务时,实际上它干了如下2件事:

一:  InnoDB存储引擎把事务写入日志缓冲(log buffer),日志缓冲把事务刷新到事务日志.

二:  InnoDB存储引擎把事务写入缓冲池(Buffer pool).

做完上面2件事,整个事务提交就完成了.

InnoDB通过事务日志把随机IO变成顺序IO,这大大的提高了InnoDB写入时的性能。

因为把缓冲池的脏页数据刷新到磁盘可能会涉及大量随机IO,这些随机IO会非常慢,通过事务日志,避开随机IO,用顺序IO替代它.

但如果此时机器断电或者意外崩溃,那脏页数据没刷新到磁盘,岂不是数据会丢失? 

答案是否定的, mysql意外崩溃后,重启时.会根据事务日志重做事务,恢复所有buffer pool中丢失的脏页.

上面的过程是在未开启binlog的情况下的执行过程。

三.索引是什么（mylsam和innodb）

索引（Index）是帮助MySQL高效获取数据的数据结构，相当于数据的目录

分别是MyISAM搜索引擎和InnoDB搜索引擎,我们经常用到的是InnoDB搜索引擎

《高性能mysql》如此评价两个引擎：

InnoDB：MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。它被设计成为大量的短期事务，短期事务大部分情况下是正常提交的，很少被回滚。InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

MyISAM：在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。正是由于MyISAM引擎的缘故，即使MySQL支持事务已经很长时间了，在很多人的概念中MySQL还是非事务型数据库。尽管这样，它并不是一无是处的。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以使用MyISAM（但请不要默认使用MyISAM，而是应该默认使用InnoDB）

附上两个引擎的对比图：

四.Mysql 索引底层数据结构选型

1.哈希表（Hash）

哈希算法：也叫散列算法，就是把任意值(key)通过哈希函数变换为固定长度的 key 地址，通过这个地址进行具体数据的数据结构。

从算法时间复杂度分析来看，哈希算法时间复杂度为 O（1），检索速度非常快。使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

 2.二叉查找树(BST)

二叉查找树的时间复杂度是 O(lgn)，而且也可以很方便的支持范围查找。

但是二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。比如以下这个情况，二叉树已经极度不平衡了，已经退化为链表了，检索速度大大降低。

3.红黑树

红黑树并不是完全平衡的二叉树

数据是顺序插入时，树的形态一直处于“右倾”的趋势呢？从根本上上看，红黑树并没有完全解决二叉查找树的右倾问题。

虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张

4.AVL 树

因为 AVL 树是个绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

不错的查找性能（O（logn）），不存在极端的低效查找的情况,可以实现范围查找、数据排序。

数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=8 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

5.B树

 

B数单个树节点存储的数据个数是可以改变的，如上图，树节点最大存储2个数据和最大存储5个数据的对比图。

调大树节点存储key数，可以使树层级变低，进而减少查找时的磁盘io次数，加速检索。前图查找7数据需要三次磁盘io,后图查找7数据需要两次磁盘io。随着数据插入量增大，对比之下检索的效率会逐步提高。

所以数据库索引数据结构的选型而言，B 树是一个很不错的选择。总结来说，B 树用作数据库索引有以下优点：

1. 优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；

2. 尽可能少的磁盘 IO，加快了检索速度；

3. 可以支持范围查找。

6.B+树

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。

其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。

因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

五.innodb索引分类

【索引类型】

单列索引：单列索引有三种，包括普通索引、唯一索引、主键索引

组合索引

全文索引（不介绍，感兴趣的同学，可以自己调研下）

空间索引（不介绍，感兴趣的同学，可以自己调研下）

上文中已经讲述了Mysql底层索引数据结构使用的是B+树，那我们看一下innodb是如何利用这个B+树实现索引构建的。

宏观图：

InnoDB数据引擎使用B+树构造索引结构，其中的索引类型依据参与检索的字段不同可以分为主索引和非主索引；依据B+树叶子节点上真实数据的组织情况又可以分为聚族索引和非聚族索引。每一个索引B+树结构都会有一个独立的存储区域来存放，并且在需要进行检索时将这个结构加载到内存区域。真实情况是InnoDB引擎会加载索引B+树结构到内存的Buffer Pool区域。

1.主索引（主键索引/一级索引/聚簇索引）

数据全部存在主索引叶子节点中。（如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。上文有提到过）

注：主索引在数据表的索引列表中使用PRIMARY关键字进行标识，一般来说是数据表的主键字段（也有可能是复合主键）。如果开发人员删除了InnoDB引擎中某张数据表的主索引，那么这个数据表将自行寻找一个非空且带有唯一约束的字段作为主索引。如果还是没有找到那样的字段**，InnoDB引擎将使用一个隐含字段作为主索引（ROWID）**。

2.非主索引（辅助索引/二级索引/非聚簇索引）

普通单列索引

如上图user_name为索引

建立的非主索引也会单独构建一棵树，只不过他们的叶子节点存储的不是全数据，而是主键值，如果命中索引，会通过查到主键值，进行回表查询，进而从主键索引中获取查询的字段。当然如果查询的字段就在辅助索引上，那么是不需要回表的。

联合索引

• 对于联合索引来说只不过比单值索引多了几列。

• 联合索引的所有索引列都出现在索引树上，并依次顺序比较几个列的大小。

• InnoDB引擎会首先根据第一个索引列“单调递增”排序，如果第一列相等则再根据第二列排序，依次类推。

• 联合索引遵循左匹配原则。

六.索引命中

1.索引操作

-- 0.查询表含有的索引
show INDEX from happy_test;
 
-- 1.添加PRIMARY KEY（主键索引） 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) ;
-- 2.添加UNIQUE(唯一索引) 
ALTER TABLE `table_name` ADD UNIQUE (`column`);
-- 3.添加INDEX(普通索引) 
ALTER TABLE `table_name` ADD INDEX index_name (`column`);
-- 4.添加FULLTEXT(全文索引) 
ALTER TABLE `table_name` ADD FULLTEXT (`column`);
-- 5.添加多列索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);
 
--6.删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
 
--7.索引发现
EXPLAIN select * from happy_test where status = '1';

1.创建happy_test时只添加了主键索引

2.使用了主键索引

3.status没有索引，所以没有使用上索引。

4.为status列添加普通索引

添加后发现使用了索引

2.一些建议

1）没有查询条件；或者查询条件没有建立索引 ；在查询条件上没有使用引导列；查询的数量是大表的大部分，应该是30％以上； 索引本身失效；对小表查询 。

2）查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 

3）如果条件中有 or ，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因）

注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引 如果出现OR的一个条件没有索引时，建议使用 union ，拼接多个查询语句 4）like查询是以%开头，索引不会命中 只有一种情况下，只查询索引覆盖索引列，才会用到索引，但是这种情况下跟是否使用%没有关系的，因为查询索引列的时候本身就用到了索引 5） 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引 6）没有查询条件，或者查询条件没有建立索引 7）查询条件中，在索引列上使用函数（+, - ,*,/）, 这种情况下需建立函数索引 8）采用 not in, not exist 不会命中 9）B-tree 索引 is null 不会走， is not null 会走。

10）全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快，则不使用索引。

七.索引建立优化

1.一些建议

2.建议描述

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引

9) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

10) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

附（sql使用优化）

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。          2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：     select id from t where num is null     可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：     select id from t where num=0          3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。          4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：     select id from t where num=10 or num=20     可以这样查询：     select id from t where num=10     union all     select id from t where num=20          5.in 和 not in 也要慎用，否则会导致全表扫描，如：     select id from t where num in(1,2,3)     对于连续的数值，能用 between 就不要用 in 了：     select id from t where num between 1 and 3          6.下面的查询也将导致全表扫描：     select id from t where name like '%abc%'          7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：     select id from t where num/2=100     应改为:     select id from t where num=100*2          8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：     select id from t where substring(name,1,3)='abc'--name以abc开头的id     应改为:     select id from t where name like 'abc%'          9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。          10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。          11.不要写一些没有意义的查询，如需要生成一个空表结构：     select col1,col2 into #t from t where 1=0     这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：     create table #t(...)          12.很多时候用 exists 代替 in 是一个好的选择：     select num from a where num in(select num from b)     用下面的语句替换：     select num from a where exists(select 1 from b where num=a.num)          13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。          14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，     因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。     一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。          15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。     这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。          16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，     其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。          17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。          18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。          20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，     以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。          22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。          23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。 在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25.尽量避免大事务操作，提高系统并发能力。

26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。