MySQL架构

逻辑架构图和执行流程：

mysql逻辑架构

并发控制

  • 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
  • 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

事务

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

隔离级别

READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。

READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。

REPEATABLE-READ：可重复读，mysql 默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。

SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

补充：

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据数据发生了变化。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

多版本并发控制(MVVC)

可以认为MVVC是行级锁的一个变种，innoDB采用了乐观锁的策略，在每行记录后保存两个隐藏列来实现，这两个列保存了行的版本号信息，每开启一个新事务，版本号自动更新，事务开始时刻的版本号作为事务的版本号。用来和查询到的记录所带的版本号进行比较来判断。

存储引擎

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

Myisam 和 InnoDB 的区别

  • 是否支持行级锁 : MyISAM 只有表级锁 (table-level locking)，而 InnoDB 支持行级锁(row-level locking) 和表级锁, 默认为行级锁，适合高并发操作。
  • 是否支持外键： MyISAM 不支持，而 InnoDB 支持
  • 是否支持事务：MyISAM 不支持，而 InnoDB 支持
  • 缓存：MyISAM 只缓存索引，InnoDB 缓存索引和真实数据，所以对内存要求高
  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

数据库设计优化

字符串

**char 和 varchar **

char(n) ：固定长度类型，比如订阅 char(10)，当你输入 “abc” 三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

使用场景：UTF-8这种复杂的字符集。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。 金额数据在程序中选用 BigDecimal 类，数据库中 DECIMAL

时间和日期

MySQL 提供了两种相似的日期时间类型：DATETIME 和 TIMESTAMP。

1. DATETIME

能够保存从 1000 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

2. TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

数据库的三范式

  • 第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
  • 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
  • 第三范式：任何非主属性不依赖于其它非主属性。

如何设计一个关系型数据库？

首先划分为两大部分，一个存储部分，类似文件系统将数据持久化到硬盘上， 另一个是程序实例部分，创建模块： 存储管理，缓存机制，SQL 解析，日志管理，权限划分，容灾机制 **，索引管理，锁模块 **。

索引

B 树和 B + 树的概念和区别

1）B - 树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B + 树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。 2）在 B - 树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B + 树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B - 树的性能好像要比 B + 树好，而在实际应用中却是 B + 树的性能要好些。

原因：

  • 因为 B + 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B - 树多，树高比 B - 树小，这样带来的好处是减少磁盘访问次数。尽管 B + 树找到一个记录所需的比较次数要比 B - 树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B + 树的性能可能还会好些。
  • B + 树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等）。
  • B + 树的查询效率更加稳定，每次查询的效率一样。

哈希索引

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询一定要从索引的最左前列开始并且不跳过索引中的列。如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。
在创建索引的字段中第一个就是最左，每个左边的字段都是后面一个字段的一整个树，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。即要按照顺序命中索引。

为什么使用索引能提高效率

  1. 数据索引的存储是有序的
  2. 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
  3. 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)
  4. 避免全表扫描

为什么要使用索引？

  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
  • 帮助服务器避免排序和临时表
  • 将随机IO变为顺序IO

索引的优点

  • 大大减少了服务器需要扫描的数据行数。
  • 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
  • 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

什么情况下应不建或少建索引

  • 非常小的表
  • 非常大的表，建议使用分区技术
  • 经常插入、删除、修改的表
  • 数据重复且分布平均的表字段

索引优化

独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。

多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行

聚簇索引具有唯一性， 聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引 。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

聚簇索引和非聚簇索引类似查字典时直接根据经验查字的大概位置和先去查偏旁部首再去翻页查询类似。

MyISAM没有聚簇索引，都是二级索引。

优点

  • 把相关数据保存在一起，减少了磁盘I/O。
  • 数据访问更快。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称
之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢
覆盖索引就是把要查询出的列和索引是对应的，不做回表操作。

索引的使用经验

  • 在经常需要搜索的列上，可以加快搜索的速度；
  • 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
  • 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
  • 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
  • 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  • 避免 where 子句中对宇段施加函数，这会造成无法命中索引。
  • 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
  • 将打算加索引的列设置为 NOT NULL ，否则将导致引擎放弃使用索引而进行全表扫描
  • 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗
  • 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

性能分析

Explain

id

如果没有子查询或者联合就只会显示1，如果有就按照顺序编号，对应其在 原始语句的位置。

type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

简易版：
system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

三个较差情况：
1.range：
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。和index的消耗差不多。

2.index：
依然是全表扫描，只不过是按照索引的次序而不是行，可以避免排序，出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组 ，一般是where后面没有优化索引

3.all（最差）：
Full Table Scan，将遍历全表以找到匹配的行

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引，越大越好

Extra

包含不适合在其他列中显示但十分重要的额外信息，主要用来检测
order by，group by或者关联查询是否使用了索引。
有以下几种情况：
1.Using filesort 2.Using temporary 3.using join buffer都是很差的是使用方式。要建立索引。
4.impossible where：不可能出现的情况，一般为 逻辑出现错误。
5.USING index：
利用索引进行了排序或分组，
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

查询优化

单表查询优化
常见索引失效：
1 . 系统中经常出现的sql语句如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = ‘abcd’

解决：
where后筛选字段有多少就建立多少索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)

1.最佳左前缀法则

2.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描,在where后使用计算、函数、(自动or手动)类型转换都会使索引失效

3.存储引擎不能使用索引中范围条件右边的列，即在建立索引时，范围查询要放到最后

4.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

5.is not null 也无法使用索引,但是is null是可以使用索引的

6.like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

7.字符串不加单引号索引失效，javabean类型和mysql字段的字符类型要一致，不然所以会失效。

关联查询优化

1、保证被驱动表的join字段已经被索引

2、left join 时，选择小表作为驱动表，大表作为被驱动表。

3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

4、子查询尽量不要放在被驱动表，有可能使用不到索引。

5、能够直接多表关联的尽量直接关联，不用子查询。

子查询优化：

尽量不要使用not in 或者 not exists

用left outer join on xxx is null 替代

排序分组优化：

普通order by不能使用索引

无过滤 不索引，可加上过滤条件，使用索引

顺序错，必排序排序的顺序要对应

方向反 必排序 desc asc要一样

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

最后使用索引的手段：覆盖索引 即不要使用select *