MySQL学习笔记

MySQL的锁

MySQL的锁按照粒度划分为全局锁、表级锁、行级锁。按照功能划分为共享锁（读锁）和排他锁（写锁）。按照实现方式划分为悲观锁和乐观锁。

InnoDB的行级锁分为共享锁（S）和排他锁（X）。共享锁允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。排他锁允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

InnoDB会自动给UPDATE、DELETE和INSERT语句涉及的数据集加排他锁，InnoDB不会给SELECT语句加锁，但可以手动添加共享锁（LOCK IN SHARE MODE）和排他锁（FOR UPDATE）。

InnoDB表级锁是意向锁，意向锁是mysql内部使用。意向共享锁（IS）：事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务在给一个数据行加排他锁前必须先取得该表的IX锁。

只有通过索引条件检索的数据，InnoDB才使用行级锁，否则将使用表锁。

MVCC

MVCC,多版本的并发控制，英文全称：Multi Version Concurrency Control。Mysql的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，一般都实现了MVCC，实现了非阻塞的读操作，写操作也只锁定必要的行。当我们在修改数据的时候，可以为这条数据创建一个快照，后面就可以直接读取这个快照。为了实现MVCC机制，InnoDB内部为每一行添加了两个隐藏列：DB_TRX_ID(存储插入或更新语句的最后一个事务的事务ID)和DB_ROLL_PTR(回滚指针,指向写入回滚段的undo log记录).

MVCC大致查询规则是：1、只查询事务id小于等于当前事务id的数据。2、只查询未删除(回滚指针为空)或者回滚指针大于当前事务id的数据。

聚集索引

聚集索引的叶子节点会存储数据行，也就是说数据和索引是在一起。  辅助索引只会存储主键值。如果没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。

MySQL索引优化原则

1.全值匹配，按索引字段顺序匹配使用。

2.最佳左前缀法则

3.不要在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换，会导致索引失效，从而使查询转向全表扫描。

4.存储引擎不能使用范围条件右边的索引列。

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句。

6.mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。

7.is null，is not null 也无法使用索引。

8.like以通配符开头（like '%aaa'）mysql索引失效会变成全表扫描操作。

三星索引

三星索引系统的概念是《Rrelational Database Index Design and the optimizers》中提出的。

第一颗星：与查询相关的索引行是相邻的，也就是where后面的等值谓词，可以匹配索引列顺序。

第二颗星：索引行的顺序与查询语句需求一致，也就是order by 中的排序和索引顺序是否一致。

第三颗星：索引行包含查询语句中所有的列。

后记

花了一个星期时间跑马观花地学习了一下MySQL。我对MySQL的了解几乎还停留在初学者水平，简直是九牛一毛。取名“学习笔记”就可以不保证正确性（大言不惭）。东拼西凑搞了一篇学习笔记，要原创一篇有技术含量的文章的前提是：有技术。有时间想把《高性能MySQL》这本书读完，要认真看完、看懂一本书实在是太花时间了，我看了1/3就半途而废。突然想起了以前老板说的：“你们要多长时间都可以。但是市场不等人，客户不等人。”我们很难达到这样一种理想状态：在有限的时间内完成任务，老板满意，客户满意，自己也有进步。所以，我们只有不断精进、不停地学习。

参考文献

https://baijiahao.baidu.com/s?id=1669272579360136533

https://blog.csdn.net/yhl_jxy/article/details/88636685

《高性能MySQL》

-----------------------------------------------------------------------------------------

我的公众号：永宁河（peace_river）