面试知识点梳理及相关面试题（四）-- mysql

数据库三大范式是什么

1. 第一范式：每个列都不可以再拆分。
2. 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
3. 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

MySQL的binlog有有几种录入格式？分别有什么区别？

有三种格式，statement，row和mixed。

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

mysql数据类型

整数类型

包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。

• 长度：整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
• 例子，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。

小数类型

包括FLOAT、DOUBLE、DECIMAL。

• DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。
• 而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。
• 计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

日期类型

• 尽量使用timestamp，空间效率高于datetime，
• 用整数保存时间戳通常不方便处理。
• 如果需要存储微妙，可以使用bigint存储。
• datetime(3)：代表除了存储时分秒以外还可以存储3位微秒

timestamp和datetime的区别：

• 两者的存储方式不一样
  • TIMESTAMP：把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。即如果在东8区的08:00:00分保存的数据，在东9区看到的是09:00:00
  • DATETIME：不做任何改变，基本上是原样输入和输出
• 两者所能存储的时间范围不一样
  • timestamp存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
  • datetime存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
• timestamp 4个字节存储（实际上就是int），datetime 8个字节
• 如果timestamp的值超出范围，mysql不会报错
• 如果是自动更新模式，手动修改数据导致timestamp字段更新
• 同时有两个timestamp字段默认值为current_timestamp会报错

文本、二进制类型

包括VARCHAR、CHAR、TEXT、BLOB等

• varchar：
  • VARCHAR用于存储可变长字符串，它比定长类型更节省空间。
  • VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
  • VARCHAR存储的内容超出设置的长度时，内容会被截断。
  • varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法
  • 对于varchar来说，最多能存放的字符个数为65532
  • varchar(50)中50的含义：
    • 最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中（5.0之前）， 50 代表字节数，现在代表字符数。
    • varchar(50)能存放多少汉字：
      • 如果是gb2312，因为gb2312存放汉字时占两上字节,所以varchar2(200)能放100个汉字
      • 如果是utf-8，因为utf-8则占用三个字节,所以如果是utf-8则只能存200/3 约= 86 个汉字。
      • 如果是4.0版本以下，指的是50字节，如果存放UTF8汉字时，只能存33个（每个汉字3字节）
      • 5.0版本以上，指的是50字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放100个。
• char：
  • CHAR是定长的，根据定义的字符串长度分配足够的空间。
  • 如果插入数据的长度小于char的固定长度时，则用空格填充；
  • CHAR适合存储很短的字符串，或者所有值都接近同一个长度。
  • CHAR存储的内容超出设置的长度时，内容同样会被截断。
  • 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
  • 对于char来说，最多能存放的字符个数为255，和编码无关

使用策略：

• 对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
• 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
• 使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
• 尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

存储引擎

常用的存储引擎有以下：

• Innodb引擎：IInnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁、表锁和外键，上图也看到了，InnoDB是默认的MySQL引擎。。
• MyIASM引擎(原本Mysql的默认引擎)：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物、行锁，支持表锁
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

存储引擎的选择：

1. INNODB：如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
2. MyISAM：如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率，如博客系统，新闻门户网站
3. Memory：如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
4. Archive：如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

INNODB和MyISAM的区别：

存储结构方面的区别：

1. InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
2. InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
3. MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
4. InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

索引

索引优缺点：

优点：

1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
2. 可以大大加快数据的查询速度，这也是创建索引最主要的原因
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接
4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间

缺点：

• 时间上：
  • 创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率
• 空间上：
  • 索引需要占物理空间

索引的分类：

• 单列索引
  • 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
  • 唯一索引：数据列不允许重复，允许为NULL值（允许多个null值），一个表允许多个列创建唯一索引。
  • 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键
• 组合索引
• 全文索引：是目前搜索引擎使用的一种关键技术。
  全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。在5.6之前，MySQL中只有MyISAM存储引擎支持全文索引，5.6之后MyISAM和InnoDB支持
• 空间索引

索引设计原则：

1. 索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
   Mysql8中原话：

// 二级索引：非聚集索引
// 一个表最多可以包含64个二级索引。
//多列索引最多允许有16列。
// 超过限制将返回错误。
//错误1070(42000):指定的关键部件太多;最多允许16个零件
A table can contain a maximum of 64 secondary indexes.
A maximum of 16 columns is permitted for multicolumn indexes. 
Exceeding the limit returns an error.
ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

2. 避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段

3. 频繁更新的列不要设计索引

4. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引时间还要短，索引可能不会产生优化效果

5. 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

6. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度

7. 在频繁排序或分组（即group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引

8. 定义有外键的数据列一定要建立索引

9. 最左前缀匹配原则，组合索引非常重要的原则，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的顺序可以任意调整。

10. 对于定义为text、image和bit的数据类型的列不要建立索引。

11. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

12. 查询的内容尽量匹配索引，避免回表问题

创建索引的其他注意项：

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2. 然后删除其中无用数据（此过程需要不到两分钟）
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

最左匹配原则：

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

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，只有abc可以用到索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
• 两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，查询条件where name=’xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换下。这个sql也能利用到索引了
• 三个字段加上联合索引（a，b，c），如果查询a = 1 and c = 2；这种情况只有a会用到索引，c不会
• 如果是数字类型字符串，没有加单引号，也可以查询结果，但是会造成索引失效

索引失效原因有哪些

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，即where a = 1 or b = 1如果a加了索引，b没有加索引，这里a的索引还是不会生效的
   注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引，就是说如果要索引生效，必须要单独使用
2. like查询是以%开头。（以%结尾还是会生效的，如"wl%"）
   解决：使用覆盖索引：select * from user where name like '%王%'不会用到索引，但是select name from user where name like '%王%会用到
3. 对于多列索引，不是使用的第一部分，则不会使用索引
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 普通使用not in不会使用索引，in可以使用索引；如果是主键索引无论是in还是not in都会走索引
6. 不符合最左匹配原则
7. 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
8. mysql判断全表扫描比使用索引更快的时候，比如重复情况较多的字段并且正标数据不多的时候
9. 在order by时，select的字段出现了非索引字段
10. ‘=’号两边的字符集或者排序规则不一致（重要，我就是因为这个问题找了好久失效原因，不光数据库的字符集要一致、表的和字段的字符集也要一致）

013 为什么官方建议使用自增长主键作为索引？（说一下自增主键和字符串类型主键的区别和影响）

• 自增主键能够维持底层数据顺序写入
• 读取可以由b+树的二分查找定位
• 支持范围查找，范围数据自带顺序

字符串无法完成以上操作

014 使用int自增主键后 最大id是10，删除id 10和9，再添加一条记录，最后添加的id是几？删除后重启mysql然后添加一条记录最后id是几？

删除之后

• 如果重启，会从最大的id开始递增
• 如果没重启，会延续删除之前最大的id开始递增

索引数据结构（b+树，hash）

b+树

关于b树和b+树会在数据结构的面试题中整理，这里想了解可以看下数据结构的详解：数据结构-B树和B+树

1. 非叶节点只存放索引，不存放数据，n棵子tree的节点包含n个关键字
2. 所有的叶子节点通过一个有序链表构成了全部数据及指向这些数据的指针，可以按照关键码排序的次序遍历全部数据

hash表

当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

5.3 Hash表相对于B+树的优劣：

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

5.3.1 Hash表的优点：

• 正常情况下，Hash表的等值查询更快。如果存在大量重复键值，就存在Hash碰撞的问题。就要先找到键所在位置，然后再根据链表往后扫描，直到找到相应的数据

5.3.2 缺点：

因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。

• hash索引不支持使用索引进行排序，原理同上。
• hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
• hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
• hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

5.4 为什么使用B+树而不用B树：

B和B+树的区别在于，B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。

5.4.1 B+ 树的优点在于：

• 读写代价低（和磁盘IO效率有关）。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
• 查询效率稳定
• 由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
• B+树支持顺序查询：B+树的叶子结点都是相链的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历，相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
• 增删效率好

B树优点：

• 由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

聚簇索引和非聚簇索引

6.1 聚簇索引：

• innodb可以把主键索引理解成聚簇索引。
• 一张表中，如果没有指定某列是聚簇索引，那么该表的第一个唯一非空索引被作为聚集索引，如果也没有索引，那么系统会自动创建一个隐含列作为表的聚集索引。
• 一个表只能有一个聚集索引，因为聚集索引把表的数据格式转换成索引树（平衡树）的格式放置。所以一个表只能有一个主键。树中的节点（除底部节点）的数据是由主键及数据地址构成。时间复杂度O（log n）(n代表记录数)；
• 查找的时候先通过主键找到所在的叶节点，而这个叶节点叶包含了此行的所有数据信息（innodb直接存储数据信息，myisam存储着数据的指向）

6.2 非聚集索引

• 索引树结构中各节点的值来自于表中的索引字段，假如给name字段加上索引 ， 那么索引就是由name字段中的值构成。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图：
  

• 每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

• 非聚集索引和聚集索引的区别在于：通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据，如下图：
  

辅助索引

innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

非聚簇索引存在的问题：

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

我们可以通过使用覆盖（联合）索引，来避免回表问题。

覆盖索引（联合索引）避免回表

• 如果为一个索引指定两个字段， 那么这个两个字段的内容都会被同步至索引之中。
• 比如对a，b，c三个字段创建了索引，那么就相当于创建了三个索引：
  • a
  • a，b
  • a，b，c
• 所以能不用select *，能用到覆盖索引的时候，就用覆盖索引查询结果
• 例：//查询生日在1991年11月1日出生用户的用户名select user_name from user_info where birthday = '1991-11-1'
  • 非聚集索引：
    create index index_birthday on user_info(birthday);
    查询过程：先通过索引找主键，再通过主键找到数据（回表）
  • 覆盖索引：
    create index index_birthday_and_user_name on user_info(birthday, user_name);
    查询过程：通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能，

何时使用聚簇索引与非聚簇索引

索引下推：Index Condition Pushdown

MySQL 5.6引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下：

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

事务

什么是事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的四大特性：

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

脏读，幻读，不可重复读

• 脏读：一个事务读取到另一个事务未提交的数据
  例：比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读。
• 不可重复读：一个事务的操作导致另一个事务前后两次读取到不同的数据（一个事务读取另一个事务已提交的数据）
  例：以银行取钱为例，事务A开启事务–>查出银行卡余额为1000元，此时切换到事务B事务B开启事务–>事务B取走100元–>提交，数据库里面余额变为900元，此时切换回事务A，事务A再查一次查出账户余额为900元，这样对事务A而言，在同一个事务内两次读取账户余额数据不一致，这就是不可重复读。
• 虚读（幻读）一个事务的操作导致另一个事务前后两次查询的结果的数据量不同。（一个事务读取到另一个事务已提交后添加的数据）
  例：事物A查询数据库中有没有id为1的数据，查到没有，此时事物B进来，直接往数据库中插入了一条id为1的数据，此时事物B再插就会出错
• 更新丢失：2个并发事务同时对一个结果修改，后提交的事务忽略了前一个事务对数据库的影响，造成了先提交的事务对数据库的影响丢失
  例：比如学生信息，事务A开启事务–>修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务–>事务B插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作。

事务的隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别， Oracle 默认采用的 READ_COMMITTED隔离级别

数据库锁：

锁的分类：

按粒度分：

• 行锁：
  • 只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
  • 行锁必须要索引才能实现，否则锁全表
  • 两个事物不能锁通过一个索引
• 表锁：
  • 表示对当前操作的整张表加锁，表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
• 页级锁：
  • 一次锁定相邻的一组记录

按类型分：

• 共享锁（Shared Locks，S锁）:

  • 又叫做读锁。 共享锁就是多个事物对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改
  • 共享锁可以同时加上多个。

• 排他锁（Exclusive Locks， X锁）:

  • 又叫做写锁。 排他锁不能和其他任何锁共存
  • 对于UPDATE、DELETE和INSERT语句，innodb会自动给涉及数据集加排它锁（X）

• 意向锁：

  • IS锁：意向共享锁。当事务准备在某条记录上加 S 锁时，需要先在表级别加一个 IS 锁。
  • IX锁：意向独占锁。当事务准备在某条记录上加 X 锁时，需要先在表级别加一个 IX 锁。
  • 意向锁是表级锁，它们的提出仅仅为了在之后加表级别的 S 锁和 X 锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

兼容度：（第四行IS应为IX）

按实现方式分：

是行锁的不同分类：

• Record Lock
  • 记录锁，单条索引记录上加锁。
  • 锁住的永远是索引，不包括记录本身
  • 分为X锁和S锁
• Gap Locks
  • 间隙锁，对索引前后的间隙上锁，不对索引本身上锁。
  • RR隔离级别下使用加锁方式解决幻读存在的问题：就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。
• Next-Key Locks
  • 间隙锁和记录的锁的组合。
  • 锁住的是：间隙锁的索引范围+索引本身
  • InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生

总结：

• innodb对于行的查询使用next-key lock
• Next-locking keying为了解决Phantom Problem幻读问题
• 当查询的索引含有唯一属性时，将next-key lock降级为record key
• Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
• 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

按实现机制分：

• 乐观锁：
  • 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
  • 乐观锁一般会使用版本号机制或CAS算法实现
• 悲观锁：
  • 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
  • 使用数据库中的锁机制如共享锁或者排它锁

隔离级别与锁的关系

• 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
• 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
• 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
• SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

数据库死锁：

例如说两个事务，事务A锁住了1 ~ 5行，同时事务B锁住了6 ~ 10行，此时事务A请求锁住6 ~ 10行，就会阻塞直到事务B施放6 ~ 10行的锁，而随后事务B又请求锁住1 ~ 5行，事务B也阻塞直到事务A释放1~5行的锁。死锁发生时，会产生Deadlock错误。 锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。

我出现的数据库死锁的情况：

• 根据条件删除->插入。
• 解决方式：查询->存在即删除->插入

什么是MVCC

• MVCC 简单说就是在尽量减少锁使用的情况下高效避免脏读、不可重复度、幻读等问题的发送。
• 同一行数据平时发生读写请求时，会上锁阻塞住。但 MVCC 用更好的方式去处理读写请求，做到在发生读写请求冲突时不用加锁。
• 这个读是指的快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。

快照读和当前读：

• 当前读：
  • 读取的是最新数据，而不是历史数据。
  • 当前读是基于临键锁next-key lock（行锁+间歇锁）来实现的
  • insert，update，delete， select ... for update， select ... lock in share mode 语句，加了锁的 select 语句。都是当前读。
• 快照读：
  • 读取的是快照数据。
  • 快照读是基于 MVCC 和 undo log 来实现的，适用于简单的非阻塞的select 语句。

快照读在RR和RC中的区别：

• 读已提交(RC)：是事务中的每个 sql 语句生成一个 readView。那就是一个事务内多条 sql 语句，会生成多个 readView。而每条 sql 执行时，都是查询最新 readView 的值。
• 可重复读(RR)：是在事务开始的时候生成一个 readView。所以一个事务内的多条查询 sql ，查询同一条数据时，读取到的 readView 都是同一个，那么查询某条数据的值，也是同一个值。

MVCC实现原理：

它的实现原理主要是版本链，undo日志 ，Read View来实现的。
查看详解：MVCC及间隙锁详解

视图

所谓视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。

视图有哪些特点？

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• 视图是由基本表(实表)产生的表(虚表)。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新(添加，删除和修改)直接影响基本表。
• 当视图来自多个基本表时，不允许添加和删除数据。

视图的操作包括创建视图，查看视图，删除视图和修改视图。

视图的使用场景有哪些？

视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

下面是视图的常见使用场景：

• 重用SQL语句；
• 简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；
• 使用表的组成部分而不是整个表；
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点

• 查询简单化。视图能简化用户的操作
• 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
• 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

视图的缺点

• 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
• 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

存储过程

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点

1. 存储过程是预编译过的，执行效率高。
2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3. 安全性高，执行存储过程需要有一定权限的用户。
4. 存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

1. 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2. 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3. 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4. 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。
• 大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和MyISAM的区别。

MySQL中都有哪些触发器？

在MySQL数据库中有如下六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

Mysql中的关联查询

交叉连接（CROSS JOIN）

#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用
SELECT * FROM A,B(,C)
或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)

内连接（INNER JOIN）

# 多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN
SELECT * FROM A,B WHERE A.id=B.id
或者
SELECT * FROM A INNER JOIN B ON A.id=B.id

分为三类：

• 等值连接：ON A.id=B.id
• 不等值连接：ON A.id > B.id
• 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接（LEFT JOIN/RIGHT JOIN）

• 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
• 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION ...

• 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
• 如果使用UNION ALL，不会合并重复的记录行
• union会自动排序，union all不会
• 效率UNION ALL高于 UNION

全连接（FULL JOIN）

• MySQL不支持全连接
• 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id=B.id

mysql执行计划explain

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

字段解释：

id：

• id相同：表示加载表的顺序是从上到下
• id不同：id值越大，优先级越高，越先被执行
• id有相同，也有不同，同时存在：id相同可以认为是一组，从上往下顺序执行；在所有组中，id的值越大，优先级越高，越先执行

2. select_type

2.1 simple：简单查询

2.2 primary：主查询

即子查询中最外层查询

2.3 subquery ：子查询

2.4 dependent subquery：子查询中的第一个SELECT，取决于外面的查询

注意：出现此类型，坚决要修改sql语句，这个太慢了！！！
最好是修改为derived的形式。
这个意思是，自己是个子查询，并且还依赖于外层的查询结果。

详解看：https://blog.csdn.net/joenqc/article/details/73189143

2.5 derived：临时表

from中子查询产生的临时表

2.6 union及union result：

3. type：访问类型

从好到坏：system > const > eq_ref > ref > range > index > ALL

3.1 NULL

3.2 system

查询系统表专用，5.7 以上版本不再显示system直接显示all，不多解释

3.3 const

3.4 eq_ref

主要用在一对一的查询

3.5 ref

附表命中了普通索引

3.6 range

范围查询

3.7 index

查询索引列的全部数据

4. 其他字段

4.1 table

显示这一步所访问数据库中表名称有时不是真实的表明

4.2 rows

扫描行的数量

4.3 key

1. possible_keys：显示可能应用在这张表的索引，一个或多个
2. key：实际使用的索引，如果为null，则没有使用索引
3. key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
   

4.4 extra

其他的额外的执行计划信息，在该列展示

索引计划的目标：

至少要达到 range 级别，要求是ref级别，如果可以是consts最好。
说明：

1. consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2. ref 指的是使用普通的索引（normal index）。
3. range 对索引进行范围检索。

反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。

myql优化相关：

mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

1. 如果查询的两个表大小相当，那么用in和exists差别不大。
2. 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
3. not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

大表数据查询，怎么优化

• 优化shema、sql语句+索引；
• 第二加缓存，memcached, redis；
• 主从复制，读写分离；
• 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
• 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

超大分页怎么处理？

超大的分页一般从两个方向上来解决.

• 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于：select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的。这条语句需要load1000000数据然后基本上全部丢弃，只取10条当然比较慢。当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10，效率也是不错的,优化的可能性有许多种，但是核心思想都一样,就是减少load的数据。
• 从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种：

• 【推荐】利用延迟关联或者子查询优化超多分页场景。
• 说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
• 正例：先快速定位需要获取的id段，然后再关联：

 SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

开启慢查询日志

配置项：slow_query_log

可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件。

设置临界时间

配置项：long_query_time

查看：show VARIABLES like 'long_query_time'，单位秒

设置：set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

你是如何发现及优化慢查询的？

慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？ 是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？
所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果都使用了索引，可以尝试优化语句的编写，比如关联的时候，如果有重复数据，是否可以将重复的数据先通过一些方式删除
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

主键使用递增ID还是UUID？

推荐使用递增ID，不要使用UUID。

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

总之，在数据量大一些的情况下，用递增主键性能会好一些。

关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

我的项目中用的是雪花算法生成的唯一主键（还是有极微小的可能性，在分布式的情况下生成相同主键）

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

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

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

sql语句优化

优化查询过程中的数据访问

• 访问数据太多导致查询性能下降
• 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
• 确认MySQL服务器是否在分析大量不必要的数据行
• 避免犯如下SQL语句错误
  • 查询不需要的数据。解决办法：使用limit解决
  • 多表关联返回全部列。解决办法：指定列名
  • 总是返回全部列。解决办法：避免使用SELECT *
  • 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
  • 是否在扫描额外的记录。解决办法：
    • 使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
      • 使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
      • 改变数据库和表的结构，修改数据表范式
      • 重写SQL语句，让优化器可以以更优的方式执行查询。

优化长难的查询语句

• 一个复杂查询还是多个简单查询
• MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
• 使用尽可能小的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的。
• 切分查询
• 将一个大的查询分为多个小的相同的查询
• 一次性删除1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销。
• 分解关联查询，让缓存的效率更高。
• 执行单个查询可以减少锁的竞争。
• 在应用层做关联更容易对数据库进行拆分。
• 较少冗余记录的查询。

优化特定类型的查询语句

• count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)
• MyISAM中，没有任何where条件的count(*)非常快。
• 当有where条件时，MyISAM的count统计不一定比其它引擎快。
• 可以使用explain查询近似值，用近似值替代count(*)
• 增加汇总表
• 使用缓存

优化关联查询

• 确定ON或者USING子句中是否有索引。
• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

优化子查询

• 用关联查询替代
• 优化GROUP BY和DISTINCT

优化LIMIT分页

• LIMIT偏移量大的时候，查询效率较低
• 可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询

• UNION ALL的效率高于UNION

优化WHERE子句

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null
-- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

3. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
4. 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
-- 可以这样查询：
select id from t where num=10 union all select id from t where num=20

5. in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3) 
-- 对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

6. 下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索，不过只做右侧模糊，左侧不模糊还是会用到索引
7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num
-- 可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100
-- 应改为:
select id from t where num=100*2

9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’
-- name以abc开头的id应改为:
select id from t where name like ‘abc%’

10. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

数据库结构优化：

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

大数据量问题解决

单表存在的问题

1. 单库太大：数据库里面的表太多，所在服务器磁盘空间装不下，IO次数多CPU忙不过来。
2. 单表太大：一张表的字段太多，数据太多。查询起来困难。

单表问题解决办法：

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

• 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。（阿里BI数据查询时的方式）
• 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
• 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
• 分库分表：通过分库分表的方式进行优化，主要有垂直分表和水平分表

主从复制

主从复制流程：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

主从复制的作用

• 主数据库出现问题，可以切换到从数据库。
• 可以进行数据库层面的读写分离。
• 可以在从数据库上进行日常备份。

MySQL主从复制解决的问题

• 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
• 负载均衡：降低单个服务器的压力
• 可用和故障切换：帮助应用程序避免单点失败
• 升级测试：可以用更高版本的MySQL作为从库

MySQL主从复制工作原理

3个线程以及之间的关联：

• 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
• 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
• 从：sql执行线程——执行relay log中的语句；

Binary log：主数据库的二进制日志

Relay log：从服务器的中继日志

1. master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。
2. salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
3. SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

读写分离有哪些解决方案？

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读（如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave）。

1. 使用mysql-proxy代理
   • 优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用
   • 缺点：降低性能， 不支持事务
2. 使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。
   • 如果采用了mybatis， 可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve库，这样对于dao层都是透明。 plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题， 也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。
3. 使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，
   • 可以支持事务.
   • 缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

读写分离带来的问题

1. 写操作拓展起来比较困难，因为要保证多个主库的数据一致性。
2. 复制延时：意思是同步带来的时间消耗。
3. 锁表率上升：读写分离，命中率少，锁表的概率提升。
4. 表变大，缓存率下降：此时缓存率一旦下降，带来的就是时间上的消耗。

注意，此时主从复制还是单库单表，只不过复制了很多份并进行同步。

主从复制架构随着用户量的增加、访问量的增加、数据量的增加依然会带来大量的问题，那就要考虑换一种解决思路。就是分库分表。

分表

又分为垂直分表和水平分表

垂直分表

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

• 优点：
  • 适用于一个表中某些列常用，另外一些列不常用
  • 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数
  • 可以简化表的结构，易于维护
• 缺点：
  • 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决
  • 会让事务变得更加复杂
  • 对于应用层来说，逻辑算法增加开发成本

水平分表

单表的数据量太大。按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。这种情况是不建议使用的，因为数据量是逐渐增加的，当数据量增加到一定的程度还需要再进行切分，比较麻烦。所以 水平拆分最好分库

• 优点：
  • 能够支持非常大的数据量存储，应用端改造也少
• 缺点：
  • 分片事务难以解决
  • 跨界点Join性能较差，逻辑复杂，查询所有数据都需UNION操作
  • 在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

数据库分片（水平拆分）的两种常用方案：

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

分库

垂直分库

一个数据库的表太多。此时就会按照一定业务逻辑进行垂直切，比如用户相关的表放在一个数据库里，订单相关的表放在一个数据库里。注意此时不同的数据库应该存放在不同的服务器上，此时磁盘空间、内存、TPS等等都会得到解决。

水平分库

水平分库理论上切分起来是比较麻烦的，它是指将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

分库分表之后的问题

• 事务支持：
  • 分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
• 跨库join
  • 只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。 分库分表方案产品
• 跨节点的count,order by,group by以及聚合函数问题
  • 这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。
• 数据迁移，容量规划，扩容等问题
  • 来自淘宝综合业务平台团队，它利用对2的倍数取余具有向前兼容的特性（如对4取余得1的数对2取余也是1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了Sharding扩容的难度。
• ID问题
  • 一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由

数据库分布式ID生成策略

分库分表的情况下，保证表中id的全局唯一性

UUID

UUID 使用UUID作主键是最简单的方案，但是缺点也是非常明显的。由于UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。

雪花算法id

Twitter的分布式自增ID算法Snowflake 在分布式系统中，需要生成全局UID的场合还是比较多的，twitter的snowflake解决了这种需求，实现也还是很简单的，除去配置信息，核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

雪花算法的原理就是生成一个的 64 位比特位的 long 类型的唯一 id。

• 最高 1 位固定值 0，因为生成的 id 是正整数，如果是 1 就是负数了。
• 接下来 41 位存储毫秒级时间戳，2^41/(1000606024365)=69，大概可以使用 69 年。
• 再接下 10 位存储机器码，包括 5 位 datacenterId 和 5 位 workerId。最多可以部署 2^10=1024 台机器。
• 最后 12 位存储序列号。同一毫秒时间戳时，通过这个递增的序列号来区分。即对于同一台机器而言，同一毫秒时间戳下，可以生成 2^12=4096 个不重复 id。

雪花算法优点

• 高并发分布式环境下生成不重复 id，每秒可生成百万个不重复 id。
• 基于时间戳，以及同一时间戳下序列号自增，基本保证 id 有序递增。
• 不依赖第三方库或者中间件。
• 算法简单，在内存中进行，效率高。

雪花算法缺点

• 依赖服务器时间，服务器时钟回拨时可能会生成重复 id。算法中可通过记录最后一个生成 id 时的时间戳来解决，每次生成 id 之前比较当前服务器时钟是否被回拨，避免生成重复 id。