MySQL数据库 基础知识(一)锁-索引

1.存储引擎 MySQL 默认的存储引擎是 Inno DB，支持事务、外键、行级锁、自增列属性 auto_increment。 MyISAM:独立于操作系统的，建表时会在本地磁盘中生成三个文件，以表名为文件名，分别存储表的定义、数据、索引。它还有个特性 concurrent-inserts，可以一边查找数据一边插入数据。 Memory：速度快，但是 MySQL 进程一旦崩溃，数据就会消失，存的数据必须是长度不变的，它除了 B+树索引，还支持散列索引。 Merge:MyISAM 表的集合，可以用于分表。 Acive：请求数据时会实时压缩，适用于仓库、收集数据。 2.事务的概念 逻辑单元执行的一系列操作。满足四个特性。 3.事务的特性 原子性：一个事务被视为不可分割的最小工作单元。要么都提交成功，要么都失败回滚，不存在只执行一部分的情况。 一致性：数据库在任何时候都保持一致性状态，比如跨表处理事务，查询到的是提交后的新数据，或都是旧数据，不存在新、旧混合的情况。 隔离性：一个事务的状态转化不会影响到其他事务。 持久性:事务一旦提交就会持久化到数据库中。 4.事务的语法 begin 开启事务。 start transation 后可跟 read only只读、read write、启动一致性读。 可以设置 save point 保存点，之后回滚 roll back 时是回滚到保存点。 默认是启动自动提交的，每个语句都算是一个事务，如果通过系统变量关闭自动提交的话，还有隐式提交：比如 create table... 会隐式提交之前的事务、create user / drop user / set password 之类的 与用户相关的，也会隐式提交之前的事务。 5.隔离级别 数据库在并发情况下，会出现 脏写、脏读、不可重复读、幻读等问题。 对于不同情况，可以启用不同的隔离级别，Set globalssion transcation_isolation="xxx",而脏写是任何隔离级别都不允许的，应该写时加锁。 （1）读未提交:事务可以读到另一个事务未提交的数据。会出现脏读、不可重复读、幻读等问题。 （2）读已提交：解决了脏读的问题。 （3）可重复读：默认的隔离级别，解决了不可重复读的问题，但是可能出现幻读的问题。 不可重复读：一个事务 A 通过条件查询到了记录，另一个事务 B 在满足条件的前提下 更新 update 了该记录，那么事务 A 使用同样的条件进行查询，会得到不同的结果。 幻读：一个事务 A 通过条件查询到了记录，另一个事务 B 插入 insert 了满足条件的一些语句，那么事务 A 使用条件查询，就会得到 B 插入的这些新纪录——幻影记录。 （4）串行读：每一行都加锁，可以解决所有问题。 6.锁 从类型上大致分两种：S型 共享锁，事务读之前必须先获取到 S 锁。 X型 独占锁，事务写之前必须先获取到 X 锁。 从粒度上说，分为表级锁和行级锁。 表级锁：（1）S型表级锁、X型表级锁， lock table t read/write。 （2）IS型表锁、IX型表锁：意向共享锁、意向排他锁，Inno DB 自动加的，是用来解决 表锁、行锁冲突的。比方说 事务A 获取行级 独占锁之前，需要先获取 IX 意向排他锁，之后 事务 B 想给 记录所在的表 上锁时，就会被阻塞。 （3）Auto_inc 给自增属性的列赋值时，会给整个表上锁，保证 自增数据的连续性。 表锁开销小，不会出现死锁，但是并发量小。 行级锁：（1）S型记录锁 Record lock ：select... lock in share mode （2）X型记录锁：select... for update （3）gap 间隙锁，用于解决幻读问题，在记录之前的间隙不允许插入新纪录 （4）next-key 记录锁+gap 的组合 （5）插入意向锁，如果记录插入的地方已经被 gap 或 next-key 锁定，就会阻塞等待，这时会给记录关联一个锁记录。 锁记录有很多属性，重要的有：trx_id 生成锁记录的事务 ID；is_waiting 是 false 表示当前事务持有这个记录的锁。 7.两阶段封锁协议 （1）随机都可以执行锁定，InnoDB 会根据不同隔离级别自动加锁。 （2）在 commit 提交、rollback 回滚时释放锁，而且所有的锁是同时释放的。 8.各种 SQL 语句加锁分析 和隔离级别；查询时使用的是 主键索引 还是 唯一二级索引；使用的是等值查询 还是 范围查询；具体的语句 select、update、insert、delete 有关。 （1）普通 select 语句 在读未提交隔离级别下，普通 select 语句，是不加锁的，直接读取记录的最新版本，可能出现 脏读、不可重复读、幻读 问题。 在读已提交隔离级别下，每次 select 会生成一个 ReadView ，解决了脏读的问题，但是没有解决不可重复读 和 幻读 的问题。 在可重复读隔离级别下，第一次 select 时会生成一个 ReadView，之后都采用 这个 ReadView ，可以解决不可重复读的问题，但是没有解决幻读的问题。 在串行读隔离级别下，看是否开启了自动提交 Auto_commit，如果开启的话，每个语句都是一个事务，不会加锁。 而如果没有开启，就会为记录生成 S 型记录锁。 （2）select... lock in share mode 在读未提交、读已提交隔离级别下，如果是主键值进行等值查询的话，会给聚簇索引上的记录加 S 型记录锁；如果是对主键值 范围查询的话，只给符合条件的记录加 S 锁。如果是对二级索引进行等值查询，就到二级索引中，给记录加 S 型记录锁，回表时到聚簇索引中给记录加 S 型记录锁；如果是对二级索引进行范围查询，是先到二级索引加 S 型记录锁，再到对应的聚簇索引加 S 型记录锁，再到下一条 二级索引中加锁... ... 如果是全表扫描，先给记录加 S 型锁，然后交给 Server ，如果不符合条件释放锁，否则就交给客户端。 在可重复读隔离级别下，加 gap 锁，要解决幻读的问题，如果是对主键进行等值查询，比如 x=2,分主键存在和不存在的情况：如果 x=2 的记录存在，主键具有唯一性，就不会发生幻读，加 S 型记录锁就好；如果不存在，就会给聚簇索引中 >2 的最小记录加 gap 型锁。如果是对主键进行范围查询，比如 x>=2 ，会给聚簇索引中 x=2 的记录加 S 锁，给所有大于 2 的记录加 next-key 锁。如果是对唯一的二级索引进行等值查询，如果 二级索引存在，不会造成幻读，只需要 加 S 锁，然后再给它对应的聚簇索引加 S 型锁；如果不存在，给比查询值大的 最小的二级索引上 加 gap 锁。 （3）select... for update 和 select... lock in share mode 步骤一致，只是加的是 X 型记录锁。 （4）update 和 delete 和 select... for update 步骤一致，只是在可重复读隔离级别下，全表扫描时，遍历聚簇索引中的记录，先给记录加 X 型记录锁，然后如果不满足条件，锁会被释放；如果满足条件，再给对应的二级索引加 X 型锁；而 delete，是给所有的二级索引加锁。 （5）insert 一般情况下是不加锁的，如果插入的地方有 gap 锁 或 next-key 锁，就会生成插入意向锁。 如果插入的是主键值相同的记录，会报错，在报错之前，聚簇索引中的记录是加 S 锁的，不同隔离级别下，锁的类型不同：读未提交、读提交隔离级别下，是 S 型记录锁，可重复读、串行读隔离级别下，是 S 型 next-key 锁。 如果是二级索引重复，无论是什么隔离级别，都是加 next-key 锁。 9.死锁 如果死锁频繁，可以考虑加大锁的粒度，改成表级锁；尽可能按顺序访问记录。 可以开启死锁监测 innodb_deadlock_detect ,发现死锁后会主动回滚一个事务，让其他事务得以继续执行；或设置超时时间，默认是出现死锁 50s 。 10.优化锁的建议 粒度尽量小。 尽量使用较低的隔离级别。 尽可能让检索都通过索引完成。 死锁频繁考虑用表级锁。 已有 MVCC 读时尽量不要加锁。 11.索引 索引的定义:数据库中用于快速查找的已排序的数据结构。 B+树索引： InnoDB 会为主键自动生成 聚簇索引。页是磁盘和内存交互的单位，存放数据的页——数据页，为每一个数据页生成一个目录项记录，对于聚簇索引，目录项记录的内容：数据页的页码 和 这一页中主键最小值，目录项会构成页，再往上一级生成新的目录，这样就构成了目录的形式，像树一样，而叶子节点是完整的用户记录；记录与记录之间形成单链表；页与页之间形成双链表。对于自定义索引，目录项是：数据页的页码 和 索引列最小值，叶子节点是 索引列值 和 主键值，所以可能需要回表。 MyISAM 中 数据 和 索引是分开存储的，将表中的记录按照记录的插入顺序单独存在数据文件中，而索引 ，B +树的叶子节点不是完整的用户记录，而是行偏移量，根据行偏移量到数据文件中进行查询。 B+树索引使用的情景：为表的主键会自动建立聚簇索引、全值匹配、匹配列前缀、匹配范围值、排序、分组、匹配左边的列（查询的条件是从索引列左边开始连续的）、精确匹配一列并范围匹配另一列。 不会使用到B+树索引的情景：排序查询混合了asc 升序、desc 降序；靠左匹配(比方说 like'%ab')；使用了复杂的表达式（比如说 x*2>4）。 哈希索引：key 是根据索引列求得的哈希码，value 是行号，会按顺序排列。 哈希索引适用于键值唯一的等值查询，不适用于数据量过大的，容易出现哈希冲突。 怎么挑选索引：只为查询、排序 或 分组的列建立索引——索引覆盖；索引列的类型尽可能小，如果列很长的话，建议只取前缀进行匹配；为重复次数少的列建立索引；索引在表达式中单独出现。 12.索引下推 用于二级索引。有些语句中有 二级索引，但是具体查询时没有使用到，比如说 x>1 and x like '%1',x>1 使用到了索引，like '%1'没有使用索引。在没有索引下推时，需要到二级索引中找到 x>1 的叶子节点，然后逐个进行回表，找到完整的用户记录去判断是否符合 like '%1' 的条件；而有了索引下推后，到 x>1 的记录时不会立即回表，而是判断是否满足 like '%1' 的条件，满足才进行回表。这样可以减少不必要的回表次数。 13.B+树 与 B树 的区别 B+树的关键字只存在叶子节点，其他非叶子节点中放置的是索引；而 B 树的关键字遍布在整棵树中。 14.连接 连接是指笛卡尔积 a✖b ，有个 join Buffer，每次缓存多个驱动表的记录，用被驱动表的记录与驱动表的多个记录进行匹配，这样就只需要遍历一次被驱动表。 内连接：对于内连接的两个表，驱动表中的记录在 被驱动表 中找不到匹配的记录，该记录不会加入到结果集。 外连接：对于外连接的两个表，驱动表中的记录即使在 被驱动表 中没有匹配的记录，会以 NULL 加入到结果集。 左连接是指左边的表作为驱动表，右连接是指右边的表作为驱动表。