mysql其他相关知识

一、事务

事务是一系列的动作，它们综合在一起才是一个完整的工作单元，这些动作必须全部完成，如果有一个失败的话，那么事务就会回滚到最开始的状态，仿佛什么都没发生一样。

• 事务(transaction)：一组SQL语句；
• 回退(rollback)：指撤销指定SQL语句的过程；
• 提交(commit):指未将存储的SQL语句结果写入数据库表；
• 保留点(savepoint)：指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

《高性能SQL》第7页

1、事务的四个特性：ACID

事务必须保证ACID，即原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）；一个运行良好的事务处理系统，必须具备这些标准特征。当A要转钱给B时，可以开启一个事务，包括检测A的账户余额是否足够、A的账户减去钱数、B的账户加上钱数，任何一个步骤失败，则必须回滚所有的步骤。

1. 原子性(atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说不可能只执行其中的一部分操作，这就是事务的原子性。

2. 一致性(consistency)：数据库总是从一个正确的状态转换到另外一个正确的状态。事务执行前后都是合法的数据状态，不会违背任何的数据约束，保持数据库数据的完整性和一致性。假设用户A和B两者的钱总数为5000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来还是5000.

3. 隔离性(isolation)：一个事务所做的修改在最终提交以前是对其他事务不可见的。

4. 持久性(durability)：一旦事务提交，则其所做的修改就会永久的保存到数据库中，此时即使系统奔溃，修改的数据也不会丢失。

事务并发处理可能引起的问题：

1. 脏读（dirty read）:一个事务读取了另一个事务尚未提交的数据；举例：当事务A、B并发执行时，A事务进行了update但还未提交，此时由于系统的隔离性，B可以看到A尚未提交的数据，如果A此时ROLLBACK，则B select的是无效的数据（脏数据）。
2. 不可重复读（non-repeatable read）：一个事务的操作导致另一个事务前后读取到不同的数据；同样还是事务A进行update、事务B进行select，如果事务A此时已经提交，则事务B重新进行select时获取的数据和之前的数据不一样。
3. 幻读（幻行， phantom row）：一个事务的操作导致另一个事务前后两次查询的结果数据量不同。当事务B select读取数据后，事务A又插入或者删除了一条记录，那么此时B事务再进行查询，可能会发现之前不存在的数据或者之前能够查到的某个数据不见了。

2、隔离级别

https://blog.csdn.net/baidu_29258265/article/details/81774846

SQL的四种隔离级别：

1. Serializable(串行化)：最高的隔离级别，会在读取的每一行数据上都加锁（可能导致大量的超时和锁争用问题，现实中很少用到），通过强制事务串行执行，能够避免脏读、幻读、不可重复读的发生。
2. Repeatable read（可重复读）：解决办法是给写的数据加行级排他锁，事务结束释放；给读的数据加行级共享锁，事务结束后释放；解决了脏读的问题和不可重复读问题（无法解决幻读问题，因为这里加的是行级排他锁，其他事务可以对这个表除了该事务加的行级锁外的其他行进行操作，InnoDB和XtraDB存储引擎通过多版本并发控制MVCC解决了幻读问题）。（MYSQL的默认事务隔离级别）
3. Read committed（读已提交）：解决办法是给写的数据加行级排他锁（写的过程是无法读取的），事务结束释放；给读的数据加行级共享锁（读的时候无法写），但是一旦读完该行就释放共享锁 ，所以无法解决不可重复读问题；满足隔离性的简单定义，即一个事务从开始直到提交之前，所做的任何修改对其他事务是不可见的，可避免脏读的发生（因为写的时候加了行级排他锁，事务结束后释放）。（大多数数据库系统默认的隔离级别）
4. read uncommitted（读未提交）：事务中的修改，即使没有提交，对其他事务也都是可见的，所以是最低级别，任何情况都无法保证。

隔离级别规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

3、控制事务处理：

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。START TRANSACTION表示事务的开始。

SELECT * FROM trade; // 首先查看trade表中是有数据的
START TRANSACTION;   // 标注事务开始
DELETE FROM trade;   // 将trade表中的所有数据删除
SELECT * FROM trade; // trade中此时已没有数据
ROLLBACK;            // 回退START TRANSACTION之后的所有语句
SELECT * FROM trade; // 此时trade不为空
// 显然，ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)

一般MySQL语句都是针对数据库表执行和编写的，这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。但是在事务处理中，提交不会隐含地进行，需要进行明确的提交。

START TRANSACTION;
DELETE FROM orderitems WHERE order_id = 10000;
COMMIT;

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，对于更复杂 的事务处理，需要部分提交或回退，这就需要使用到保留点。

SAVEPOINT delete1;
ROLLBACK delete1;
// 每个保留点都取标识它的唯一名字，以便在回退时MySQL知道要回退到何处。保留点在事务处理完成后自动释放，也可以使用RELEASE SAVEPOINT明确地释放保留点。


SET autocommit = 0； 标识取消MySQL的自动提交，直到autocommit被设置为真（等于1）为止

二、数据库锁

1、乐观锁

总是假设冲突不会发生，在操作数据时不加锁，之后再进行判断是否有冲突。通常适用于读多写少的场景。
乐观锁通常由我们自己实现，一般来说有两种实现方式：

1. 使用数据版本记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的“version”字段来实现。当读取数据时，将version字段的值一同取出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库对应记录的当前版本信息与第一次取出来的version值进行对比，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。
   如上图所示，如果更新操作顺序执行，则数据的版本（version）依次递增，不会产生冲突。但是如果发生有不同的业务操作对同一版本的数据进行修改，那么先提交的操作（图中B）会把数据version更新为2，当A在B之后提交更新时发现数据的version已经被修改，那么A的更新操作会失败。

2. 乐观锁的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，字段类型使用时间戳（timestamp），和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突，更新失败。

2、悲观锁

总是假设冲突会发生，在进行每次操作时都要通过获取锁才能进行对相同数据的操作。悲观锁分为共享锁和排他锁。
同时，悲观锁是由数据库自己实现了的，直接调用数据库的相关语句。for update(排他)/ lock in share mode(共享)。

1. 排他锁（写锁）：如果一个事务加了排他锁，其他事务就不能再给它加任何锁了。update/insert/delete语句会自动使用排他锁，select语句默认不会加任何类型的锁。已经加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update、lock in share mode锁的方式查询数据，但是可以通过select ... from ..进行数据的查询，因为普通查询没有任何锁机制。select使用排他锁的语句为： select * from table for update;
2. 共享锁（读锁）：共享锁就是多个事务对于统一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。使用方式为 ： begin; > select * from table where id = 1 lock in share mode;
3. 行锁：给某一行加上锁，笼统的讲，数据库能够确定哪些行需要锁的情况下使用行锁，如果不知道会影响哪些行，就使用表锁。表锁的作用范围是整张表。

3、使用MySQL做分布式锁

分布式锁是指在分布式的部署环境下，通过锁机制来让客户端互斥的对共享资源进行访问。使用MySQL做分布式锁并不一定性能就很差，硬件跟的上的话，QPS能达到十万以上。

1. 利用MySQL的悲观锁：当我们想要锁住某个方法或者资源时，我们在数据库表中增加一条记录，想要释放锁的时候就删除这条记录。比如说对于一张table表，我们把name设置为唯一索引，如果同时有多个请求提交数据到table中时，数据库只会保证有一个操作可以成功，那么我们便认为操作成功的那个请求获得了该方法的锁，可以使用该共享资源。
2. 利用MySQL的乐观锁：还是使用版本号，读取数据时将version一起读出，更改后version，并且将原始的version与当前数据库版本号进行比较，一致则更新执行，否则更新失败。
3. 除此还有基于缓存的分布式锁redis，基于zookeeper和etcd的分布式锁：
   1. redis：使用红锁（Redlock），在集群中需要半数以上的节点同意才能获得锁，保证了数据的完整性。https://zhuanlan.zhihu.com/p/145128291
   2. zookeeper：

4、InnoDB为了让表锁和行锁共存，使用了意向锁：

假设行锁和表锁能够共存：事务A锁住表中的一行（写锁）；事务B锁住整个表（写锁）。但我们可以发现，事务A既然锁住了某一行，说明其他事务就不可能修改这一行；而事务B锁住整个表，所以能够修改表中的任意一行，两者产生了冲突，所以没有意向锁的时候，表锁和行锁不能够共存。

意向锁在InnoDB中是表级锁，分为意向共享锁（IS）和意向排他锁（IX）：

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

InnoDB行锁兼容模式列表如下图所示：

InnoDB行锁的实现方式：

1. 记录锁（Record-lock）：InnoDB是通过索引上索引项来实现的（这一点ＭySQL与Oracle不同，后者是通过在数据中对相应数据行加锁来实现的）；InnoDB这种行锁的实现特点意味着：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将会使用表锁。
2. 间隙锁（gap-lock）:当检索条件利用范围条件而不是相等条件获取排他锁时，InnoDB会给符合条件的所有数据加锁。对于在范围内但是不存在的记录，叫做间隙。间隙锁，顾名思义锁间隙，不锁记录。锁间隙的意思就是锁定某一个范围，会阻塞插入意向锁。（防止幻读）
3. next-key锁：本质上就是记录锁加上间隙锁。在InnoDB的RR（可重复读）隔离级别下，InnoDB对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁。为啥呢？因为唯一索引能够确定行数，而其他索引不能确定行数，有可能在其他事务中会再次添加这个索引的数据造成幻读。
4. 例子：https://blog.csdn.net/qq_41701956/article/details/81623448

三、MVCC

https://www.jianshu.com/p/f692d4f8a53e

1.多版本并发控制MVCC

MVCC，Multi-Version Concurrency Control，多版本并发控制。一种并发控制事务隔离技术，思想是同一份数据临时保留多版本(undo log)，给调用方展示数据在某个时间点的快照。 MVCC是通过保存数据在某个时间点的快照来实现的，也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

优点：可以认为MVCC是行锁级的一个变种，在很多情况下避免了加锁操作，因此开销很低；虽然实行机制有所不同，实现了非阻塞的读操作，写操作也只锁定必要的行。 缺点是每行记录都需要额外的存储空间，需要更多的行维护和检查工作。

MVCC只在Repeatable Read和read committed两个隔离级别下工作，其他两个隔离级别和MVCC不兼容，因为read uncommitted总是读取最新的数据行，而不是符合当前事务版本的数据行，也就是说MVCC在创建版本和删除版本是发生在事务提交后；而Serializable则会对有所读取的行都加锁。所以MVCC主要作用于事务性的、有行锁控制的数据库模型。

2、InnoDB引擎MVCC的实现：

如图所示：

简单来说，在一行数据中额外保存两个隐藏的列（实际上是通过当前版本和undo log里的语句计算而生成的）：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，指向undo log，用于事务回滚）。每个事务都有一个版本号，每当开始新的事务时，版本号会自动递增。

3、MVCC下InnoDB的增删改查：

• select：在查询时只会查到符合以下两个条件的记录：
  1. 删除版本号未指定或者大于当前事务版本号，这样可以确保事务读取的行在事务开始之前未被删除；
  2. 创建版本号小于或者等于当前版本号，这样要查找的行的版本号是早于当前事务的，能够确保事务读取的行要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的；
• insert：插入记录的创建版本号即是当前事务的版本号；
• delete：删除记录的删除版本号即是当前事务的版本号；
• update：InnoDB为插入一条新记录，保存当前系统版本号为行版本号，同时保存当前系统版本号为原来的行作为行删除标识。

保存这两个版本号，使得大多数读操作都可以不用加锁，这样设计使得数据操作很简单、性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

4、并发控制会导致的两种锁：

1. 活锁：如果事务T1封锁了数据R，事务T2又请求封锁R，于是T2等待。T3也请求封锁R，当T1释放了R上的封锁之后系统首先批准了T3的请求，T2仍然等待。然后T4又请求封锁R，当T3释放了R上的封锁之后系统又批准了T4的请求，...，T2有可能永远等待，这就是活锁的情形，如图8.4(a)所示。 避免活锁的简单方法是采用先来先服务的策略。
2. 如果事务T1封锁了数据R1，T2封锁了数据R2，然后T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁。接着T2又申请封锁R1，因T1已封锁了R1，T2也只能等待T1释放R1上的锁。这样就出现了T1在等待T2，而T2又在等待T1的局面，T1和T2两个事务永远不能结束，形成死锁。死锁没有完全解决的办法，只能尽量预防。约定好加锁顺序、降低隔离级别（比如从RR调整到RC，可以避免掉很多因为gap锁造成的死锁）、尽可能一次锁定所需要的所有资源。

5、MYSQL日志

1. WAL（Write Ahead Log）预写日志：是数据库系统中常见的一种手段，用于保证数据操作的原子性和持久性。

   1. WAL是关系数据库系统中用于提供原子性和持久性的一系列技术；
   2. 在使用WAL的系统中，所有的修改在提交之前都要先写入log中；

2. MySQL三大日志： InnoDB中跟数据持久性、一致性有关的日志有以下几种：

   1. Bin Log: 用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。
      使用场景：
      1. 主从复制：在master段开启binlog，然后将binlog发送到各个slave端，slave端重放binlog从而达到主从数据一致；
      2. 数据恢复：通过使用mysqlbinlog工具来恢复数据。
   2. Redo Log：
      1. 必要性：事务的四大特性之一就是持久性，只要事务提交成功，对数据库的操作就被永久的保存下来，具体操作就是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。这样做会产生两个问题：
         1. Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，比较浪费资源。
         2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上不连续，随机IO写入性能太差。
      2. 基本概念：mysql每执行一条语句，先将记录写入redo log buffer（内存中的日志缓冲），后续某个时间点再一次性将多个操作记录写到redo log life（磁盘上的日志文件）。这种先写日志，再写磁盘的技术就是WAL技术。
      3. 使用场景：适用于崩溃恢复。
      4. 与binlog的区别：binlog日志只用于归档，只依赖binlog是没有崩溃恢复能力的。但是redolog也不行，因为redolog是innodb特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redolog二者同时记录，才能保证数据库发生宕机时，数据不会丢失。
   3. Undo log： 除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它记录了修改的反向操作，比如，插入对应删除，修改对应修改为原来的数据。这样在发生错误时，通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本数据，例如MVCC的实现。

6、当前读与快照读

https://www.jianshu.com/p/cef49aeff36b

在mysql中，有两种并发版本控制事务隔离技术，第一个是MVCC，第二个是基于锁的并发控制，用于支持RC和RR的隔离级别实现。在一个支持MVCC的并发系统中，我们需要支持两种读：快照读和当前读。

1. 快照读：当mysql使用MVCC机制来保证被读取数据的一致性，读取数据时不需要对数据进行加锁，但读取到的数据可能是旧的数据。快照是第一次执行select的时候生成的。优势是不需要加锁，并发性高；缺点就是不是实时数据。
2. 当前读：特殊的读操作，插入/更新/删除，属于当前读，需要加锁（记录锁、间隙锁、next-key），操作的是最新的数据。优势就是获取的是实时数据，但是需要加锁，导致并发性不高。

四、查询执行的基础

上图展示了MySQL执行一个查询的过程：

1. 客户端发送一条查询给服务器；
2. 服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果；否则进入下一阶段
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
4. MYSQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5. 将结果返回给客户端。

1、查询缓存

在解析一个查询语句之前，如果查询缓存是打开的（SQL_CACHE和SQL_NO_CACHE提示高速Mysql这个结果集是否应该缓存在查询缓存中），那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。

2、查询优化处理

1. 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一棵对应的解析树。MySQL解析器将使用MYSQL语法规则验证和解析查询；预处理器则根据一些MySql规则进一步检查解析树是否合法；

2. 现在语法树被认为是合法的了，并且由优化器将其转化成执行计划。在查询优化器里，一条SQL语句的查询可以有不同的执行方案（所以可能会产生执行的时候没有使用创建的索引）。在一条SQL语句真正执行之前，MySQL的查询优化器会找出执行该语句的所有可能使用的方案，对比之后找出成本更低的方案，这个成本最低的方案就是所谓的执行计划。优化过程大致为：1.根据搜索条件，找出所有可能使用的索引； 2. 计算全表扫描的代价； 3. 计算使用不同索引执行查询的代价； 4. 对比各种执行方案的代价，找出成本最低的那一个。

3. 执行计划：MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果，最终的执行计划包含了重构查询的全部信息。

五、慢查询

1、定位慢查询 X10

查询性能低下最基本的原因是访问的数据太多，所以大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化，同时注意以下两个方面：

1. 是否向数据库请求了不需要的数据：有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源。主要有：

   1. 查询不需要的记录；
   2. 多表关联时返回全部列；
   3. 总是取出全部列；
   4. 重复查询相同的数据

2. MySQL是否扫描额外的记录：在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。主要衡量指标有：响应时间、扫描的行数、返回的行数；

   1. 响应时间：等于服务时间和排队时间之和。服务时间是指数据库处理这个查询真正花了多长时间；排队时间是指服务器因为等待某些资源而没有真正执行查询的时间（可能是等待I/O操作完成，也可能是等待行锁）；
   2. 了解扫描的行数和返回的行数可以在一定程度上说明该查询找到需要的数据的效率高不高。

mysql慢查询：

https://blog.csdn.net/qq_40884473/article/details/89455740：
可以通过在mysql数据库的配置文件中添加配置命令开启慢查询日志，从而定位慢查询，如下图所示：

其中：

• show_query_log： 慢查询日志开启
• long_query_time: 定义慢查询的时间
• slow_query_log_file：慢查询日志存放位置
• log_query_not_using_indexes： 记录没有使用索引的查询

2、explain的使用

在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL，然后使用explain去分析SQL的执行计划，比如是全表扫描、还是索引扫描。

定位出慢查询sql后，我们可以使用explain命令查看优化器去怎样执行sql查询的，使用方式为：explain + select + sql语句，执行计划包含的信息有：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra。

1. id：表示查询中select子句或操作表的执行顺序，id如果相同，可以认为是一组，从上往下顺序执行；在所有的组中，id值越大越先执行；
2. select_type：查询中每个select子句的类型（简单OR复杂），具体值如下图:
3. table:表名
4. type： 表示MySQL在表中找到所需行的方式，又称“访问类型”，常见类型有：ALL、index、range、ref、eq_ref、const、system、NULL，从左到右，性能依次变好。具体如下图:
5. possible_keys:指出MySQL可能能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用；
6. key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
7. key_len：表示索引中使用的字节数，显示的值为索引字段的最大可能长度，并非实际使用的长度；
8. ref：哪些列或常量被用于查找索引列上的值；
9. rows：表示MySQL根据表统计信息及索引选用情况，估算找到所需记录所需要读取的行数；
10. Extra：包含不适合在其他列显示但十分重要的额外信息。Using Index表示相应的select操作中使用了覆盖索引；Using Where表示没有可用的索引就能找到所需，需要通过回表查询数据；Using Temporary表示mysql需要使用零时表来存储结果集，常见于排序和分组查询；Using filesort表示无法利用索引完成的操作称为“文件排序”。

除此之外，还可以使用show profiles用来分析当前会话SQL语句执行的资源消耗情况，可用于SQL的调优测量。先使用show profiles，后续执行的SQL语句都将记录其资源开销，诸如IO、上下文切换、CPU、Memory等；

六、优化：

1、SQL优化

1. 查询具体的字段而非全部字段：任何地方都不要select *，而是查询所需的具体字段，可以提升速度以及减少网络传输的带宽压力；
2. 优化子查询：尽量使用JOIN语句来代替子查询，因为子查询是嵌套查询，会创建一张临时表，创建销毁都会占用资源和花费时间，而join不会；
3. 尽量使用小表驱动大表：如果B表的数据小于A表的数据，那执行的顺序就是先查B表再查A表；同时内表数据大的用exists，外表数据大的用in
4. 适当增加冗余字段：增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所以可以适当增加冗余字段，以减少多张表的关联查询；
5. 当只要一行数据时，使用limit 1：这是一问MySQL数据库引擎会在找到一条结果后停止搜索，而不是继续查询下一条是否符合标准，从而提高性能；
6. 对于连续数值，使用BETWEEN不用IN，因为当IN的取值范围较大时可能会导致索引失效；
7. 排查慢SQL；参考二
8. 尽量避免大事务操作，避免执行一个大的DELETE或INSERT，因为这俩操作会锁定表，影响并发。

1、索引优化：

索引优化要做的主要就是选择合适的索引类型，在查询的时候避免索引失效而导致全表扫描：

1. 避免前导模糊查询，对于使用like的查询，查询如果是'%a'则不会用到索引，因为前面是模糊的，所以不能利用索引的顺序；
2. 尽量避免在where子句中使用!= 或<>操作符，因为这些操作符会导致查询引擎放弃索引而进行全表扫描；
3. 在索引列上避免使用IS NULL或IS NOT NULL操作，索引是不能够索引空值的；索引无法存储null值，因为索引是有序的，null值进入索引时，无法确定其在索引树的位置。
4. 不要在索引列上使用表达式，否则会导致查询引擎无法正确的使用索引；
5. 字符串不加单引号索引失效：如果列类型是字符串，那一定要在条件中使用引号，否则不使用索引；
6. 对于组合索引，如果没有使用第一列索引，则索引会失效；
7. 避免在where中使用or来连接，因为当or语句前后没有同时使用索引时，该索引失效而进行全表扫描，只有当or左右查询字段均为索引时，才会生效。

3、数据库结构优化

1. 表的字段长度尽可能小：一般来说数据库的表越小，那么它的查询速度就越快，能使用varchar就不要使用char；
2. 使用最简单的数据类型：能使用int类型就不要使用varchar类型，因为int类型比varchar类型的查询效率更高；
3. 尽量少定义text类型：text类型的查询效率很低，如果必须要使用的话，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率；
4. 适当分表、分库策略：分表分库方案也就是我们常说的垂直分隔（分表），水平分隔（分库）。分表是指当一张表中的字段更多时，尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率；分库是指将一个数据库分为多个数据库，比如我们把一个数据库拆分成了多个数据库，一个主数据库用于写入和修改树库，其他的用于同步主数据库并提供给客户端查询，这样就把一个库的读写压力分摊给了多个库，从而提高了数据库整体的运行效率。

4、系统硬件优化

5、其他的优化

LIMIT的优化：select * from table_name limit 10000, 10，这条SQL的执行逻辑是从数据库读出10010条数据，根据offset抛弃前面10000条数据，返回剩余的10条数据；所以此次查询中会首先查询10010条数据，会很慢；

1. 利用自增主键进行优化： select * from table_name where id>9999 limit 10；
2. 利用子查询优化：利用子查询先找出第一条数据的id（主键），然后大于等于这条数据的id就是要获取的数据，如图
3. 利用临时表优化：1）建立临时表（含自增主键）；2）插入查询结果到临时表；3）利用临时表的自增主键进行查询。

6、常见面试题：

1. MySQL的优化方案有哪些？
2. 如何排查慢查询？
3. 索引失效的情况？
4. MySQL单表数据量过千万，解决方案：https://www.toutiao.com/i6633207458275787268/?tt_from=weixin&utm_campaign=client_share&wxshare_count=2&from=singlemessage&timestamp=1544497057&app=news_article&utm_source=weixin&iid=36618779372&utm_medium=toutiao_android&group_id=6633207458275787268&pbid=6634051816228275726

https://www.cnblogs.com/gaoquanquan/p/11030999.html

https://www.cnblogs.com/zsql/p/13808417.html#_label0_0

mysql索引面试题：https://www.cnblogs.com/williamjie/p/11187470.html
https://zhuanlan.zhihu.com/p/29118331

当发现索引性能比较慢时，可以使用explain进行sql语句分析：https://www.cnblogs.com/kongzhongqijing/articles/3544020.html

七、索引

1、基础知识：

索引：（在MYSQL中也叫做键（key））是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，当数据量逐渐增大时，性能则会急剧下降。

索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MYSQL只能高效地使用索引的最左前缀列。

索引是在存储引擎层而不是服务器层实现的，所以并没有统一的标准，不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

索引可以让服务器快速的定位到表的指定位置；同时，最常见的B+ Tree索引是按照顺序存储数据的，所以MYSQL可以用来做ORDER BY和GROUP BY操作。总结下来，索引有三个优点：1. 索引大大减少了服务器需要扫描的数据量（查找数据时不需要扫描全表）；2. 索引可以帮助服务器避免排序和临时表；3. 索引可以将随机I/O变为顺序I/O。

创建索引时，我们需要确保该索引是应用在SQL查询语句的条件（一般作为WHERE子句的条件），而不是在select的字段中。实际上，索引也是一张“表”，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert/update/delete，因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。

skk面试总结：

1. 索引的定义： 对数据库表中的一个或多个列的值进行排序的结构。如果想按姓来查找他/她，则与在表中搜索所有的行相比，索引有助于更快的查询到所需结果。

2. 索引的优点：

   1. 提高检索速度；
   2. 建立唯一性索引，保证数据库表中每一行数据的唯一性；
   3. 加速表和表之间的连接；
   4. 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

3. 索引的缺点：

   1. 增删改时需要维护索引的正确性，影响增删改的速度；
   2. 需要额外的存储空间。

2、索引的类别

1. 根据索引字段个数划分

为了应对不同的数据检索需求，索引即可以仅包含一个字段，也可以同时包含多个字段。单个字段组成的索引可以称为单值索引，否则称之为复合索引或组合索引或多值索引。

这个很好理解，假如我们有一张表，有三个属性，分别是 id，age 和 name 。假如在id上建立索引，那这就是单值索引；如果在 name 和 age 上建立索引，那这就是复合索引。

复合索引的索引的数据顺序跟字段的顺序相关，包含多个值的索引中，如果当前面字段的值重复时，将会按照其后面的值进行排序。
如下图所示：

2. 根据是否在主键上建立索引进行划分

1. 主键索引： MYSQL中是根据主键来组织数据的，所以每张表都必须有主键索引，主键索引只能有一个，不能为null，同时必须保证唯一性。建表时如果没有指定主键索引，则会自动生成一个隐藏的字段作为主键索引。

2. 辅助索引：如果不是主键索引，则就可以称之为非主键索引，又可以称之为辅助索引或二级索引。主键索引的叶子节点存储了完整的数据行，而非主键索引的叶子节点存储的则是主键索引值，通过非主键索引查询数据时，会先找到主键索引，然后再到主键索引上去查找对应的数据（所以需要两次索引查找，也称回表查询）。

3. 根据数据与索引的存储关联性划分

根据数据与索引的存储关联性，可以分为聚簇索引和非聚簇索引（也叫聚集索引和非聚集索引）。聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。简洁的说法就是，这俩的区别就是索引的存储顺序和数据的存储顺序是否是关联的，有关就是聚簇索引，无关就是非聚簇索引。具体实现方式根据索引的不同会有所不同，以B+树实现的索引为例，来说明两者的区别。

1. 聚簇索引

   1. 实际上就是InnoDB的主键索引（利用主键建立的索引），非叶子节点存储的是索引指针，叶子节点存储了索引和行的全部数据。如下图所示，可以发现，索引和数据的存储顺序是强相关的，因此是典型的聚簇索引。
      
   2. InnoDB将通过主键聚集数据，这也就是说上图中“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。
   3. 聚簇数据的优点：
      1. 可以把相关数据保存在一起。例如在实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部数据。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O；
      2. 数据访问更快：聚簇索引将索引的数据保存在同一个B+树中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快；

2. 非聚簇索引

   1. MyISAM中索引和数据文件分开存储，B+Tree的叶子节点存储的是数据存放的地址，而不是具体的数据，是典型非聚簇索引。换言之，数据可以在磁盘上随便找地方存，索引也可以在磁盘随便找地方存，只要叶子节点记录了对数据存放地址就行。因此索引存储顺序和数据存储关系无关联，是典型的非聚簇索引，InnoDB中的辅助索引也是非聚簇索引。
      

   2. 通过非聚簇索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查到所需的数据。二次查找

3. 覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。能够极大地提高性能。所以说，非主键索引也不一定就会查询很多次，例如覆盖索引也可以只查询一次。

参考来源：https://cloud.tencent.com/developer/article/1603199；高性能mysql 第5章

3、索引的数据结构

适合索引的三种数据结构：B树、B+树、哈希。

使用B+树作为索引的数据库有MYSQL、SQL Server、Oracle。

1、B树：

1. 2-3树，其中每个节点都具有两个孩子(称为2节点)或者三个孩子(称为3节点)。并且2-3树中所有的叶子都在同一层上。如下图所示，一个2节点包含一个元素和两个孩子（或者没有孩子）,且与二叉排序树相似，左子树包含小于根节点的元素，右子树包含大于根节点的元素，不同的是，2节点要么没有孩子，要么有两个，不能只有一个；一个3节点包含一小一大两个元素和三个孩子（或者没有孩子），如果有三个孩子的话，左子树包含小于小根节点的元素，右子树包含大于较大根节点的元素，中间子树包含介于两元素之间的元素。
   

2. 2-3-4树：是2-3树的扩展，包括了4节点的使用，一个4节点包含一小一中一大三个元素和四个孩子（或者没有孩子）。与上面2-3节点相似，当一个4节点包含4个孩子时，左子树包含小于最小元素的元素；右子树大于最大元素的元素；第二子树包含大于最小元素、小于中元素的元素；第三子树包含大于中元素、小于最大元素的元素。

3. B树（B-树）：上面所说的2-3树、2-3-4树都是B树的特例，B树是一种多路平衡查找树，结点最大孩子的数目称为B树的阶（order）。一个m阶的B树具有如下属性：a.如果根节点不是叶节点，则其至少有两棵子树；b.每一个非根的分支结点都有K-1个元素和K个孩子，其中m/2 <= K <= m；每一个叶子节点n都有k-1个元素，其中m/2 <= K <= m；c.所有叶子节点都位于同一层次。例如下图，灰色方块表示当前节点的个数，在B树上查找的过程是一个顺指针查找节点和在节点中查找关节字的交叉过程。
   
   比如在B树上查找数字7，首先从外存（比如硬盘中）读取到的根节点3、5、8三个元素，发现7不在，但是在5和8之间，因此再通过A2再读取外存的6、7节点，查找到所要的元素。所以B树结构可以大大减少内存与外存交换数据的次数。在一个典型的B树应用中，要处理的硬盘数据量很大，无法一次性全部装入内存，因此会对B树进行调整，使得B的阶数（或节点的元素）与硬盘存储的页面大小相匹配。比如一棵阶数为1001的B树（即一个节点能存储1001个关键字），树的高度为2，可以存储超过10亿个关键字（根节点的），我们只要将根节点持久地保留在内存中，那么就在这棵树上，寻找某一个关键字最多需要进行两次硬盘的读取即可。所以可以说，B树的数据结构就是为内外存的数据交互准备的。
   

2、B+树（多路平衡查询树）：

在B树中，我们往返于每个节点之间，也就意味着，我们就必须得在硬盘的页面之间进行多次访问，如下图所示，我们希望遍历这棵B树，假设每个节点都属于硬盘的不同页面，我们为了中序遍历所有的所有的元素，页面2-1-3-1-4-1-5。

而且我们每次经过节点遍历时，都会对节点进行一次遍历，非常的糟糕。为了可能让遍历时每个元素只访问一次，所以B+树就应运而生了，B+树是应文件系统所需而衍生出的一种B的变形树，如下图所示，出现在分支节点中的元素会被当做它们在该分支节点位置的中序后继者（叶子节点）中再次列出，另外，每个叶子节点都会保存一个指向后一个叶子节点的指针。下图中灰色关键字即是根节点中的关键字在叶子节点中再次列出，并且所有叶子节点都连在一起。

总结：

1. 对于B树：

   1. 所有的节点（包括非叶子节点和叶子节点）不止含有字段值，还有指向真实记录的指针；
   2. 非叶子节点和叶子节点的字段值不会重复；
   3. 上一个叶子节点没有指向下一个叶子节点的指针，所以无法从叶子节点向后遍历；
   4. B树减少了磁盘IO次数，但是没有解决元素遍历效率低下的问题。

2. 对于B+ 树：

   1. 所有的非叶子节点仅记录参与索引的字段值，而叶子节点不仅含有字段值，还有指向真实记录的指针；
   2. 非叶子节点和叶子节点的字段值会重复；
   3. 上一个叶子节点指向下一个叶子节点的指针，所以可以从第一个叶子节点遍历其后的所有叶子节点；
   4. B+树的叶子节点形成有序链表，便于执行范围操作（数据库经常要执行范围操作）。
   5. 查询效率更稳定，B树的查询效率在1到树高之间，而B+树的查询时间复杂度则稳定为树高，因为所有数据都在叶子节点。

3. 作为B树的加强版，B+树与B树的差异在于：

   1. 有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）；
   2. 所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序链接；
   3. 非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字。

4. B+与红黑二叉树的对比：磁盘选择B+树，内存选择红黑树

   1. B+树属于多路平衡查找树，与红黑树相比，最大的优势是树高更小，能够有效减少磁盘的随机访问；同时，B+树节点相互邻近，能够发挥磁盘顺序读取的优势。
   2. 红黑树更适合在内存操作，因为插入、删除、搜索效率都维持在O(logn)左右；B+树常用于文件系统和数据库索引，能够将所有叶子节点放入链表中，使得数据更加紧凑，而磁盘有局部加载的优化。

3、哈希索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效。

优缺点：高性能mysql P147

• 哈希索引数据并不是按照索引值顺序存储的，所有也就无法用于排序和范围查询；
• 哈希索引只支持等值比较查询，如 =、IN()、<=> 操作；
• 哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

4、关于B、B+ tree的问题

skk面经 P25
为什么B+树比B树更适合做索引？

• https://www.cnblogs.com/williamjie/p/11187470.html；
• https://blog.csdn.net/mine_song/article/details/63251546

1. B+树空间利用率高，可减少I/O次数：一般来说索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。因为B+树的非叶子节点只是作为索引使用，而不像B-树那样每个节点都需要存储硬盘指针。也就是说，B+树每个非叶子节点没有指向某个关键字具体的信息，所以每一个节点都可以存放更多的关键字数量，即一次性读入内存所需要查找的关键字也就越多，减少了I/O操作；
2. 增删文件（节点）时，效率更高：因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率，同时可提高区间访问的性能。
3. B+树的查询效率更加稳定：因为B+树的每次查询过程中，都需要遍历从根节点到叶子节点的某条路径，所有关键字的查询路径长度相同，导致每一次查询的效率相当。

B+ Tree 索引和hash索引的区别：

1. 哈希索引适合等值查询，无法进行范围查询；
2. 哈希索引没办法利用索引完成排序；
3. 哈希索引不支持多列联合索引的最左匹配规则；
4. 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

4、索引的实现原理

不同存储引擎对索引的实现方式是不同的：

1. MyISAM索引实现：B+ Tree，叶节点的data域存放的是数据记录的地址，其索引文件和数据文件是分离的。
   
   在MyIASM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

2. InnoDB索引实现：目前有两种B+Tree和hash索引。在这里与MyISAM不同的是，1. InnoDB的数据文件本身就是索引文件。叶节点data域保存了完整的数据记录，叫做聚集索引。这个索引的key就是数据库表的主键。
   2. InnoDB的辅助索引data域存储相应记录主键的值而不是地址。辅助索引搜索需要两遍索引：首先检索辅助索引获得主键，然后用主键到聚簇索引中检索获得记录。
   

3. 除此，hash索引在InnoDB引擎有一种特殊的用途：“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B+ Tree索引之上再创建一个哈希索引，这样让B+Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。（这是一个完全自动的、内部的行为，用户无法控制或者配置）。在实际开发中，如果存储引擎不支持hash索引，我们可以模拟像InnoDB一样创建哈希索引。一般使用在辅助索引。

5、索引失效

1. null：
   1. 查询时采用 is null 条件，不能利用到索引，只能全表扫描；因为索引无法存储null值（索引是有序的，null值进入索引时，无法确定其应该放在索引树的哪里）。
   2. 解决办法：可以把null值转化成为一个特定的值，在WHERE中检索时，用该特定值查找；建立一个复合索引，通过在复合索引中指定一个非空常量值。
2. 前导模糊查询（like '%XX'）
   1. 查询时where中的语句若是前导模糊的，就不能利用索引的顺序，只能一个个去查找是否满足条件，这样就会导致全表扫描。
   2. 解决办法：将模糊词放到固定词的后面。
3. 使用了or：如果条件中有or，即使其中有条件带索引也不会使用；正确的使用应该是将or条件中的每个列都加上索引。
4. 使用不等于（!= 或者 <>）不能使用索引；
5. 索引查询不能使用表达式；
6. 复合索引的失效：如果对a、b、c三列建立了一个复合索引，则应该满足最左前缀原则，即使用索引a、(a、b)、(a、b、c)进行查找是有效的，其他无效。

八、MySQL引擎对比：（InnoDB、MyISAM、Memory）

概述：MySQL支持的存储引擎有ISAM、MYISAM、HEAP（Memory）、INNODB、BERKLEY（BDB），主要简介如下：

1. ISAM:被查询的次数要远大于更新的次数，读取操作的速度很快，但是不支持事务处理、不能够容错；

2. MYISAM：是ISAM的扩展格式，除了提供ISAM没有的索引和字段管理功能，还使用一种表格锁定的机制来优化多个并发的读写操作；所以其读写速度很快

3. HEAP允许只驻留在内存里的临时表格，驻留在内存里使得其比ISAM和MYISAM都要快，但是管理的数据不稳定，如果在关机之前不保存的话，所有的数据都会丢失。不能建立太大的表。

4. InnoDB引擎：MySQL的默认引擎

   1. 提供事务支持，实现了四种隔离级别，默认为RR（可重复读）
   2. 使用行级锁，由于其粒度更小，所以在并发较高时，会提升效率；
   3. 支持外键；
   4. 是聚集索引，而MyISAM是非聚集索引
   5. 当数据库需要使用事务时，需要使用该引擎。

5. MyIASM引擎：

   1. 不支持事务、不支持行级锁（写操作需要锁定整个表，效率低下）、不支持外键;
   2. 如果表的读操作远远多于写操作时，且不需要数据库事务的支持时，优先选择MyISAM引擎。

九、分布式数据库

主要是分库分表，因为使用的时间久的话，肯定会导致大量的数据需要存到数据库中，所以就需要对数据库进行分割。

1. 分库分表：就是按照一定的规则，对原有的数据和表进行拆分，把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。

   1. 垂直分库/分表：垂直划分数据库就是根据业务进行划分，例如将商场库中涉及商品、订单、用户的表分别划分出一个库，同样的分表也是将一个大表根据业务功能拆分成一个个子表。但是这种方式并没有解决高数据量带来的问题，读写压力依旧很大。
   2. 水平分库/分表：是根据一定规则，例如时间或者id值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提高性能。但是涉及到分布式事务、数据一致性问题。

2. 主从复制：用来建立一个和主数据库（master）完全一样的数据库环境，称为从数据库（slave）；主数据库一般是准实时的业务数据库。具体操作流程：将主数据中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行，从而使得从数据库的数据与主数据库保持一致。

   1. 采用读写分离，所有的写操作对应到master中，读操作可以在master和slave机器上进行，slave和master的结构完全一样，一个master可以有多个slave，通过此方式可以有效的提高数据库集群的QPS。
   2. 实现了读写分离，提升了读的性能。
   3. 可以看出master是集群的瓶颈，当写操作过多时会严重影响到master的稳定性。

3. 当读过多时会产生瓶颈：首先可以使用缓存技术，但读依旧还是瓶颈时，就可以选择“读写分离”架构了，在主从复制的基础上，只在主服务器上写，只在从服务器上读。

4. 大表问题：假设mysql中有一张以亿为单位的表，查询时已经到达了单表单机的极限，怎么处理和优化？

   1. 优化sql语句、和索引
   2. 加缓存：redis
   3. 主从复制、读写分离
   4. 垂直切分
   5. 水平切分

5. SQL注入：所谓SQL注入就是通过把SQL命令插入到web表单递交或页面请求的查询字符串中，最终达到欺骗服务器执行恶意的SQL命令。

   1. 预防：输入校验：对用户的输入进行校验，不要完全相信；不要使用动态化拼装SQL，可以使用SQL参数化查询或者使用参数化的存储过程。

6. 分页实现：使用mysql数据库中的limit来实现。

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

   1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；
   2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
   3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
   4. 如果业务处理不好可以用分布式事务锁或者使用乐观锁。

8. MySQL相关面试题：https://blog.csdn.net/ThinkWon/article/details/104778621

十、选择优化的数据类型

• 更小的通常更好，应该尽量使用可以正确存储数据的最小数据类型，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少；
• 简单就好：简单数据烈性的操作通常需要更少的CPU周期；
• 尽量避免NULL：可谓NULL的列使得索引、索引统计和值比较都更复杂；

1、 整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别使用8、16、24、32、64位存储空间（1字节、2字节、3字节、4字节、8字节），可以存储的值的范围从-2^(N-1)到2(N-1)-1，其中N为存储空间的位数；

3、字符串类型

VARCHAR和CHAR是两种最主要的字符串类型：

• VARCHAR是变长的，需要1或2个额外字节记录字符串的长度：如果列的最大长度小于等于225字节，则使用1个字节表示，否则使用2个字节；VARCHAR(10)需要11个字节的存储空间，VARCHAR(1000)则需要1002个字节，因为需要2个额外字节存储长度信息。VARCHAR节省了空间，所以对性能也有帮助
• CHAR()是定长字符串，MYSQL总是根据定义的字符串长度分配足够的空间，同时，MYSQL还会删除所有的末尾空格。所以CHAR适合存储很短的字符串，或者所有值都接近同一个长度；例如CHAR非常适合存储密码的MD5值。
• 例如：属性A的类型是char(10)，如果为此属性存入字符串"avi",那么该字符串后会追加7个空格来使其达到10个字符的串长度。反之，如果属性B的类型是varchar(10)，存入字符串"avi",则不会增加空格。
• BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储；

使用枚举(ENUM)代替字符串类型：枚举列可以把一些不重复的字符串存储成一个预定义的集合。

4、日期和时间类型

• DATETIME:能保大范围的值，从1001到9999年，精确度为秒，保存格式为YYYYMMDDHHMMSS，与时区无关，使用8个字节；
• TIMESTAMP：保存从1970年1月1日以来的秒数，和UNIX时间戳相同，只使用了4个字节的存储空间，所以只能表示从1970年到2038年。同时FROM_UNIXTIME()函数把Unix时间戳转换成日期，UNIX_TIMESTAMP()函数把日期转换为Unix时间戳，同时还可以自动更新

4.2 schema设计中的陷阱

• 太多的列
• 太多的关联
• 全能的枚举
• 变相的枚举

4.3 范式和反范式

数据库的范式

1. 第一范式(1NF):属于第一范式关系的所有属性都不可再分，即数据项不可分。第一范式强调数据表的原子性，是其他范式的基础， 如下图所示：
   现实生活中仅用第一范式来规范表格是远远不够的，依然会存在数据冗余过大、删除异常、插入异常、修改异常的问题，此时就需要对数据进行规范化，一个低一级的关系模式通过模式分解可以转化为若干个高一级范式的关系模式的集合，这个过程就叫做规范化。

2. 第二范式(2NF):是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或行必须可以被唯一的区分。即表中每一列都与主键完全依赖，而不仅仅是依赖于主键的一部分。
   如上图，另一个理解就是不同的课程名称会有相同的学生去选择，这样的话学生列就会重复，造成数据冗余，所以可以拆开来，如下图：就满足了第二范式。

3. 第三范式(3NF)：数据库中的每一列和主键直接相关，而不是间接相关；也就是属性不能依赖于主属性：
   

4. 三大范式只是一般设计数据库的基本思想，可以建立冗余较小、结构合理的数据库，在实际的设计过程中，需求>性能>表结构。

范式化设计的缺点就是需要关联，因为表的数量增加了，各个表之间都需进行至少一次关联，所以不仅代价昂贵，也可能使一些索引策略无效；反范式化的设计时所有的数据都在一张表中，所以可以很好的避免关联，但是查询会比较慢。实际使用中需要将范式化和反范式进行混合使用。