MySql笔记

一、MySql 索引

1、索引类型

• 1.1 聚簇（聚集）索引

  • innodb默认选用主键作为索引，当主键不存在的时候会选用第一个非空的列作为聚集索引。聚集索引决定了存储的物理顺序，所以一个表只能有一个聚集索引

  • 聚集索引的特点是将行数据和索引数据存放在一个btree中，也就是叶子节点就是行的数据，所以不需要回表查询，速度很快

• 1.2 普通索引

  • 普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度

  • 应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引

  • 普通索引允许被索引的数据列包含重复的值

• 1.3 唯一索引

  • 用关键字UNIQUE把普通索引定义为一个唯一索引

• 1.4 复合索引

  • 索引可以覆盖多个数据列

  • 这种用法仅适用于在复合索引中排列在前的数据列组合，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用

• 1.5 全文索引（InnoDB不支持）

  • full-text index

  • 针对较大段的文字，用于检索那些包含着一个或多个给定单词的数据记录

• 1.6 外键索引

  • 如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件

2、索引优化

2.1、创建索引

• 复合索引最左前缀原则，mysql会一直向右匹配直到遇到(> < between like)就停止，=和in可以乱序

  • 比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

• 尽量选择区分度高的列作为索引

• 尽量的扩展索引，不要新建索引

  • 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可，因为创建(a,b)相当于创建了a索引，(a,b)索引

• 索引列不能参与计算，保持列“干净”

2.2、使用索引

• 索引列上不要使用函数和进行运算,会全表

• 索引列上不要使用 != 或 not in或 <> 等否定操作符，会全表

• 查询条件使用LIKE时，只有在第一个字符不是通配符的情况下才能使用索引

  • LIKE ‘abc%’，使用索引

  • LIKE ‘%abc’，不使用索引

• 在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引

• 索引不会包含有NULL值的列

• 不要滥用索引，索引提高查询速度，却会降低更新表的速度

• 在JOIN操作中，只有在主键和外键的数据类型相同时才能使用索引

二、MySql innodb 行锁

1、事务的四种隔离级别

• 未提交读（Read uncommitted） - 低

  • 所有的事务都能看到未提交的执行结果

  • 可能：脏读、不可重复读、幻读，不加锁读

• 已提交读（Read committed） - 一般（常用）

  • 只能读取到已经提交的数据

  • 可能：不可重复读、幻读，不加锁读

• 可重复读（Repeatable read） - 高 （MySql默认级别）

  • 可重复读。在同一个事务内的查询都是事务开始时刻一致的

  • 事务任何阶段，查询的结果都一样，就算过程中有别的更新的事务提交，查询的结果也一样

  • 可能：幻读，不加锁读

• 可串行化（Serializable ） - 最高

  • 完全串行化的读，每次读都需要获得表级共享锁，其他事务对该表将只能进行读操作，而不能进行写操作

  • 加锁读

2、MySql 锁模式

2.1、共享/排它锁 (S锁/X锁) (Shared and Exclusive Locks)(读锁/写锁)

• 可以用在行级锁也可用在表级锁

• SS不冲突，SX、XS、XX都冲突

• 普通select 不加锁（非Serializable级别），Serializable 级别读时会加S锁

• S锁

  • select..lock in share mode 加S锁

  • Serializable读时

• X锁

  • select for update 加X锁

  • update、delete、insert 加X锁

2.2、意向锁 (IS锁/IX锁) (Intention Locks)

• 表级别锁

• 意向锁是InnoDB自动加的，不需要用户干预

3、MySql 锁类型

3.1、Record Locks （单条记录锁/行级锁）

• 行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住

• 可以加在主键索引或者二级索引上

3.2、Gap Locks

• 对索引项之间的间隙、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身

• 在READ COMMITTED隔离级别下，不会使用gap lock

3.3、Next-Key Locks

• 锁定一个范围的记录并包含记录本身,首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）

• 与record lock加锁的粒度一样，都是加在一条索引记录上的，外加两边间隙

3.4、Insert Intention Locks

• 一种特殊的间隙锁，执行insert之前会向插入的间隙加上Insert Intention Lock

• 与gap lock冲突

• Insert Intention Lock与Insert Intention Lock之间不冲突，因此允许了同时向同一个间隙插入不同主键的数据

4、加锁情况（RC级别）

4.1、update

• 命中主键索引

  • 单个：在主键索引上加Record Lock

  • 范围：所有命中行的主键索引加Record Lock

• 命中唯一索引

  • 单个：唯一索引和主键索引都加上Record Lock

  • 范围：所有命中行的唯一索引和主键索引都加上Record Lock

• 命中二级索引

  • 单个：二级索引及主键索引都加上Record Lock

• 未命中索引

  • 对于update和delete操作，RC只会锁住真正执行了写操作的记录，这是因为尽管innodb会锁住所有记录，会进行过滤并把不符合条件的锁当即释放掉

4.2、insert

• a) 唯一索引冲突检查

• b) 对插入的间隙加上 Insert Intention Lock

• c) 插入记录的所有索引项加X锁

5、MVCC（多版本并发控制）

• 不可重复读和幻读，可以用不加锁的方式解决，即MVCC，乐观锁的一种实现

• 不可重复读：事务一中，别的事务提交了update、detele，导致事务一前后读取的结果不一致

• 幻读：事务一中，别的事务提交了insert，导致事务一前后读取的结果不一致，后一次读凭空多出了一条记录

乐观锁&悲观锁

• 悲观锁

  • 上文就是讲的悲观锁，整个数据处理过程中，将数据处于锁定状态

  • 悲观锁的实现，往往依靠数据库提供的锁机制

• 乐观锁（MVCC实现）

  • 基于数据版本（ Version ）记录机制实现

  • 会在每行数据后添加两个额外的隐藏的值，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）

  • 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增

• RR级别下

  • SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号

  • INSERT时，保存当前事务版本号为行的创建版本号

  • DELETE时，保存当前事务版本号为行的删除版本号

  • UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

  • 一个事务只能读取到事务开始的那个时刻的数据快照

• RC级别下

  • 事务总是读取数据行的最新快照，会产生不可重复读的问题

当前读&快照读

• 快照读

  • 普通的select

• 当前读，特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。

  • select … lock in share mode

  • select … for update

  • insert

  • update

  • delete

主从同步

• 语句级别的复制

• master服务器将数据的改变记录二进制binlog日志

• salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致

参考

• 高性能mysql