云时代架构阅读笔记十四——通过MySQL存储原理来分析排序和锁

小灰灰 2021-11-01 16:56 224阅读 0赞

先提出几个问题：

1、为什么不建议使用订单号作为主键？

2、为什么要在需要排序的字段上加索引?

3、For update的记录不存在会导致锁住全表?

4、redolog和binlog 有什么区别?

5、MySQL如何回滚一条sql?

6、char(50)和varchar(50) 效果是一样的么?

索引知识回顾：

对于 MySQL 数据库而言,数据是存储在文件里的，而为了能够快速定位到某张表里的某条记录进行查询和修改,我们需要将这些数据以一定的数据结构进行存储，这个数据结构就是我们说的索引。回忆一下我们大学里学过的算法与数据结构，能够支持快速查找的数据结构有：顺序数组、哈希、搜索树。

数组要求插入的时候保证有序，这样查找的时候可以利用二分查找法达到　O(log(N))　的时间复杂度，对范围查询支持也很好，但是插入的时候如果不是在数组尾部，就需要摞动后面所有的数据，时间复杂度为　O(N)　。所以有序数组只适合存储静态数据，例如几乎很少变动的配置数据，或者是历史数据。这里应该会有人有疑问：我用另外一种线性数据结构链表来替代数组不就可以解决数组插入因为要移动数据导致太慢的问题了么，要回答这个问题我们需要了解操作系统读取文件的流程，磁盘 IO 是一个相对很慢的操作，为了提高读取速度，我们应该尽量减少磁盘 IO 操作，而操作系统一般以　４kb 为一个数据页读取数据，而 MySQL 一般为 16kb 作为一个数据块，已经读取的数据块会在内存进行缓存，如果多次数据读取在同一个数据块，则只需要一次磁盘 IO ，而如果顺序一致的记录在文件中也是顺序存储的，就可以一次读取多个数据块，这样范围查询的速度也可以大大提升，显然链表没有这方面的优势。

InnoDB 中，有聚簇索引和普通索引之分，聚簇索引根据主键来构建，叶子节点存放的是该主键对应的这一行记录，而普通索引根据申明这个索引时候的列来构建，叶子节点存放的是这一行记录对应的主键的值，而普通索引中还有唯一索引和联合索引两个特例，唯一索引在插入和修改的时候会校验该索引对应的列的值是否已经存在，而联合索引将两个列的值按照申明时候的顺序进行拼接后在构建索引。、

根据以上描述我们可以得到以下信息：

数据是以行为单位存储在聚簇索引里的，根据主键查询可以直接利用聚簇索引定位到所在记录，根据普通索引查询需要先在普通索引上找到对应的主键的值，然后根据主键值去聚簇索引上查找记录，俗称回表。

普通索引上存储的值是主键的值，如果主键是一个很长的字符串并且建了很多普通索引，将造成普通索引占有很大的物理空间，这也是为什么建议使用 自增ID 来替代订单号作为主键，另一个原因是 自增ID 在插入的时候可以保证相邻的两条记录可能在同一个数据块，而订单号的连续性在设计上可能没有自增ID好，导致连续插入可能在多个数据块，增加了磁盘读写次数。

如果我们查询一整行记录的话，一定要去聚簇索引上查找，而如果我们只需要根据普通索引查询主键的值，由于这些值在普通索引上已经存在，所以并不需要回表，这个称为索引覆盖，在一定程度上可以提高查询效率，由于联合索引上通过多个列构建索引，有时候我们可以将需要频繁查询的字段加到联合索引里面，例如如果经常需要根据 name 查找 age 我们可以建一个 name 和 age 的联合索引。

查询的时候如果在索引上用了函数，将导致无法用到根据之前列上的值构建的索引，索引遵循最左匹配原则，所以如果需要查询某个列的值中间是否包含某个字符串，将无法利用索引，如果有这种需求可以利用全文索引，而如果查询是否以某个字符串开头就可以，联合索引根据第一个列查询可以用到索引，仅仅根据第二个列将无法用到索引，查询的时候用 IN 的效率高于 NOT = 。

转载于:https://www.cnblogs.com/DaisyYuanyq/p/11056031.html