【MySQL】MySQL

MySQL

• MySQL与Oracle的区别

  MySQL免费，Oracle付费

  MySQL自动提交事务，Oracle需要手动commit提价

  MySQL对于字符类型支持char和varchar，Oracle支持CHAR，NCHAR，VARCHAR2和NVARCHAR2

  MySQL事务默认隔离级别REPEATABLE READ，Oracle默认隔离级别READ COMMITTED，且都支持SERIALIZABLE

  READ COMMITTED(提交读)，大多数数据库系统的默认隔离级别（MySQL不是）。一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。也叫不可重复读(nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果。

  REPEATABLE READ(可重复读)，（MySQL的默认事务隔离级别），解决了脏读问题。保证了在同一个事务中多次读取同样记录的结果是一致的。

  SERIALIZABLE(可串行化)，最高的隔离级别。它通过强制事务串行执行，避免了幻读问题（幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行【与不可重复读的区别就在于换行重点为新增或删除，不可重复读重点为修改】）。简单来说，可串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。只有在非常需要确保数据一致且可以接受没有并发的情况下使用该级别。

  MySQL用limit关键字就可以实现分页，Oracle分页查询需要用到伪列rownum和嵌套查询

  --MySQL，从第6个开始取10个数
  select * from table_name limit 5,10 
  

  --Oracle，取21到40行
  SELECT *
    FROM (SELECT a.*, ROWNUM rn
            FROM (SELECT *
                    FROM table_name) a
           WHERE ROWNUM <= 40)
   WHERE rn >= 21
  

  MySQL使用InnoDB存储引擎时才支持事务，Oracle默认支持事务

• MySQL存储引擎

  存储引擎： 数据文件在物理磁盘上不同的组织形式，主要包括InnoDB、MyISAM、MEMORY。

  InnoDB ：MySQL的默认存储引擎

  索引结构为b+树、自适应hash（需要时自动优化为hash索引）。

  索引与数据在一个文件

  支持事务、外键、行级锁

  MyISAM ：

  索引结构为b+树

  索引和数据在不同的文件

  不支持事务、行级锁

  MEMORY ：

  索引结构为哈希

  InnoDB优势在于提供了良好的事务处理、崩溃修复能力和并发控制；MyISAM优势在于占用空间小、处理速度快，会提前保存好行数，查询整张表有多少条数据时很快；MEMORY可以用于需要读写速度很快，数据安全性要求不高，数据库表要求较小时。

• 索引结构为什么选择b+树（其他索引结构的特性）

  • hash索引

    1. hash算法容易出现冲突
    2. hashmap不能访问文件，只能从内存取数据，所以利用hash存储需要将数据全都放到内存，耗费内存空间
    3. hash查找是根据key值定位数据位置，只适用于等值查询，不适合范围查询

  • 二叉搜索树bst

    1. 按顺序插入会退化成链表

  • 二叉平衡树avl

    1. 为解决二叉搜索树的问题，可通过旋转，使最高子树与最低子树高度不超过1。
    2. 查询快了，但插入慢，需要频繁旋转

  • 红黑树

    1. 最高子树最高是最低子树的两倍，根结点是黑色，插入结点是红色，插入时保证每个分支黑色结点个树相同，同时不能有连续两个红色结点，否则会通过旋转和变色保证满足条件。
    2. 红黑树是一种弱平衡二叉树，旋转次数小于平衡二叉树，但高度一般高于平衡二叉树，还是会出现深度很高的情况，影响io效率。

  • b树

    1. 二叉树或红黑树一个节点只放一个值，树的深度过深会影响磁盘io速度。

    2. 而b树是一种平衡多叉树，每个节点以16k的大小存放多个值，降低了树的高度，减少了io次数。

       

  • b+树

    1. b+树相比b树，非叶子结点只存储索引，不存储数据，使每个磁盘块可以存储更多索引，比b树存储更多数据，树的高度更低，所有数据都有序存在叶子结点，叶子结点之间有双向链表，更适用于范围查找。
       

• MySQL索引分类

  聚簇索引：

  数据行的物理顺序和列值相同

  如果一个主键被定义了，那么这个主键就是作为聚簇索引

  如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚簇索引

  如果没有主键也没有合适的唯一索引，那么innodb会用6个字节的row_id列作为聚簇索引

  非聚簇索引：

  其他索引，比如：普通索引、唯一索引、联合索引都是非聚簇索引。

  主键索引，唯一索引，普通/二级/辅助索引，全文索引，组合索引。

  一个索引对应一个b+树

  普通列的索引叶子结点存放的不是数据而是key，通过二级索引查到key，再回表查询聚簇索引。如果查询的列为key会出现索引覆盖，不再需要回表。

  索引下推，mysql自己的服务要跟磁盘交互查询磁盘中的数据，有了索引下推直接根据多个索引获取数据，不需要mysql服务层做任何的数据筛选。缺点是将原先内存中的筛选放到了磁盘查找数据的环节，但因为数据是聚集的，所以索引下推会减少整体io量，提升性能。

  非分布式建议使用主键自增，确保索引按顺序插入，否则容易出现页分裂，页合并
  分布式用雪花算法

  索引何时会失效：

  根据索引的匹配列前缀规则，用%a便会失效

  当索引中有多个列，即联合索引，此时遇到范围查找(>、<、between、like)剩下的索引就会失效。例如（a，b）联合索引，条件为a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

  根据最左匹配原则，当索引中有多个列时（即联合索引），存储数据时将按照定义索引时列的顺序排序。例如ALTER TABLE tb_student ADD INDEX test_index(name,job);就先按照name排序，当name相等时再按照dob排序。如果是where name = 1 and job = 2 或 where job = 1 and name = 2【查询引擎会自动优化为联合索引的顺序】 或 where name = 1都可以使用索引，而 where job = 1 不会使用索引。

  用or时，如果组合索引包含全部查询的列会用索引，否则索引失效

• MySQL优化及explain

  一、explain查看sql执行情况，是否用到了索引：

  id

  1. id相同时，执行顺序由上至下

  2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

  3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

  select_type

  显示查询中每个select子句的类型

  (1) SIMPLE(简单SELECT，不使用UNION或子查询等)

  (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

  (3) UNION(UNION中的第二个或后面的SELECT语句)

  (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

  (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

  (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

  (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

  (8) DERIVED(派生表的SELECT, FROM子句的子查询)

  (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

  table

  显示这一步所访问数据库中表名称

  type

  type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种，从好到差依次是

  system > const > eq_ref > ref > range > index > all

  • system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  • const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
  • eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
  • range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
  • index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
  • all Full Table Scan 将遍历全表以找到匹配的行

  possible_keys 和 key

  • possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

  • key实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

  key_len

  表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。

  ref

  列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  rows

  根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

  Extra

  该列包含MySQL解决查询的详细信息,有以下几种情况：

  Using filesort

  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

  Using temporary

  使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

  Using index

  表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

  Using join buffer

  表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

  二、检查sql

  1、尽量避免子查询，sql返回多少行，子查询就要执行多少次，可以使用inner join替换子查询。或者用between或exists替换in。

  2、不能将索引作为表达式的一部分或作为函数的参数，会使索引失效。

  3、尽量将字段都添加上NOT NULL DEFAULT VALUE属性，如果列值存储了大量的NULL，会影响索引的稳定性。

  4、索引很长的字符列可以使用前缀索引，不过会提高重复率，且排序无法使用前缀索引。

  5、select语句指明字段名称而不是select *，SELECT *增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性

  6、当只需要一条数据的时候，使用limit 1，使EXPLAIN中type列达到const类型

  7、如果限制条件中其他字段没有索引，尽量少用or，用 union all 或者是union代替or。

  8、尽量用union all代替union，union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

  三、如何建立索引？

  建立联合索引时：

  ①使用频率高的放前面

  ②过滤效率高的放前面

  select count(distinct name) / count(*) as name, count(distinct author) / count(*) as author from books;
  

  最后得出结果如下：

  Name	author
  0.95	0.9

  显然name字段的选择性更高，那么如果把name放第一列，在name条件过滤时就可以排除更多的列，减少接下来 author的过滤

  ③避免排序

  索引的值都是有序排列的，在创建索引时还可以显式指定每个列的排序方式，例如

  create index idx_books_author_created_at on books (author, created_at DESC);
  

  此时，如果执行下面的的查询

  select * from books where author = 'author' order by created_at DESC;
  

  由于满足auhtor的索引的created_at列都是有序排列的，所以不需要再进行额外的排序操作。

  ④使用覆盖索引，减少回表操作

  例如下面的查询

  select * from books where author = 'author1';
  

  那么在有二级索引(author, created_at)的情况下，MySQL先通过二级索引找到满足author1的所有books的id，然后再通过id在聚簇索引中找到具体数据。

  如果我们只是想要该作者的书名，可以将(author, createdat)扩展为(author, createdat,name)，然后将sql修改如下

  select name from books where author = 'author1';
  

  由于索引中已经有name的信息，此时就不会再次回表

  ⑤避免重复索引

  创建一个（a，b，c）的联合索引相当于创建了（a），（a，b），（a，b，c）三个联合索引。

  ⑥索引并非越多越好

  索引是对原表的数据冗余，必须要保证数据的一致性。如果原表增加了一条数据，索引也需要增加。如果原表修改了一条数据，那么对应的索引可能也要修改内容以及排序的位置，这可能会造成页分裂或页合并。一个表如果索引过多，那么维护索引与表的数据一致性也是不小的压力。通常建议在满足需求前提下，索引越少越好。

• Innodb的行锁及实现方式

  • 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

    【即读取当前行时禁止其他事务更改此行数据】

    共享锁：SELECT 。。。LOCK IN SHARE MODE;

  • 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

    【即更新此行数据时禁止其他事务读取或更新此行数据】

    排他锁：SELECT 。。。 FOR UPDATE

• 乐观锁和悲观锁

  乐观锁：每次获取数据时都认为不会有其他事务修改数据，所以不会加锁。在提交修改时会判断一下在此期间是否有其他事务修改过数据。通过添加version字段实现，每次修改成功都会加1，可通过version值确定是否有修改

  悲观锁：每次获取数据都认为其他事务会修改数据，所以每次都会加锁，阻止其他线程获取数据。实现悲观锁首先手动设置为非autoCommit模式，在查询时加上for update排他锁。

• MySQL事务特性与隔离级别

  事务特性ACID:

  原子性(atomicity)，一个事务必须被视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。

  一致性(consistency)，数据库总是从一个一致性的状态转换到另外一个一致性的状态。

  隔离性(isolation)，通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。

  持久性(durability)，一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

  ACID实现：

  通过MVCC使得事务隔离级别达到了可重复读，使用锁机制消除了幻读，实现了事务隔离。通过数据库的redo log重做日志，来保证事务的持久性与原子性。通过数据库的undo log撤销日志，来保证事务的一致性。

  4种隔离级别:

  READ UNCOMMITTED(未提交读)，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。很少使用。

  READ COMMITTED(提交读)，大多数数据库系统的默认隔离级别（MySQL不是）。一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。也叫不可重复读(nonrepeatable read)，因为两次执行同样的查询，可能会得到不一样的结果。

  REPEATABLE READ(可重复读)，（MySQL的默认事务隔离级别），解决了脏读问题。保证了在同一个事务中多次读取同样记录的结果是一致的。

  SERIALIZABLE(可串行化)，最高的隔离级别。它通过强制事务串行执行，避免了幻读问题（幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行【与不可重复读的区别就在于换行重点为新增或删除，不可重复读重点为修改】）。简单来说，可串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。只有在非常需要确保数据一致且可以接受没有并发的情况下使用该级别。

• InnoDB如何解决幻读

  幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入或删除了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

  快照读：简单的select操作，属于快照读，不加锁。

  • select * from table where ?;

  当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

  • select * from table where ? lock in share mode;
  • select * from table where ? for update;
  • insert into table values (…);
  • update table set ? where ?;
  • delete from table where ?;

  MySQL通过mvcc、 Next-Key锁、串行化隔离级别避免幻读：

  mvcc全称是multi version concurrent control（多版本并发控制）。mysql把每个操作都定义成一个事务，每开启一个事务，系统的事务版本号自动递增。每行记录都有两个隐藏列：创建版本号和删除版本号

  事务每次只能读到创建版本号小于等于此次系统版本号的记录，同时行的删除版本号不存在或者大于当前事务的版本号。

  SERIALIZABLE串行化隔离级别：

  可串行化会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。

  Next-Key Lock是Gap Lock（间隙锁：锁定一个范围）和Record Lock（行锁）的结合版，即锁定一个范围且包含记录本身

• InnoDB如何解决死锁

  所谓死锁：是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

  表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

  等待，直到超时(innodb_lock_wait_timeout=50s)。

  发起死锁检测，主动回滚一条事务，让其他事务继续执行(innodb_deadlock_detect=on)。

  由于性能原因，一般都是使用死锁检测来进行处理死锁。

  死锁检测

  死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

  回滚

  检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚

• MySQL主备同步

  MySQL主备同步就是指一台服务器充当数据库主服务器，另一台或多态服务器充当从数据库服务器。主数据库对数据修改的操作记录到binlog日志，从服务器通过binlog日志执行更新，保证主备同步。

  同步方式：

  1.基于语句的复制 ：主库把sql语句写入到bin log中，完成复制

  2.基于行数据的复制：主库把每一行数据变化的信息作为事件，写入到bin log，完成复制

  3.混合复制：上面两个结合体，默认用语句复制，出问题时候自动切换成行数据复制

  线程如何工作：

  1.Master 数据库只要发生变化，立马记录到Binary log 日志文件中

  2.Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志

  3.Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。

  4.Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据