mysql相关整理

存储引擎：

 

               存储引擎是针对表选择的，不同的表可选择不同的存储引擎

 

            MyISAM：每张表有3个文件：.frm文件存储表结构 .MYD文件存储数据 .MYI文件存储索引

         

•                     索引文件和数据文件是分离的
•                     主键索引为非聚簇索引，随机存储， 叶子节点 存储的是那行数据的物理地址，即行指针
•                      只支持表级索， 不支持事务，不支持外键

           

 

 

                InnoDB：每张表有2个文件： .frm文件存储表结构 .ibd文件存储索引及数据

       

•                     表数据文件本身也是一个B+树结构的索引文件
•                     主键索引为聚簇索引，顺序存储， 叶子节点包含了完整的数据记录
•                     非主键索引存储的是主键，在根据主键到聚簇索引中回表查询完整数据
•                     支持表级锁和行级锁，支持事务，支持外键

 

索引种类：

 

•            普通索引：仅加速查询
•            唯一索引：加速查询+列值唯一可为null
•            主键索引：加速查询+列值唯一且不可为null
•            联合索引：多字段组合建索引，效率大于多个单字段索引（索引合并），以联合索引字段顺序作为优先级进行排序

 

 

索引数据结构：

 

       PS：为什么mysql索引结构不选择二叉树、红黑树：

 

                二叉树结构 在自增主键列上会退化成链表，相当于全表扫描，且树的高度太大，查询性能很低

                              

               红黑树（二叉平衡树，当树的一遍明显长于另一边时，会自动平衡）结构在海量数据情况下，树的高度太大，不可控，查询性能很低

                                          

               B数（多路平衡搜索树）：

•                         叶子节点和非叶子节点都存储数据，因此 相同内存空间下，读入的索引相较于B+数更少，IO次数更多                      
•                         查询性能是不稳定的，好的情况下查到根节点，坏的情况下查到叶子节点
•                         数据分布在整棵树中，不适合做范围查找

 

                                

                

               B+树：

•                         树高一般为2-3，这样可以减少磁盘IO，大概可存两千万左右索引，因此单表数据超过千万级别后，若不分表，树的层级可能会超过3，造成查询性能降低
•                         数据都存在叶子节点上，只会在叶子节点上命中，查询性能稳定，非叶子节点存储叶子节点的索引
•                         叶子节点上的索引是有序排列的且有指针相连，是一个有序链表，适合范围查找

 

               Hash：

•                         对索引列的值进行hash运算得出数据所在的物理地址，即行指针
•                         当发生hash碰撞时，则键对应的值以链表形式存储
•                         单看查询性能是高于B+树的，尤其在数据量大的情况下，但不适合范围查找（不选用Hash结构的最主要原因）

       

     

     为何推荐使用整型自增主键：

•                         插入数据时，顺序添加到当前索引节点的后续位置，如果数据页的大小满了，直接申请新的数据页写就行了
•                         相较于随机的主键可以减少数据页的分裂以及数据的迁移，减少索引维护的开销，插入数据效率更高
•                         占用空间更小

     

 

 

     联合索引最左前缀匹配：

                联合索引的底层结构上是根据联合索引字段的顺序作为优先级进行排序，也就是最左侧的字段也是第一个字段是排好序的，在第一个字段基础上对第二个字段进行排序，以此类推。如果单看除第一个之外的字段，是无序的，所以查询时不会走索引

                                         

 

                                                           

                                                                          只有第一条SQL会走索引

 

隔离级别：

 

       事务的四个特征（ACID）

       事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

• 原子性： 事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
• 一致性： 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
• 隔离性： 一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持续性： 也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

 

        Read Uncommitted（读未提交）

        在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

       Read Committed（读已提交）

        这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也存在不可重复读的问题，因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

       Repeatable Read（可重复读）

         这是MySQL的默认事务隔离级别 ，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。幻读指用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。 InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题 。

       Serializable（串行化）

        这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

                                                                      

 

 

        幻读例子：表结构id、name、age

                               

 

InnoDB如何解决幻读问题：通过快照读和当前读两种模式解决幻读

•                   当前读情况下，通过临键锁来解决幻读问题

•                   快照读情况下，通过多版本并发控制（MVCC）来解决幻读问题：通过给每行数据维护两个隐藏字段，一个是数据行的创建版本号，一个是数据行的过期版本号，每开启一个事务，事务的版本号都会递增，当前事务只会查找小于和等于当前事务版本号的数据行，这样就确保读取的数据行要么是已经存在的，要么是当前事务自身插入或修改的。

 

 

锁：

     操作粒度分：

     表锁：操作时，锁定整个表

    行锁：操作时，只会锁定当前操作行， innoDB引擎下，行锁是依赖于索引的，当查询条件为非索引列时，行锁会升级为表锁。

    

    操作类型分： 

     读锁（共享锁/S锁）：多个事务可同时持有读锁，当数据读锁被持有时，写锁无法被其他事务持有，解决不可重复读             select ..........lock in share mode

    写锁（排它锁/X锁）：当数据的写锁被事务持有时，该数据的读锁及写锁都无法被其他事务持有，解决脏读                           select .......... for update              update操作mysql会自动加排它锁，不用显式声明for update

    意向锁（IS锁、IX锁）： 表级锁， 在获取 共享锁/排它锁 之前获取，用于协调行锁和表锁共存问题，快速返回结果，提高性能。例如：当事务A获取某 行 数据的排它锁时，同时会获取该 表 的意向排它锁，这时事务B想获取该 表 的排它锁时，会因为该表已经存在意向排它锁而快速失败（若没有意向锁，则需要判断该表每一行数据是否存在排它锁）

                                        

                  意向排它锁与表级的共享锁/排它锁会冲突，而意向共享锁只与表级的排它锁冲突，与表级的共享锁不冲突。（意向锁都是针对表级锁）

   

    自增锁： 表级锁， 当表中存在自增字段（一般为主键）时，mysql会自动维护一个自增锁，保证每个事务插入行的主键值是连续的且不受事务回滚影响，在insert语句执行完立即释放。

    记录锁（Record Lock）：行级锁，也是排它锁 ，锁定存在的数据行 ，只作用于唯一索引列且查询条件为精准匹配（=），否则退化为为临键锁   

 

    间隙锁（ Gap Lock ）： 行级锁， 也是排它锁， RR隔离级别下才有

•                     锁定开区间范围内的一段间隔，不锁定记录本身，只锁定索引间隙，即不存在的记录
•                     唯一索引列上查询，只有锁定多条记录或一条不存在的记录才会产生间隙锁
•                     非唯一索引列上无论锁定单挑还是多条记录都会产生间隙锁
•                    锁查询条件前后相邻两个索引之间的区间

 

    临键锁（ Next-key Lock ）： 行级锁， 也是排它锁， RR隔离级别下才有， 解决当前读下的幻读问题

•                    基于非唯一索引（唯一索引下会退化为行锁）
•                    每个数据行上的非唯一索引列的索引上都存在一把临键锁
•                    记录锁和间隙锁的组合， 会锁住一段 左开右闭 的区间，包含记录本身
•                    当查询没有命中记录时，会退化为间隙锁

    

    PS：可通过 show status like 'innodb_row_lock%'命令来分析行锁的竞争情况