Mysql基础

MySQL 的架构

• 第一层：对客户端的连接处理、安全认证、授权等，每个客户端连接都会在服务端拥有一个线程，每个连接发起的查询都会在对应的单独线程中执行。
• 第二层：MySQL的核心服务功能层，包括查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树
• 第三层：存储引擎，负责数据的存储和提取，MySQL服务器通过API与存储引擎通信，屏蔽了各种引擎之间的差异，常见的存储引擎有：InnoDB、MyISAM。

InnoDB和MyISAM的区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

• 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
• 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
• 是否支持外键： MyISAM不支持，而InnoDB支持。
• 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

数据库设计范式

• 第一范式：1NF 原子性 数据不可再分
• 第二范式：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范（2NF）必须先满足第一范式（1NF）。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。
• 第三范式：必须先满足第二范式（2NF）。，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

为什么 InnoDB 使用 B+ 树

• B+树的磁盘读写代价更低，因为B+树内部结点没有指向关键字具体信息的指针，内部结点相对B树小。
• B+树的查询更加稳定，因为非终端结点并不是指向文件内容的结点，仅仅是作为叶子结点的关键字索引，因此所有的关键字查询都会走一条从根节点到叶子结点的路径。即所有关键字查询的长度是一样的，查询效率稳定。

B+树和B-树的区别

• 相同点:
  1. 根节点至少一个元素
  2. 非根节点元素范围：[m/2]-1 <= k <= m-1
• 不同点:
  1. B+树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点。
  2. 内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
  3. 每个叶子结点都存有相邻叶子结点的指针，叶子结点内部按关键字的大小自小而大顺序连接。
  4. 父节点存有右孩子的第一个元素的索引。

事务

事务：事务是逻辑上的一组操作，要么都执行，要么都不执行。 事务的四大特性：

• 原子性(Atomicity): 事务中所有操作是不可再分割的原子单元。事务中所有操作要么都执行成功,要么都执行失败。
• 一致性(Consistency): 事务执行后,数据库状态与其他业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账户余额之和应该保持不变
• 隔离性(Isolation): 隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会互相干扰。
• 持久性(Durability): 一旦事务提交成功，事务中所有的数据操作都必须被持久化保存到数据库中，即使提交事务后，数据库崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

• 读未提交（READ UNCOMMITTED）使用查询语句不会加锁，可能会读到未提交的行（Dirty Read）
• 读提交 （READ COMMITTED）只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，所以再多次使用查询语句时，可能得到不同的结果（Non-Repeatable Read）
• 可重复读 （REPEATABLE READ）多次读取同一范围的数据会返回第一次查询的快照，不会返回不同的数据行，但是可能发生幻读（Phantom Read）
• 串行化（SERIALIZABLE）InnoDB 隐式地将全部的查询语句加上共享锁，解决了幻读的问题

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

可重复读是 MySQL 的默认级别。

不同事务隔离级别的下的问题 原文来自

• 脏读:在一个事务中，读取了其他事务未提交的数据。 当事务的隔离级别为 READ UNCOMMITED 时，我们在 SESSION 2 中插入的未提交数据在 SESSION 1 中是可以访问的。
• 不可重复读:在一个事务中，同一行记录被访问了两次却得到了不同的结果。 当事务的隔离级别为 READ COMMITED 时，虽然解决了脏读的问题，但是如果在 SESSION 1 先查询了一行数据，在这之后 SESSION 2 中修改了同一行数据并且提交了修改，在这时，如果 SESSION 1 中再次使用相同的查询语句，就会发现两次查询的结果不一样。
• 幻读:在一个事务中，同一个范围内的记录被读取时，其他事务向这个范围添加了新的记录。 重新开启了两个会话 SESSION 1 和 SESSION 2，在 SESSION 1 中我们查询全表的信息，没有得到任何记录；在 SESSION 2 中向表中插入一条数据并提交；由于 REPEATABLE READ 的原因，再次查询全表的数据时，我们获得到的仍然是空集，但是在向表中插入同样的数据却出现了错误。 这种现象在数据库中就被称作幻读，虽然我们使用查询语句得到了一个空的集合，但是插入数据时却得到了错误，好像之前的查询是幻觉一样。 在标准的事务隔离级别中，幻读是由更高的隔离级别 SERIALIZABLE 解决的，但是它也可以通过 MySQL 提供的 Next-Key 锁解决：

乐观锁和悲观锁

• 乐观锁: 乐观锁是一种思想，它其实并不是一种真正的『锁』，它会先尝试对资源进行修改，在写回时判断资源是否进行了改变，如果没有发生改变就会写回，否则就会进行重试，在整个的执行过程中其实都没有对数据库进行加锁

• 悲观锁 悲观锁就是一种真正的锁了，它会在获取资源前对资源进行加锁，确保同一时刻只有有限的线程能够访问该资源，其他想要尝试获取资源的操作都会进入等待状态，直到该线程完成了对资源的操作并且释放了锁后，其他线程才能重新操作资源

锁的种类

• 共享锁（读锁）：允许事务对一条行数据进行读取
• 互斥锁（写锁）：允许事务对一条行数据进行删除或更新 而它们的名字也暗示着各自的另外一个特性，共享锁之间是兼容的，而互斥锁与其他任意锁都不兼容 稍微对它们的使用进行思考就能想明白它们为什么要这么设计，因为共享锁代表了读操作、互斥锁代表了写操作，所以我们可以在数据库中并行读，但是只能串行写，只有这样才能保证不会发生线程竞争，实现线程安全。

多版本并发控制 Multi-Version Concurrency Control

MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

• 隐式字段

  1. DB_TRX_ID:6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID
  2. DB_ROLL_PTR:7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
  3. DB_ROW_ID:6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

  Id	Name	DB_TRX_ID	DB_ROW_ID	DB_ROLL_PTR
  1	Looper	1	1	0x1235656

  DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID,而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

• undo-log

  1. insert undo-log:代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
  2. update undo-log:事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除

• Read View Read View是session进行快照读操作的时候生产的读视图(Read View)，在该session执行的快照读的那一刻，会生成数据库系统当前的一个快照。 Read View主要是用来做可见性判断的, 即当执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前session能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo-log里面的某个版本的数据。执行查询时【所有】未提交的事务Id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id:未提交、已提交）组成

• 可见性算法 依次比较记录版本链中每一条记录，符合规则就返回该条数据，不符合根据回滚指针取链路中的下一条数据； trx_id为记录版本链里的DB_TRX_ID

  1. 如果trx_id < min_id,表示这个版本是已经提交的事务生成的，这个数据肯定是可见的；
  2. 如果trx_id > max_id ,表示这个版本是由将来的启动的事务生成的，是肯定不可见的；
  3. 如果min_id <= trx_id <= max_id ，那就包括两种情况 a、若trx_id在未提交的事务Id数组里，表示这个版本是由未提交的事务产生的，不可见，当前自己的事务是可见的； b、若trx_id不在未提交的事务Id数组里，表示这个版本是已经提交的事务生成的，可见

Mysql 数据类型

• 整型:tinyint,smallint,mediumint,int,bigint
• 浮点型:flat, double
• 日期类型:date,time,year,datetime,timestamp
• 字符类型:char,varchar,text

Char和Varchar的区别

• char是固定长度字符串，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844
• char实际占用的字节数即存储的字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3
• MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会

MySql索引

• 主键索引（PRIMARY KEY）:也简称主键。它可以提高查询效率，并提供唯一性约束。
• 常规索引(INDEX或KEY)
  1. 单列索引
  2. 组合索引，组合索引最左前缀原则
• 唯一索引（UNIQUE KEY）

聚集索引与非聚集索引

• 聚集索引:聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。 优点:聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。 缺点:依赖有序的数据，更新代价大
• 非聚集索引:数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

Mysql 优化技巧

• 建表
  1. 建表时，字段尽量不要使用null，需NULL时，需设置字段默认值，默认值不为NULL
  2. 选择合适的字段类型，定长char,可变长度varchar(n)n 尽可能小，浮点数用decimal代替double和float
  3. 索引：建立合适的索引，经常查询的列，ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。
  4. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了,典型的就是IP地址转成Int
• 查询
  1. 禁止 * 查询，会造成回表
  2. 应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
  3. 避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
  4. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
  5. select id from table where name like ‘%abc%’，前置百分号会导致全表扫描
  6. 如果在where子句中使用表达式，参数，函数，也会导致全表扫描
  7. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
  8. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
  9. 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)，用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
  10. 索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过5个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  11. where子句一般将选择性最高的列放到索引最前列
  12. 关联查询，一般以小表驱动大表
  13. 禁用or 语句，这会让mysql放弃索引，导致全表扫描，建议用关联查询代替
  14. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

主从复制

1. 在主库上把数据更改记录到二进制日志（Binary Log）中（这些记录被称为二进制日志事件）。
2. 备库将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 备库读取中继日志中的事件，将其重放到备库数据之上。

EXPLAIN

• id:这一列总是包含一个编号，标识SELECT所属的行。
• select_type:这一列显示了对应行是简单还是复杂SELECT
• table:这一列显示了对应行正在访问哪个表
• type:访问类型
  1. ALL 代表全表扫描
  2. index 这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行，开销将会非常大。
  3. range 范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是带有BETWEEN或在WHERE子句里带有>的查询。
  4. ref 这是一种索引访问（有时也叫做索引查找），它返回所有匹配某个单个值的行。
  5. eq_ref 使用这种索引查找，MySQL知道最多只返回一条符合条件的记录。
  6. const, system 当MySQL能对查询的某部分进行优化并将其转换成一个常量时，它就会使用这些访问类型。
  7. NULL 这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
• possibIe_keys:这一列显示了查询可以使用哪些索引，这是基于查询访问的列和使用的比较操作符来判断的。
• key:这一列显示了MySQL 决定采用哪个索引来优化对该表的访问。如果该索引没有出现在possible_keys列中，那么MySQL选用它是出于另外的原因——例如，它可能选择了一个覆盖索引，哪怕没有WHERE子句。
• key_len:该列显示了MySQL在索引里使用的字节数
• ref:这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。
• rows:这一列是MySQL估计为了找到所需的行而要读取的行数。
• fiItered:它显示的是针对表里符合某个条件（WHERE子句或联接条件）的记录数的百分比所做的一个悲观估算。
• Extra:这一列包含的是不适合在其他列显示的额外信息。
  1. Using index:此值表示MySQL将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
  2. Using where:这意味着MySQL服务器将在存储引擎检索行后再进行过滤。许多WHERE条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带WHERE子句的查询都会显示Using where。有时Using where的出现就是一个暗示：查询可受益于不同的索引。
  3. Using temporary:这意味着MySQL在对查询结果排序时会使用一个临时表。
  4. Using filesort:“这意味着MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。”