[面试]MySQL

MySQL的索引

索引的分类

• 主键索引：假如为某一个表建立主键之后，数据库会自动给主键所在的字段建立索引

  • 删除主键索引就等于删除主键；修改主键索引必须drop掉原来的索引，在新建主键索引

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

• 唯一索引：索引列的值必须唯一，但是可以为空

• 索引是对数据库表中一列或多列的值进行排序的一种结构（是一种数据结构），使用索引可快速访问数据库表中的特定信息，可以加快查询速度

• 索引往往以索引文件（.MSI）形式存储于磁盘上，索引一般指B树组织结构的索引

• InnoDB 存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在 B+ 树

• 索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

索引的劣势

• 用户过多的操作insert 等DML语句的时候，同时也需要维护索引
• 索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

索引使用场景

• 主键自动建立索引
• 频繁作为查询条件的字段应该建立索引
• 对排序、分组、联合查询频率高的字段创建索引
• 有主外键关系的字段需要建立索引
• 排序的字段，添加上索引，效率会更高
• 尽量使用数据量少的索引
• 尽量使用前缀来索引
• 删除不再使用或者很少使用的索引

不适合使用索引

• 表的记录太少
• 经常删改的字段或者是表
• where里面用不到的字段不用建立索引
• 过滤性不好的字段（比如带范围的，比如sex字段）

创建删除索引语句

CREATE TABLE

在建表时创建索引

ALTER TABLE

ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）

ALTER TABLE可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式
//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主键索引
alter table table_name add primary key (column_list) ;

CREAT INDEX

CREATE INDEX index_name ON table_name(username(length))

使用索引不一定能提高检索速度

• 由于索引是以索引文件形式存在于磁盘上,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率，那些不必要的索引反而会使查询反应时间变慢

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%

• 基于非唯一性索引的检索

如何删除百万级别数据

删除数据的速度和创建的索引数量是成正比

• 可以先删除索引
• 删除其中无用数据
• 对剩余数据重新建索引
  比直接删除快速，防止万一删除中断,一切删除会回滚

B树与B+树区别

• B树每个节点都存储键值和键值外所有数据的数据记录，所有节点组成这棵树，并且叶子节点指针为null
• B+树只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针，B+树上增加了顺序访问指针即每个叶子节点增加一个指向相邻叶子节点的指针

B树在存储中的作用和优点

B树特点

• 一种二叉搜索树。
• （M阶B树）除根节点外的所有非叶节点至少含有（M/2（向上取整）-1）个关键字，每个节点最多有M-1个关键字，并且以升序排列。所以M阶B树的除根节点外的所有非叶节点的关键字取值区间为[M/2-1(向上取整),M-1]
• 每个节点最多有M-1个关键字
• B树中叶节点包含的关键字和其他节点包含的关键字是不重复的

优点

• 采用二叉树存数数据，那么频繁的增加和删除数据，会让二叉树退化成一个链表，采用平衡二叉树维护所耗费的资源很大，计算也十分复杂
• B树的增加和删除节点对于整体的结构来说，改动非常的小，十分适合用来作为大数据存储的数据结构
• B树的每一个节点都包含key和value，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率

B+树在存储中的作用和优点

B+树特点

• 有n棵子树的非叶子结点中含有n个关键字（b树是n-1个），这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）
• 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接（叶子节点组成一个链表）
• 非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字
• 通常在b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点，同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素
• B+树的索引项只包含对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址

B+树优点

• 所有的叶子结点使用链表相连，便于区间查找和遍历。B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好
• 需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可
• b+树的中间节点不保存数据，能容纳更多节点元素

为什么底层数据结构使用B+树，而不是B树？

评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少

• B+树的内部结点并没有指向关键字具体信息的指针（内节点不存储data），节点就可以存储更多的key所以B+树IO代价更低

• 叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历；B树则需要进行每一层的递归遍历，相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好

• B+树是B树的变种，B+树的非叶子节点只用来保存索引，不存储数据，所有的数据都保存在叶子节点（B树非叶也会保存数据）使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径

B+树索引和哈希索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

• 等值查询，哈希索引只需要经过一次算法即可找到相应的键值（前提键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据）

• 范围查询无法使用哈希算法（原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索）

• 哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询

• 哈希索引也不支持多列联合索引的最左匹配规则

• B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题

聚簇索引和非聚簇索引

• 聚簇索引（主键索引），其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引，因为索引只能按照一种方法进行排序

• 非聚簇索引（普通索引），叶子节点内容是主键的值

MySQL回表

• 如果语句是 select * from User where id=3，即主键查询方式，则只需要搜索 主键索引树。
• 如果语句是 select * from User where uid=23，即普通索引查询方式，则需要先搜索 普通索引树，得到其对应的主键值为 3，再到主键索引树搜索一次这个过程称为回表

覆盖索引

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段

B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据，

• 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
• 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询

非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询

索引的最左前缀原则

最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边

索引下推

• 在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。
• MySQL5.6引入的索引下推优化，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率

最左前缀匹配原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

哪些列上适合创建索引？创建索引有哪些开销？

• 经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度
• 除了创建索引有开销外，还需要需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并），对性能造成影响。

自增主键：

为了在插入数据的时候不需要调整主键索引树的结构，强烈建议在建立表的时候使用自增主键。主键的顺序按照数据记录的插入顺序排列，自动有序
（主键会自动创建聚簇索引）

常见索引原则

• 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
• 为经常需要排序、分组和联合操作的字段建立索引
• 为常作为查询条件的字段建立索引。
• 限制索引的数目: 越多的索引，会使更新表变得很浪费时间。
• 尽量使用数据量少的索引， 太长查询速度会受到影响，如果索引字段的值很长，最好使用值前缀来索引。
• 删除不再使用或者很少使用的索引
• 尽量选择区分度高的列作为索引 区分度的公式是表示字段不重复的比例
• 索引列不能参与计算，保持列“干净”:带函数的查询不参与索引
• 尽量的扩展索引，不要新建索引

避免全表扫描

MySQL全表扫描

• 数据表是在太小了，全表扫描优于做索隐键查找（表的记录总数小于10且记录长度比较短）
• 没有合适用于索引字段

避免

• 通常小表适合全表扫描，大表需要进行优化避免全局扫描
• 执行 ANALYZE TABLE tbl_name== 更新要扫描的表的索引键分布==
• 使用 FORCE INDEX 告诉MySQL，做全表扫描的话会比利用给定的索引更浪费资源
• 启动 mysqld 时使用参数 --max-seeks-for-key=1000 或者执行 SET max_seeks_for_key=1000 来告诉优化程序所有的索引都不会导致超过1000次的索引搜索

EXPLAIN

• EXPLAIN关键字可以模拟优化器执行sql从而知道MySQL如何处理SQL，分析SQL或者表结构的性能瓶颈（并未执行该SQL）

作用

通过 ‘‘explain SQL ;’’

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用，被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

输出执行计划包含的信息：
id+select_type+table+type+possible_keys+key+key_len+ref+rows+Extra

性能指标参数

id 执行顺序

说明每个表的执行顺序，id越大执行顺序越早，id一样的按照顺序从前往后执行

select_type查询类型

主要用于区别普通查询；联合查询，子查询等复杂查询

• SIMPLE 简单查询，单表，无字查询 UNION
• PRIMARY 主查询
  包含子查询时，MySQL会递归执行子查询并把结果放在临时表
• DERIVED 衍生查询
• UNION

table

显示这一行数据来自哪张表

type

• 显示查询使用的索引类型，从好到坏依次是：system>const>eq_ref>ref>range>index>ALL

• system：系统参数 （已经查询出放入内存再查一次）
  const：记录在表中的常量（即固定在索引树上的查询量，不需要过滤）
  eq_ref：唯一性索引索引扫描对于每个索引键只有一条记录匹配，常见于主键或唯一索引扫描

  ref：非唯一性索引扫描，返回匹配某个值的所有行（例如子查询id，该id为ref类型，是索引树）
  本质上也是一种索引访问，可能会找到多个多个符合条件行，属于查找扫描的混合体
  多表查询时尽量关联到索引，一般都是ref级别

  range：范围
  index：FULL Index Scan index只遍历索引树（查询目标刚好在索引树上）
  all：FULL Index Scan 将遍历全表以找到匹配的行，未使用索引

• ALL代表本次查询没有使用到索引（典型的select*），简单查询容易出现ALL，并非不好只是没有使用索引

• 多表查询时需要注意，未使用索引为ALL则SQL性能差

• 一般需要保证查询至少达到range级别，最好能达到ref（多表查询时建立索引，尽量使索引在ref，range index级别，sql性能会较高）

ref

多表查询所关联的的索引字段是什么

possible_keys

多表关联时，可能用到的索引（一般是两张表所有索引）

keys

多表关联时，实际使用的索引

key_len

多表关联时，实际使用的索引索引字段的最大可能长度，key长度过长会影响查询效率

rows

根据表统计信息及索引的选用情况大致估算出找到所有记录所需要读取的行数

extra

包含不适合在其他列中显示但重要的信息

索引失效

• 将组合索引在查询时全部匹配
• 尽量使用覆盖索引，减少使用select*
• 最左前缀原则
• 不要在索引上任何操作，会导致索引失效进而转为全局扫描
• mysql在使用不等于（或大于小于）时，会出现无法使用索引导致全局扫描
• null/not null 可能会使索引失效
• like查询若以通配符开头‘%abc’）mysql索引会失效导致全局扫描
• 字符串不加单引号可能导致索引失效
• or可能会导致索引失效

MySQL引擎

MySQL常见的存储引擎有哪些

MySQL中InnoDB和MyISAM区别如下

• MyISAM不支持事务；InnoDB是事务类型的存储引擎
• MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁
• MyISAM引擎不支持外键；InnoDB支持外键
• MyISAM支持全文索引（FULLTEXT）；InnoDB不支持
• 对于count(*)查询来说MyISAM更有优势，因为其保存了行数
• InnoDB是为处理巨大数据量时的最大性能设计的存储引擎适合表经常更新处理多重并发的更新请求

MySQL的逻辑架构

逻辑架构包括Server层和存储引擎层。其中Server层包括连接器，分析器，优化器以及执行器；存储引擎层包括多种支持的存储引擎

各个逻辑部件的作用如下：

• 连接器：验证客户端权限，建立和断开MySQL连接
• 分析器：进行SQL语句的语法分析
• 优化器：选择索引，生成具体的SQL语句执行计划
• 执行器：操作存储引擎，执行SQL，返回执行结果
• 存储引擎层：各个不同的存储引擎都提供了一些读写接口来操作数据库

SQL

SQL分类

• DQL (Data Query Language)数据查询语言
  • SELECT，WHERE，ORDER BY，GROUP BY和HAVING都用DQL的常用保留字
• DML（Data Manipulation Language）数据操作语言
  • INSERT，UPDATE和DELETE
• DCL （Data Control Language）数据控制语言
  - GRANT、DENY、REVOKE
• DDL （Data Definition Language）数据定义语言
  • 其语句包括动词ALTER、CREATE和DROP
• 指针控制语言（CCL）
  像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作
• TPL (Transaction Processing Language)事务处理语言
  • 它的语句能确保被DML语句影响的表的所有行及时得以更新BEGIN TRANSACTION，COMMIT和ROLLBACK

超键、候选键、主键、外键

• 超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键
• 候选键(candidate key):不含有多余属性的超键称为候选键
• 主键(primary key):用户选作元组标识的一个候选键程序主键
• 外键:在一个表中存在的另一个表的主键称为此表的外键

SQL约束

• NOT NULL -指示某列不能存储NULL值
• UNIQUE -保证某列的每行必须具有唯一的值
• PRIMARY KEY -NOT NULL和UNIQUE的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速的找- 出表中的一个特定的记录
• FOREIGN KRY -保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK -保证列中的值符合指定的条件
• DEFAULT -规定没有给列赋值时的默认值

SQL关联查询

• 交叉连接(CROSS JOIN)
  (没有任何关联条件,查询所有表)

   select * from a cross join b cross join c
   select * form a,b,c 
  

• 内连接(INNER JOIN)（INNER JOIN 可以缩写为 JOIN）
  (多表中同时符合某种条件的数据记录的集合)

   select* from a inner join b on a.id = b.id
   select * from a,b where a.id = b.id
  

  • 等值连接: on a.id = b.id
  • 不等值连接 : on a.id > b.id
  • 自连接: select * from a as a1 inner join a as a2 on a.id = a2.pid

• 外连接(LEFT JOIN/RIGHT JOIN)

  • 左外连接:LEFT OUTER JOIN （缩写 LEFT JOIN——以左表为主,先查询出左表,按照ON之后关联条件匹配右表,没有匹配到的用NULL填充
  • 右外连接:RIGHT OUTER JOIN（缩写 RIGHT JOIN）以右表为主,先查询出右表,按照ON之后关联条件匹配左表,没有匹配到的用NULL填充

• 联合查询(UNION 与 UNION ALL)
  联合查询表的数据结构一定要一样,把多个结果集集中到一起,UNION前的结果为基准（union 相同的记录行会合并
  union all 不会合并重复的记录行）

• 全连接(FULL JOIN)
  全连接( 完整外部连接返回左表和右表中的所有行)，mysql中不支持全连接,可以使用LEFT JOIN 和 UNION 和 RIGHT JOIN 联合使用

子查询

一个查询的结果作为另一个查询的条件，出现有查询的嵌套则把内部的查询称为子查询（子查询要使用括号必须始终出现在圆括号内）

• where 型子查询 把内层查询的结果当作外层的比较条件

    select * from stu where score = (select max(score) from stu);
    查询每个学生成绩最高的一门
    select * from stu where score in (select max(score) from stu group by name);
  

• from型子查询:把内层的查询结果当成临时表，供外层sql再次查询

    查询每个学生成绩最高的一门
    （1）先把表中的数据按照成绩排序
    	select * from stu order by name asc,score desc;
    （2）然后把上次的查询结果作为一个临时表，再去查询
    	select * from (select * from stu order by name asc,score desc) as tmp group by name;
  

• exists型子查询：把外层的查询结果，拿到内层的查询是否成立

    		查询有商品的栏目
    select * from tableA where exists(select * from tableB where tableB.tmp = tableA.tmp);
  

MySQL中 in 和 exists 区别

exists

• exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果为true ,此时外层的查询语句将进行查询
• 如果子查询没有返回任何行，那么exists的结果为false,此时外层语句将不进行查询
• 需要注意的是，当我们的子查询为 SELECT NULL 时，MYSQL仍然认为它是True
  

in

in 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列的值将提供给外层查询语句进行比较操作

区别

• 外层查询表小于子查询表，则用exists
• 外层查询表大于子查询表，则用in
• 如果外层和子查询表差不多，则爱用哪个用哪个

varchar与char区别

定长和变长

char 定长，插入的长度小于定义长度时，则用空格填充
varchar 变长小于定义长度时，还是按实际长度存储，插入多长就存多长
（char因为定长存取速度比varchar要快，方便程序的存储与查找；varchar以时间换空间

存储的容量不同

char 最多能存放的字符个数 255，和编码无关
varchar 最多能存放 65532 个字符，最大有效长度由最大行大小和使用的字符集确定，整体最大长度是 65,532字节

varchar(50)中50的涵义

• varchar(50)中50指最多存放50个字符
• varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存

int(10)中10的含义

int范围

有符号的整型范围是-2147483648~2147483647 无符号的整型范围是0~4294967295

• int(M)的作用于int的范围明显是无关的，int(M)只是用来显示数据的宽度，我们能看到的宽度。当字段被设计为int类型，那么它的范围就已经被写死了

int(10)

• int(10)的意思是假设有一个变量名为id，它的能显示的宽度能显示10位
• 在使用id时，假如我给id输入10，那么mysql会默认给你存储0000000010。当你输入的数据不足10位时，会自动帮你补全位数
• 假如我设计的id字段是int(20)，那么我在给id输入10时，mysql会自动补全18个0，补到20位为止

drop，truncate，delete

• drop直接删掉表

• truncate删除的是表中的数据，再插入数据时自增长的数据id又重新从1开始；

• delete删除表中数据，可以在后面添加where字句。

MySQL中where、group by、having

• where子句用来筛选from子句中指定的操作所产生的的行
• group by 子句用来分组where子句的输出
• having子句用来从分组的结果中筛选行

UNION 和 UNION ALL

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

having和where的区别

• where搜索条件在进行分组操作之前应用

• having搜索条件在进行分组操作之后应用
  having可以包含聚合函数sum、avg、max等
  having子句限制的是组，而不是行

同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序

• 执行where子句查找符合条件的数据
• 使用group by 子句对数据进行分组
• 对group by 子句形成的组运行聚集函数计算每一组的值
  最后用having 子句去掉不符合条件的组

SQL优化

SQL运行很慢的原因

• 大多数情况下很正常，偶尔很慢

  • 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘
  • 执行的时候，遇到锁，如表锁、行锁

• 一直执行的很慢

  • 没有用上索引
  • 数据库选错了索引

大表数据查询优化

• 合理使用索引
• 避免或简化排序
  当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤，正确地增建索引，合理地合并数据库表，排序不可避免，那么应当试图简化它，如缩小排序的列的范围
• 消除对大型表行数据的顺序存取
• 避免相关子查询

尽量设定一个主键

• 主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键
• 设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全

主键使用自增ID还是UUID?

推荐使用自增ID,不要使用UUID.

• 因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及按顺序排列的全部的数据
• 如果主键索引是自增ID,那么只需要不断向后排列即可
• 如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降

在数据量大一些的情况下,用自增主键性能会好一些.

字段为什么要求定义为not null

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况

数据库的优化

• 避免网站页面出现访问错误

  • 由于数据库连接timeout产生页面5xx错误
  • 由于慢查询造成页面无法加载
  • 由于阻塞造成数据无法提交

• 增加数据库的稳定性
  很多数据库问题都是由于低效的查询引起的

• 优化用户的体验

  • 流畅页面的访问速度
  • 良好的网站功能体验

数据库优化顺序

• 硬件优化
• 数据库调优（增加索引，优化慢查询）
• 引入缓存，减轻数据库压力
• 数据表，程序的优化
• 读写分离或分库分表

数据库结构优化

1. 减少数据冗余
2. 尽量避免数据维护中出现更新，插入和删除异常.
3. 节约数据存储空间
4. 提高查询效率

123可以通过范式进行分表解决
4需要通过反范式化或索引等其他手段

范式化和反范式化

• 范式化：可以尽量的减少数据冗余，数据表更新快体积小；范式化的更新操作比反范式化更快(因为每一个表的大小都更小了）；降低查询性能，因为越高的范式往往意味着更多的级联，进行更多的查询。更难进行索引优化（因为表多了，所以索引更难建立了）
• 反范式化（在范式化设计的基础上，因为在各式各样的需求上，我们需要冗余数据来加快查询，或者不得不冗余数据）：可以减少表的级联，可以更好的进行索引优化；存在数据冗余及数据维护异常，对数据的修改需要更多的成本(CPU,内存等硬件设备和人力资源)

mysql数据库cpu飙升到500%怎么处理

• 多实例的服务器，先top查看是那一个进程，哪个端口占用CPU多
• show processeslist查看是否由于大量并发，锁引起的负载问题
• 查看慢查询，找出执行时间长的sql；explain分析sql是否走索引，sql优化
• 再查看是否缓存失效引起，需要查看buffer命中率

大表优化

等价于：某个表有近千万数据，CRUD比较慢，如何优化？分库分表是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重

• 通过提升服务器硬件能力来提高数据处理能力，比如增加存储容量 、CPU等，这种方案成本很高，并且如果瓶颈在MySQL本身那么提高硬件也是有很的
• 把数据分散在不同的数据库中，使得单一数据库的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的，数据库拆分为若干独立的数据库，并且对于大表也拆分为若干小表，通过这种数据库拆分的方法来解决数据库的性能问题

垂直分表

• 将一个表按照字段分成多表，每个表存储其中一部分字段
• 当表数据量很大时，可以将表按字段切开，将热门字段、冷门字段分开放置在不同库中，这些库可以放在不同的存储设备上，避免IO争抢。垂直切分带来的性能提升主要集中在热门数据的操作效率上，而且磁盘争用情况减少。

性能提升

1. 避免IO争抢并减少锁表的几率
2. 充分发挥热门数据的操作效率

垂直拆分原则

• 经常组合查询的列放在一张表中
• 把不常用的字段单独放在一张表
• 把大字段拆分出来放在附表中

垂直分库

• 通过垂直分表性能得到了一定程度的提升，但磁盘空间仍不够，因为数据始终在一台服务器
• 库内垂直分表只解决了单一表数据量过大，未将表分布到不同的服务器上，每个表仍竞争同一个物理机的CPU、内存、网络IO、磁盘
• 垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用

提升

• 解决业务层面的耦合，业务清晰

• 能对不同业务的数据进行分级管理、维护、监控、扩展等

• 高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈

• 垂直分库通过将表按业务分类，然后分布在不同数据库，并且可以将这些数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果，但是仍未解决单表数据量过大的问题

水平分库

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上

提升

解决了单库大数据，高并发的性能瓶颈。
提高了系统的稳定性及可用性。

水平分表

水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中

提升

• 优化单一表数据量过大而产生的性能问题
• 避免IO争抢并减少锁表的几率
• 库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能

总结

• 垂直分表：可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。

• 垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。

• 水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。

• 水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

MySQL的日志模块binlog和redo log

在MySQL的使用中，更新操作频繁，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录的成本都很高。所以，出现了日志模块，update更新操作是先写日志，在合适时间写磁盘，日志更新完毕就将执行结果返回给了客户端。MySQL中的日志模块主要有redo log（重做日志）和binlog（归档日志）

redolog

- redo log是==InnoDB引擎特有的日志模块==，redo log是==物理日志，记录了某个数据页上的修改==
- redo log==是固定大小==的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么redo log总共就可以记录 4GB的操作。从头开始写，写到末尾就又回到开头循环写。
- redo log具有crash-safe能力，==保证了数据库发生异常重启之后，之前提交的记录不会丢失==

binlog

• binlog是Server层自带的日志模块，binlog是逻辑日志，记录本次修改的原始逻辑（即SQL语句）

• binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。通过mysqlbinlog可以解析查看binlog日志

• binlog日志文件（录入）格式：statement，row，mixed
  - statement格式的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点是准确性差，遇到SQL语句中有now()等函数会导致不准确
  - row格式的binlog中记录的是数据行的实际数据的变更，优点就是数据记录准确，缺点就是日志文件较大。
  - mixed格式的binlog是前面两者的混合模式

  业界目前推荐使用的是 row 模式，因为很多情况下对准确性的要求是排在第一位的。
  在更新数据库的时候，通过redo log和binlog的两阶段提交，可以确保数据库异常崩溃之后数据的正确恢复。
  在对数据库误操作之后，可以通过备份库+binlog可以将数据库状态恢复到“任意“时刻。

为什么MySQL会突然变慢一下？

更新数据库时是先写日志，当合适（空闲）时才会更新磁盘。当redo log 写满了，要 flush 脏页，即把内存里的数据写入磁盘，会导致MySQL执行速度突然变慢一瞬间。

（当内存数据页和磁盘数据页内容不一致的时候，这个内存页就是==“脏页”==，内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，这个时候的内存页就是”干净页“）

PLSQL

plsql有两种，有返回值叫函数，无返回值为存储过程

函数

存储过程(特定功能的 SQL 语句集)

一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象

存储过程优化思路:

• 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
• 中间结果存放于临时表，加索引。
• 少使用游标
  sql 是个集合语言，对于集合运算具有较高性能，而== cursors 是过程运算==。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次
  读取。
• 事务越短越好
  如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁，导致查询极慢，cpu 占用率极低。
• 使用 try-catch 处理错误异常。
• 查找语句尽量不要放在循环内。

触发器(一段能自动执行的程序)

触发器是一段能自动执行的程序，是一种特殊的存储过程

触发器和普通的存储过程的区别是:

触发器是当对特定表进行操作时触发，诸如:update、insert、delete 这些操作的时候，系统会自动调用执行该表上对应的触发器

MySQL事务

MySQL事务特性

事务是单个逻辑工作单元执行的一系列操作，是一个不可分割的工作单位。满足如下的四大特性：

• 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；
• 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态；
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
• 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存

如果不做控制，多个事务并发操作数据库会产生哪些问题吗

• 丢失更新：
  两个不同事务同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的更新给覆盖掉，这种情况先提交的事务所做的更新就被覆盖，导致数据更新丢失。

• 脏读：
  事务A读取了事务B未提交的数据，由于事务B回滚，导致了事务A的数据不一致，结果事务A出现了脏读；

• 不可重复读：
  一个事务在自己没有更新数据库数据的情况，同一个查询操作执行两次或多次得到的结果数值不同，因为别的事务更新了该数据，并且提交了事务。

• 幻读：
  事务A读的时候读出了N条记录，事务B在事务A执行的过程中增加 了1条，事务A再读的时候就变成了N+1条，这种情况就叫做幻读。

MySQL数据库事务的隔离级别有哪些

为了避免数据库事务操作中的问题，MySQL定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

• 读未提交(Read Uncommitted)：
  允许脏读取。如果一个事务已经开始写数据，则不允许其他事务同时进行写操作，但允许其他事务读此行数据。

• 读已提交(Read Committed)：
  允许不可重复读取，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

• 可重复读(Repeatable Read)：
  禁止不可重复读取和脏读取，但是有时可能出现幻读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。

• 序列化(Serializable)：
  提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。

事务的隔离级别越高，对数据的完整性和一致性保证越佳，但是对并发操作的影响也越大。MySQL事务默认隔离级别是可重复读，Oracle的默认隔离级别为读已提交

隔离级别与锁的关系

• 数据库为了维护事务特性，尤其是一致性和隔离性，一般使用加锁这种方式
• 数据库又是个高并发的应用，同一时间会有大量的并发访问，如果加锁过度，会极大的降低并发处理能力
• 对于加锁的处理是数据库对于事务处理的精髓所在
• 实现隔离机制的方法主要有两种 ：加读写锁；一致性快照读即 MVCC

MySQL中的锁机制

• 数据库的锁从对数据操作的类型可分为读锁（共享锁）和写锁（排他锁）
• 从对数据操作的粒度可分为表锁，行锁
• 也可分为悲观锁和乐观锁

读写锁

MySQL从锁的类别上来讲，有共享锁和排他锁。

排他锁（X锁）

• 独占锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受
• 执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放
  在select命令中使用独占锁的SQL语句为：select … for update;

共享锁（S锁）：

共享锁顾名思义，那就是其锁定的资源可以被其它用户读取，但其它用户不能修改
如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select … lock in share mode

一个事务在一行数据上加入了独占锁，那么其余事务不可以在该数据行上加入任何锁。即加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制

行级锁表级锁

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)=、表级锁(MYISAM引擎)和页级锁(BDB引擎 )

行级锁

• 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁
• 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 是一种排他锁，防止其他事务修改此行;在使用以下语句时，Oracle 会自动应用行级锁:

1. INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
3. 使用 COMMIT 或 ROLLBACK 语句释放锁

行锁优化

-尽可能让数据检索通过索引完成避免无索引行锁升级为表锁

• 合理设计索引缩小缩范围
• 尽可能少检索条件避免间隙锁
• 控事物大小减少锁定资源量和时间

表级锁

• 表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持，锁粒度大锁冲突概率高并发度低
• 最常使 用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁 (排他锁)
• 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
• 在偏读型数据库（MyISAM）中使用表锁
• 读读共享，读写互斥，写写互斥

页级锁

• 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。页级折中一次锁定相邻的一组记录=

• 特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

锁常见问题

锁升级

无索引操作时，注意锁升级（行级锁升级为表级锁）

间隙锁危害

• 间隙锁是指使用范围条件而不是相等条件来检索数据请请求共享或排他锁时，对于键值在条件范围内但不存在的记录成为间隙
• innoDB会对间隙枷锁即间隙锁
• Query执行过程中范围查找时会锁定整个范围内所有键值即使不存在，造成锁定时无法插入被锁定键值范围内的任何数据

如何锁定一行

MySQL中的死锁：

两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象

MySQL的死锁解决

我们可以在业务上和数据库设置上来解决MySQL死锁。分别介绍如下

业务逻辑上的死锁解决方案

- 指定锁的获取顺序
- 大事务拆分成各个小事务
- 在同一个事务中，一次锁定尽量多的资源，减少死锁概率
- 给表建立合适的索引以及降低事务的隔离级别

数据库的设置来解决死锁

- 通过参数 innodb_lock_wait_timeout 根据实际业务场景来==设置超时时间==，InnoDB引擎默认值是50s。
-  ==发起死锁检测==，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）

悲观锁与乐观锁

数据库的并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳

悲观锁

利用数据库的锁机制（排他锁，共享锁）实现，在整个数据处理过程中都加入了锁，以保持排他性
一般多写场景下用悲观锁就比较合适。

自己修改某条数据的时候，不允许别人读取该数据只有等自己的整个事务提交了，才释放自己加上的锁，才允许其他用户访问那部分数据

乐观锁

乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据

• 乐观锁可以利用CAS实现，在操作数据的时候进行一个比较，按照当前事务中的数据和数据库表中的该数据是否一致来决定是否要执行本次操作
• 乐观锁可以利用版本号机制实现

乐观锁适用于多读场景，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量

时间戳

时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字段也读出来，当写回去的时候，把该字段加 1，提交之前 ，跟数据库的该字段比较一次，如果比数据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁机制，但是这种方法可以大大提高数据库处理的并发量，

乐观锁的ABA问题有了解吗？如何解决？

ABA问题是指在当前事务读取该行数据时是A，经过别的事务修改成B，但是在当前事务要更新数据的时候，该行数据又被别的事务修改为A，事实上数据行是发生过改变的，存在并发问题。

• ABA问题可以通过基于数据版本（Version）记录机制来解决。也就是为数据增加一个版本标识。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。根据当前事务的数据版本号和数据库中数据的版本号对比来决定是否更新数据。
• 与给当前数据增加一个数据版本类似，我们也可以增加基于时间戳机制来解决ABA问题，通过时间戳来记录当前数据行变化

在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )

MySQL中InnoDB引擎的行锁实现

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

行级锁什么时候会锁住整个表

InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的

有SQL优化或者MySQL故障排查经历

• 遇到一个SQL异常的时候，可以通过explain查看当前SQL语句的执行情况。explain +SQL语句可 以查看当前的SQL语句使用的索引以及其扫描了多少行数据

• 也可以使用下边的语句来查看数据表的一些信息：
  show create table TableXX；查看当前表TableXX的建表语句
  show index from TableXX；查看当前表TableXX上的索引
  查看了数据表的信息，一般情况下我们可以通过建立索引来提高查询速度，或者修改SQL语句，利用索引下推或者最左前缀原则等来加快查询速度

InnoDB存储引擎的锁的算法有三种

• Record lock：单个行记录上的锁

• Gap lock：间隙锁，锁定一个范围，不包括记录本身

• Next-key lock：record+gap 锁定一个范围，包含记录本身

MySQL建表的约束条件有哪些？

约束条件是我们建表的时候对数据库表做的一个限制条件。MySQL建表时候一般有如下的五个约束条件：

• 主键约束（Primay Key Coustraint） 唯一性，非空性
• 唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个
• 检查约束 (Check Counstraint) 对该列数据的范围、格式的限制
• 默认约束 (Default Counstraint) 该数据的默认值
• 外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

视图

视图是一种虚拟表，具有和物理表相同功能，可以对视图进行增删改查不会影响基本表，通常是一个或多个表的行或列的子集（只暴露部分字段给外部）

视图作用

-简化sql查询 提高了重用性，如函数一般，一次创建多次使用

• 对数据库重构，却不影响程序的运行
• 提高了安全性能。可以对不同的用户，设定不同的视图

使用场景

• 计算列的需要
• 不同表字段聚合，信息重组
• 兼容老的数据表

游标

• 关系数据库中的操作是在完整的行集合上执行的， 由SELECT 语句返回的行集合包括满足该语句的WHERE 子句所列条件的所有行，该行集合叫做结果集，往往需要对结果集一行或多行进行处理
• 从表中检索出结果集，从中每次指向一条记录进行交互的机制

游标作用

• 指定结果集中特定行的位置
• 基于当前的结果集位置检索一行或连续的几行
• 在结果集的当前位置修改行中的数据
• 对其他用户所做的数据更改定义不同的敏感性级别
• 可以以编程的方式访问数据库

避免使用游标

因为游标效率较差，如果游标操作的数据超过1万行，那么就应该改写；
如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作

limit分页查询使用方式

主键自增长设置方式：auto_increment

SQL语句的优化有哪些？

order by是怎么工作的？

MVCC多版本并发控制以及undo log（回滚日志）（加分项）

分布式事务，两阶段，三阶段提交协议等。（加分项）

MVCC

• 多版本并发控制，通过保存数据在某个时间点的快照来实现的
• 锁机制可以控制并发操作,但系统开销较大,而MVCC可以在大多数情况下代替行级锁使用MVCC,能降低其系统开销. 提高数据库高并发场景下的吞吐性能
• 大多数的MYSQL事务型存储引擎,如,InnoDB，Falcon以及PBXT都不使用一种简单的行锁机制，事实上,都和MVCC–多版本并发控制来一起使用
• MVCC的实现不同引擎实现不同，典型有乐观锁和悲观锁

MVCC的悲观锁乐观锁实现

悲观锁

• 排它锁，当事务在操作数据时把这部分数据进行锁定，直到操作完毕后再解锁，其他事务操作才可操作该部分数据。这将防止其他进程读取或修改表中的数据
• 实现：大多数情况下依靠数据库的锁机制实现
• 实现方式：
  一般使用 select …for update 对所选择的数据进行加锁处理，例如select * from account where name=”Max” for update， 这条sql 语句锁定了account 表中所有符合检索条件（name=”Max”）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录

乐观锁

实现方式：
1、认为没有并发而执行，一旦之前有更新，自己的更新应当是被拒绝的，重新操作。
2、实现：大多数基于数据版本（Version）记录机制实现

• 具体可通过给表加一个版本号或时间戳字段实现，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一
• 当我们提交更新的时候，判断当前版本信息与第一次取出来的版本值大小，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，拒绝更新，让用户重新操作

InnoDB MVCC实现并发控制原理

• InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了该行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)
• 每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID

MySQL数据类型

• TINYINT 1 byte (-128，127) (0，255) 小整数值
• SMALLINT 2 bytes (-32 768，32 767) (0，65 535） 大整数值
• MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
• INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
• BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
• FLOAT 4 bytes 单精度浮点数值
• DOUBLE 8 bytes 双精度浮点数值
• DECIMAL，对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2，小数值 （银行余额）