Fork me on GitHub

极客时间《MySQL实战45讲》学习笔记

MySQL的事务启动方式

  1. 显示启动事务:begin/start transaction - commit/rollback
  2. 隐式启动事务:配置 set autocommit=0,随便执行一个 select就可以启动事务,不会自动提交,直到主动 commit/rollback 或断开连接。注:这种方式容易导致长连接。

建议采用第一种,考虑多一次交互的问题,使用commit work and chain 语法代替 commit,表示提交当前的同时开启下一段事务。使用以下命令监控超过 60s 的事务。

1
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

索引模型

MySQL 索引模型,常见的三种:哈希、有序数组、搜索树。

  1. 哈希:O(1)时间复杂度,但 key 较分散,不适合区间查询。

  2. 有序数组:使用二分法,O(logN)时间复杂度,适合区间查询,但更新数据时过于麻烦,适合读多写少的场景。

  3. 搜索树,考虑到写盘问题,MySQL 使用多叉搜索树,对于一个 InnoDB 的整数索引,N 叉可以是 1200,如果树高度是 4,那么可以存储 1200^3的数字,高达 17 亿。

  4. 其他:跳表、LSM树等,不做赘述。

InnoDB 使用 B+树做引擎,在 create Table 时,字段 k 设为索引:index(k)。主键索引存的是整行的值,称为聚簇索引;非主键索引存的是主键值,称为二级索引。涉及至少两棵B+树,最终要查回主键 B+树,这种操作叫做回表

从性能的角度考虑,使用自增主键时(建议),往主键 B+树上新增记录,只会在数据块后端追加记录,而不是插入数据块中间(造成大量记录的移动,甚至结点分裂或重组)。如果不使用自增主键,而是业务逻辑的字段,可能存在主键的字节数过长、插入无序等情况,增加了写成本,只适合于有且仅有唯一索引的类似 KV 的场景中。

覆盖索引:使用覆盖索引可以避免一些不必要的回表。在非主键索引查询时,就已经获取到了所需要的字段信息,没必要再去查一遍主键 B+树。使用较冗余的方式实现一些高频请求的效率提升。

不止是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。比如针对 name 和 age 建立了联合索引,查询的 where 用name like '张%' and age = 10;,索引生效,会找到所有的张某某,但是 age 的条件没有生效,所有的张某某都需要进行回表,再确定是否满足 where 条件。MySQL5.6 之后增加了索引下推优化,会将匹配出的所有的张某某的索引字段进行判断,过滤掉不满足条件的记录,比如过滤掉 age 不为 10 的记录,减少最终的回表次数。

-------------The End-------------