理解MySQL数据库的原理_mysql 数据表冷热表 csdn

一、数据库

1、思考：为什么要使用数据库

数据持久化：将内存的数据保存到硬盘上

2、DBMS的选择：

目前使用最多的 DBMS 分别是 Oracle、 MySQL 和 SQL Server。这些都是关系型数据库（RDBMS）。

Oracle 更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求。

MySQL 由于其体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码这一特点，使得很多互联网公司、中小型网站选择了MySQL作为网站数据库。

3、关系型和非关系型数据库

• 关系型数据库（RDBMS）：以 行(row) 和 列(column) 的形式存储数据，以便于用户理解。这一系列的行和列被称为 表(table) ，一组表组成了一个库(database)

• 非关系型数据库：可看成传统关系型数据库的功能阉割版本 ，基于键值对存储数据，不需要经过SQL层的解析， 性能非常高 。同时，通过减少不常用的功能，进一步提高性能

3.1 非关系型数据库的分类

• 键值型数据库：作为 内存缓存 。 Redis 是最流行的键值型数据库。
• 文档型数据库：一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的“值”。MongoDB
• 搜索引擎数据库：核心原理是“倒排索引”。Elasticsearch
• 列式数据库：列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的I/O，适合于分布式文件系统，不足在于功能相对有限。典型产品：HBase等。
• 图形数据库：描述网络关系

4、关系型数据库设计规则

关系型数据库的典型数据结构就是 数据表 ，这些数据表的组成都是结构化的（Structured）。
将数据放到表中，表再放到库中。
一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计。

二、SQL

1、语言分类

SQL语言在功能上主要分为如下3大类：

• DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
  主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

• DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。
  SELECT是SQL语言的基础，最为重要。

• DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
  主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

2、基本规则

• SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
• 每条命令以 ; 或 \g 或 \G 结束
• 关键字不能被缩写也不能分行

关于标点符号

• 必须保证所有的()、单引号、双引号是成对结束的
• 必须使用英文状态下的半角输入方式
• 字符串型和日期时间类型的数据可以使用单引号（’ '）表示
• 列的别名，尽量使用双引号（" "），而且不建议省略as

规范：
1、MySQL 在 Linux 环境下是大小写敏感的

• 数据库名、表名、表的别名、变量名是严格区分大小写的
• 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。

2、推荐采用统一的书写规范：

• 数据库名、表名、表别名、字段名、字段别名等都小写
• SQL 关键字、函数名、绑定变量等都大写

三、存储引擎

作用：为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎（表处理器）的功能

在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端，

存储引擎的功能就是接收上层传下来的指令，对表中数据进行提取或写入操作

1、InnoDB:事务存储引擎

优势：

• 更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求。
• 支持事务，是MySQL默认事务型引擎，用来处理大量短期事务，确保事务的完整提交和回滚
• 自动灾难恢复：与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
• 外键约束：MySQL支持外键的存储引擎只有InnoDB。
• 支持行级锁
• 支持自动增加列AUTO_INCREMENT属性。

应用：

• 除了增加和查询外，还需要更新和删除操作，优先选择InnoDB
• 除非有特别的原因，否则优先考虑InnoDB

2、MyISAM:非事务处理存储引擎

特点：

• 不支持事务、外键、行级锁，崩溃后无法安全恢复
• 访问速度快（直接通过地址找到数据记录，而InnoDB二级索引需要拿到主键再去聚簇索引中查找数据记录）

应用：

• 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
• 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。

对于count(*)，查询效率高（专门存储了这个字段）

四、索引

索引：是帮助MySQL高效获取数据的数据结构（排好序的快速查找数据结构）

思考：为什么使用索引

索引是存储引擎快速找到数据记录的一种数据结构（进行查询操作时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据；不符合则需要全表扫描，一条一条去查找，直到找到符合条件的记录）

1、优点：

• 降低数据库的IO成本（提高检索效率）
• 通过创建唯一索引（唯一约束），保证数据库表中每一行数据的唯一性
• 加速表连接（对有依赖关系的子表和父表联合查询时，可提高查询速度）
• 在使用分组和排序子句进行查询时，可显著减少查询中分组和排序的时间，降低CPU消耗

2、缺点

• 创建索引和维护索引耗费时间
• 占据磁盘空间
• 降低更新表的速度（对表中的数据进行增加、删除和修改时，需要维护索引，这样就降低了数据的维护速度）

3、分类

按物理实现方式，分为聚簇索引和非聚簇索引（又称二级索引、辅助索引）
按功能逻辑分，普通索引、唯一性索引（UNIQUE）、主键索引（特殊的唯一性索引，NOT NULL + UNIQUE）、全文索引
按作用字段个数分，单列索引、联合索引

3.1 聚簇索引

一种数据存储方式（所有的用户记录都存储在叶子节点）
索引即数据，数据即索引
InnoDB存储引擎自动创建聚簇索引
只有搜索条件是主键值时才能发挥作用（因为B+树的顺序都是按照主键值排序的）

特点：
1、使用记录主键值的大小进行记录和页的排序

• 页内的记录按主键大小顺序排成单向链表
• 各个存放用户记录的页按页中用户记录的主键大小顺序排成双向链表
• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

2、B+树的叶子节点存储的是完整的用户记录（包括隐藏列）
3、目录项记录主键+页号

优点：

• 数据访问速度快（索引和数据保存在同一个B+树中）
• 对于主键的排序查找和范围查找速度快
• 节省大量的IO操作（按索引找到目标页，由于数据紧密相连，不用从多个数据块提取数据）

缺点：

• 插入速度依赖于插入顺序（按主键顺序插入是最快的方式，否则会出现页分裂，严重影响性能。一般定义一个自增的ID列为主键）
• 更新主键的代价高（导致被更新的行移动，一般定义主键不可更新）

3.2 非聚簇索引（又称二级索引、辅助索引）

以非主键值列建立的索引（索引条件为非主键值列）

特点：
1、使用记录c2列的大小进行记录和页的排序

• 页内记录按c2列大小顺序排成单向链表
• 各个存放用户记录的页也是根据页中记录的c2的顺序排成一个双向链表
• 存放目录项记录的页分为不同的层次，在同一层次的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表

2、B+树叶子结点存储的不是完整的用户记录，只有c2列+主键这2个列的值
3、目录项记录c2列+页号的值

缺点：找到目标列值，拿到主键后，需要再去聚簇索引查找具体的行数据（称为回表）

聚簇索引和非聚簇索引的区别：

• 聚簇索引叶子节点存储的是完整的数据记录，而非聚簇索引叶子节点存储索引列+主键（即数据位置）
• 一个表只能有一个聚簇索引，可以有多个非聚簇索引
• 使用聚簇索引时，数据查询效率高（无需回表），使用非聚簇索引时，对非搜索列数据增删改效率高（无需改非聚簇索引）

3.3 联合索引

属于非聚簇索引的一种
以多个非主键值列建立的索引（索引条件多个非主键值列）
最左前缀原则

特点：
1、使用记录c2列的大小和记录c3列的大小进行记录和页的排序

• 页内记录按c2列大小顺序（如果c2列相等则按c3列大小顺序）排成单向链表
• 各个存放用户记录的页也是根据页中记录的c2的顺序（如果c2列相等则按c3列大小顺序）排成一个双向链表
• 存放目录项记录的页分为不同的层次，在同一层次的页也是根据页中目录项记录的c2列大小（如果c2列相等则按c3列大小顺序）顺序排成一个双向链表

2、B+树叶子结点存储的不是完整的用户记录，只有c2列+c3列+主键这3个列的值
3、目录项记录c2列+c3列+页号的值

缺点：找到目标列值，拿到主键后，需要再去聚簇索引查找具体的行数据（称为回表）

4、索引创建的过程

5、InnoDB和MyISAM索引的区别

InnoDB索引即数据

MyISAM将索引和数据分开存储（索引仅仅保存数据记录的地址，一定存在回表）

• 将表中记录按照记录插入的顺序（而不是主键大小排序）单独存储在一个文件中（数据文件，并不划分数据页）
• 索引信息另外存储到称为索引文件的另一个文件
• MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值+数据记录地址
• MyISAM可以没有主键（不区分聚簇索引非聚簇索引），而InnoDB必须有主键（如果没有显式指定，MySQL会自动选择非空唯一列作为主键，如果没有这种列，会自动生成隐含字段作为主键，生成聚簇索引）
• 

6、索引的代价

思考：为什么选择B+树作为底层的存储数据结构

1. Hash结构：根据hash算法设计的数据结构（同一地址值hashcode一定相同，hashcode相同的地址值不一定相同，hash碰撞-在同一位置采用链式存储）
   Redis存储的核心就是hash表
   InnoDB虽然不支持hash索引，但提供自适应hash索引功能（对于经常查找的一个数据，当满足一定条件时，会将这个数据页地址存放到hash表中，下次查询可直接找到这个页面所在位置）
   
2. 二叉搜索树-二叉平衡树（AVL）-M树-多路平衡查找树（B-Tree, Balance Tree）
   B树中非叶子节点也存放了用户数据
   B+树是基于B树的改进，也是一种多路搜索树

7、创建和删除索引

1. 在创建表时以隐式的方式创建索引（自动创建）：
   
2. 在创建表时以显式的方式创建索引：
   
3. 在已存在的表中创建索引

4. 删除索引

8、索引的设计原则

限制索引的数目

在实际工作中也需要注意平衡，索引的数目不是越多越好。要限制每张表上的索引数目，建议单张表索引数量不超过6个,原因：

1.每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就人。

2.索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。

3.优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有很多个
索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能

适合创建索引的情况

1. 字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

2. 频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立 组合索引。

总结：

• 如果单独用GROUP BY，就针对对应字段建立索引，若对多个字段进行GROUP BY，可以建立联合索引。
• 如果既有GROUP BY又有ORDER BY可以考虑联合索引，此联合索引中要把GROUP BY 的字段写在前面，ORDER BY的字段写在后面，且8.0中若是降序的话加上DESC后效果更好

4. UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5. DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6. 多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

7. 使用列的类型小的创建索引

这里所说的类型大小指的就是该类型表示的数据范围的大小。
在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUNINT、INT、BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果想要对某个整数列建立索引的话。在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如能使用INT就不要使BIGINT，能使用MEDIUMINT就不要使用INT。这是因为:

• 数据类型越小，在查询时进行的比较操作越快
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O。

8. 使用字符串前缀创建索引

假设字符串很长，那存储一个字符串就需要占用很大的存储空间。在需要为这个字符串列建立索引时，那就意味若在对应的B+树中有这么两个问题:

• B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
• 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。

9. 区分度高(散列性高)的列适合作为索引

列的基数: 指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

可以使用公式 select count(distinct a)/count(*) from t1计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了。

10. 使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

不适合创建索引的情况

1. 在where中使用不到的字段，不要设置索引

WHERE条件(包括GROUP BY、ORDER BY)里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

2. 数据量小的表最好不要使用索引

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

3. 避免对经常更新的表创建过多的索引

第一层含义︰频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义:避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。

4. 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

5. 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

6. 不要定义冗余或重复的索引

有时候有意或者无意的就对同一个列创建了多个索引，比如: index(a,b,c)相当于index(a)、index(a,b)、index(a,b,c)。

7. 数据唯一性差的字段不使用索引

什么情况下设置了索引但无法使用

• 如果条件中有or，即使其中有部分条件带索引也不会使用（要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）
• 对于有索引但不是使用的第一部分，则不会使用索引
• like查询以%开头，不使用索引
• 存在索引列的数据类型隐形转换，则用不上索引（比如列类型为字符串，那一定要在条件中将数据使用引号引用起来）
• where子句中有对索引列的数学运算，用不上索引
• where子句有对索引列使用函数，用不上索引

五、InnoDB数据存储结构

1、页

InnoDB将数据划分为若干页，页的默认大小是16KB，页是磁盘和内存交互的基本单位，是数据库IO操作的最小单位

按类型分：数据页（保存B+树节点，包括叶子节点和非叶子节点）、系统页、Undo、事务数据页

1.1 页的内部结构

2、页的上层结构

思考：为什么划分区和段

六、数据库调优

思考：SQL执行慢如何解决

索引设计的问题
JOIN表过多问题
数据表设计问题
服务器参数配置的问题
需要增加缓存的问题

解决步骤图解：

1、统计SQL查询成本

value为查询的页数

2、定位执行慢的SQL：慢查询日志

开启慢查询

修改执行阈值（暂时的，重启MySQL后失效，永久设置需要改配置文件参数）

执行SQL

使用工具分析慢查询日志：mysqldumpslow

关闭慢查询日志

重置慢查询日志

先删除，再生成空的日志文件

3、查看SQL执行成本

开启 Profile

执行SQL（根据慢查询日志定位到的SQL）

查看信息，定位问题

1、 show profiles
得到SQL编号信息3

2、定位执行慢的操作

executing-分析具体的SQL语句

• 索引设计的问题
• JOIN表过多问题
• 数据表设计问题
  

注意

4、分析SQL语句：explain

定位了查询慢的SQL之后，就可以用EXPLAIN或者DESCRIBE工具做针对性的分析查询语句（查看查询语句的具体执行计划），帮助我们有针对性的提升查询语句的性能

5、SQL优化（索引和查询优化）

数据库调优的维度：

• 索引失效、没有充分利用到索引->建立索引
• 关联查询过多JOIN，设计缺陷或不得已的需求->SQL优化
• 服务器调优及各个参数设置（缓冲、线程数）->调整my.cnf
• 数据过多->分库分表

SQL查询优化分为：

• 物理查询优化：通过索引和表连接方式优化
• 逻辑查询优化：通过SQL等价变换（换一种写法）

5.1 索引优化

MySQL中提高性能最有效的方式是对数据表设计合理的索引（快速定位表中的某条记录，如果没有使用索引，查询语句就会扫描表中所有的记录）

其实，用不用索引，最终是优化器决定的，它是基于cost开销、不是基于规则，也不是基于语义。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

了解索引失效的案例

了解即可

5.2 关联查询优化

5.2.1 测试左外连接和内连接

首先理解驱动表和被驱动表：

• 驱动表就是主表，被驱动表就是从表、非驱动表
• 查看哪个是驱动表、哪个是被驱动表：EXPLAIN执行结果的记录中，上面的是驱动表，下面的是被驱动表

总结：

1. 对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
2. 对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
3. 对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”

5.2.2 join语句原理

join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5 版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中,MySQL通过引入BNLJ算法来优化嵌套执行。从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

1. Simple Nested-Loop Join(简单嵌套循环连接)

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result…以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断

2. Index Nested-Loop Join(索引嵌套循环连接)

Index Nested-Loop Join其优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

3. Block Nested-Loop Join(块嵌套循环连接)

如果存在索引，那么会使用index的方式进行join，如果join时被驱动表的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了IO的次数。为了减少被驱动表的IO次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率

注意:

这里缓存的不只是关联表的列,select后面的列也会缓存起来。

在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

总结：

1. 整体效率比较: INLJ > BNLJ > SNLJ
2. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数目) (小的度量单位指的是表行数*每行大小)
3. 为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
4. 增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)
5. 减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多)
6. 保证被驱动表的J0IN字段已经创建了索引
7. 需要JOIN的字段，数据类型保持绝对一致
8. LEFT JOlN时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。INNER JOIN时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略
9. 能够直接多表关联的尽量直接关联，不用子查询(减少查询的趟数)
10. 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用JOIN来代替子查
11. 衍生表建不了索引
1
2
3
4
5
6
7
8
9
10
11

4. Hash Join

Nested Loop:
对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。

Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小(相对较小）的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行

• 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
• 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高IO的性能。
• 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHERE A.COL1=B.COL2)，这是由Hash的特点决定的

5.3 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

问题：

子查询是MySQL的一项重要的功能，可以通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。

原因:

① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询

② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响

③ 对于返回结果集比较大的子查询。其对查询性能的影响也就越大

解决：

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。
尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

5.4 排序优化

思考：在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢?

解答:

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

• Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
• FileSort 排序则一般在内存中进行排序，占用CPU 较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率校低

filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buiffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSot 排序。

index: 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

优化建议:

① SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER EY子句避免使用 FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，还是要避免，以提高查询效率。
② 尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列;如果不同就使用联合索引
③ 无法使用lndex时，需要对FileSort方式进行调优

经验总结：

INDEX a_b_c(a,b,c)

order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
- 
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
- 
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC 	/* 排序不一致 */
- WHERE g = const ORDER BY b,c 	/*丢失a索引*/
- WHERE a = const ORDER BY c 	/*丢失b索引*/
- WHERE a = const ORDER BY a,d 	/*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

结论：

• 两个索引同时存在，mysql自动选择最优的方案。但是， 随着数据量的变化，选择的索引也会随之变化的 。
• 当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

5.5 GROUP BY优化

• group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
• group by 先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• where效率高于having，能写在where限定的条件就不要写在having中了
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢

5.6 分页查询优化(limit优化)

问题：
EXPLAIN select * from student limit 2000000,10;
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
在大数据量的分页查询时,limit后的起始位置越靠后，耗时越长

解决：
① 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a WHERE t.id = a.id;

② 该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询
EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

总结：
limit优化思路：通过覆盖索引+子查询的方式来优化

5.7 优先考虑覆盖索引

尽量使用覆盖索引（覆盖索引：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少使用selecl *.

覆盖索引： SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据

理解方式一： 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二： 非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

5.8 常见的SQL语句优化

• join表小结果集驱动大结果集，join的表不超过5个（在表之间匹配数据，消耗计算资源，查询性能低）
• like模糊查询时避免使用%%（会导致索引失效）
• limit基数大时，使用between and （limit(10000,5)，会匹配查询出10005条数据）
• 尽量使用批量插入（节省交互）
• 尽量使用更多的where限制，缩小查询范围
• 避免使用select * （节省内存）
• 避免在列上运算（会导致索引失效）
• 避免使用rand函数随机获取记录
• 避免使用null（查询匹配效率低）
• 避免使用count(id)，使用count(*)
• 避免无谓的排序，尽量在索引中排序

6、数据库调优策略

6.1 选择合适的DBMS

6.2 优化表设计

6.3 优化逻辑查询

6.4 优化物理查询

6.5 使用redis或者memcached作为缓存

6.6 库级优化

当单机上的表的设计和SQL优化已经做到极限，在高并发的场景下，考虑多个服务器
库级优化是站在数据库的维度上进行的优化策略，比如控制一个库中的数据表数量。另外，利用外援的方式，通过主从架构优化我们的读写策略，通过对数据库垂直或水平切分，突破单一数据库或数据表的访问限制，提升查询的性能

6.7 优化数据库结构

6.7.1 拆分表：冷热数据分离

6.7.2 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。
把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此提高查询效率
注意：涉及数据的修改时，需要修改两张表的数据（常用的做法是对原表进行修改时，删除中间表重新建立）

6.7.3 增加冗余字段（反范式化）

6.7.4 优化数据结构

优先选择存储需要的最小的数据类型

6.7.5 优化插入记录的速度

针对InnoDB引擎：

6.7.6 使用非空约束

7、MySQL服务器优化

7.1 优化服务器硬件

7.2 优化MySQL参数

七、数据库范式

思考：什么的范式？

一张数据表的表结构所符合的某种设计标准的级别

数据库三范式：
1）、符合1NF的关系中的每个属性都不可再分。
2）、2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。
3）、3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。
4）、BCNF在3NF的基础之上，消除主属性对于码的部分与传递函数依赖。

一些前置概念：
超键：能唯一标识元组的属性集（理解：候选键加任意键的集合）
候选键：可唯一标识元组的属性，可以有多个（又称码）
主键：选取候选键中的任意一个（又称主码）
外键：如果表1中的某个属性不是表1的主键，而是表2的主键，那这个属性就是表1的外键
主属性：包含在任一候选键中的属性
非主属性：不包含在任一候选键中的属性

1、第一范式：

1NF的定义为：符合1NF的关系中的每个属性都不可再分。

问题：依赖关系大，对数据的操作不灵活

2、第二范式：

消除了非主属性对于码的部分函数依赖

理解：
满足数据表中的每一条数据记录，都是可唯一标识的，而且所有非主键字段，都必须完全依赖候选键（码），不能只依赖候选键（码）的一部分

案例：

解决方案：

总结：
1NF告诉我们字段属性需要时原子性的，而2NF告诉我们一张表就是一个独立的对象，一张表只表达一个意思

3、第三范式：

消除了非主属性对于码的传递函数依赖。

理解：第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段（候选键）直接相关，要求表中的所有非主键字段不能依赖于其他非主键字段（所有非主键属性之间不能有依赖关系，必须相互独立）

总结：每个非主键属性依赖于主键，依赖于整个主键，并且除了主键别无他物

4、巴斯范式（BCNF）

BCNF被认为没有新的设计规范加入，只是对第三范式中设计规范要求更强，使得数据库冗余度更小，称为是修正的第三范式或扩充的第三范式

BCNF：一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性
一般来说，一个数据库设计符合3NF或BCNF即可

思考：什么是反范式化？

问题：

使用经验：

当冗余信息有价值或者能大幅度提高查询效率时，才会采取反范式优化

八、数据库事务

1、事务的ACID特性

原子性Atomicity：事务是不可分割的整体（undo log日志）
一致性Consistency：事务执行前后状态合法（undo log日志）
隔离性Isolation：事务之间相互独立（锁机制和MVCC快照读 ）
持久性Durability：事务提交后持久化到数据库（redo log日志（先写日志再修改数据库））

2、事务的状态

最终状态要么是提交的（刷盘）要么是终止的（回滚）

3、事务的使用

4、事务的隔离级别

4.1 数据并发问题

理解：

• 脏写：写无效（在事务B写的过程中进行了写操作）
• 脏读：读到事务B未提交的数据（其他事务最终进行了回滚）
• 不可重复读：两次读取过程中事务B对数据有修改（解决：锁行）
• 幻读：两次读取过程中事务B对数据有新增或删除（解决：锁表）

4.2 SQL中的四种隔离级别

读未提交、读已提交、可重复读、序列化。

事务的隔离性由锁机制实现
事务的原子性、一致性、持久性由事务的redo日志和undo日志来保证
redo log称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性
undo log称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性和一致性

redo的整体流程：

5、MVCC

多版本并发控制:multi-version concurrency control，主要是为了提高数据库的并发性能，不用去竞争锁，针对读已提交和可重复读

同一行数据平时发生读写请求时，会上锁阻塞住，但mvcc用更好的方式去处理读写请求，做到在发生读写请求冲突时不用加锁

这个读是指快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。

5.1 常见的概念

• undo log
• 版本链
• readview：判断回滚到版本链的哪一条记录(判断版本链中哪个版本可用)，在代码里通常为一个对象

• 读已提交:每个select查询时生成一个readview，
  如果2个select语句在一个事务中，当执行到第二个select时刚好update事务提交了，那么2次查询的结果就是不一致的，即没有实现可重复读
• 可重复读:每个事务生成一个readview，
  一个事务中无论有几个select语句，都是共用一个readview，从而实现可重复读。

m_ids：没有commit的事务列表
min_trx_id:m_ids中最小的事务id
max_trx_id:系统中即将分配的事务id
creator_trx_id:生成readview的事务的事务id

判断逻辑：

可以访问：trx_id == creator_trx_id 在版本链内
可以访问：trx_id < min_trx_id 已经提交的事务
不可访问：trx_id > max_trx_id 超出版本链
min_trx_id <= trx_id <= max_trx_id:如果trx_id在m_ids中（未提交），不可访问，反之可以访问（已提交）