MySQL数据库笔记

MySQL数据库笔记

索引

语句

• create index indexname on mytable(username(length))

• alter table tableName add inedx indexName(columnNmame)

• create table mytable{

  id int not null,

  username varchar(16) not null,

  index indexName (userName(length))

  }

优缺点

优点

• 快速检索，减少I/O次数；
• 根据索引分组和排序，可加快速度

缺点

• 索引本身也是表，因此会占用存储空间
• 索引表的创建和维护需要成本，成本随数据量的增加而增加；创建索引会降低数据表写的效率，因此修改数据表的同时还要修改索引表

备注

• 减少IO主要指的是减少磁盘IO。磁盘IO是很慢的，因此操作系统每次读磁盘的时候是一页一页读的。

索引的分类

主键索引、唯一索引、普通索引、全文索引、组合索引

1. 主键索引，根据主键建立索引，不可重复不可为空。

2. 唯一索引，唯一，可以空

3. 普通索引，没有任何限制，仅仅用来查询

4. 全文索引，用大文本对象的列构建的索引

5. 组合索引，多个列组合构建的索引，多列中的值不能为空

   ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,‘col2’,‘col3’)；

   其中，遵循最左前缀原则，有常用的为最左，依次递减。相当于建立了col1,col1col2,col1col2col3三个索引，col2和col3不能用。

   ALTER TABLE ‘table_name’ ADD INDEX index_name(col1(4),col2（3))；

   为防止列名过长，可以指定列名 的前几个字符作为索引。

   最左前缀原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。因为对于(a,b,c)这样的组合索引，只有a是有序的，bc均是无序的，因此无法进行索引。

6. 覆盖索引。查询列被所使用的索引覆盖，不需要从数据表中select，直接从索引表即可。type=index,其他索引为ref

索引的实现原理

B树索引，B+树索引，哈希索引，全文索引。

• 哈希索引，仅Memory存储引擎支持。一一对应，不支持范围查找和排序。原理是索引列的值计算出hashcode，然后根据hashcode可以直接得到主键，根据主键得到记录位置。
• 全文索引，仅用于MyISAM和InnoDB，mysql仅可用于MyISAM。将大段文本分词，分词后对单词进行检索。要求单词出现频率低于50%，长度超过4个字节。
• B树和B+树索引

1. InnoDB的索引方案

   • https://mp.weixin.qq.com/s?__biz=MzIxNTQ3NDMzMw==&mid=2247483678&idx=1&sn=913780d42e7a81fd3f9b747da4fba8ec&scene=19#wechat_redirect

   • InnoDB：将数据划分成若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16kb。
   • InnoDB记录存储结构，分为四种，具体我没记，内容是以什么样的格式存储一行记录。有的是当页存储，多余分页，也有的是当页存储地址，地址指向实际存储页，还有的是对实际储存页进行压缩。
   • InnoDB数据页结构：数据页包含多个记录行数据。
     
     记录在页中的组织形式为按照主键大小排列的单链表
     
   • 在1页中有那么多条记录，且是用单链表形式保存的，如何快速查找，或者进行范围查找？
     

   思路是用Directory。即目录保存每几个记录的最大主键号，然后查找时可以按照Directory进行二分法，从而快速查找。Directory是一个排序好的数组，directory内是一个链表可以遍历查找。

   • 数据页之间是双链表
     
   • 总结（缺Directory）
     

2. 再聊索引

   • 没有索引的单页查找
     • 查找主键，在每一页的Directory中用二分法快速定位再遍历单链表
     • 查找其他列，从最小记录依次遍历每一页的每条记录后再遍历下一页
   • 没有索引的多页查找
     • 1. 定位到记录所在的页
     • 2. 从所在的页内查找相应的记录
     • 但是因为我们无法定位到记录所在的页（抱歉，主键也不行，主键只用于页内定位），因此会很慢。

   所以各位明白了？本质上，索引是用于页的快速定位的。下面正式开始！

   

   讲解一下，每个页中以单链表实行连接，且页与页之间也有严格的大小划分，A页的最大值必须小于B页的最小值。然后把每一页的最小值抽出来做目录。

   

   为什么用B+树？因为B+树比起二叉树矮胖，一个子节点可以存储更多的信息，这样查找的次数少，即IO次数少。B+比起B来，叶子节点存储所有数据，有利于查找区间。

   • B树是什么 度为M（相当于有几个分叉，2叉树度为2,3叉树度为3）。键，即元素树为M-1。很显然，如果有2个元素，那么必然有3个叉。然后比如（2,8,12）,左边就是比2小，中间就是2到8,8到12，右边是12以上。
     
   • B+树，两个变化，1.非叶节点键和叉相同，比如(2,8,12)，左边2到8，中间8到12，右边12以上。2.叶节点用指针连起来，形成循环链表。
     

3. 聚簇索引、二级索引

   • 聚簇索引指的是B+树的叶子节点存储的是完整的用户记录，这个完整指的是存储了所有列的值。InnoDB会自动帮我们创建聚簇索引。索引即数据。
   • 二级索引，聚簇索引只有当搜索条件为主键的时候才能发挥作用，但是如果把别的列作为主键会怎么样呢？当然是再建几个B+树，但是这些B+树并不是存储了所有数据，而只是存储了那个列和主键，当找到叶节点的时候再根据主键去搜聚簇索引，从而获得了完整的记录。得到主键再去搜的这个过程叫做回表。
   • 联合索引 按照（c2,c3)为联合主键时，先以c2排序，再以c3排序，因此只保证c2有序
     c3不一定有序。
   • 非聚簇索引，叶子节点保存的是指向数据的逻辑指针。

4. MyISAM的索引方案

   • MyISAM也使用B+树，但是将索引和数据分开存放。B+树中存放的是主键值+行号，因此通过索引查找到行号再去查找相应的记录。问题来了？有主键值不就好了，为什么还要行号呢？因为在MyISAM中，记录是按照插入时间顺序存储在一块存储空间上的，而不是对主键进行排序的，因此无法通过二分法查找主键，所以只能额外的用行号来表示一条记录。B+树查找还是根据主键大小来排序，但是排序完是为了拿到行号。在InnoDB中，B+树是为了查找到主键值。

     

5. 如何用索引

   • 只为用于搜索，排序或分组的列创建索引
   • 考虑列的基数。列的基数指的是一列中不重复数据的个数。基数越大，越适合建立索引。基数越小说明数据约集中，极端情况都是一个数字，那B+树搜索就没意义了。
   • 索引列的类型尽量小。采用尽量小的数据类型，可以有效降低索引表的大小。
   • 索引字符串的前缀。采用字符串的前几个字符进行索引，就可以大概的进行排序了。
   • 尽量使用联合索引，可以降低B+树的数量，当然用起来也有局限。
   • 让索引在比较表达式中单独出现。即WHERE my_col < 4/2，不要对mycol做任何计算或函数。
   • like %ABC是用不到索引的。遇到> < between之后不能用索引。比如说select * from table where a = ‘1’ and b > ‘2’ and c=‘3’ 这种类型的也只会有a与b走索引，c不会走。因为a是确定的，然后根据b排序，所以b也可以用，但是不保证c有序。
   • 主键插入顺序。比如某个数据页存储的记录已经满了，但是现在要插入进来，就会导致当前的页分裂成两个，最坏情况下所有的页面都要分裂。因此主键最好使用自增序列，这样就不会发生后来的主键插入之前的页中。
   • 避免冗余和重复索引。比如建立了（a,b,c）联合索引，再建立一个a索引就是沙雕操作。但是可以建立b索引。
   • 覆盖索引。不要用select *。因为覆盖索引的存在，如果你要搜索的值恰好在（a,b,c）中，那么就不需要回表了。

数据库的并发控制

非Mysql的一般数据库怎么用锁

• 在RC隔离级别下，修改数据会加排它锁，事务结束释放，其他事务不许读，解决脏读问题。（共享锁当场释放）
• 在RR隔离级别下，读数据加共享锁，事务结束释放，其他事务不许修改，解决不可重复读。（共享锁事务结束释放）
• RC情况下，共享锁导致的死锁
  • 一个用户①进行查询并加了lock in share mode,另一个用户②也可以进行查询并添加lock in share mode，但当用户①进行增改删操作时，相当于他又要申请一把排它锁。但是因为共享锁和排它锁互斥，因此他会阻塞。当用户②进行增改删操作时，表示用户2也像获得排它锁，但是共享锁和排它锁互斥，于是他也阻塞，此时产生死锁。

mysql怎么用锁

• RC级别一样
• RR级别用MVCC实现

锁等级

• 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 页面锁：中庸
• 行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度最高

锁的思想

• 乐观锁：CAS，为每个数据设计一个version字段，compare and set。先拿出version，但是当我们要修改的时候再看一眼这个version，如果没改变就update，否则更新version值后等待下一次更新。乐观表示认为并发量不够大，或者并发写不够多，默认数据不会被更改。乐观锁数据库没有实现，需要自己加一个version字段。
• 悲观锁：就是怂，认为数据一定会发生冲突，所以每次操作时都要获得锁才能进行进行操作。悲观锁是数据库自己实现的，调用数据库相关语句即可。
  • 共享锁 又被称为读锁，是读取操作创建的锁。其他用户可以并发获取数据，但任何事物都无法对数据进行修改，直到已释放所有共享锁（即所有拥有共享锁的事务提交）。如果事物对读锁进行修改，可能造成死锁。也叫S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
    • sql语句：select * from table where id=4 lock in share mode
    • 加共享锁后，对读开放，与写锁为互斥关系。
  • 排它锁 又被称为写锁，只能这个事务对其进行读写，在这个事务结束之前，其他事务不能对其加任何锁。排它锁会阻塞所有其他的排它锁和共享锁。
    • sql语句：selcet * from table where id=1 for update;
    • 一个事务提交之后，后面的事务才能对这条记录有操作，包括读写。即锁在事务提交后释放。
  • 非MVVC情况下，如何通过共享锁与排它锁进行RC与RR隔离
    • RC隔离 对写加排它锁，保证事务读到的数据一定是提交过的数据，但是前后读取可能存在不同。
      
    • RR隔离 一定是可重复读的，但是可能存在幻读，因为只对单条记录上锁。
      、

MyISAM（有索引的顺序访问方法）表锁

• 锁模式
  

  • 表共享读锁
  • 表独占写锁
  • 说明
    • 表的读操作，不会阻塞其他用户对于同一个表的读请求，但会阻塞同一个表的写请求。
    • 表的写操作，会阻塞其他用户对同一个表的读写操作。
    • 表的读写操作之间，以及写操作之间都是串行的。即读读并发。
    • 读锁对应共享锁，写锁对应排它锁。

• 如何加锁

  • MyISAM在执行查询前，会自动执行表的加锁、解锁操作。但是也可以进行显式操作。

      lock table t1 read, t2 read;
      select count(t1.id1) as 'sum' from t1;
      select count(t2.id1) as 'sum' from t2;
      unlock tables;
    

• 并发插入问题

  • concurrent_insert系统变量，用于控制并发行为
    • 0 不允许插入
    • 1 MyISAM运行一个进程读表，一个进程从表尾插入。
    • 2 始终允许在表尾并行插入

• 锁调度

  • 读写锁互斥
  • 写锁优先级高，即使读请求先到，也会先执行写锁。这就是为什么MyISAM不适合大量的更新操作和查询操作的原因。

InnoDB行锁

• 行锁（相等条件查询且用到索引，少量的写操作）。多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免脏读等数据一致性的问题。后来的事务可以操作其他行数据，解决了表锁高并发时性能低的问题。本质上就是根据索引B+树找主键B+树，然后索引所在叶节点加锁（就是对索引表的那个记录加锁），主键所在叶节点加锁。（对数据表的那个记录加锁）
  • InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁。
  • 本质上来说InnoDB的行锁是对索引加锁，而不是针对记录加锁。如果索引失效，则从行锁升级为表锁。
  • 因为行锁要找到针对哪一行加锁，因此必须用到索引，没有索引查找太慢了，会进一步加大开销。尽管有索引，但是行锁还是加锁慢，开销大。
  • 加锁方式：update，insert和delete语句，InnoDB会自动加锁，select不会加锁，除非我们用for update进行显式的加锁。
  • 如果表中大量数据都需要用行锁，就会默认用表锁。因为那么多行锁，还不入干脆把整个表锁起来。
• 间隙锁（范围查询）。当我们适用范围条件检索数据时，Select * from emp where empid > 100 for update;此时就算最大empid=110，但是110以上的间隙也会加锁。
  • 目的：防止幻读，以满足相关隔离级别的要求。为了满足其恢复和赋值的需要。

死锁之后怎么办？？

• InnoDB引擎会自动探索到死锁。原理为wait-for graph。将事务看做一个个节点，资源就是各个事务占用的锁，当事务1需要等待事务2的锁时，就生成一条从1指向2的有向边，从而生成一个有向图。如果图中有环，就说明互相等待，则形成死锁。

MVVC Multi-Version Concurrency Control

传统数据库使用共享锁和排它锁使读写操作串行；MySQL使用MVCC和排它锁，读写可并行。MySQL在RR隔离级别以下，和传统方式表现一致，在RR隔离级别，和传统方式有差异，体现在本事务更新某条数据后，能读取到其他事务对该条数据已提交的修改。

• 与锁的关系
  • 排它锁 是 串行执行
  • 共享锁 是 读读并发
  • 数据多版本 是 读写并发。

• MySQL 加锁处理分析 http://hedengcheng.com/?p=771#_Toc374698322

• MVVC是多版本的并发控制协议，与MVVC相对的是基于锁的并发控制。
  • 读不加锁
  • 读写不冲突
  • 读操作分为 快照读 snapshot read和当前读current read。
    • 快照读 读取的是记录的可见版本，有可能是历史版本
    • 当前读 读取的是记录的最新版本，并且当前读返回的记录都会加上锁，以保证其他事务不会再并发的修改这条记录。
• 问题:哪些是快照读，哪些是当前读呢？

1. 简单的select语句属于快照读。
2. 特殊的读，比如lock in share mode,for update。写操作。
   
   解释，为什么写操作也叫做“当前读”。因为update和delete都要先读取到记录在哪里，然后再对记录进行操作。insert可能会存在unique key的冲突检查，因此也会进行当前读。

• 传统的两段式锁。
  

• 不用MVCC情况下的加锁

  • 在RC隔离级别下，修改数据会加排它锁，事务结束释放，其他事务不许读，解决脏读问题。（共享锁当场释放）
  • 在RR隔离级别下，读数据加共享锁，事务结束释放，其他事务不许修改，解决不可重复读。（共享锁事务结束释放）

• MVCC下的加锁

  • id主键+RC 聚簇索引锁主键所在记录，排它锁
  • id唯一索引+RC 有两个B+树，先去第一个B+树，然后锁了id所在记录，得到主键后去第二个B+树，找到主键记录后加锁。排它锁
  • id非唯一索引+RC 第一个B+树会搜到若干个id记录，都加排它锁，然后到主键的B+树，继续加锁。
  • id无索引，没有B+树，直接对聚簇索引进行加锁。先给每条记录加锁，然后再对不满足条件的记录放锁。
  • id主键+RR（可重复读） 与RC一致。为什么不用间隙锁？因为主键保证唯一。
  • id唯一索引+RR 与RC一致。唯一索引保证唯一。
  • id非唯一索引+RR GAP是锁在了间隙上，即两条记录之间。不允许在查询到的记录集上进行insert和delete操作。同理，在范围查找也是一样的。此时，第一个B+树有X锁和间隙锁，第二个B+树上，即聚簇索引上只有X锁。因为第一个B+树都不会插入了，第二个显然也不会插入。
    
  • id无索引+RR
    
    全表加锁，包括间隙。
  • 串行化
    select * from t1 where id = 10在RC,RR中都是快照读，不加锁的。但是串行化中，不存在快照度，必须进行当前读。

• 死锁

  • 两个Session的两条SQL产生死锁
    
    session1的第一条sql执行后，阻塞了，因为5被锁了。session2的第一条sql执行后，也阻塞了，因为1被锁了。然后大家都阻塞在这，因此形成死锁。
  • 两个Session的一条SQL产生死锁
    
  • 死锁的本质在于，两个或以上的session加锁的顺序不一致。
  • 发生死锁怎么办

• 实现原理

  • InnoDB存储
    隐藏列
    
    如果没有建立主键则会自动生成row_id
    

    • DATA_TRX_ID表示最新更新这行记录的事务id
    • DATR_ROLL_PTR回归指针
    • DB_ROW_ID 默认主键
    • DELETE BIT用于标识删除，commit的时候才删除

  • 流程
    begin->用排它锁锁定该行->记录redo log->修改当前行的值，写事务编号->回滚指针指向undo log中修改前的行

  • CURD

    • select
      • 只查找版本号早于或等于当前事务版本的数据行，即快照。确保事务读取的行是之前已经存在的，或者当前事务创建或修改的
      • 行的删除版本你一定是未定义的或者大于当前版本号的，表示本事务开始之前，没有被删除
      • 总结：早存在的，晚删除的
    • insert
      • 更新记录DATA_TRX_ID
    • delete
      • 更新记录DATA_TRX_ID
    • update
      • 是insert和delete的结合
      • 更新记录DATA_TRX_ID

  • 数据多版本实现的原理是：

    1，写任务发生时，首先复制一份旧数据，以版本号区分

    2，写任务操作新克隆的数据，直至提交

    3，并发读的任务可以继续从旧数据（快照）读取数据，不至于堵塞

  • 本质

    • Innodb只是借了MVCC这个名字，提供了读的非阻塞而
    • 真正的MVCC思想其实你也见过，JUC包里面的读时复制数组。要读的时候就复制出来一个数组让你读，写在原数组上面做。

事务与隔离级别

• 四大特性ACID
  • 原子性 一个事务内的sql语句，要么都生效，要么都不生效。体现在回滚性。redis不支持回滚，但是官方认为其具有原子性。
  • 一致性 针对约束而言，事务的开始和结束都满足数据库的约束，即状态转移的前后以及转移的过程中不会发生错误。redis中没有约束。
  • 隔离性 具有一定的隔离能力。redis单线程，显然具有隔离性。
  • 持久性 数据库的操作是持久化的，物理层面而言是写入硬盘的，而不是在内存中。redis具有部分持久性，表现形式为AOF和RDB，但是可能丢失2s数据。
• 隔离级别
  • 串行化 从MVCC并发控制退化成基于锁的并发控制。不区别快照读和当前读，所有读操作均为当前读，读加共享锁，写加排它锁。
  • 重复读（MVCC解决） 针对当前读，保证对读取到的数据加锁，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入（间歇锁），不存在幻读。
  • 读已提交（共享锁+排他锁解决） 保证对读取到的记录加锁，存在幻读，存在不可重复读，但是不存在脏读
  • 读未提交 什么锁都不加
• 脏读、不可重复读、幻读
  • 脏读 一个事务读取到另一个事务还未提交的数据
  • 不可重复读（单条数据） 同一个事务内执行两次同样的查询操作，但是结果却不同
  • 幻读（多条数据） 在一组事务操作中先后读取一个范围内的数据，比如select * from user where age=23;发现两次读取到得数据条数不一样，比如本来返回10条，由于其他的事务在事务1还未执行完成的时候添加了一条，这时候看到了11条。

数据库引擎的区别

• InnoDB

  • 索引为聚簇索引，最终目的是在主键所在B+树中找到对应页，然后从页中通过二分法获得最后记录。（不记录行号）
  • 表中必须有主键。
  • 支持事务。
  • 具有表锁与行锁，RC级别通过对写操作上排它锁，RR下通过MVCC控制。RC模式下读读并发，读写，写写串行。RR模式下，读写并发。
  • 不支持外键。
  • 查询count(*)慢，因为没有表的总行数
  • 不可全文检索
  • 适合大量insert和update，因为支持并发读写。
  • .frm描述表结构，其他所有表放在同一个文件下。

• MyISAM

  • 索引为非聚簇索引，B+树中也是根据主键排序的，但是是为了获得行号。行号相当于是这条记录的指针，指向了这条记录的真正位置。（记录行号）
  • 允许表中没有主键和索引。
  • 不支持事务。
  • 只有表锁，通过读锁和写锁控制并法。读读并法，读写，写写串行。
  • 支持外键。
  • 查询count(*)快，因为保存有表的总行数
  • 可全文检索
  • 适合大量select操作,因为B+树的节点不包含数据，只包含指针，因此消耗内存小，一次性载入的索引多。而且表锁开销小，因此读取快。
  • 存储形式为数据表，索引表，.frm描述表结构。优势在于便于拷文件，innodb必须用xxx.sql的备份，很慢，因为实际数据库一般很大。

• Memory

  • 特点是保存在内存中，性能强，但没有持久化。
  • 用于目标数据较小，数据临时，不需要持久化。
  • 索引支持B树和散列。

数据库的主从复制

• 概论

  • 一主多从，读写分离。
    

  • 多主一从
    

  • 双主复制

  • 级联复制
    

  • 主从复制原理图
    

• 具体操作

  • 主服务器：

    • 开启二进制日志（开启bin.log的将作为master

    • 配置唯一的server-id（比如是1）

      

    • 获得master二进制日志文件名及位置（指定要同步的数据库）

    • 创建一个用于slave和master通信的用户账号

      

  • 从服务器：

    • 配置唯一的server-id（不得为1，可以用2）

    • 使用master分配的用户账号读取master二进制日志

        CHANGE MASTER TO MASTER_HOST='47.102.108.38', MASTER_USER='repl', MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',  MASTER_LOG_POS=1026;
      

    • 启用slave服务

        start slave；
        show slave status\G;
      

      

• 原理

  • 三个线程，一个运行在主节点log dump thread，两个IO线程，I/O thread与SQL thread

  • 主节点log dump thread

    • 用于发送bin-log的内容。
    • 在读取时，此线程对主节点的bin-log加锁，当读取完成，甚至在发送给从节点之前，锁被释放。

  • 从节点IO thread

    • 从节点执行start slave后，创建io线程，用来连接主节点，请求主库更新bin-log。IO线程接收到主节点bin-log dump进程发送来的更新后，保存在relay-log（中继日志）中

  • 从节点SQL thread

    • SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从一致性。

  • 当有多个从节点时，主节点中会有多个log dump thread，以确保每一对主从都有完整的3个进程。

  • 整体流程

    

  • 流程解释

    • 从节点IO进程连接主节点，想要读binlog，请求从指定日志文件的指定位置的日志内容。
    • 主节点通过bin-log dump线程写binlog。参数需要binlog的filename以及position。
    • 从节点IO进程接收到内容后，将内容更新至relay log，并更新binlog的filename和position。
      
      备注：position已经更改了。
    • 从节点sql进程读取reply log中新添加的内容，将内容解析后在数据库中执行。

  • 主从复制模式（客户端的感知）

    • 异步模式
      
      异步模式下，主节点不会主动将binlog内容推送至从节点。

    • 半同步模式（只要有一个从机备份即commit）
      
      半同步模式下，确认主机至少同步到了一台从节点上，解决数据丢失问题。

    • 同步模式

      • 主节点与从节点全部执行commit并确认才会向客户端返回成功。

  • mysql支持的复制类型(binlog记录格式)

    • 基于sql语句的复制。只记录写操作的sql语句，节约binlog大小。对于now()这种函数，复制不正确。
    • 基于行的复制。将改变的行复制过去，但是会产生大量日志：因为记录一条sql语句可以改变n行，特别是表操作，日志就更大了，导致同步时间增加。并且维护时无法分析sql语句，只能看到数据的改变。
    • 混合模式。优先sql，sql不行的，在用行复制来解决。

  • GTID复制模式(与binlog复制模式对应的，5.7后新提出的一种复制模式)

    • 传统复制模式通过binlog的filename和position来确定从哪里复制。问题是容易出错。
    • GTID指的是全局事务ID。
      • 主节点写操作均会生成GTID，并记录在binlog中。
      • 从节点将binlog写入relaylog。
      • sql线程对比relaylog与本地binlog（注意，此时从节点也需要开始binlog）中的GDID对比，如果已经存在说明已经执行，就丢弃，否则执行。

  • 如何解决延迟和主机宕机?

    • 主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。
      所以 MySQL 实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。
      这个所谓半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
      并行复制，5.7之后表现为slave和master的放回是一致的。即master上怎么执行，slave就怎么执行。在binlog加入一些字段来标志可并行操作的事务组，然后并行的执行。
    • 如果还有延迟？可以将一个主库拆成多个主库，并发量就减少了。

触发器与存储过程

• 存储过程
  • 是可编程的函数，在数据库中创建并保存，是预编译的。由sql语句和控制结构组成
  • 优点
    • 增强sql语句的灵活性，具有流程控制的特点。
    • 因为是预编译的，因此速度很快。而批处理的sql语句每个都需要重新编译，因此较慢。
    • 只需要发送一条sql命令即可，减少网络通讯速度。
    • 对存储过程的权限进行限制，比较安全。相当于是数据库级别的防御，比应用层级别的防御更可靠。
  • 缺点
    • 移植性差
    • 从哲学上来说sql本来就是结构化查询语句，不应该控制流程，专业的事情让专业的人去做
    • 无法集群
• 触发器
  • 触发器是特殊的存储过程，由事件触发，事件包括insert，update，delete。当表中出现特定事件时，将激活相应操作。

视图与游标

• 视图
  • 视图是虚拟存在的表，是一个逻辑表。比如select出来的结果，就是一张视图，你可以把它保存成视图。
  • 优点
    • 简单：使用视图的用户不需要关心实际表的结构，约束等，已经是过滤好复合条件的结果集。
    • 安全：对视图的操作有限，相当于权限收到约束。比如说员工表的salary不想让你知道，就select员工表的其他列给你。
    • 数据独立：视图确定后，即可脱离原数据单独存在。原表增加列对视图没有影响，但是可以手动刷新视图。
    • 总结：保障数据安全，提高查询效率
• 游标
  • 结果集中有很多行。但是我们没办法得到第一行，下一行或者下10行。但是使用游标就可以在结果集中进行以行为单位的操作。
  • 必须用在存储过程与函数汇总。
  • 在select前申明游标，表示对select的结果集可以通过游标操作。
  • 游标被打开后，通过fetch可以访问记录集中的每一行。

数据库优化

https://blog.csdn.net/si444555666777/article/details/82111355

• sql语句及索引优化

  • 如何发现有问题的？
    • 通过mysql慢查询日志对有效率问题的sql进行监控。何为慢查询日志？mysql会记录下查询超过指定时间的sql语句，这种语句被称为慢查询。
    • 通过explain查询和分析sql的执行计划。
    • sql语句的优化。
      • insert一次插入多个
      • 避免在where中有范围查找，如果有，考虑是否能使用索引
      • 避免在where中对字段进行null值判断，否则会放弃使用索引
      • 子查询可以被join取代
  • 索引优化
    • 对需要查询，排序的字段增加索引
    • 以下情况会索引失效
      • %开头的like
      • or语句前后没有同时使用索引
      • 数据类型刚出现隐式转化，比如varchar不加’'会被转化为int
      • 对多列索引，即组合索引，检查是否满足最左匹配原则
      • 对于count()，sum()或运算，会导致不能使用索引
    • 其他优化见本文索引章节的最后

• 数据表结构优化

  • 选择合适数据类型，能小就小，能简单就简单。
  • 运用范式与反范式。
  • 对表进行垂直拆分即把含有多个列的表拆分成多个表。
    • 把不常用的字段单独放入一个表中。
    • 把大字段单独放在一起一个表里。
    • 吧经常用的放在一起。
    • 拆分后衣物清洗，系统之间整合或扩展容易。
  • 水平拆分。比如一个表有1000万条记录，可以分到10张表去，每张表的结构完全一致。
    • 对ID进行hash化。
  • 分库。将数据放到多个数据库中，提高系统的总体可用性。

• 系统配置的优化

  • 增加TCP队列数。
  • mysql配置文件优化。

• 硬件优化

  • CPU，内存，SSD。

牛客网面试题

1. 索引的实现方式

2. 触发器的作用（触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。）

3. 存储过程优缺点（创建一次，多次使用。调用简单，可提供部程序调用。缺点：移植性差）

4. 索引作用、优缺点、底层（优点：加速对数据的索引。缺点：降低数据库录入速度，占空间，后期维护等，B+树）

5. 事务的特性、并发问题、隔离级别（ACID：A原子性、C一致性、I隔离性、D持久性。并发问题:脏读、不可重复读、幻读。 隔离级别：读未提交、读已提交、可重复读、串行化）

6. 视图和游标（视图：是一种虚拟的表，具有和物理表相同的功能。游标：是对查询出来的结果集作为一个单元来有效的处理）

7. 视图优缺点（优点：选择性的读取数据库、通过简单查询的得到一些原本需要复杂查询才能得到的结果、维护数据的独立性，试图可从多个表检索数据、对于相同的数据可产生不同的视图。缺点：查询视图时，必须把视图的查询转化成对基本表的查询）

8. 数据库查询语句很慢，如何优化（索引、优化sql尽量不做全表查询、减少表关联、优化查询字段等等）

9. 数据库三范式与反范式（1NF：确保每一列的原子性 2NF：非键字段必须依赖于键字段 3NF：在1NF基础上，除了主键以外的其它列都不传递依赖于主键列，或者说： 任何非主属性不依赖于其它非主属性
   （在2NF基础上消除传递依赖））

10. union和union all有什么不同（union：合并去重、union all ：只是合并，一般没有重复数据用union all 因为更快）

11. SQL语言共分为四大类（数据查询语言DQL：select /from/where，数据操纵语言DML：insert/update/delete，数据定义语言DDL:create/drop/truncate/alert，数据控制语言DCL:grant/rollback）

12. drop、delete、truncate区别（不带where的delete和truncate只删除表的数据，不会删除表的结构。速度drop> truncate > delete，delete是dml要等事务生效 、drop和truncate属于ddl理解生效）

13. 数据库的乐观锁和悲观锁（乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作）

14. mysql设置隔离级别（语句：set session transaction isolation level read uncommitted 可选参数：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE）

15. SQL约束（NOT NULL不为空、UNIQUE唯一、PRIMARY KEY主键、FOREIGN KEY外键、CHECK控制字段范围）

16. Mysql数据库引擎MyISAM和InnoDB（MyISAM：表锁、不支持事务、不支持外键、全文索引、快、内存利用低，InnoDB：行锁、外键、事务）

17. varchar和char（varchar可变长度、char长度固定）

18. 数据库锁分类（按锁的粒度分：表锁、行锁、页级锁 。按锁的级别划分：共享锁、排他锁 。按加锁方式分：自动锁、显示锁。按操作划分：DML锁、DDL锁 。按使用方式分：悲观锁、乐观锁）

19. 常用数据库默认隔离级别（mysql：可重复读 oracle：读已提交 SQLServer：读已提交）

20. Oracle高水位理解（Oracle段（segments）就像水库记录历史水位，一般不会下降，使用truncate命令可以置零）

21. 内连接外连接区别（内：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件 外：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接）

22. 查询语句有like（尽量避免%XXX%，用不到索引，%在前面不能用到索引，如%ABC不能用索引，而ABC%能用索引 ps：这里感谢大佬@Apiao 大佬指出错误，已更正，万分感谢！）

23. mysql数据库优化（explain写的SQL、只要一行数据用limit 1、使用enum而不是varchar、固定长度的表会更快、分库分表）

24. oracle数据库优化（调整数据结构设计、调整操作系统参数、调整数据库sql语句、调整服务器内存分配、调整硬盘I/O）

25. InnoDB的聚集（聚簇）索引是什么？辅助索引是什么？

26. InnoDB的redo log和MySQL的binlog有什么异同？

27. InnoDB里实现了哪几种锁？

28. MVCC是怎么实现的？

29. 数据库水平切分，竖直切分

30. 继承映射

31. 数据库连接池

32. 诊断数据库耗时，mybatis层面加拦截器，做个插件拦截SQL执行时间怎么做？

33. 数据库多个线程修改查询怎么保证一致？

34. 数据库join怎么实现的

35. 数据库死锁

36. 索引失效

37. 数据库中如何处理并发冲突，能解决什么问题

38. 数据库的底层数据结构有什么