innodb 索引文件过大

InnoDB和MyISAM存储引擎的区别

	InnoDB	MyISAM
事务	支持事务和外键	不支持事务和外键
锁	默认行锁，但是如果执行一个SQL不能确定扫描范围，则会锁表。比如：如果一个语句没有命中索引，则退化成表锁；没有用到索引，也会使用表锁。	表锁
全文索引	5.7开始支持全文索引	支持
索引类型	聚簇索引，顺序存储。数据文件本身就是主键索引，B+树中的key就是数据表的主键，叶子节点的data域存储着完整的数据记录。辅助（普通）索引的data域存储的是主键，如果查询的话除了查询辅助索引得到主键，还需要去主键索引查一次，这个过程成为回表。	非聚簇索引，随机存储。数据文件和索引文件分开存储，索引文件仅仅保存数据记录的地址，主键索引和辅助索引的结构一样，不一样的是主键索引要求key是唯一的，辅助索引可以重复。
行数	不保存行数，执行select count(*) from XXX需要全表扫描。因为InnoDB的事务特性，同一时间表的行数对于不同事务可能是不一样的。	用一个变量保存了整个表的行数
索引压缩	不压缩，体积大	压缩，体积小
存储文件	表定义文件：frm，数据文件：ibd	表定义文件：frm，数据文件：myd，索引文件：myi

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MySQL推荐主键自增的原因？

1. 索引本质是一个B+树，一个表有多少索引就有多少个B+树，数据按顺序保存在B+树上。

2. MySQL底层是以“数据页”为单位存储数据的，一个数据页默认大小16k，一个数据页写满了，才会去申请下一个数据页。

3. 如果自增ID的话，写满一个数据页，直接申请并写入下一个数据页就行了；如果不规则ID呢，写满之后申请数据页，还要把上个数据页的部分数据移动到新的数据页上，造成了B+树频繁的分裂合并，效率低下。

数据库的四种隔离级别

• 脏读：一个事务读到另一个事务未提交的数据
• 不可重复读：一个事务读到另一个事务已提交的更新的数据。【多次读取数据内容不一致】
• 幻读：一个事务读到另一个事务已提交的新插入的数据。【多次读取数据数量不一致】

⑴ 读未提交
在这种隔离级别下，查询不加锁，会产生“脏读”、“不可重复读”、“幻读”。

⑵ 读已提交
只能读到已经提交的内容。查询不加锁，能够避免脏读。可能会产生“不可重复读”、“幻读”。

⑶ 可重复读
重复读取，查询不加锁，但在数据读出来之后加锁。可能会产生“幻读”。

⑷ 串行化
在该级别下，事务顺序执行，不仅避免了脏读，不可重复读，而且避免了幻读。由于加锁，导致大量的请求超时，因此性能会比较低下。

MVCC（Multi-Version Concurrency Control）

MVCC，即多版本并发控制，这个词太高大上了。它是数据库用来处理读写冲突的手段，目的在于提高数据库在高并发场景下的吞吐性能。

人们一般把基于锁（如行级锁）的并发控制机制成为悲观机制，把MVCC机制称为乐观机制。

MVCC能在大多数情况下替代行级锁，降低系统性能开销，但是只在 【读已提交】 和 【可重复读】两个隔离级别下工作。

第一，【读未提交】会读取到未提交的数据，而MVCC创建和删除版本发生在事务提交之后。第二，【串行化】是对所有操作都加锁，没有MVCC的事情了。

为什么说它能提高吞吐性能呢？

1. 最初的锁，只要锁住一个共享资源，其他人是禁止访问的。

2. 而最常见的场景则是读多写少的，进而出现了读写锁，读锁和读锁之间不互斥，写锁和读锁、写锁都互斥。

3. 后来又提出，能不能让读写并行呢？于是出现了MVCC：读的时候可以通过快照读，读多个版本避免了使用共享锁，因此可以使得读事务和写事务互不干扰。

MVCC实现

InnoDB每行数据有三个隐藏域：（注意《高性能MySQL》里说的两个隐藏域是误人子弟）

InnoDB adds three fields to each row stored in the database. A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byteDB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated. A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted. If InnoDB generates a clustered index automatically, the index contains row ID values. Otherwise, the DB_ROW_ID column does not appear in any index.

（6字节）DB_TRX_ID	行事务id，在上次事务INSERT或UPDATE之后添加，也就是所谓的“版本号”
（7字节）DB_ROLL_PTR	回滚指针，指向undo log中的一个版本
（6字节）DB_ROW_ID	随着新行插入而单调递增的行ID，当表没有主键或唯一索引时，InnoDB就会使用这个行ID自动产生聚簇索引

 

 

 

 

 

 

一致性读取：

一致性读取意味着InnoDB使用多版本来控制在某个时间点向查询操作展示的数据库快照。查询操作只能看到在此时间点之前提交的事务所做的更改，看不见之后或未提交的事务所做的更改。一致性读取不会在它访问的表上设置任何锁，因此其他会话可以在对该表执行一致读取的同时自由地修改那些表。

使用 快照信息可基于某个时间点显示查询结果，而不管同时运行的其他事务执行的更改如何。

1. 对于隔离级别为【读已提交】，同一事务中，每个一致性读取都将设置并读取其自己的新快照。还记得这个隔离级别会出现【多次读取数据内容不一致】的问题吗？就是因为该级别下的MVCC每次读取都产生一个新的快照。

2. 对于隔离级别为【可重复读】，同一事务中的一致性读取将读取第一次读取建立的快照。这解释了可重复读，因为多次读取按照第一次读取生成的版本为准。

3. 另外，只有普通的查询【SELECT】才会产生快照并执行快照读，比如【SELECT ... FOR UPDATE】则是加锁，即当前读。

也就是说，MVCC依赖于三个隐藏域和事务查询操作产生的快照。

等等！！查询也涉及事务吗？？没错，在【可重复读】隔离级别是由MVCC+lock实现的，比如有范围的查询会加上间隙锁，更多参考：https://www.zhihu.com/question/266467051

MVCC更新原理：

1. 先用【排它锁】锁住目标数据行 A

2. 将当前数据保存到undo log 中，生成旧版本数据 B

3. 将数据行A的【回滚指针】字段指向数据B，然后更新数据

4. 提交事务

5. 如果失败则回滚undo log中的数据

MVCC读取原理：

1. 对于【读已提交】，每次读取都会取最新的已提交的那个版本。

2. 对于【可重复读】，总是取小于当前活动事务ID的那个版本。

redo log，undo log，bin log

redo log：重做日志，当事务提交之后会把所有修改信息都会存到该日志中，并且会把redo log持久化到磁盘，在崩溃恢复期间用于纠正不完整事务写入的数据。

undo log： 回滚日志，用于记录数据被修改前的信息。由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到被修改前的状态。

bin log：记录了对数据库执行更改的所有操作，不包括查询操作。用来进行数据恢复、数据库复制。

区别：

1. redo是物理日志，记录的是页的物理修改操作；undo是逻辑日志，根据每行记录进行记录。

2. redo用来保证事务的持久性；undo用来保证事务的原子性，帮助事务回滚。

3. redo是顺序写的，undo是随机读写的。

回过头来看事务特性，所有技术方案都是为了解决问题的。

1. 对于原子性，如果一个组合操作永远100%成功，那么原子性就失去了意义，所以原子性肯定主要是为了异常回滚而出现的，所以这也是undo log的意义所在

2. 对于持久性，由于事务提交之后就被写到redo log并持久化到磁盘，所以redo log 保证了持久性。

3. 对于隔离性，如何避免资源争抢呢？最好的方法就是同一时间只能有一人获得或者一人一份，加锁和MVCC多版本并发控制机制正是保证了这一点。

4. 对于一致性，也就是从一个一致的状态到另一个一致的状态，不存在中间状态，那么原子性+持久性+隔离性是不是等于一致性呢？

所以，数据库的事务是基于：undo log + redo log + 锁 + MVCC 实现的。

3种行锁算法

Record Lock：单个行记录上的锁，实际上是锁住索引记录

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身

Next-Key Lock：前面二者的组合，锁定一个范围，包括记录本身

InnoDB默认的隔离级别是【可重复读】，在此隔离级别下，用Next-Key Lock的方式来加锁。而在【读已提交】的隔离级别下，用的是Record Lock。

 

count(*)、count(1)、count(列)的区别

网上说1比*的效率高，实践出真知，我有个250w的表，18个字段，来比较一下：

===第一次===

mysql> select count(*) from send_code;
+----------+
| count(*) |
+----------+
|  2560454 |
+----------+
1 row in set (0.49 sec)

mysql> select count(1) from send_code;
+----------+
| count(1) |
+----------+
|  2560454 |
+----------+
1 row in set (3.60 sec)

mysql> select count(id) from send_code;
+-----------+
| count(id) |
+-----------+
|   2560454 |
+-----------+
1 row in set (3.59 sec)

===第二次===

mysql> select count(*) from send_code;
+----------+
| count(*) |
+----------+
|  2560454 |
+----------+
1 row in set (0.54 sec)

mysql> select count(1) from send_code;
+----------+
| count(1) |
+----------+
|  2560454 |
+----------+
1 row in set (4.31 sec)

mysql> select count(id) from send_code;
+-----------+
| count(id) |
+-----------+
|   2560454 |
+-----------+
1 row in set (5.28 sec)

===第三次===

mysql> select count(*) from send_code;
+----------+
| count(*) |
+----------+
|  2560454 |
+----------+
1 row in set (4.28 sec)

mysql> select count(1) from send_code;
+----------+
| count(1) |
+----------+
|  2560454 |
+----------+
1 row in set (4.16 sec)

mysql> select count(id) from send_code;
+-----------+
| count(id) |
+-----------+
|   2560454 |
+-----------+
1 row in set (0.51 sec)

===第四次===

mysql> select count(*) from send_code;
+----------+
| count(*) |
+----------+
|  2560454 |
+----------+
1 row in set (3.96 sec)

mysql> select count(1) from send_code;
+----------+
| count(1) |
+----------+
|  2560454 |
+----------+
1 row in set (1.23 sec)

mysql> select count(id) from send_code;
+-----------+
| count(id) |
+-----------+
|   2560454 |
+-----------+
1 row in set (3.92 sec)

...

我再多查询也没有个标准答案，他们之间按照情况来看好像没有谁一定快的。explain看一下：

mysql> explain select count(*) from fcbox_send_code;
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key           | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
|  1 | SIMPLE      | send_code | index | NULL          | idx_send_code | 33      | NULL | 2484113 | Using index |
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
1 row in set (0.03 sec)

mysql> explain select count(1) from fcbox_send_code;
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key           | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
|  1 | SIMPLE      | send_code | index | NULL          | idx_send_code | 33      | NULL | 2484113 | Using index |
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
1 row in set (0.03 sec)

mysql> explain select count(id) from fcbox_send_code;
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key           | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
|  1 | SIMPLE      | send_code | index | NULL          | idx_send_code | 33      | NULL | 2484113 | Using index |
+----+-------------+-----------+-------+---------------+---------------+---------+------+---------+-------------+
1 row in set (0.03 sec)

全部用到了索引，扫描的行数也一样，我是不是可以说，这三种count在效率方面没有太大的区别吗？

然后我又测了一下连续的执行同一个查询，发现执行速度是越来越快的，可能是利用了缓存吧，但是并不能说明count(*)效率差、count(1)、count(id)效率高。

建立索引的几大原则

1. 最左前缀原则

MySQL会一直向右匹配，遇到范围查询(>、<、between、like)就停止；建议查询频率高的放在最左边。

比如a = 1 and b = 2 and c > 3 and d = 4

• 如果建立(a,b,c,d)顺序的索引，d是用不到索引的
• 如果建立(a,b,d,c)的索引则都可以用到，(a,b,d)的顺序可以任意调整（建立索引时他们三个的顺序）

2. =和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式

3. 选择区分度高的列作为索引

区分度的公式是count(distinct 列)/count(*)，表示字段不重复的比例

4. 作为索引的列不能是函数的参数或者表达式的一部分

比如select * from user where id + 1 = 1000;

 

关于最左前缀和B+树的联系

如果建立了一个(col1,col2,col3)的联合索引，则B+树会以col1作为关键字建立索引结构，然后在叶子节点上，按照字典顺序对联合索引进行排序。

类似这种结构

 

1. 先按照第一个关键字建立索引

2. 叶子节点按照字典排序

字典顺序和自然顺序

自然顺序：

1

2

10

20

字典顺序：

1

10

2

20

 

小LUA 面对敌人的严刑逼供，我一个字也没说，而是一五一十写了下来。