mysql 数据库知识点

数据库

MySQL

参考 《高性能mysql》，教材：《数据库系统原理》，《MYSQL实用教程》

一，MySQL服务器逻辑架构图(mysql运行原理)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4t12BMj7-1591670382054)(en-resource://database/568:1)]
客户端发送连接请求，

• 长连接与短连接区别，优缺点，应用，
  mysql的短连接是每次查询都需要经历建立连接，查询，断开连接。
  mysql的长连接只需要建立一次连接，可以进行多次操作，mysql会维持这个链接
  因为mysql对连接数有限制，长连接适用于用户量少，操作频繁的场景（比如后台管理系统）。短连接适合于用户量大的系统
• 使用单例构建连接池

二，MySQL如何实现并发控制

锁的类型，作用。

• 粒度分类：
  表级锁：粒度最大的一种锁，会锁住当前操作的整张表，并发性能低，但表锁的实现简单，耗费资源少，加锁快，不会出现死锁。
  行级锁：粒度最小的一种锁，只会锁住当前操作的数据行。行锁极大地提高了Mysql的并发性能，但行锁的开销较大，速度较慢，会出现死锁

• 性质分类
  共享锁（读锁）： 共享锁锁住数据行，其他事务可以读取，但不能修改，并且也只能对该数据行加共享锁，而不能加排它锁。
  排它锁（写锁）：排它锁锁住的数据行，会阻塞其他事务进行读写，也不允许其他事务添加任何锁。

• 表锁：意向共享锁（IS），意向排他锁（IX）。
  意向共享锁（IS）：意向锁的作用是表明该事务想对该表加一个共享/排他锁，但并没有真正把锁加上去。比如，当事务想对一个被排他锁锁住的表加上共享锁/排他锁时，必须先在该表上添加一个意向共享锁/意向排他锁，直到锁住表的排他锁被释放
  事务在给一个数据行加共享锁前必须先取得该表的IS锁，在加排它锁前必须先取得该锁的IX锁。并且意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在

• 行锁：InnoDB行锁是通过给索引上的索引项加锁来实现的，这意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
  Innodb中的行级锁有以下几种：
  1） Record Lock: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
  2） Gap Lock: 对符合条件范围的“间隙”加锁，锁定记录的范围，不包含索引项本身。其他事务不能在锁范围内插入数据。“间隙（GAP)”是指 键值在条件范围内但并不存在的记录。
  3） Next-key Lock： 锁定索引项本身和间隙。Record Lock和Gap Lock的结合，Next-key Lock就是我们所说的间隙锁，可解决幻读问题。

• 死锁
  不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务，我们可以采取以上方式避免死锁：
  1） 通过表级锁来减少死锁产生的概率；
  2） 多个程序尽量约定以相同的顺序访问表；
  3 ）同一个事务尽可能做到一次锁定所需要的所有资源。

  参考

• 悲观锁和乐观锁

  • 乐观锁，对加锁持有一种乐观的态度，即先进行业务操作，不到最后一步不进行加锁，"乐观"的认为加锁一定会成功的，在最后一步更新数据的时候在进行加锁。实现：乐观锁的实现方式一般为每一条数据加一个版本号,修改数据的时候首先把这条数据的版本号查出来，update时判断这个版本号是否和数据库里的一致，如果一致则表明这条数据没有被其他用户修改，若不一致则表明这条数据在操作期间被其他客户修改过，此时需要在代码中抛异常或者回滚等
  • 悲观锁，对数据加锁持有一种悲观的态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制
    参考

事务的定义，特性

• 事务是mysql操作的逻辑工作单元 特性：ACID
  原子性： 事务的操作全部成功或者全部失败
  一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态
  隔离性：不同事务(事务A与事务B)之间具有隔离性
  持久性： 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久的

事物的隔离级别和各自存在的问题（脏读、不可重复读、幻读）和实现方式

• mysql的默认隔离级别是 可重复读

  隔离级别	出现问题	如何实现
  读未提交（RU：read-uncommitted）	脏读，不可重复读，幻读	一级封锁协议
  读已提交 （RC：read-committed）	不可重复读，幻读	二级封锁协议
  可重复读 （RR）	幻读	三级封锁协议
  串行化 （SERIALIZABLE ）		加表级的读写锁

• 产生的问题
  丢失修改：丢失修改：指事务1和事务2同时读入相同的数据并进行修改,事务2提交的结果破坏了事务1提交的结果,导致事务1进行的修改丢失（不满足读未提交会出现丢失修改的问题）
  脏读：当前事务可以查看到别的事务未提交的数据
  不可重复读：在同一事务中，两个相同的查询返回了不同的结果
  幻读：事务A修改表中的数据，事务B向表中插入一行数据

如何实现隔离级别（三级封锁协议）

• 一级封锁协议：事务T在修改数据A之前必须对其加X锁，直到事务结束才释放。
  事务结束包括正常结束（Commit）和非正常结束（RollBack）。
  二级封锁协议：一级封锁协议加上事务T在读取数据A之前必须先对其加S锁，读完后即可释放S锁。
  进一步防止了读“脏”数据
  三级封锁协议：一级封锁协议加上事务T在读取数据A之前必须先对其加S锁，直到事务结束才释放。
  进一步防止了不可重复读
  一级封锁协议可防止丢失修改。

mysql innodb RR隔离级别下，如何防止幻读问题的发生

• mysql 默认隔离级别为可重复读，但是通过某种手段解决了部分幻读问题，在一定程度上达到了串行化的隔离级别。
• 为什么会产生幻读
  在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，**却无法锁住insert的数据，**所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据。

  快照读和当前读
  快照读：就是普通的selectselect * from table ….;
  当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。

• 对于快照读的场景，通过mvcc版本管理来解决幻读的问题。就是a事务只做了两次查询操作，两次查询中间即使有符合条件的插入，第二次查询的结果也是原来的数据信

  mvcc:
  在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下：SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。INSERT时，保存当前事务版本号为行的创建版本号DELETE时，保存当前事务版本号为行的删除版本号UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行
  参考

• 对于当前读的情况，mysql是通过Next-Key锁(间隙锁) 搞定的。就是再当前读的情况下，会加入一个范围锁，锁住一个区间，区间内如果有别的事务进行插入操作，是要等待当前事务提交的。
• 间隙锁
  间隙锁是一个在索引记录之间的间隙上的锁。
  当时用唯一索引来搜索语句时，不会产生间隙锁；使用 普通索引或者多个条件会产生
  间隙的范围根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）
• 产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的 “ 间隙 ” 。为了解决幻读问题， InnoDB 只好引入新的锁，也就是间隙锁 (GapLock) 。对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”锁的就是两个值之间的空隙。在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。
  间隙锁详解 间隙锁详解
• MVCC
  • MVCC ：使innodb在事务隔离级别下执行一致性操作有了保证，（就是查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值）这样查询就不用等待另一个事务释放锁，增强了数据库的并发性。MVVC被认为是一种乐观锁的实现方式
  • MVCC原理： mysql中MVCC的实现，就是通过在每行中添加DB_TRX_ID：当前存储版本号，DB_ROLL_PTR：事务回滚指针，DB_ROW_ID：自增行ID三个字段，通过对三个字段的操作来实现不加锁的读写操作。
    “多版本并发控制”
    MVCC增删改查：
  • 增： InnoDB为每个新增行，记录当前系统版本号作为创建ID。
  • 删：将当前系统版本号记录在删除行ID字段
  • 改：复制了一行。这个新行的版本号使用了系统版本号。把系统版本号作为了（被复制行）删除行的版本。
  • 查：读取早于当前事务版本的数据行。
    行的删除操作的版本一定是未定义的或者大于当前事务的版本号。
    （这确保当前事务 读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行）
    参考1，参考2
• 关于间隙锁等一切锁，都是指的是在默写情况下，mysql为了保证隔离级别，避免出现各种问题，回去加不同的锁。

三，MySQL存储引擎

innodb和myisam存储引擎特性，区别

MyISAM存储引擎的特点是：表级锁、不支持事务、支持全文索引
InnoDB存储引擎的特点是：行级锁、事务安全（ACID兼容）、支持外键、老版本不支持全文索引（5.6）（InnoDB 中不保存表的具体行数）
MyISAM适合：
（1）做很多count 的计算；
（2）插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；
（3）没有事务。
InnoDB适合：
（1）可靠性要求比较高，或者要求事务；
（2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；
（3）如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表

（参考：https://m.jb51.net/article/157181.htm）

• mysql 5.5 之前默认引擎为 MyISAM，之后默认为InnoDB

四，数据库原理

数据库三大范式

第一范式（1NF）要求数据库表的每一项都是不可分割的基本数据项
第二范式（2NF）在第一范式的基础上，每一个非主属性完全函数依赖于候选键
第三范式（3NF）满足第二范式，且每一个非主属性都不传递函数依赖于候选键

理解：
第二范式确保数据库中的每一列都和主键相关，而不能和主键的一部分相关
第三范式需要数据表中的每一列都直接和主键相关，而不能间接相关

五，索引

索引定义

索引是是在存储引擎层实现的，不同的存储引擎实现索引的方式不同。
如果在查找的列添加了索引，则此列上的数据就以索引所在的结构（b+等）存储，然后根据匹配到的索引记录找到对应的数据行

索引的使用

• 最左前缀原则
  单列索引和多列索引（在多个列上添加一个索引）的使用时，多列索引只有在where条件中含有索引中的首列字段时才有效
• MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)
• 创建索引时，尽量选择经常在where和join中出现的列
• 考虑列中值的分布，索引的列的基数越大，索引的效果越好
• 不要过度使用索引，更新表时索引也会更新，索引越多消耗时间越长

（参考：https://my.oschina.net/imxiangyang/blog/37205）

• 复合索引在什么情况下使用
  如果一个表中的数据在查询时有多个字段总是同时出现，则这些字段就可以作为复合索引,形成索引覆盖可以提高查询的效率，
  复合索引的使用原则是第一个条件应该是复合索引的第一列,依次类推,否则复合索引不会被使用，所以正常情况下复合索引不能替代多个单一索引。
• 索引使用策略
  • 独立的列（索引的列必须是独立的，不可以出现在函数或者式子中）
  • 前缀索引（对于列字段较长的列建立索引时，应建立前缀索引）和索引选择性
• 唯一索引 比 普通索引好吗
  对于可以使用唯一索引的列使用唯一索引是比普通索引效果要好，唯一索引有最好的索引选择性（为1）（指：不重复的索引值比记录总数），索引选择性高的可以使mysql在查询时过滤掉更多的行
• 选择

索引的类型和区别

主键索引（PRIMARY），普通索引（normal），唯一索引（unique），全文索引（full text）

• 如何选择索引
  为字段（列）选择索引时要根据列的用途和数据类型，对于列中存放的具有唯一性的信息使用唯一索引（例如：身份证号，编号，学号），对于文本信息使用全文索引（只能加在CHAR、VARCHAR或者TEXT类型的列上）（备注，文章），对于没有特殊要求的列使用普通索引即可（姓名）。
  注意主键索引是唯一的，但不是唯一索引，因为唯一索引可以为空，主键索引不可以为空
  参考

索引的存储方式

• 聚簇索引和非聚簇索引的区别

  • innodb使用的是聚簇索引，myisam使用的是非聚簇索引（都是B+树）
    聚簇和非聚簇指的是数据和索引是否在一起存放

  • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据（存放数据）（B+树的叶子节点data中存放的事数据）
    innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。先通过辅助索引中检索列的值，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。
    innnodb 的主键索引为聚簇索引，其他所有列上的索引都是二级索引，或者称为辅助索引
    聚簇索引具有唯一性，一个表仅有一个聚簇索引，其他索引都是辅助索引。一个表至少有一个聚簇索引（主键索引），聚簇索引默认添加在主键上，当没有定义主键时，默认添加在非空列上。

  • 非聚簇索引：（存放地址）将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行**（B+树的叶子节点data中存放的是指向数据的指针）**，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。
    参考

什么是覆盖索引

查询列被所使用的索引覆盖
当覆盖索引时，使用explain 的 extra列可以看到 using index

索引的存储结构（索引的方法）

B+ Tree 和 Hash
Mysql 索引的结构为B+树，MongoDB选用B-树

• B-tree
  参考

1. B 树的定义
   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JTQQTOXL-1591670382060)(en-resource://database/560:1)]

B-tree 是一颗多路平衡查找树（我们描述一颗B树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子结点，一般用字母m表示阶数。当m取2时，就是我们常见的二叉搜索树）。在实际应用中的B树的阶数m都非常大（通常大于100，一般m的大小取决于磁盘页的大小），即使存储大量的数据，B树的高度仍然比较小。

2. 特点
   1）每个结点最多有m-1个关键字。
   2）根结点最少可以只有1个关键字。
   3）非根结点至少有Math.ceil(m/2)-1个关键字。
   4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
   5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。

3. 查找
   从根节点向下查找，对于每个节点的关键字符合特点第四条

4. 插入
   若查找到相应的值，则直接替换，若没有，则插入，注意每个节点不能超过m-1，超过后选取中间值 上移（左旋右旋），然后此节点分裂为两部分，直至每个节点都满足不能超过m-1（平衡）（特点1：每个结点最多有m-1个关键字。）

5. 删除
   查找失败，则删除失败，查找成功后，若删除的key位于非叶子节点，则用后继（值差别最小的）的key覆盖当前key，即位于叶子节点的后继key上升；若此时大于 ceil(m/2)-1 则结束，小于则调整，向兄弟节点借key，或者父节点下移与两个孩子节点合并。

• B+Tree
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E2EDBufU-1591670382064)(en-resource://database/562:1)]

1. 特性
   1. B+树包含2种类型的结点：内部结点（也称索引结点）和叶子结点。根结点本身即可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。
   2. B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中。
   3. m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。
   4. 内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列（大于等于决定了分裂的方式）
   5. 每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接
2. 插入
   插入后，若该节点个数小于m-1（m），则插入结束，若大于，则拆分为两部分放入两个子节点，其中 m/2+1处的key 复制到父节点
   （B树和B+树区别在于插入时，B树中没有重复元素。B+树的节点是从叶子节点中提取的）
3. 删除
   若没有查找到相应的点则删除失败，若查找到则删除。删除后，若该节点大于Math.ceil(m-1)/2 – 1 则结束，若小于，则向兄弟节点借相邻的key，并且此key复制到父节点，若兄弟节点借出后可以满足特性则借出，若不满足，则合并。此时在调整父节点，直至满足特性

• Hash

B-Tree和B+Tree的区别

结构区别：根据b树和b+树的特点可以看出，b树的非叶子节点也存储数据，b+树的只在叶子节点存储数据，且叶子节点使用单链表连接…

• 为什么说B+树比B树更适合数据库索引？
  1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
  2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
  3、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

B树（B+树）与二叉树相比的优点

因为我们要考虑磁盘IO的影响，它相对于内存来说是很慢的。
（主存和磁盘之间的数据交换不是以字节为单位的，而是以n个扇区为单位的（一个扇区有512字节），通常是4KB（8个扇区），8KB（16个扇区），16KB，……64KB为单位的。假设，我们现在选择4KB作为内存和磁盘之间的传输单位，那么我们在设计B+树的时候，不论是索引结点还是叶子结点都使用4KB作为结点的大小）
数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而“矮胖”就是b树的特征之一，它的每个节点最多包含m个孩子，m称为b树的阶，m的大小取决于磁盘页的大小。
参考

使用ORM是否可以不关心索引

orm的作用是在关系型数据库和对象之间作一个映射，这样就可以以操作对象的方式来代替sql操作；框架大多都提供orm模型；orm在一定程度实现了业务逻辑和数据逻辑的解耦。
orm工具能生产合适的，符合逻辑的查询，但是很难生成适合索引的查询。所以除非是简单的主键查询，否则还是需要关心索引的

六，SQL语法

存储过程，存储函数，触发器

• drop、truncate、delete区别
  1. DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作
  2. TRUNCATE TABLE 则一次性地从表中删除所有的数据，不能回滚，不会触发触发器。
  3. drop语句删除表结构及所有数据，并将表所占用的空间全部释放；不能回滚，不会触发触发器；drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态

SQL语法

• char 和 varchar 的区别
  varchar是变长，char是定长；
  char 类型mysql会删除和填充空格，‘str ’会变为 ‘str’
  varchar（10）的列需要11个字节的存储空间，多出的一个字节存储长度。；适合varchar的场景，字符串的最大长度比平均长度大很多，更新较少的列；虽然对于varchar（5）和varchar（200）存储‘hello’的空间开销是一样的但是更长的列作为表或者排序的一部分被加载到内存中，会浪费内存
• blob 和 text
  都是用来存储大数据设计的，不同的是，blob 使用的是二进制，text 使用的是字符。所以 blob 没有排序规则和字符集，排序的话，也只对每个列前 max_sort_length（默认值）进行排序。但是mysql在对存储这两个类型的值进行查找时，需要使用磁盘临时表和文件排序。所以尽量少使用，比如一些评论，完全可以使用varchar ，少于万字的博客也可以使用varchar。此类型可以存储一些图片，blob（图片的二进制流）
  +枚举类型
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VMBBycRl-1591670382069)(en-resource://database/570:1)]
• 时间
  datetime 年-月-日 时：分：秒，timestamp ，秒级时间戳，但在时间戳转换为时间显示在mysql中受时区影响
  +范式化和反范式化
  范式化的优点：范式化操作更快；范式化数据更少，修改更有优势；符合范式化的表查询时会更少使用分组（group by）去重（distinct）语句
  反范式化优点：数据在一张表中，避免关联；
  反范式化的设计：多是复制列，或者缓存表。所以，在业务中，反范式化，就是更人性化，最终根据业务进行调整表的列
• 见《SQL使用总结》，关键在于按照要求写出SQL

SQL结构优化

• mysql 执行计划 explain**（如何优化sql）**
  (1) 打开慢查询，set global slow_query_log=1;
  查看慢查询相关：show variables like ‘%slow%’;
  设置慢查询的时间：set global long_query_time=1;（一般3s）
  得到的慢查询文件会记录慢查询的sql语句，方便进行分析
  set profiling = 1； 可以在执行sql后查看到影响的行数和执行时间
  (2)，对于慢查询的语句进行explain 分析
  结构如下：
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5RClACvV-1591670382074)(en-resource://database/580:1)].
  其中type表示连接类型，连接类型由好到坏依次是：
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• 查询语句不同元素（where、jion、limit、group by、having等等）执行先后顺序
  from ，on， join， where，group by， select ， having ，distinct，order by，limit（top）
  having 中出现的列必须在select列中

1. 只返回需要的数据
   • SQL分析查询缓慢和解决方式（explain，慢查询，show profile）
   • 查询不需要的记录（查询100条，只使用十条，者使用应该家LIMIT）
   • 不需要是不要返回全部列select *
   • 重复查询相同结果，应对相同结果进行缓存
2. 不要扫描额外的记录
   • 响应时间，扫描行数，返回行数
3. 重构查询

• 衡量一个复杂查询还是多个简单查询
• 切分查询
• 分解关联查询（可以提高缓存，减少锁竞争，减少亢余字段）
• mysql如何执行关联查询
  嵌套循环关联操作，即，现在第一个表中循环取出单条数据，然后嵌套循环下一个表中寻找匹配的行，然后在各个表匹配的行中查询需要的各个列，在最后一个关联表中找到所有匹配的行，然后返回上一层关联，迭代执行
• mysql优化器会调整关联表的关联顺序，以达到更好的效果，当然也可以人工查询，但是如果关联表数量太多，优化器只能“贪心”选择最优
• mysql 的 in 子查询并不好，执行时会先执行扫描外层全表，然后逐个与内层查询。(MariaDB在此处有改进)。
  解决办法：所以可以使用 Exists 和关联查询替代 in ，或者是左外连接（对两种查询进行分析，有时候并不一定哪个更快）
• union的限制：union会把联合的表放在一个临时表中进行操作，所以最后需要limit，可以先在union表中操作
• mysql 不支持等值传递，并行执行，哈希关联，松散索引扫描（某些功能MariaDB可以实现）

SQL 使用总结

1，LIMIT
Limit n ： 从0开始取n条数据
LIMIT m,n : 表示从第m+1条开始，取n条数据
LIMIT m OFFSET n：从 m 开始 取（偏移） n 条数据
2，join查询：
注意left，right，inner区别
FROM tablea a
(LEFT) JOIN tabkeb b
ON a.field = b.field
注意多次 join ，且需要左右连接时
3，自然连接：
from a，b
a.qwe = b.qwe
4，GROUP BY：
按照某个字段进行分组，多接having 对分组后的数据进行处理，如 count，avg, max函数等 ；需要注意的是，这些函数都可以在select后当做查询的字段使用，但如果要在WHERE 中使用，必须在HAVING 后
GROUP BY field HAVING field
SELECT emp_no,count(emp_no) as t
FROM salaries
GROUP BY emp_no
HAVING t > 15;
5，子查询 ：
WHERE a NOT IN/IN(
SELECT a FROM …
)
6，非重复查询
DISTINCT 用于返回唯一不同的值
SELECT DISTINCT 列名称 FROM 表名称
DISTINCT 可以加在任何字段前，也就是说不仅可以加在查询字段前，也可以用在数据库函数中的字段前
7，运算符操作
算数运算：SELECT 6+ 加法操作, 6-4 减法操作, 6* 4 乘法操作, 6/2 除法操作, 6 DIV 2 除法操作, 6%4 求模操作, 6 MOD 4 求模操作;
位运算 ： \ &
逻辑运算：AND &&

七，高性能MySQL

MySQL分区，分表

• 分区表的原理
  分区表是由多个底层表实现的，底层表由句柄对象表示，我们可以直接访问各个分区。
  优化器可以根据分区的函数来过滤一些分区。
• 分区表的特性
  分区表达式必须是整数，如果包含主键和唯一索引，那么所有的主键和唯一索引都必须包含；分区表无法使用外键约束；
• 分区表的优点
  相关数据在一个分区中，热点数据保存；更容易维护，方便删除数据；分区表可以分布在不同物理设备；在大数据集的情况下表现特别好
• 分区表的增删改查操作
  对于分区表数据进行操作时，mysql都会打开并锁住所有的底层表，然后确定某些分区去进行更新，删除，插入或者过滤某些分区去进行查找
• 分区的类型
  范围分区，哈希，列表
• 分区问题
  null 值会使分区过滤无效，按照某字段分区，若莫字段有空值，则会出现在一个分区，查询时很有可能会访问此分区，也未有可能出现
  分区列和索引列不匹配。
• 分区实例

视图

视图本身是一个虚拟表，不存放任何数据。在使用sql语句访问视图时，是从其他表中返回数据，mysql对待视图和表操作基本相同，但不能对视图创建触发器。
视图的使用：将select的结果放在临时表中，下次使用；在合适的情况下，更新视图就可以更新相关的表，但是视图中包含group_by，

外键约束

MySQL中只有InnoDB支持外键约束，
缺点：修改数据时需要检查外键，增加系统开销。优点：外键的两个表始终有一致性的数据，

主从复制

mysql 复制分为两种，基于语句的复制（将主库的sql在执行一次）和基于行的复制（将实际的数据记录在二进制日志文件中），这两种都是通过主库上记录的二进制日志，在备库重放日志的方式来实现异步的数据复制。复制通常不会增加主库的开销，主要是启用二进制日志带来的开销，（备库请求主库的二进制文件）

• mysql复制的作用
  负载均衡，可以将读操作分布在多个服务器上；数据备份，复制是数据备份的补充；使用高版本MySQL作为备库，可以保证更好升级
• mysql复制如何工作
  主库把数据更改记录在二进制日志中（日志事件）
  备库开启一个i/O线程，连接主库，将主库上的日志复制到自己的中继日志中
  备库读取中级日志中的时间，将其重放到备库数据之上
• 如何选择
  基于语句的复制：优点：基于sql使用更灵活；缺点：数据库（环境问题）的版本造成某些sql执行不一致
  基于行的复制：有点：基于实际数据，更加安全直观；缺点：无法判断执行了什么sql。因为基于行的复制，更类似于数据的更改而不是sql的执行
• 如何使用
  定期清理日志文件，
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LLuNhRBQ-1591670382083)(en-resource://database/572:1)]
• 主从复制（一主多备）
  一主多备的优点在于配质检单，方便管理。且多个备库可以承担不同的角色，例如远程备份，升级使用。也方便主备分离。
  mysql还有多种复制模式，比如主动-主动主-主复制，主动-被动主-主复制，拥有备库的主-主结构，环形复制，金字塔型
• 复制的问题
• mysql非正常关闭导致数据丢失
  主库意外关闭，使用工具检查主备一致性，从备库下载二进制日志文件
  从库意外关闭，观察mysql错误日志，找出备库指向主库的偏移量
• 磁盘中的二进制日志损坏.
  主库的二进制日志损坏，应忽略忽略损坏位置（SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1），然后可以在主库开始新的日志文件，在将备库指向改文件.
  备库中级文件损坏，丢弃损坏位置重新获取

负载均衡

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PAK2WV1H-1591670382087)(en-resource://database/586:1)]

读写分离

定义：使用主库进行写操作，并将读操作分配到主库和备库上。原因：备库的复制是异步的，所以

八，MySQL容灾备灾（构建高可用MySQL）

导致宕机的原因

运行环境问题（磁盘空间好近），sql问题，索引，列值不规范，复制导致主备数据不一致，删除的错误

提升平均失效时间，降低平均恢复时间

提升平均失效时间：就是要规避错误，及时监控，定期检测
降低平均恢复时间：就是提供亢余和故障转移的能力的架构

• 数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）
• 崩溃恢复 策略
• mysql日志