【烈日炎炎战后端】MySQL理论（2.8万字）

1. 数据库三大范式

什么是范式：简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系.所以建立科学的，规范的的数据库是需要满足一些.

1. 第一范式（列不可再分）： 数据库表中的所有字段值都是不可分解的原子值，如一张表里有一个字段是高级职称，但是在高校里高级职称包括副教授和教授，这属于可分的，所以不符合第一范式.

2. 第二范式（非主属性完全依赖于码 ）：在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中.

   例如，在选课关系表(学号，课程号，成绩，学分)，关键字为组合关键字(学号，课程号)，但由于非主属性学分仅依赖于课程号，对关键字(学号，课程号)只是部分依赖，而不是完全依赖，因此此种方式会导致数据冗余以及更新异常等问题，解决办法是将其分为两个关系模式：学生表(学号，课程号，分数)和课程表(课程号，学分)，新关系通过学生表中的外关键字课程号联系，在需要时进行连接.

3. 第三范式（确保每列都和主键列直接相关，而不是间接相关）：减小了数据冗余.有一张表(学号，系名，系主任)，这里存在传递依赖.学号->系名，系名->系主任 传递依赖，需要将系名和系主任另外新建一张表.

4. BCNF范式(排除了任何属性对码的传递依赖和部分依赖)：

   • 所有非主属性对每一个码都是完全函数依赖.

   • 所有的主属性对每一个不包含它的码，也是完全函数依赖.

   • 没有任何属性完全函数依赖于非码的任何一组属性.

完全函数依赖
定义：设X，Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X.

比如: 当主键为学号时，学号->姓名

部分函数依赖
定义：设X，Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X.

这主要针对于组合属性，比如，当主键为（学号，课程号）的组合属性时，学分->课程号，不依赖于其中的学号，即只依赖于这个集合的一部分，为部分函数依赖.

传递函数依赖：
设X，Y，Z是关系R中互不相同的属性集合，存在X→Y(Y !→X)，Y→Z，则称Z传递函数依赖于X.

数据库的表中**：列即属性**；行即元组.
参考网站： link.

原文链接：https://blog.csdn.net/qq_40511966/article/details/104047883

2. char 和 varchar 的区别？

char(n) ：固定长度类型.
varchar(n) ：可变长度.

varchar(10) 和 varchar(20) 的区别？

varchar(10) 中 10 的涵义最多存放 10 个字符，但varchar(10) 和 varchar(20) 存储在存储同样字符串时（如"hello"）所占空间一样，但后者在排序时会消耗更多内存，因为 order by col 采用 fixed_length 计算 col 长度.

3. Mysql的存储引擎以及区别

https://www.cnblogs.com/rgever/p/9736374.html

Innodb：Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别.该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引.但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表.当需要使用数据库事务时，该引擎当然是首选.由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率.但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表.
MyISAM：MyISAM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些.不过和Innodb不同，MyISAM中存储了表的行数，于是SELECT COUNT(#) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描.如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择.

主要区别：

• MyISAM不支持事务；InnoDB是事务类型的存储引擎。
• MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
• MyISAM引擎不支持外键；InnoDB支持外键。

常用的两种引擎简单来说：

1. MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyISAM
2. InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

拓展：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q3qPKuyU-1596676378904)(X:\Users\xu\AppData\Roaming\Typora\typora-user-images\image-20200629213215393.png)]

应用场景：
InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制.如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势.如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）.
MyISAM：插入数据快，空间和内存使用比较低.如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率.如果应用的完整性、并发性要求比 较低，也可以使用.
MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高.如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY.它对表的大小有要求，不能建立太大的表.所以，这类数据库只使用在相对较小的数据库表.
注意：同一个数据库也可以使用多种存储引擎的表.如果一个表要求比较高的事务处理，可以选择InnoDB.这个数据库中可以将查询要求比较高的表选择MyISAM存储.如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎.

4. 一条SQL查询是如何执行的？

https://blog.csdn.net/belalds/article/details/91430614

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分.

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等.

而存储引擎层负责数据的存储和提取.其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎.现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎.

也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB.不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory， 来指定使用内存引擎创建表.不同存储引擎的表数据存取方式不同，支持的功能也不同，在后面的文章中，我们会讨论到引擎的选择.

从图中不难看出，不同的存储引擎共用一个 Server 层，也就是从连接器到执行器的部分.你可以先对每个组件的名字有个印象，接下来我会结合开头提到的那条 SQL 语句，带你走一遍整个执行流程，依次看下每个组件的作用.

连接器

第一步，你会先连接到这个数据库上，这时候接待你的就是连接器.连接器负责跟客户端建立连接、获取权限、维持和管理连接.连接命令一般是这么写的：

mysql -h$ip -P$port -u$user -p

输完命令之后，你就需要在交互对话里面输入密码.虽然密码也可以直接跟在 -p 后面写在命令行中，但这样可能会导致你的密码泄露.如果你连的是生产服务器，强烈建议你不要这么做.

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接.在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码.

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行.
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限.之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限.

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限.修改完成后，只有再新建的连接才会使用新的权限设置.

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它.文本中这个图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接.

客户端如果太长时间没动静，连接器就会自动将它断开.这个时间是由参数 wait_timeout 控制的，默认值是 8 小时.

如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query.这时候如果你要继续，就需要重连，然后再执行请求了.

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接.短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个.

建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接.

但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的.这些资源会在连接断开的时候才释放.所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了.

怎么解决这个问题呢？你可以考虑以下两种方案.

1. 定期断开长连接.使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连.
2. 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源.这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态.

查询缓存

连接建立完成后，你就可以执行 select 语句了.执行逻辑就会来到第二步：查询缓存.

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句.之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中.key 是查询的语句，value 是查询的结果.如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端.

如果语句不在查询缓存中，就会继续后面的执行阶段.执行完成后，执行结果会被存入查询缓存中.你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高.

但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利.

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空.因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了.对于更新压力大的数据库来说，查询缓存的命中率会非常低.除非你的业务就是有一张静态表，很长时间才会更新一次.比如，一个系统配置表，那这张表上的查询才适合使用查询缓存.

好在 MySQL 也提供了这种“按需使用”的方式.你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存.而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；

需要注意的是，MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了.

分析器

如果没有命中查询缓存，就要开始真正执行语句了.首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析.

分析器先会做“词法分析”.你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么.

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句.它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”.

做完了这些识别以后，就要做“语法分析”.根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法.

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”.

mysql> elect * from t where ID=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容.

优化器

经过了分析器，MySQL 就知道你要做什么了.在开始执行之前，还要先经过优化器的处理.

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序.比如你执行下面这样的语句，这个语句是执行两个表的 join：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20.
• 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10.
  这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案.

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段.如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等等.

执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句.

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示.

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行.打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口.

比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行.
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端.

至此，这个语句就执行完成了.

对于有索引的表，执行的逻辑也差不多.第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的.

你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行.这个值就是在执行器每次调用引擎获取数据行的时候累加的.

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此 引擎扫描行数跟 rows_examined 并不是完全相同的.

5. 什么是回表

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树.

因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供MySQL查询速度.

6. MySQL是如何解决幻读的

一、什么是幻读

在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行

二、为什么要解决幻读

在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。

三、MySQL 是如何解决幻读的

如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。

1. 多版本并发控制（MVCC）（快照读）

多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。以 InnoDB 为例，每一行中都冗余了两个字段。一个是行的创建版本，一个是行的删除（过期）版本。版本号随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。

普通的 select 就是快照读。

select * from T where number = 1;

原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

2. next-key 锁 （当前读）

next-key 锁包含两部分

1. 记录锁（行锁）
2. 间隙锁

记录锁是加在索引上的锁，间隙锁是加在索引之间的。（思考：如果列上没有索引会发生什么？）

select * from T where number = 1 for update;
select * from T where number = 1 lock in share mode;
insert
update
delete

原理**：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的**。

其他：MySQL InnoDB 引擎 RR 隔离级别是否解决了幻读
引用一个 github 上面的评论 地址：

Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。
a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。

如果这样理解的话，Mysql的RR级别确实防不住幻读

有道友回复 地址：

在快照读读情况下，mysql通过mvcc来避免幻读。
在当前读读情况下，mysql通过next-key来避免幻读。
select * from t where a=1;属于快照读
select * from t where a=1 lock in share mode;属于当前读

不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用。所以我认为mysql的rr级别是解决了幻读的。

先说结论，MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题。

如引用一问题所说，T1 select 之后 update，会将 T2 中 insert 的数据一起更新，那么认为多出来一行，所以防不住幻读。看着说法无懈可击，但是其实是错误的，InnoDB 中设置了 快照读 和 当前读 两种模式，如果只有快照读，那么自然没有幻读问题，但是如果将语句提升到当前读，那么 T1 在 select 的时候需要用如下语法： select * from t for update (lock in share mode) 进入当前读，那么自然没有 T2 可以插入数据这一回事儿了。

注意
next-key 固然很好的解决了幻读问题，但是还是遵循一般的定律，隔离级别越高，并发越低。

以上所述是小编给大家介绍的MySQL是如何解决幻读的详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！

7. 主从复制原理

整理自深入浅出mysql：https://download.csdn.net/download/qq_16399991/10660150

https://blog.csdn.net/qq_16399991/article/details/82749333

概述

mysql从3.23版本开始提供复制功能，复制是将主库的DDL和DML操作通过二进制日志传递到复制服务器（从库）上，然后从库对这些日志重新执行（重做），从而使得主库和从库保持数据一致。

mysql复制的优点：

• 如果主库出现问题，可以快速切换到从库提供服务
• 可以在从库执行查询操作，降低主库的访问压力。
• 可以在从库进行备份，以免备份期间影响主库的服务。

注意：由于mysql实现的异步复制，所以主库和从库数据之间存在一定的差异，在从库执行查询操作需要考虑这些数据的差异，一般只有更新不频繁和对实时性要求不高的数据可以通过从库插叙，实行要求高的仍要从主库查询。

复制原理

mysql的复制原理大致如下。

(1)首先，mysql主库在事务提交时会把数据库变更作为事件Events记录在二进制文件binlog中；mysql主库上的sys_binlog控制binlog日志刷新到磁盘。

(2)主库推送二进制文件binlog中的事件到从库的中继日志relay log,之后从库根据中继日志重做数据库变更操作。通过逻辑复制，以此来达到数据一致。

Mysql通过3个线程来完成主从库之间的数据复制：其中BinLog Dump线程跑在主库上，I/O线程和SQl线程跑在从库上。当从库启动复制（start slave）时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发给I/O线程，I/O线程获取到数据库事件更新到从库的中继日志Realy log中去，之后从库上的SQl线程读取中继日志relay log 中更新的数据库事件并应用。

8.mysql日志中redo和undo日志概念以及应用

https://blog.csdn.net/q1060701529/article/details/102393291

ACID

首先来讨论事务的四大特性ACID

• 原子性（Atomicity）：事务作为一个整体来执行，要不都执行，要不都不执行

• 一致性（Consistency）：事务必须保证数据库从一个一致状态转移到另一个一致状态。不能破坏关系数据的完整性以及业务逻辑的一致性。完整性一般就是数据的域完整性、实体完整性以及参照完整性。域完整性始址我们在创建表的时候指定的数据类型，输入限制。实体完整性规定我们的记录必须唯一，也就是说一个记录中必须存在一个或者多个字段唯一标示这一条记录。参照完整性则一般对应于关系表之间的关系，保证主键和外键之间的参照关系。不能因为执行事务儿破坏数据的完整性。

  逻辑业务一致性举个例子。再银行转账操作中，a、b初始值1000，a像b转100，但是不能因为我们的事务操作使得b只收到了50。事务要保证业务操作中我们的业务一致性不能乱。

• 隔离性（Isolation）：多个事务并发的时候，一个事务执行的时候不会影响另一个事务。

• 持久性（Durability）：已被提交的事务必须保存再数据库中。

undo日志到底做了什么? undo日志会记录事务执行过程中，每次修改的数据的原始值。

x =5,y  = 8
t1 begin:
	//undo日志记录x=5
	x = x- 1;
	//undo日志记录y=8
	y = y-2;
	//事务执行临近结束，将undo日志写入到磁盘
	//将数据写入到磁盘
commit

我们都知道，事务是具有原子性的要不全做，要不全部做。可到底是什么机制协助了数据库，undo日志就可以保证数据库事务操作的原子性，从上面的流程我们可以得知每次进行事务修改之前，都会吧未修改之前的值存储到undo日志中，当然再提交的时候也是先将undo写到磁盘，再把修改后的数据写到磁盘。倘若再undo写入磁盘之前发生了异常，根本就不需要做任何操作，这时候事务是被认为执行失败的，也不需要回滚，因为undo日志没有写入磁盘，数据库被认为处于没有执行事务的状态。若再数据写入磁盘的时候发生故障，则可以根据undo日志进行回滚，整个过程下来起码实现了原子性以及持久性。

undo操作的特点总结如下：

1. 在更新数据前把数据记录到undo操作
2. 持久性，只要数据提交则必定保存到了数据库
3. undo log必须先于数据持久化到磁盘，这样的话若数据写入磁盘或者进行commit是出错，可以根据undo日志进行回滚
4. 若事务再undo持久化之前出错，则数据库中的数据还保持在事务之前的状态。undo日志中也没有相应的记录，不需要回滚

当然undo的缺陷也很明显，他需要提交一次undo日志到磁盘，和一次数据到磁盘。io次数过多，性能太低。

redo的出现

为了解决undo性能过低的问题，就引入了redo，redo与undo正相反，他记录的是新数据的备份。并且事务在提交的时候只需将redo日志持久化到磁盘即可，数据可以根据redo日志异步的持久到磁盘。当发生异常的时候，只要redo日志写入到磁盘我们就可以根据redo日志来进行回滚。倘若redo日志再持久化到磁盘的时候出错，数据库就相当于没有执行过当前事务。

数据库恢复策略

1.只按照redo日志进行回复，所有事务（包括未提交的事务）都按照redo日志，也就是视图提交后的结果进行恢复

2.第二种分两步走，第一部和第一种一样，先根据redo日志对所有事务进行恢复 操作。再根据undo日志对未提交的日志进行回复操作。

Mysql采用的是第二种恢复策略。显然第一种策略也能进行事无恢复，但是为什么Mysql会采用第二种呢。我个人理解是这样的，虽然只按照redo日志进行回复也能回复数据。但是redo日志记录的是事务修改后的数据，这对于已经提交的事务是没有问题的。但是对于未提交的事务，当灾难发生时，事务并没有被提交，逻辑上应该认为这个事务是操作失败的，也就是类似于在做普通的sql的时候出现了异常，此时我们需要做回滚操作。只按照redo日志进行恢复，那么那些未提交的事务会直接恢复到事务提交后的结果。

不妨设想一个场景，a向b转100块钱，这个事务执行的顺序也就是，将 a-100和b+100的结果写入到redo日志，redo日志进行持久化操作，最后一步就是事务提交。但是再事务提交的节骨眼儿上服务器宕了，这时候由于用户a长时间得不到服务器响应，客户端也应该向用户显示操作失败吧（假设需要同步的显示操作结果），但是根据redo日志进行回复后，a-100 以及 b+100的操作被数据库恢复了，但是用户那边的情况却恰恰相反。这样对于一个可靠性要求比较高的系统来说本就是灾难

【MySQL索引】

https://cloud.tencent.com/developer/article/1125452

https://www.jianshu.com/p/7c0709976f40

[1] 什么是MySQL索引？

索引（Index）的建立是为了优化数据库查询性能而建立的数据结构。

[2] B+树相对于B-树的优势？

数据库的索引结构是B+树.

1. 相比于其他查找树（B-树），B+树单一节点存储更多的关键字，也就是说B+树更加矮胖，使得查询时的平均IO次数更少，查询效率更高；
2. B+树的关键字信息全部存储在叶子结点中，非叶子结点只存储索引地址.所以查询都要从根节点查找到叶子节点，也就是说查询路径长度相同，查询性能稳定；
3. B+树的所有叶子节点形成有序链表，便于范围查询.

[3] MySQL索引实现

1. MyISAM索引实现（非聚集索引）

MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址。MyISAM的索引文件仅仅保存数据记录的地址。

2. InnoDB索引实现（聚集索引）

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同，第一个重大区别是InnoDB的数据文件本身就是索引文件。

InnoDB为什么必须要求表必须有主键？且是单调自增的？

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

什么情况下可以用到B树索引

(1) 定义有主键的列一定要建立索引。因为主键可以加速定位到表中的某行

(2) 定义有外键的列一定要建立索引。外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接

(3) 对于经常查询的数据列最好建立索引。

① 对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间

② 经常用在 where子句中的数据列，将索引建立在where子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，加快查询的时间

[4] 聚簇索引（非聚簇索引）

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法.特点是存储数据的顺序和索引顺序一致.
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针.

聚集索引：一个表中只能有一个，聚集索引的顺序与数据真实的物理存储顺序一致.查询速度贼快，聚集索引的叶子节点上是该行的所有数据 ，数据索引能加快范围查询(聚集索引的顺序和数据存放的逻辑顺序一致).主键!=聚集索引.

辅助索引(非聚集索引)：一个表中可以有多个，叶子节点存放的不是一整行数据，而是键值，叶子节点的索引行中还包含了一个’书签’，这个书签就是指向聚簇索引的一个指针，从而在聚簇索引树中找到一整行数据.

聚集索引与辅助索引的区别：叶子节点是否存放的为一整行数据

[6] 最左前缀原则

link1，link2

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

2. 索引的最左前缀原则：MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引.在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度.这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符.最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段.

1. 一个 2 列的索引 (name， age)，对 (name)、(name， age) 上建立了索引；
2. 一个 3 列的索引 (name， age， sex)，对 (name)、(name， age)、(name， age， sex) 上建立了索引.

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

[7] 联合索引

MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列遵循最左前缀规则，对where，order by，group by 都生效.

[8] 覆盖索引

SQL语句只通过索引，就取到了所需要的数据，这个过程就叫做索引覆盖，换句话说查询列要被所使用的索引覆盖。

解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息。

注：遇到以下情况，执行计划不会选择覆盖查询。

• select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。

• where条件中不能含有对索引进行like的操作。

[9] 什么情况下索引会失效？

即查询不走索引

下面列举几种不走索引的 SQL 语句：

\1. 索引列参与表达式计算：

SELECT 'sname' FROM 'stu' WHERE 'age' + 10 = 30;

\2. 函数运算：

SELECT 'sname' FROM 'stu' WHERE LEFT('date'，4) < 1990; 

\3. %词语%–模糊查询：

SELECT * FROM 'manong' WHERE `uname` LIKE '码农%' -- 走索引 
SELECT * FROM 'manong' WHERE `uname` LIKE "%码农%" -- 不走索引 

\4. 字符串与数字比较不走索引：

CREATE TABLE 'a' ('a' char(10)); EXPLAIN SELECT * FROM 'a' WHERE 'a'="1" -- 走索引 EXPLAIN SELECT * FROM 'a'WHERE 'a'=1 -- 不走索引，同样也是使用了函数运算 

\5. 查询条件中有 or ，即使其中有条件带索引也不会使用.换言之，就是要求使用的所有字段，都必须建立索引：

select * from dept where dname='xxx' or loc='xx' or deptno = 45;

\6. 正则表达式不使用索引.

\7. MySQL 内部优化器会对 SQL 语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引.

怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因?

使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息.可以通过其中和索引有关的信息来分析是否命中了索引，例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度.

[10] 什么情况下不建议建索引

• 对于那些查询中很少涉及的列、重复值比较多的列不要建立索引 例如，在查询中很少使用的列，有无索引并不能提高查询的速度，相反增加了系统维护时间和消耗了系统空间；又如，“性别”列只有列值“男”和“女”，增加索引并不能显著提高查询的速度 对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大，要么很小，不利于使用索引。
• 表记录比较少 例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引
• 索引的选择性较低 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值 Index Selectivity = Cardinality / #T 显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。情况下不建议建索引
  • 对于那些查询中很少涉及的列、重复值比较多的列不要建立索引 例如，在查询中很少使用的列，有无索引并不能提高查询的速度，相反增加了系统维护时间和消耗了系统空间；又如，“性别”列只有列值“男”和“女”，增加索引并不能显著提高查询的速度 对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大，要么很小，不利于使用索引。
  • 表记录比较少 例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，个人有个人的看法，我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引
  • 索引的选择性较低 所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值 Index Selectivity = Cardinality / #T 显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

[11] Hash索引

因为底层是哈希表，数据存储在哈希表中顺序是没有关联的，所以他不适合范围查找，如果要范围查找就需要全表扫描，他只适合全值扫描；简单的来说就是hash索引适合等值查找，不适合范围查找。

[<事务专题>]

[1] 事务的四种特性？

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

事务是数据库的概念**：园艺搁池**

[2] 并发操作会产生的问题

link

假如有事务1，事务2两个事务.

• 第一类丢失更新：由于事务1的回滚，导致事务2更新的的数据丢失.
• 第二类丢失更新：由于事务1的提交，导致事务2更新的的数据丢失.
• 脏读：事务2读取了事务1未提交的数据，由于事务1回滚，导致的数据不一致.
• 不可重复读：事务1前后多次读取，由于事务2期间修改了数据，导致数据内容不一致.
• 幻读：事务1读的时候读出了N条记录，事务2在事务1执行的过程中增加 了1条，事务1再读的时候就变成了N+1条.

更脏不换

第一类更新丢失：

第二类更新丢失：

脏读

不可重复读

幻读

不可重复复和幻读的区别在于前者是对于单行，后者是对于多行而言，因此前者主要是针对更新操作，后者是针对于插入操作

[3]事务的隔离级别有哪些？

Mysql如果不加限制，会造成一系列的并发问题，所以需要进行加锁操作.但是加锁会影响性能，所以存在矛盾，事务的隔离划分出不同的隔离级别就是为了在不同场景内选择适合的隔离级别.事务的隔离级别越高，对数据的完整性和一致性保证越佳，但是对并发操作的影响也越大.MySQL事务默认隔离级别是可重复读.

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏 感，可能更关心数据并发访问的能力。

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。下表很好地概括了这4个隔离级别的特性。
原文链接：https://blog.csdn.net/soonfly/article/details/70238902

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8IjV2Pcb-1596676378909)(X:\Users\xu\AppData\Roaming\Typora\typora-user-images\image-20200629224535553.png)]

所有写操作都会加排它锁， 排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是对不加锁的读(select)就不起作用了。

READ UNCOMMITTED(读未提交) ： 对于修改的项加排它锁，直到事务结束释放；没有快照读，只能读最新版本的数据，不能避免任何并发问题。

READ COMMITTED(读已提交) ：对于修改的项加排它锁，直到事务结束释放；有快照读，快照的粒度是语句级。可以避免更新丢失和脏读。

REPEATABLE READ(可重复度) ：对于修改的项加排它锁，直到事务结束释放；有快照读，快照的粒度是事务级。可以避免不可重复读。

SERIALIZABLE（串行化） ：对于修改的项加排它锁， 使用间隙锁解决幻读问题，不会引发并发问题。

可重复读：https://blog.csdn.net/wangbaosongmsn/article/details/106874093

InnoDB在每行记录后面保存两个隐藏的列来，分别保存了这个行的创建时间和行的删除时间.这里存储的并不是实际的时间值，而是系统版本号，当数据被修改时，版本号加1
在读取事务开始时，系统会给当前读事务一个版本号，事务会读取版本号<=当前版本号的数据
此时如果其他写事务修改了这条数据，那么这条数据的版本号就会加1，从而比当前读事务的版本号高，读事务自然而然的就读不到更新后的数据了.

/MVCC会让修改操作（读取，删除）增加一个版本号，可重复读让读事务指定同样的版本号，不会读到另外事务修改的版本号./

【MySQL中的锁】

原文链接：https://blog.csdn.net/soonfly/article/details/70238902

首先从思想上无论在java还是mysql都有这乐观锁和悲观锁的概念，数据库可以分为悲观锁（共享锁和排他锁），本文就从这里入手

[1] 为什么要加锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制 的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。

[2] 不同引擎所支持的锁？

它们都支持表级锁，BDB采用页面锁，InnoDB采用的是行级锁。

MyISAM和MEMORY存储引擎：采用的是表级锁（table-level locking）；

BDB存储引擎：采用的是页面锁（page-level locking），但也支持表级锁；

InnoDB存储引擎：既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度 来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用，如一些在线事务处理（OLTP）系统。

MySQL的表级锁有两种模式**：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）**。

[3] 行锁：悲观锁

InnoDB实现了以下两种类型的悲观锁。

• X锁（Exclusive Lock）—排他锁或独占锁：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁.它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止.在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁.

  注意：排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是不加锁的就没办法控制了.

排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁，而并非不能读

在select命令中使用独占锁的SQL语句为：select … for update;

• S锁（Shared Lock）—共享锁：若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁.这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改.

如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select … lock in share mode

InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

[4] 意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。
意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。
事务可以通过以下语句显式给记录集加共享锁或排他锁：

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
排他锁（X）：SELECT * FROM table_name WHERE … FOR UPDATE。
用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

[5] 间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使 用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需 要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

在mysql的innoDB存储引擎中，如果更新操作是针对一个区间的，那么它会锁住这个区间内所有的记录，比如update xxx where id between a and b那么它会锁住a到b之间所有记录，注意是所有记录，甚至这个记录并不存在也会被锁住，这个时候，如果另外一个连接需要插入一条记录到a到b之间，那么它就必须等到上一个事务结束。

[5] MVCC：乐观锁

https://www.jianshu.com/p/56fa361e0d94

mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能。 什么是多版本并发控制呢 ？

MVCC：multiversion concurrency control。其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号。 而每一个事务在启动的时候，都有一个唯一的递增的版本号。

1、在插入操作时 ： 记录的创建版本号就是事务版本号。比如我插入一条记录, 事务id 假设是1 ，那么记录如下：

2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。比如，针对上面那行记录，事务Id为2 要把name字段更新

update table set name= 'new_value' where id=1;

3、删除操作的时候，就把事务版本号作为删除版本号。比如

delete from table where id=1;

4、查询操作：
从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1. 删除版本号 大于 当前事务版本号，就是说删除操作是在当前事务启动之后做的。
2. 创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在事务中（等于的情况）或者事务启动之前。

创建版本号<=当前事务版本号<删除版本号

1. 这样就保证了各个事务互不影响。从这里也可以体会到一种提高系统性能的思路，就是： 通过版本号来减少锁的争用。
2. 另外，只有read-committed和 repeatable-read 两种事务隔离级别才能使用MVCC
   read-uncommited由于是读到未提交的，所以不存在版本的问题
3. 而serializable 则会对所有读取的行加锁。

[6] 什么时候会加锁？

在数据库增删改查四种操作中**，insert、delete和update都是会加排它锁**(Exclusive Locks)的，select不会加锁，只有显式声明才会加锁:

• select: 即最常用的查询，是不加任何锁的
• select … lock in share mode: 会加共享锁(Shared Locks)
• select … for update: 会加排它锁

[MySQL优化总结]

https://www.nowcoder.com/discuss/150059?type=0&order=0&pos=8&page=0

https://zhuanlan.zhihu.com/p/98146427

1. 数据库设计和表创建时考虑性能
2. sql的编写注意优化
3. 分区
4. 分库
5. 分表

[1] 数据库设计和表创建时考虑性能

设计表时注意：

• 表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替
• 尽量使用INT而不是BIGINT，如果非负加上UNSIGNED，当然使用TINYINT、SMALLINT、MEDIUMINT更好.
• 使用枚举或整数代替字符串类型
• 尽量使用timestamp而非datatime
• 单表不要有太多字段，建议在20以内
• 用整型来存IP

索引：

• 不用外键，由程序保证约束
• 尽量不用UNIQUE，由程序保证约束
• 字符字段最好不要做主键
• 字符字段只建前缀索引
• 值分布稀少的字段不适合建索引，例如’性别’
• 应尽量避免在where子句中对字段进行null值判断，否则将导致引擎进行全表扫描
• 使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引
• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描

使用合适的数据类型：

• 使用可存下数据的最小数据类型， 整型<datetime<char<varchar<blob
• 使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂.如，int类型存储时间类型，bigint类型转ip函数.
• 使用合理字段属性长度，固定长度的表会更快.使用enum、char而不是varchar
• 尽可能使用not null定义字段
• 尽量少用text，非用不可最好分表

选择合适的索引列：

• 查询频繁的列，在where，group by，order by， on从句中出现的列
• where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
• 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
• 离散度大的列，放在联合索引前面.查看离散度，通过统计不同的列值来实现，count越大，离散程度越高

[2] SQL的编写需要注意优化

• 使用limit对查询结果的记录进行限定
• 避免select * ，将需要查找的字段列出来
• 使用连接（join）来代替子查询
• 拆分大的delete或insert语句
• 通过开启慢查询日志来找出较慢的SQL
• 不做列运算：select id where age+1=10， 任何对列的操作都将导致全表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能移至等号右边
• sql语句尽可能简单：一条sql只能在一个CPU运行；大语句拆小语句，减少锁时间；一条大的SQL可堵死整个库
• OR改写成IN： OR的效率是N级别，IN的效率是log(n)级别，IN的个数建议控制在200以内
• 不要函数和触发器 ，在应用程序实现
• 避免%xxxx式查询
• 少用join
• 使用同类型比较，比如用‘123’和‘123’比，123和123比
• 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
• 对于连续数值，使用BETWEEN不用IN: SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

[3] 谈谈你对慢查询的理解，如何解决慢查询？

link

分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”.

慢查询的优化方法？

1. 只返回必要的列：最好不要使用 SELECT * 语句.
2. 只返回必要的行：使用 LIMIT 语句来限制返回的数据.
3. 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的

[4] 分区

mysql的分区是一种简单的水平分区，用户在建表时加上分区参数，对应用是透明的

对用户来说分区表是一个独立的逻辑表，但是底层由多个物理子表组成.用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，我测试，查询时不带分区条件的列，也会提高速度，故该措施值得一试.

分区的好处是：

• 可以让单表存储更多的数据
• 分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据.另外，还可以对一个独立分区进行优化、检查、修复等操作
• 部分查询能够从查询条件确定只落在少数分区上，速度会很快
• 分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
• 可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
• 可以备份和恢复单个分区

分区的限制和缺点：

• 一个表最多只能有1024个分区
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
• 分区表无法使用外键约束
• NULL值会使分区过滤无效
• 所有分区必须使用相同的存储引擎

分区的类型：

• RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
• LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
• HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算.这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
• KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数.必须有一列或多列包含整数值
• 具体关于mysql分区的概念请自行google或查询官方文档，我这里只是抛砖引玉了.

[5] 分表

分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户.

分表分为垂直拆分和水平拆分，通常以某个字段做拆分项.比如以id字段拆分为100张表： 表名为 tableName_id%100

但：分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本，故：只适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线了再做修改，成本太高！！！而且选择这个方案，都不如选择我提供的第二第三个方案的成本低！故不建议采用.

[6] 分库

把一个数据库分成多个，建议做个读写分离就行了，真正的做分库也会带来大量的开发成本，得不偿失！不推荐使用.