MySQL的详细讲解-更新次数2

目录

• Mysql的架构
• Schema与数据优化
  • 各种数据类型的特点
  • 范式和反范式
  • MySQL 使用原则和设计规范
• 创建高性能的索引
  • 索引的类型
  • 索引的优缺点
  • 高性能索引的策略
  • 查询性能优化

Mysql的架构

第二层的架构是所有的跨引擎的功能实现的地方，例如：存储，触发器，视图等。

第三层半酣了 存储引擎，负责MySQL中的数据存储和提取。

连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。当客户端(应用)连接MySQL服务器时，服务器需要对其进行认证。

优化与执行

解析查询，内部会创建数据结构（解析树），然后对其优化，选择合适的索引，可以请求优化器的解释，优化器不关心用的什么引擎，但是存储的引擎对优化查询是有影响的，对于select语句，解析查询的时候，先查缓存，命中了就返回缓存的结果集。

并发控制

俩个层面的并发控制：服务器层与存储引擎层。

简单的处理就是上锁，例如读写锁。

锁力度

一种提高共享资源的并发性的方式是让锁定对象更具有选择性。但是问题是加锁也是需要消耗资源的，所以需要把握平衡。MySQL提供了多种选择：有着自己的锁策略和锁粒度。

表锁

表锁是MySQL中最基本的锁策略，并且是开销最小的策略。它会锁定整张表。

行级锁

行级锁可以最大程度地支持并发处理(同时也带来了最大的锁开销)。行级锁只在存储引擎层实现，而MySQL服务器层没有实现，服务器层完全不了解存储引擎中的锁实现。

事务

MySQL实战45讲（01--05）-笔记-隔离性

ACID

这里讨论一下隔离性，实际比想象的要复杂。在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

读未提交	一个事务还没提交时，它做的变更就能被别的事务看到	直接返回记录上的最新值，没有视图概念
读提交	一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的	这个视图是在每个 SQL 语句开始执行的时候创建的
可重复读	未提交变更对其他事务是不可见的	这个视图是在事务启动时创建的，整个事务存在期间都用这个视图
串行化	顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。	直接用加锁的方式来避免并行访问

Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。
本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

 

Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。
它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。
这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），
因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

 

Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。
简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，
当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

 

Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

死锁

死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

事务日志

MySQL实战45讲（01--05）-笔记-重要的日志模块

多版本并发控制

你可将MVCC看成行级别锁的一种妥协，它在许多情况下避免了使用锁，同时可以提供更小的开销。根据实现的不同，它可以允许非阻塞式读，在写操作进行时只锁定必要的记录。

MVCC会保存某个时间点上的数据快照。这意味着事务可以看到一个一致的数据视图，不管他们需要跑多久。这同时也意味着不同的事务在同一个时间点看到的同一个表的数据可能是不同的。

Innodb

Schema与数据优化

选择优化的数据类型，有几个简单的规则：

1. 更小通常是最好的：越小，占用cpu、磁盘和内存越少。
2. 尽量简单：简单的数据类型可以减少cpu开销。
3. 尽量避免null：包含null值的列在sql语句中通常很难优化。

各种数据类型的特点

整数类型

数据类型	占用存储空间	范围
tinyint	8位	-128~127
smallint	16位	-32 768，32 767
mediumint	24位	-8388608~8388607
int	32位	-2147483648~2147483647
bigint	64位	±9.22*10的18次方

注意：
1、整数类型有个 UNSIGED 属性，表示不允许你负值，这大致可以让数据类型的上限提高一倍。
2、很多情况下，对于 int（1）和 int（20），只是限制了显示字符的个数，对于存储和计算是相同的。

实数类型

float(M,N)：4个字节存储，8位精度

double(M,N)：8个字节存储，16位精度（ M：总长度 N：小数位数 ）

decimal不仅可以存带有小数点的数据，也可以存储比 bigint 还大的数据

decimal(M,D)
M: 数字的最大长度 (5.0 以后，最大上限位 65 ； 之前是 254 位)
D: 小数位数 (D < M , D < 30)

注意：
cpu不支持对decimal的直接计算，所以decimal只是一种存储格式，在计算的时候，还是会转换为double类型

字符串类型

1. varchar

   特点:
   1、存储可变长字符串
   2、设置： ROW_FORMAT = FIXED ，会变成每一行都定长存储，浪费空间
   3、需要额外的 1 到 2 个字节存储字符串长度
   
   适用的列：很少去更新的那种
   

2. char

   适合的列 ：
   1、很短的字符串
   2、所有值长度都近似
   
   InnoDB：建议使用 varchar类型
   char 和 varchar 保存和检索方式是不同，最大长度和是否尾部空格被保留等方面也不同。
   

3. binary 和 varbinvar：存储二进制字符串 （binary 使用 \0 来填充）

4. text ：存储数据量非常大的字符串

   和varchar的对比：
   1、不能有默认值
   2、必须创建前缀索引
   3、不能像 varcha(n) 一样设置n
   

5. blob ：存储数据量非常大的二进制字符串

   和text对比：
   1、存储方式不同：text 存储文本，区分大小写，blob 二级制方式存储，不区分大小写
   2、blob 只能整体读出
   3、text 可以指定字符集，blob 不能
   

日期时间类型

datetime

1、范围：1001 ~ 9999，精度位秒，与时区无关，8字节存储
2、格式：YYYYMMDDHHMMSS

timestamp

1、范围： 1970.01.01 ~ 2038.12.31
2、与时区有关
3、此类型默认位 not null 类型，如果你插入的时候，没有填写时间，默认位当前时间

综合对比：
1、通常情况下，使用 timestamp ，空间效率更高
2、但是 timestamp 的行为比较复杂，你在选用这个数据类型的时候，需要验证以下它的行为是否符合你的需要

位数据类型

bit

1. 用来存储一个或者多个 true / false
2. 最大长度 64 位
3. mysql，它被作为字符串类型存储

范式和反范式

范式是基础规范，反范式是针对性设计。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，还又称完美范式）。

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

范式优缺点

• 避免数据冗余，减少维护数据完整性的麻烦。
• 减少数据库的空间。
• 数据变更速度快。

同时，也有如下缺点：

• 按照范式的规范设计的表，等级越高的范式设计出来的表数量越多。
• 获取数据时，表关联过多，性能较差。

表的数量越多，查询所需要的时间越多。也就是说所用的范式越高，对数据操作的性能越低。

反范式

范式是普适的规则，满足大多数的业务场景的需求。对于一些特殊的业务场景，范式设计的表，无法满足性能的需求。此时，就需要根据业务场景，在范式的基础之上进行灵活设计，也就是反范式设计。

反范式设计主要从三方面考虑：

• 业务场景。
• 响应时间。
• 字段冗余。

反范式设计就是用空间来换取时间，提高业务场景的响应时间，减少多表关联。主要的优点如下。

• 允许适当的数据冗余，业务场景中需要的数据几乎都可以在一张表上显示，避免关联。
• 可以设计有效的索引。

MySQL 使用原则和设计规范

讲完范式，接下来我们看看 MySQL 使用中的一些使用原则和设计规范。

MySQL 虽然具有很多特性并提供了很多功能，但是有些特性会严重影响它的性能，比如，在数据库里进行计算，写大事务、大 SQL、存储大字段等。

想要发挥 MySQL 的最佳性能，需要遵循 3 个基本使用原则。

1. 首先是需要让 MySQL 回归存储的基本职能：MySQL 数据库只用于数据的存储，不进行数据的复杂计算，不承载业务逻辑，确保存储和计算分离；
2. 其次是查询数据时，尽量单表查询，减少跨库查询和多表关联；
3. 还有就是要杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。

• 大事务，运行步骤较多，涉及的表和字段较多，容易造成资源的争抢，甚至形成死锁。一旦事务回滚，会导致资源占用时间过长。
• 大 SQL，复杂的 SQL 意味着过多的表的关联，MySQL 数据库处理关联超过 3 张表以上的 SQL 时，占用资源多，性能低下。
• 大批量，意味着多条 SQL 一次性执行完成，必须确保进行充分的测试，并且在业务低峰时段或者非业务时段执行。
• 大字段，blob、text 等大字段，尽量少用。必须要用时，尽量与主业务表分离，减少对这类字段的检索和更新。

创建高性能的索引

索引(在MySQL中也叫做“键(key)") 是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一一个真正“最优”的索引经常需要重写查询。

索引的类型

B树

索引的底层结构：数据结构-B树

哈希索引

哈希索引基于哈希表实现，对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，通过哈希码能以 O(1) 时间进行查找，但是无法用于排序与分组，并且只支持精确查找，无法用于部分查找和范围查找。

在MySQL 中，只有Memory引擎显式支持哈希索引。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

空间数据索引（R-Tree）

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

索引的优缺点

优点

• 索引大大减少了服务器需要扫描的数据量
• 通过索引可以帮助服务器避免排序和临时表，降低CPU消耗
• 可以将随机IO变为顺序IO，加快IO速度

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

高性能索引的策略

独立的列

如果MySQL查询的列不是独立的，就不会使用索引，“独立的列”指的是，索引列不能是表达式的一部分，也不能是函数的参数。

 mysql> SELECT id, name FROM t_user WHERE id + 1 = 5; 

前缀索引和索引的选择性

有时候需要索引很长的字符列，这会让索引变得大且慢，比如对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。

多列索引

很多人对于多列索引的理解都不够，一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能，所以引入“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并策略有时候是一种优化的结果，但是实际上表明表上的索引建立的很槽糕。

合适的索引列顺序

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

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

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

InnoDB 通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引来代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

聚集的数据的优缺点

优点：

• 可以把相关的数据保存在一起
  • 例如实现电子邮箱时，根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据就可以获取某个用户的全部邮件，如果没有聚簇索引，获取每封邮件都会导致一次磁盘IO
• 数据访问更快，聚簇索引将索引和数据保存在同一个B+树中，能更快的查找数据
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

• 聚簇数据最大限度提高了IO密集型应用的性能，但是如果数据全部放在内存中，则访问的顺序就不重要，聚簇索引也没有优势
• 插入速度严重依赖于插入顺序，如果不是按照主键的顺序加载数据，那么加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表，所以建议选择自增的主键
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

非聚簇索引

将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

覆盖索引

索引覆盖所有需要查询的字段的值

好处：

• 索引条目远小于数据行大小，所以可以几大减少数据访问量以及更容易全部放到内存
• 索引是按照列值顺序存储，对于IO密级型的范围查询会比随机从磁盘读取每一行数据的IO要少得多
• 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）
• InnoDB 的二级索引（非聚簇索引）在叶子结点保存了行的主键值，如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

查询性能优化

查询的时候要在不同的地方花费时间，包括网络，CPU计算，删生成统计信息和执行任务，执行并且返回客户端。优化查询就是减少或者消除这些不必要的操作。

慢查询基础：优化数据的访问

分析步骤：

1. 确认是否检索大量超过需要的数据
2. 服务层是否在分析大量超过需要的行

本文还有很多不足的地方，后面会继续加油。