字节面试杂谈——MySQL、Redis_redis数据库使用的memery数据引擎嘛

目录

一、架构：Server层，引擎层

二、引擎：InnoDB，MyISAM

三、聚簇索引和非聚簇索引

四、事务及其四大特性、MySQL中事务提交的过程

五、并发事务带来的问题

六、事务的隔离级别

七、InnoDB存储引擎的锁算法、MySQL什么时候行锁升级为表锁

八、大表优化

九、分库分表之后id主键的处理方式

十、一条SQL语句的执行过程

一 MySQL 基础架构分析

1.1 MySQL 基本架构概览

1.2 Server 层基本组件介绍

二 语句分析

2.1 查询语句

2.2 更新语句

三 总结

十一、MySQL高性能优化规范、书写高质量SQL的建议

数据库命令规范

数据库基本设计规范

数据库字段设计规范

索引设计规范

数据库 SQL 开发规范

数据库操作行为规范

使用数据库的30条建议

十二、一条SQL语句执行得很慢的原因

十三、MySQL日志：redo log、binary log、undo log

1、简介1

2、简介2

3、简介3

4、简介4

十四、数据库范式（三范式）

十五、char，varchar 与 varchar(10)，varchar(20)

十六、建立索引的情况，索引底层的数据结构

十七、B树，B+树，哈希表

十八、覆盖索引

十九、索引分类

二十、最左前缀

二十一、查看索引是否被用到，什么情况下索引会失效

二十二、主从复制

二十三、主从同步延迟的原因

二十四、数据库读写分离

二十五、MVCC

二十六、当前读、快照读

（1）当前读

（2）快照读

（3） 普通读（快照读）

（4）当前读

二十七、连接

1、介绍1

2、介绍2

3、介绍3

二十八、常用SQL-->group by与having 、explain 等等

（1）explain

（2）group by

附加：

（1）提交读和可重复读的实现原理

（2）主键和索引的区别

（3）千万数据分页查询，与用limit分页的区别

（4）主从复制

一、架构：Server层，引擎层

二、引擎：InnoDB，MyISAM

三、聚簇索引和非聚簇索引

四、事务及其四大特性、MySQL中事务提交的过程

五、并发事务带来的问题

六、事务的隔离级别

        MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）。

七、InnoDB存储引擎的锁算法、MySQL什么时候行锁升级为表锁

八、大表优化

        垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user_partition | p1,p4      | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where; Using index |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

九、分库分表之后id主键的处理方式

十、一条SQL语句的执行过程

一 MySQL 基础架构分析

1.1 MySQL 基本架构概览

        下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

        先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图，在 1.2 节中会详细介绍到这些组件的作用。

        •连接器： 身份认证和权限相关(登录 MySQL 的时候)。

        •查询缓存:  执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。

        •分析器:  没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

        •优化器： 按照 MySQL 认为最优的方案去执行。

        •执行器:  执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

        •Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

        •存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

1.2 Server 层基本组件介绍

1) 连接器

        连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

        主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

2) 查询缓存(MySQL 8.0 版本后移除)

        查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

        连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

        MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

        所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

        MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

3) 分析器

        MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

        第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

        第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

        完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

4) 优化器

        优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

        可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

5) 执行器

        当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

二 语句分析

2.1 查询语句

        说了以上这么多，那么究竟一条 sql 语句是如何执行的呢？其实我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

结合上面的说明，我们分析下这个语句的执行流程：

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id='1'。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

  a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。  b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

       那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

2.2 更新语句

以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下：

update tb_student A set A.age='19' where A.name=' 张三 ';

        我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块式 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

•先查询到张三这一条数据，如果有缓存，也是会用到缓存。

•然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。

•执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。

•更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

        这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

        并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

•先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 bingog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

•先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

        如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

•判断 redo log 是否完整，如果判断是完整的，就立即提交。

•如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就解决了数据一致性的问题。

三 总结

        •MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。

        •引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。•SQL 等执行过程分为两类，一类对于查询等过程如下：权限校验---》查询缓存---》分析器---》优化器---》权限校验---》执行器---》引擎

        •对于更新等语句执行流程如下：分析器----》权限校验----》执行器---》引擎---redo log prepare---》binlog---》redo log commit

十一、MySQL高性能优化规范、书写高质量SQL的建议

数据库命令规范

        •所有数据库对象名称必须使用小写字母并用下划线分割

        •所有数据库对象名称禁止使用 MySQL 保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）

        •数据库对象的命名要能做到见名识意，并且最好不要超过 32 个字符

        •临时库表必须以 tmp_为前缀并以日期为后缀，备份表必须以 bak_为前缀并以日期 (时间戳) 为后缀

        •所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）

数据库基本设计规范

1. 所有表必须使用 Innodb 存储引擎

        没有特殊要求（即 Innodb 无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用 Innodb 存储引擎（MySQL5.5 之前默认使用 Myisam，5.6 以后默认的为 Innodb）。

        Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

2. 数据库和表的字符集统一使用 UTF8

        兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。

3. 所有表和字段都需要添加注释

        使用 comment 从句添加表和列的备注，从一开始就进行数据字典的维护

4. 尽量控制单表数据量的大小,建议控制在 500 万以内。

        500 万并不是 MySQL 数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题。

        可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

5. 谨慎使用 MySQL 分区表

        分区表在物理上表现为多个文件，在逻辑上表现为一个表；

        谨慎选择分区键，跨分区查询效率可能更低；

        建议采用物理分表的方式管理大数据。

6.尽量做到冷热数据分离,减小表的宽度

MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节。

        减少磁盘 IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO）；

        更有效的利用缓存，避免读入无用的冷数据；

        经常一起使用的列放到一个表中（避免更多的关联操作）。

7. 禁止在表中建立预留字段

        预留字段的命名很难做到见名识义。

        预留字段无法确认存储的数据类型，所以无法选择合适的类型。

        对预留字段类型的修改，会对表进行锁定。

8. 禁止在数据库中存储图片,文件等大的二进制数据

        通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时。

        通常存储于文件服务器，数据库只存储文件地址信息

9. 禁止在线上做数据库压力测试

10. 禁止从开发环境,测试环境直接连接生成环境数据库

数据库字段设计规范

1. 优先选择符合存储需要的最小的数据类型

原因：

        列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差。

方法：

        a.将字符串转换成数字类型存储,如:将 IP 地址转换成整形数据

                MySQL 提供了两个方法来处理 ip 地址

                        •inet_aton 把 ip 转为无符号整型 (4-8 位)

                        •inet_ntoa 把整型的 ip 转为地址

        插入数据前，先用 inet_aton 把 ip 地址转为整型，可以节省空间，显示数据时，使用 inet_ntoa 把整型的 ip 地址转为地址显示即可。

        b.对于非负型的数据 (如自增 ID,整型 IP) 来说,要优先使用无符号整型来存储

        原因：

                无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647UNSIGNED INT 0~4294967295

        VARCHAR(N) 中的 N 代表的是字符数，而不是字节数，使用 UTF8 存储 255 个汉字 Varchar(255)=765 个字节。过大的长度会消耗更多的内存。

2. 避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据

        a. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中

        MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差，但是不是说一定不能使用这样的数据类型。

        如果一定要使用，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时一定不要使用 select * 而只需要取出必要的列，不需要 TEXT 列的数据时不要对该列进行查询。

        b.TEXT 或 BLOB 类型只能使用前缀索引

        因为MySQL[1] 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的

3. 避免使用 ENUM 类型

        修改 ENUM 值需要使用 ALTER 语句

        ENUM 类型的 ORDER BY 操作效率低，需要额外操作

        禁止使用数值作为 ENUM 的枚举值

4. 尽可能把所有列定义为 NOT NULL

原因：

        索引 NULL 列需要额外的空间来保存，所以要占用更多的空间

        进行比较和计算时要对 NULL 值做特别的处理

5. 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间

        TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

        TIMESTAMP 占用 4 字节和 INT 相同，但比 INT 可读性高

        超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储

        经常会有人用字符串存储日期型的数据（不正确的做法）

        •缺点1：无法用日期函数进行计算和比较

        •缺点 2：用字符串存储日期要占用更多的空间

6. 同财务相关的金额类数据必须使用 decimal 类型

        •非精准浮点：float,double

        •精准浮点：decimal

        Decimal 类型为精准浮点数，在计算时不会丢失精度

        占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节

        可用于存储比 bigint 更大的整型数据

索引设计规范

1. 限制每张表上的索引数量,建议单张表索引不超过 5 个

        索引并不是越多越好！索引可以提高效率同样可以降低效率。

        索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

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

2. 禁止给表中的每一列都建立单独的索引

        5.6 版本之前，一个 sql 只能使用到一个表中的一个索引，5.6 以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。

3. 每个 Innodb 表必须有个主键

        Innodb 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

        Innodb 是按照主键索引的顺序来组织表的

                •不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）

                •不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）

                •主键建议使用自增 ID 值

4. 常见索引列建议

        •出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列

        •包含在 ORDER BY、GROUP BY、DISTINCT 中的字段

        •并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好

        •多表 join 的关联列

5.如何选择索引列的顺序

        建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

        •区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）

        •尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）

        •使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

6. 避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）

        •重复索引示例：primary key(id)、index(id)、unique index(id)

        •冗余索引示例：index(a,b,c)、index(a,b)、index(a)

7. 对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引

覆盖索引的好处：

        •避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。

        •可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

8.索引 SET 规范

尽量避免使用外键约束

        •不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引

        •外键可用于保证数据的参照完整性，但建议在业务端实现

        •外键会影响父表和子表的写操作从而降低性能

数据库 SQL 开发规范

1. 建议使用预编译语句进行数据库操作

        预编译语句可以重复使用这些计划，减少 SQL 编译所需要的时间，还可以解决动态 SQL 所带来的 SQL 注入的问题。

        只传参数，比传递 SQL 语句更高效。

        相同语句可以一次解析，多次使用，提高处理效率。

2. 避免数据类型的隐式转换

隐式转换会导致索引失效如:

select name,phone from customer where id = '111';

3. 充分利用表上已经存在的索引

        避免使用双%号的查询条件。如：a like '%123%'，（如果无前置%,只有后置%，是可以用到列上的索引的）

        一个 SQL 只能利用到复合索引中的一列进行范围查询。如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到。

        在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧，使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。

4. 数据库设计时，应该要对以后扩展进行考虑

5. 程序连接不同的数据库使用不同的账号，进制跨库查询

        •为数据库迁移和分库分表留出余地

        •降低业务耦合度

        •避免权限过大而产生的安全风险

6. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

原因：

        •消耗更多的 CPU 和 IO 以网络带宽资源

        •无法使用覆盖索引

        •可减少表结构变更带来的影响

7. 禁止使用不含字段列表的 INSERT 语句

如：

insert into values ('a','b','c');

应使用：

insert into t(c1,c2,c3) values ('a','b','c');

8. 避免使用子查询，可以把子查询优化为 join 操作

        通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

        子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

        由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

9. 避免使用 JOIN 关联太多的表

        对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置。

        在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。

        如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

        同时对于关联操作来说，会产生临时表操作，影响查询效率，MySQL 最多允许关联 61 个表，建议不超过 5 个。

10. 减少同数据库的交互次数

        数据库更适合处理批量操作，合并多个相同的操作到一起，可以提高处理效率。

11. 对应同一列进行 or 判断时，使用 in 代替 or

        in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

12. 禁止使用 order by rand() 进行随机排序

        order by rand() 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。

        推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

13. WHERE 从句中禁止对列进行函数转换和计算

        对列进行函数转换或计算时会导致无法使用索引

不推荐：

where date(create_time)='20190101'

推荐：

where create_time >= '20190101' and create_time < '20190102'

14. 在明显不会有重复值时使用 UNION ALL 而不是 UNION

        •UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作

        •UNION ALL 不会再对结果集进行去重操作

15. 拆分复杂的大 SQL 为多个小 SQL

        •大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL

        •MySQL 中，一个 SQL 只能使用一个 CPU 进行计算

        •SQL 拆分后可以通过并行执行来提高处理效率

数据库操作行为规范

1. 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作

大批量操作可能会造成严重的主从延迟

        主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间， 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

binlog 日志为 row 格式时会产生大量的日志

        大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因

避免产生大事务操作

        大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。

        特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批

2. 对于大表使用 pt-online-schema-change 修改表结构

        •避免大表修改产生的主从延迟

        •避免在对表字段进行修改时进行锁表

        对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。

        pt-online-schema-change 它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉。把原来一个 DDL 操作，分解成多个小的批次进行。

3. 禁止为程序使用的账号赋予 super 权限

        •当达到最大连接数限制时，还运行 1 个有 super 权限的用户连接

        •super 权限只能留给 DBA 处理问题的账号使用

4. 对于程序连接数据库账号,遵循权限最小原则

        •程序使用数据库账号只能在一个 DB 下使用，不准跨库

        •程序使用的账号原则上不准有 drop 权限

使用数据库的30条建议

1、查询SQL尽量不要使用select *，而是select具体字段。

反例子：

select * from employee;

正例子：

select id，name from employee;

理由：

• 只取需要的字段，节省资源、减少网络开销。

• select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

2、如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1

假设现在有employee员工表，要找出一个名字叫jay的人.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

反例：

select id，name from employee where name='jay'

正例

select id，name from employee where name='jay' limit 1;

理由：

• 加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。

• 当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

3、应尽量避免在where子句中使用or来连接条件

新建一个user表，它有一个普通索引userId，表结构如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假设现在需要查询userid为1或者年龄为18岁的用户，很容易有以下sql

反例:

select * from user where userid=1 or age =18

正例：

//使用union all
select * from user where userid=1 
union all 
select * from user where age = 18
 
//或者分开两条sql写：
select * from user where userid=1
select * from user where age = 18

理由：

• 使用or可能会使索引失效，从而全表扫描。

对于or+没有索引的age这种情况，假设它走了userId的索引，但是走到age查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并 如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

4、优化limit分页

我们日常做分页需求时，一般会用 limit 实现，但是当偏移量特别大的时候，查询效率就变得低下。

反例：

select id，name，age from employee limit 10000，10

正例：

//方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id>10000 limit 10.
 
//方案二：order by + 索引
select id，name from employee order by id  limit 10000，10
 
//方案三：在业务允许的情况下限制页数：

理由：

• 当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。

• 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少。

• 方案二使用order by+索引，也是可以提高查询效率的。

• 方案三的话，建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

5、优化你的like语句

日常开发中，如果用到模糊关键字查询，很容易想到like，但是like很可能让你的索引失效。

反例：

select userId，name from user where userId like '%123';

正例：

select userId，name from user where userId like '123%';

理由：

• 把%放前面，并不走索引，如下：

• 把% 放关键字后面，还是会走索引的。如下：

6、使用where条件限定要查询的数据，避免返回多余的行

假设业务场景是这样：查询某个用户是否是会员。曾经看过老的实现代码是这样。。。

反例：

List<Long> userIds = sqlMap.queryList("select userId from user where isVip=1");
boolean isVip = userIds.contains(userId);

正例：

Long userId = sqlMap.queryObject("select userId from user where userId='userId' and isVip='1' ")
boolean isVip = userId！=null;

理由：

• 需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。

7、尽量避免在索引列上使用mysql的内置函数

业务需求：查询最近七天内登陆过的用户(假设loginTime加了索引)

反例：

select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();

正例：

explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

• 索引列上使用mysql的内置函数，索引失效

• 如果索引列不加内置函数，索引还是会走的。

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫

反例：

select * from user where age-1 =10；

正例：

select * from user where age =11；

理由：

• 虽然age加了索引，但是因为对它进行运算，索引直接迷路了。。。

  

9、Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

• Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

• left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

• right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

        都满足SQL需求的前提下，推荐优先使用Inner join（内连接），如果要使用left join，左边表数据结果尽量小，如果有条件的尽量放到左边处理。

反例:

select * from tab1 t1 left join tab2 t2  on t1.size = t2.size where t1.id>2;

正例：

select * from (select * from tab1 where id >2) t1 left join tab2 t2 on t1.size = t2.size;

理由：

• 如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点。

• 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

10、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

反例：

select age,name  from user where age <>18;

正例：

//可以考虑分开两条sql写
select age,name  from user where age <18;
select age,name  from user where age >18;

理由：

• 使用!=和<>很可能会让索引失效

11、使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

表结构：（有一个联合索引idx_userid_age，userId在前，age在后）

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

反例：

select * from user where age = 10;

正例：

//符合最左匹配原则
select * from user where userid=10 and age =10；
//符合最左匹配原则
select * from user where userid =10;

理由：

• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

• 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

12、对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描。

反例：

select * from user where address ='深圳' order by age ;

正例：

添加索引
alter table user add index idx_address_age (address,age)

13、如果插入数据过多，考虑批量插入。

反例：

for(User u :list){
 INSERT into user(name,age) values(#name#,#age#)   
}

正例：

//一次500批量插入，分批进行
insert into user(name,age) values
<foreach collection="list" item="item" index="index" separator=",">
    (#{item.name},#{item.age})
</foreach>

理由：

• 批量插入性能好，更加省时间

打个比喻:假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖（最多放500）,你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?

14、在适当的时候，使用覆盖索引。

        覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，大大提高了查询效率。

反例：

// like模糊查询，不走索引了
select * from user where userid like '%123%'

正例：

//id为主键，那么为普通索引，即覆盖索引登场了。
select id,name from user where userid like '%123%';

15、慎用distinct关键字

distinct 关键字一般用来过滤重复记录，以返回不重复的记录。在查询一个字段或者很少字段的情况下使用时，给查询带来优化效果。但是在字段很多的时候使用，却会大大降低查询效率。

反例：

SELECT DISTINCT * from  user;

正例：

select DISTINCT name from user;

理由：

• 带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程会占用系统资源，cpu时间。

16、删除冗余和重复索引

反例：

  KEY `idx_userId` (`userId`)
  KEY `idx_userId_age` (`userId`,`age`)

正例:

  //删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
  KEY `idx_userId_age` (`userId`,`age`)

理由：

• 重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能的。

17、如果数据量较大，优化你的修改/删除语句。

避免同时修改或删除过多数据，因为会造成cpu利用率过高，从而影响别人对数据库的访问。

反例：

//一次删除10万或者100万+？
delete from user where id <100000;
//或者采用单一循环操作，效率低，时间漫长
for（User user：list）{
   delete from user；
}

正例：

//分批进行删除,如每次500
delete user where id<500
delete product where id>=500 and id<1000；

理由：

• 一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

18、where子句中考虑使用默认值代替null。

反例：

select * from user where age is not null;

正例：

//设置0为默认值
select * from user where age>0;

理由：

• 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关。

如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件！=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的。

• 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点。

19、不要有超过5个以上的表连接

• 连表越多，编译的时间和开销也就越大。

• 把连接表拆开成较小的几个执行，可读性更高。

• 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

20、exist & in的合理利用

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下SQL:

select * from A where deptId in (select deptId from B);

这样写等价于：

先查询部门表B

select deptId from B

再由部门deptId，查询A的员工

select * from A where A.deptId = B.deptId

可以抽象成这样的一个循环：

   List<> resultSet ;
    for(int i=0;i<B.length;i++) {
          for(int j=0;j<A.length;j++) {
          if(A[i].id==B[j].id) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

显然，除了使用in，我们也可以用exists实现一样的查询功能，如下：

select * from A where exists (select 1 from B where A.deptId = B.deptId);

因为exists查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true或者false），来决定主查询的数据结果是否得意保留。

那么，这样写就等价于：

select * from A,先从A表做循环

select * from B where A.deptId = B.deptId,再从B表做循环.

同理，可以抽象成这样一个循环：

   List<> resultSet ;
    for(int i=0;i<A.length;i++) {
          for(int j=0;j<B.length;j++) {
          if(A[i].deptId==B[j].deptId) {
             resultSet.add(A[i]);
             break;
          }
       }
    }

        数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。即mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。

        因此，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist。

21、尽量用 union all 替换 union

如果检索结果中不会有重复的记录，推荐union all 替换 union。

反例：

select * from user where userid=1 
union
select * from user where age = 10

正例：

select * from user where userid=1 
union all
select * from user where age = 10

理由：

• 如果使用union，不管检索结果有没有重复，都会尝试进行合并，然后在输出最终结果前进行排序。如果已知检索结果没有重复记录，使用union all 代替union，这样会提高效率。

22、索引不宜太多，一般5个以内。

• 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。

• insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。

• 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。

23、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

反例：

king_id` varchar（20） NOT NULL COMMENT '守护者Id'

正例：

`king_id` int(11) NOT NULL COMMENT '守护者Id'`

理由：

• 相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。

24、索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

        因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

25、尽量避免向客户端返回过多数据量。

假设业务需求是，用户请求查看自己最近一年观看过的直播数据。

反例：

//一次性查询所有数据回来
select * from LivingInfo where watchId =useId and watchTime >= Date_sub(now(),Interval 1 Y)

正例：

//分页查询
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit offset，pageSize
 
//如果是前端分页，可以先查询前两百条记录，因为一般用户应该也不会往下翻太多页，
select * from LivingInfo where watchId =useId and watchTime>= Date_sub(now(),Interval 1 Y) limit 200 ;

26、当在SQL语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰。

反例：

select  * from A inner
join B on A.deptId = B.deptId;

正例：

select  memeber.name,deptment.deptName from A member inner
join B deptment on member.deptId = deptment.deptId;

27、尽可能使用varchar/nvarchar 代替 char/nchar。

反例：

  `deptName` char(100) DEFAULT NULL COMMENT '部门名称'

正例：

  `deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'

理由：

• 因为首先变长字段存储空间小，可以节省存储空间。

• 其次对于查询来说，在一个相对较小的字段内搜索，效率更高。

28、为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

反例：

select job，avg（salary） from employee  group by job having job ='president' 
or job = 'managent'

正例：

select job，avg（salary） from employee where job ='president' 
or job = 'managent' group by job；

29、如何字段类型是字符串，where时一定用引号括起来，否则索引失效

反例：

select * from user where userid =123;

正例：

select * from user where userid ='123';

理由：

• 为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

30、使用explain 分析你SQL的计划

日常开发写SQL的时候，尽量养成一个习惯吧。用explain分析一下你写的SQL，尤其是走不走索引这一块。

explain select * from user where userid =10086 or age =18;

十二、一条SQL语句执行得很慢的原因

1、大多数情况是正常的，只是偶尔会出现很慢的情况。

2、在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。

针对这两种情况，我们来分析下可能是哪些原因导致的。

针对偶尔很慢的情况

        一条 SQL 大多数情况正常，偶尔才能出现很慢的情况，针对这种情况，我觉得这条SQL语句的书写本身是没什么问题的，而是其他原因导致的，那会是什么原因呢？

1、数据库在刷新脏页（flush）

        当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

刷脏页有下面4种场景（后两种不用太关注“性能”问题）：

• redolog写满了：redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。

• 内存不够用了：如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。

• MySQL 认为系统“空闲”的时候：这时系统没什么压力。

• MySQL 正常关闭的时候：这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

2、拿不到锁

        这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。

        如果要判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态哦，这里我要提醒一下，有些命令最好记录一下，反正，我被问了好几个命令，都不知道怎么写，呵呵。

        下来我们来访分析下第二种情况，我觉得第二种情况的分析才是最重要的

针对一直都这么慢的情况

        如果在数据量一样大的情况下，这条 SQL 语句每次都执行的这么慢，那就就要好好考虑下你的 SQL 书写了，下面我们来分析下哪些原因会导致我们的 SQL 语句执行的很不理想。

我们先来假设我们有一个表，表里有下面两个字段,分别是主键 id，和两个普通字段 c 和 d。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

1、没用到索引

没有用上索引，我觉得这个原因是很多人都能想到的，例如你要查询这条语句

select * from t where 100 <c and c < 100000;

（1）、字段没有索引

刚好你的 c 字段上没有索引，那么抱歉，只能走全表扫描了，你就体验不会索引带来的乐趣了，所以，这回导致这条查询语句很慢。

（2）、字段有索引，但却没有用索引

好吧，这个时候你给 c 这个字段加上了索引，然后又查询了一条语句

select * from t where c - 1 = 1000;

我想问大家一个问题，这样子在查询的时候会用索引查询吗？

答是不会，如果我们在字段的左边做了运算，那么很抱歉，在查询的时候，就不会用上索引了，所以呢，大家要注意这种字段上有索引，但由于自己的疏忽，导致系统没有使用索引的情况了。

正确的查询应该如下

select * from t where c = 1000 + 1;

有人可能会说，右边有运算就能用上索引？难道数据库就不会自动帮我们优化一下，自动把 c - 1=1000 自动转换为 c = 1000+1。

（3）、函数操作导致没有用上索引

如果我们在查询的时候，对字段进行了函数操作，也是会导致没有用上索引的，例如

select * from t where pow(c,2) = 1000;

这里我只是做一个例子，假设函数 pow 是求 c 的 n 次方，实际上可能并没有 pow(c,2)这个函数。其实这个和上面在左边做运算也是很类似的。

所以呢，一条语句执行都很慢的时候，可能是该语句没有用上索引了，不过具体是啥原因导致没有用上索引的呢，你就要会分析了，我上面列举的三个原因，应该是出现的比较多的吧。

2、数据库自己选错索引了

我们在进行查询操作的时候，例如

select * from t where 100 < c and c < 100000;

        我们知道，主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而且存放主键字段的值。

        也就是说，我们如果走 c 这个字段的索引的话，最后会查询到对应主键的值，然后，再根据主键的值走主键索引，查询到整行数据返回。

        好吧扯了这么多，其实我就是想告诉你，就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表，找出所有符合 100 < c and c < 100000 的数据。

为什么会这样呢？

        其实是这样的，系统在执行这条语句的时候，会进行预测：究竟是走 c 索引扫描的行数少，还是直接扫描全表扫描的行数少呢？显然，扫描行数越少当然越好了，因为扫描行数越少，意味着I/O操作的次数越少。

        如果是扫描全表的话，那么扫描的次数就是这个表的总行数了，假设为 n；而如果走索引 c 的话，我们通过索引 c 找到主键之后，还得再通过主键索引来找我们整行的数据，也就是说，需要走两次索引。而且，我们也不知道符合 100 c < and c < 10000 这个条件的数据有多少行，万一这个表是全部数据都符合呢？这个时候意味着，走 c 索引不仅扫描的行数是 n，同时还得每行数据走两次索引。

所以呢，系统是有可能走全表扫描而不走索引的。那系统是怎么判断呢？

        判断来源于系统的预测，也就是说，如果要走 c 字段索引的话，系统会预测走 c 字段索引大概需要扫描多少行。如果预测到要扫描的行数很多，它可能就不走索引而直接扫描全表了。

        那么问题来了，系统是怎么预测判断的呢？这里我给你讲下系统是怎么判断的吧，虽然这个时候我已经写到脖子有点酸了。

        系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。所以呢，基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。

所以呢，一个索引的基数越大，意味着走索引查询越有优势。

那么问题来了，怎么知道这个索引的基数呢？

        系统当然是不会遍历全部来获得一个索引的基数的，代价太大了，索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。

        扯了这么多，重点的来了，居然是采样，那就有可能出现失误的情况，也就是说，c 这个索引的基数实际上是很大的，但是采样的时候，却很不幸，把这个索引的基数预测成很小。例如你采样的那一部分数据刚好基数很小，然后就误以为索引的基数很小。然后就呵呵，系统就不走 c 索引了，直接走全部扫描了。

        所以呢，说了这么多，得出结论：由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这，也是导致我们 SQL 语句执行的很慢的原因。

这里我声明一下，系统判断是否走索引，扫描行数的预测其实只是原因之一，这条查询语句是否需要使用使用临时表、是否需要排序等也是会影响系统的选择的。

不过呢，我们有时候也可以通过强制走索引的方式来查询，例如

select * from t force index(a) where c < 100 and c < 100000;

我们也可以通过

show index from t;

来查询索引的基数和实际是否符合，如果和实际很不符合的话，我们可以重新来统计索引的基数，可以用这条命令

analyze table t;

来重新统计分析。

        既然会预测错索引的基数，这也意味着，当我们的查询语句有多个索引的时候，系统有可能也会选错索引哦，这也可能是 SQL 执行的很慢的一个原因。

好吧，就先扯这么多了，你到时候能扯出这么多，我觉得已经很棒了，下面做一个总结。

四、总结

以上是我的总结与理解，最后一个部分，我怕很多人不大懂数据库居然会选错索引，所以我详细解释了一下，下面我对以上做一个总结。

一个 SQL 执行的很慢，我们要分两种情况讨论：

        1、大多数情况下很正常，偶尔很慢，则有如下原因

                (1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。

                (2)、执行的时候，遇到锁，如表锁、行锁。

        2、这条 SQL 语句一直执行的很慢，则有如下原因。

                (1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

                (2)、数据库选错了索引。

十三、MySQL日志：redo log、binary log、undo log

1、简介1

（1）redo log 

　　 重做日志

　　作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

　　内容：物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

（2）bin log

　　归档日志（二进制日志）

　　作用：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 
用于数据库的基于时间点的还原。

　　内容：逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

        但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

　　binlog 有三种模式：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）

（3）undo log

　　回滚日志

　　作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

　　内容：逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

2、简介2

（1）redo log

　　mysql，如果每次更新操作都要写进磁盘，然后磁盘要找到对应记录，然后再更细，整个过程io成本、查找成本都很高。

　　解决方案：WAL技术（Write-Ahead Logging）。先写日志，再写磁盘。

　　具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

　　write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

　　write pos 和 checkpoint 之间的是log上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

　　有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

（2）bin log

　　MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

　　这两种日志有以下三点不同。

　　1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

　　2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

　　3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

update 语句时的内部流程：

　　1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

　　2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

　　3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

　　4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

　　5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

最后三步，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

两阶段提交

　　两阶段提交，是为了binlog和redolog两分日志之间的逻辑一致。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

　　由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。可能造成的问题：

 　　update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

　　1. 先写 redo log 后写 binlog。

　　假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

　　2. 先写 binlog 后写 redo log。

　　如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

　　如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

　　扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致线上出现主从数据库不一致的情况。

（3）undo log

　　undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

　　undo日志用于记录事务开始前的状态，用于事务失败时的回滚操作；redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是0，修改后的值为1，那么Undo日志为<T1, X, 0>，Redo日志为<T1, X, 1>。

3、简介3

        日志系统主要有redo log(重做日志)和binlog(归档日志)。redo log是InnoDB存储引擎层的日志，binlog是MySQL Server层记录的日志， 两者都是记录了某些操作的日志(不是所有)自然有些重复（但两者记录的格式不同）。

（1）redo log日志模块
        redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。

        在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。

        有了redo log日志，那么在数据库进行异常重启的时候，可以根据redo log日志进行恢复，也就达到了crash-safe。

        redo log日志的大小是固定的，即记录满了以后就从头循环写。

        该图展示了一组4个文件的redo log日志，checkpoint之前表示擦除完了的，即可以进行写的，擦除之前会更新到磁盘中，write pos是指写的位置，当write pos和checkpoint相遇的时候表明redo log已经满了，这个时候数据库停止进行数据库更新语句的执行，转而进行redo log日志同步到磁盘中。

（2）binlog日志模块
        binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的

redo log和binlog区别
        redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。
        redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑
        redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
        binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

一条更新语句执行的顺序
        update T set c=c+1 where ID=2;

        执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
        执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
        引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
        执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
        执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
       

（3）innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：
        1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
        2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

4、简介4

一、重做日志（redo log）
作用：

        确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

二、回滚日志（undo log）
作用：

        保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

三、二进制日志（binlog）：
作用：

        用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 
用于数据库的基于时间点的还原。

        数据库数据存放的文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer；既然数据库数据有缓存，就很难保证缓存数据（脏数据）与磁盘数据的一致性。比如某次数据库操作：

update driver_info set driver_status = 2 where driver_id = 10001;

        更新driver_status字段的数据会存放在缓存中，等待存储引擎将driver_status刷新data_file，并返回给业务方更新成功。如果此时数据库宕机，缓存中的数据就丢失了，业务方却以为更新成功了，数据不一致，也没有持久化存储。

上面的问题就可以通过事务的ACID特性来保证。

BEGIN trans；

update driver_info set driver_status = 2 where driver_id = 10001;

COMMIT;

这样执行后，更新要么成功，要么失败。业务方的返回和数据库data file中的数据保持一致。要保证这样的特性这就不得不说存储引擎innodb的redo和undo日志。

redo日志、undo日志：

        存储引擎也会为redo undo日志开辟内存缓存空间，log buffer。磁盘上的日志文件称为log file，是顺序追加的，性能非常高，注：磁盘的顺序写性能比内存的写性能差不了多少。

        undo日志用于记录事务开始前的状态，用于事务失败时的回滚操作；redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么Undo日志为<T1, X, 5>，Redo日志为<T1, X, 15>。

梳理下事务执行的各个阶段：

（1）写undo日志到log buffer；

（2）执行事务，并写redo日志到log buffer；

（3）如果innodb_flush_log_at_trx_commit=1，则将redo日志写到log file，并刷新落盘。

（4）提交事务。

可能有同学会问，为什么没有写data file，事务就提交了？

在数据库的世界里，数据从来都不重要，日志才是最重要的，有了日志就有了一切。

因为data buffer中的数据会在合适的时间 由存储引擎写入到data file，如果在写入之前，数据库宕机了，根据落盘的redo日志，完全可以将事务更改的数据恢复。好了，看出日志的重要性了吧。先持久化日志的策略叫做Write Ahead Log，即预写日志。

分析几种异常情况：

        innodb_flush_log_at_trx_commit=2（innodb_flush_log_at_trx_commit和sync_binlog参数详解）时，将redo日志写入logfile后，为提升事务执行的性能，存储引擎并没有调用文件系统的sync操作，将日志落盘。如果此时宕机了，那么未落盘redo日志事务的数据是无法保证一致性的。
undo日志同样存在未落盘的情况，可能出现无法回滚的情况。

checkpoint：

checkpoint是为了定期将db buffer的内容刷新到data file。当遇到内存不足、db buffer已满等情况时，需要将db buffer中的内容/部分内容（特别是脏数据）转储到data file中。在转储时，会记录checkpoint发生的”时刻“。在故障回复时候，只需要redo/undo最近的一次checkpoint之后的操作。
 

十四、数据库范式（三范式）

十五、char，varchar 与 varchar(10)，varchar(20)

十六、建立索引的情况，索引底层的数据结构

十七、B树，B+树，哈希表

        磁盘的数据存储单位是扇区，默认大小512字节，文件系统EXT4 最小单元是块，大小为4K，而我们InnoDB存储引擎的最小存储单元叫页，一个页的大小默认是16K，通过show variables like 'innodb_page_size';来查看或者自定义。

十八、覆盖索引

十九、索引分类

二十、最左前缀

二十一、查看索引是否被用到，什么情况下索引会失效

二十二、主从复制

二十三、主从同步延迟的原因

二十四、数据库读写分离

二十五、MVCC

二十六、当前读、快照读

（1）当前读

　　select...lock in share mode (共享读锁)
　　select...for update
　　update , delete , insert

　　当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。

　　例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产生冲突。所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

• 当前读的实现方式：next-key锁(行记录锁+Gap间隙锁)

 　　间隙锁：只有在Read Repeatable、Serializable隔离级别才有，就是锁定范围空间的数据，假设id有3,4,5，锁定id>3的数据，是指的4，5及后面的数字都会被锁定，因为此时如果不锁定没有的数据，例如当加入了新的数据id=6，就会出现幻读，间隙锁避免了幻读。

　　1.对主键或唯一索引，如果当前读时，where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁。

　　2.没有索引的列，当前读操作时，会加全表gap锁，生产环境要注意。

　　3.非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁。例如，某表数据如下，非唯一索引2,6,9,9,11,15。如下语句要操作非唯一索引列9的数据，gap锁将会锁定的列是(6,11]，该区间内无法插入数据。

这里有点疑问，有的版本是左右全开，有的版本是左开右闭。

（2）快照读

　　单纯的select操作，不包括上述 select ... lock in share mode, select ... for update。　　　　

　　Read Committed隔离级别：每次select都生成一个快照读。

　　Read Repeatable隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读。

• 快照读的实现方式：undolog和多版本并发控制MVCC

　　下图右侧绿色的是数据：一行数据记录，主键ID是10，name='Jack'，age=10,  被update更新set为name= 'Tom'，age=23。

　　事务会先使用“排他锁”锁定改行，将该行当前的值复制到undo log中，然后再真正地修改当前行的值，最后填写事务的DB_TRX_ID，使用回滚指针DB_ROLL_PTR指向undo log中修改前的行DB_ROW_ID。

'

　　DB_TRX_ID: 6字节DB_TRX_ID字段，表示最后更新的事务id(update,delete,insert)。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。

　　DB_ROLL_PTR: 7字节回滚指针，指向前一个版本的undolog记录，组成undo链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。
　　

        DB_ROW_ID: 6字节的DB_ROW_ID字段，包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。如果表中没有主键或合适的唯一索引, 也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引, 聚簇索引会使用DB_ROW_ID的值来作为主键; 如果表中有主键或者合适的唯一索引, 那么聚簇索引中也就不会包含 DB_ROW_ID了 。  

　　其它：insert undo log只在事务回滚时需要, 事务提交就可以删掉了。update undo log包括update 和 delete , 回滚和快照读 都需要。

（3） 普通读（快照读）

1.1 定义

        普通读（也称快照读，英文名：Consistent Read），就是单纯的 SELECT 语句，不包括下面这两类语句：

SELECT ... FOR UPDATE 
SELECT ... LOCK IN SHARE MODE

        普通读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。

小贴士

对于 SERIALIZABLE 隔离级别来说，如果 autocommit 系统变量被设置为OFF，那普通读的语句会转变为锁定读，和在普通的 SELECT 语句后边加 LOCK IN SHARE MODE 达成的效果一样。

1.2 实现方式

        普通读是通过 undo log + MVCC 来实现的，具体我们再仔细聊聊：

        下图右侧黄色部分是数据：一行数据记录，主键 ID 是 10，object = 'Goland'  ，被 update 更新为 object = 'Python' 。

事务会先使用“排他锁”锁定该行，将该行当前的值复制到 undo log 中，然后再真正地修改当前行的值，最后填写事务的 DB_TRX_ID ，使用回滚指针 DB_ROLL_PTR 指向 undo log 中修改前的行。

这里解释一下 DB_TRX_ID 和 DB_ROLL_PTR 所代表的含义：

• DB_TRX_ID :  6 字节 DB_TRX_ID 字段，表示最后更新的事务 id ( update , delete , insert ) 。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。

• DB_ROLL_PTR :  7 字节回滚指针，指向前一个版本的 undo log 记录，组成 undo 链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。

小贴士

insert undo log 只在事务回滚时需要, 事务提交就可以删掉了。update undo log 包括 update 和 delete , 回滚和快照读都需要。

（4）当前读

聊完快照读，再聊聊当前读（也称锁定读，Locking Read）。

2.1 定义

        当前读，读取的是最新版本，并且需要先获取对应记录的锁，如以下这些 SQL 类型：

        select ... lock in share mode 、

        select ... for update、

        update 、delete 、insert

        当然，获取什么类型的锁取决于当前事务的隔离级别、语句的执行计划、查询条件等因素。例如，要 update 一条记录，在事务执行过程中，如果不加锁，那么另一个事务可以 delete 这条数据并且能成功 commit ，就会产生冲突了。所以 update 的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

2.2 实现方式

当前读是通过 next-key 锁(行记录锁+间隙锁)来是实现的。

这里补充下行锁的 3 种算法：

行锁（Record Lock）：锁直接加在索引记录上面。

间隙锁（Gap Lock）：是 Innodb 为了解决幻读问题时引入的锁机制，所以只有在 Read Repeatable 、Serializable 隔离级别才有。

Next-Key Lock ：Record Lock + Gap Lock，锁定一个范围并且锁定记录本身 。

下面通过一个例子来说明当前读的实现方式，例如下面这条 SQL：

delete from T where age = 7;

进行下面的实验：

测试可知 delete from T where age = 7; 语句在 age 上的加锁区间为 (4,10) ,图解如下：

二十七、连接

1、介绍1

        本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

        你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

MySQL多表连接查询

连接(join)：将一张表中的行按照某个条件（连接条件）和另一张表中的行连接起来形成一个新行的过程。

　　根据连接查询返回的结果，分3类：

　　　　内连接（inner join）

　　　　外连接（outer join）

　　　　交叉连接（cross join）

　　根据连接条件所使用的操作符，分2类：

　　　　相等连接（使用等号操作符）

　　　　不等连接（不使用等号操作符） 

标准的连接语法：

注意：

　　在连接查询中，一个列可能出现在多张表中，为了避免引起歧义，通常在列名前面加上表名或表别名作为前缀(例：s.sid、x.sid)---使用表别名作为前缀，可以使得SQL代码较短，使用的内存更少(例：stu s,xuanke as x)。

搭建环境：模拟选课

mysql> select * from stu;
+------+--------+---------+
| sid  | sname  | sphonum |
+------+--------+---------+
|    1 | 张三   |     110 |
|    2 | 李四   |     120 |
|    3 | 王五   |     130 |
+------+--------+---------+
3 rows in set (0.00 sec)


mysql> select * from tea;
+------+-----------+---------+
| tid  | tname     | tphonum |
+------+-----------+---------+
| 1113 | 相老师    |    1111 |
| 1114 | 冯老师    |    1112 |
+------+-----------+---------+
2 rows in set (0.00 sec)



mysql> select * from course;
+------+--------+
| cid  | cname  |
+------+--------+
|    1 | linux  |
|    2 | mysql  |
|    3 | hadoop |
+------+--------+
3 rows in set (0.00 sec)



mysql> select * from xuanke;
+------+------+------+--------+
| sid  | tid  | cid  | xuefen |
+------+------+------+--------+
|    1 | 1113 |    2 |      2 |
|    1 | 1114 |    1 |      4 |
|    1 | 1113 |    3 |      6 |
|    2 | 1113 |    2 |      2 |
|    2 | 1114 |    1 |      2 |
|    2 | 1113 |    3 |      2 |
+------+------+------+--------+
6 rows in set (0.00 sec)

1、内连接inner join

　　只返回两张表中所有满足连接条件的行，即使用比较运算符根据每个表中共有的列的值匹配两个表中的行。(inner关键字是可省略的)

①传统的连接写法：

　　在FROM子句中列出所有要连接的表的名字（进行表别名），以逗号分隔；

　　连接条件写在WHERE子句中；

注意：一旦给表定义了别名，那么原始的表名就不能在出现在该语句的其它子句中

mysql> select s.sname,c.cname,t.tname,x.xuefen
    -> from stu s,tea t,course c,xuanke x
    -> where s.sid=x.sid and t.tid=x.tid and c.cid=x.cid;
+--------+--------+-----------+--------+
| sname  | cname  | tname     | xuefen |
+--------+--------+-----------+--------+
| 张三    | linux  | 冯老师     |      4 |
| 李四    | linux  | 冯老师     |      2 |
| 张三    | mysql  | 相老师     |      2 |
| 李四    | mysql  | 相老师     |      2 |
| 张三    | hadoop | 相老师     |      6 |
| 李四    | hadoop | 相老师     |      2 |
+--------+--------+-----------+--------+
6 rows in set (0.08 sec)

 ②使用on子句(常用)：笔者比较喜欢的方法，因为觉得结构清晰明了。

mysql> select s.sname,t.tname,c.cname,x.xuefen
    -> from stu s
    -> join xuanke x
    -> 　　on s.sid=x.sid
    -> join tea t
    -> 　　on x.tid=t.tid
    -> join course c
    -> 　　on c.cid=x.cid;

结果如上……

表之间的关系以JOIN指定，ON的条件与WHERE条件相同。

 ③使用using子句

mysql> select s.sname,t.tname,c.cname,x.xuefen
    -> from stu s
    -> join xuanke x
    -> 　　using(sid)
    -> join tea t
    -> 　　using(tid)
    -> join course c
　　 -> 　　using(cid);

结果如上……

表之间的关系以join指定，using(连接列)进行连接匹配，类似于on。（相对用的会比较少） 

2、外连接outer join

　　使用外连接不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。

在MySQL数据库中外连接分两类(不支持全外连接)：

　　左外连接、右外连接。(outer关键字可省略)。

共同点：都返回符合连接条件和查询条件（即：内连接）的数据行

不同点：

　　①左外连接还返回左表中不符合连接条件，但符合查询条件的数据行。(所谓左表，就是写在left join关键字左边的表)

　　②右外连接还返回右表中不符合连接条件，但符合查询条件的数据行。(所谓右表，就是写在right join关键字右边的表)

mysql> select s.sname,x.xuefen
    -> from stu s
    -> left join xuanke x
    -> on s.sid=x.sid;
+--------+--------+
| sname  | xuefen |
+--------+--------+
| 张三   |      2  |
| 张三   |      4  |
| 张三   |      6  |
| 李四   |      2  |
| 李四   |      2  |
| 李四   |      2  |
| 王五   |   NULL  |
+--------+--------+
7 rows in set (0.00 sec)

解析：stu表是左表，xuanke表是右表：left join是左连接，stu表中”王五”没有选课，在xueke表中没有数据行，不符合连接条件，返回符合查询条件的数据行，所以xuefen为null。

mysql> select s.sname,x.xuefen
    -> from xuanke x
    -> right join stu s
    -> on x.sid=s.sid;

结果如上(用的是右连接的方式)

给连接查询附加条件：

　　1、写在WHERE子句中

　　2、使用AND和连接条件写在一起

！！！但是：

　　对于内连接，两种写法结果相同；

　　对于外连接，两种写法结果不同。

mysql> select s.sname,x.xuefen
    -> from stu s
    -> left join xuanke x
    -> on x.sid=s.sid
    -> 　　where sname='张三';
+--------+--------+
| sname  | xuefen |
+--------+--------+
| 张三    |      2 |
| 张三    |      4 |
| 张三    |      6 |
+--------+--------+
3 rows in set (0.01 sec)

mysql> select s.sname,x.xuefen                                                                 
    -> from (select * from stu where sname='张三') s
    -> left join xuanke x
    -> on x.sid=s.sid;
+--------+--------+
| sname  | xuefen |
+--------+--------+
| 张三    |      2 |
| 张三    |      4 |
| 张三    |      6 |
+--------+--------+
3 rows in set (0.00 sec)

①先连接后过滤

　　select ……from ……

　　left join ……

　　on 连接条件

　　　　where 过滤条件;

②先过滤后连接

　　select ……from (select ……from ……where 过滤条件)

　　left join ……

　　on 连接条件;

3、交叉连接—笛卡尔积

　　因为没有连接条件，所进行的表与表间的所有行的连接。

特点：

　　①连接查询没有写任何连接条件

　　②结果集中的总行数就是两张表中总行数的乘积(笛卡尔积)

注意：在实际中，应该要避免产生笛卡尔积的连接，特别是对于大表

mysql> select * from stu,tea,course,xuanke;
　　……
　　……
108 rows in set (0.00 sec)

若是想专门产生笛卡尔积，可以使用交叉连接

mysql> select *
    -> from stu
    -> crosss join tea;
+------+--------+---------+------+-----------+---------+
| sid  | sname  | sphonum | tid  | tname     | tphonum |
+------+--------+---------+------+-----------+---------+
|    1 | 张三    |     110 | 1113 | 相老师     |    1111 |
|    1 | 张三    |     110 | 1114 | 冯老师     |    1112 |
|    2 | 李四    |     120 | 1113 | 相老师     |    1111 |
|    2 | 李四    |     120 | 1114 | 冯老师     |    1112 |
|    3 | 王五    |     130 | 1113 | 相老师     |    1111 |
|    3 | 王五    |     130 | 1114 | 冯老师     |    1112 |
+------+--------+---------+------+-----------+---------+
6 rows in set (0.00 sec)

2、介绍2

1、内联接（典型的联接运算，使用像 =  或 <> 之类的比较运算符）。包括相等联接和自然联接。     
        内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。   
    
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     
        1）LEFT  JOIN或LEFT OUTER JOIN     
                左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       
        

        2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN     
                右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       

        3）FULL  JOIN 或 FULL OUTER JOIN
                完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   

3、交叉联接   
        交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    
        FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。     

例子：   

 1） 内连接   

  2）左连接   

 3） 右连接   

 4） 完全连接   

3、介绍3

一、交叉连接（CROSS JOIN）
        交叉连接（CROSS JOIN）：有两种，显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积。

例如：下面的语句1和语句2的结果是相同的。

语句1：隐式的交叉连接，没有CROSS JOIN。
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1

语句2：显式的交叉连接，使用CROSS JOIN。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1
 

二、内连接（INNER JOIN）
        内连接（INNER JOIN）：有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表）。

例如：下面的语句3和语句4的结果是相同的。

语句3：隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C,ORDERS O
WHERE C.ID=O.CUSTOMER_ID

语句4：显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROM CUSTOMERS C
INNER JOIN ORDERS O
ON C.ID=O.CUSTOMER_ID

三、外连接（OUTER JOIN）：

        外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
        

        三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：
                左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
                右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
                全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。

        说明：左表就是在“（LEFT OUTER JOIN）”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中，OUTER 关键字是可省略的。

下面举例说明：
语句5：左外连接（LEFT OUTER JOIN）
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
LEFT OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID

语句6：右外连接（RIGHT OUTER JOIN）
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
RIGHT OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID

注意：WHERE条件放在ON后面查询的结果是不一样的。

语句7：WHERE条件独立。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
LEFT OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID
WHERE O.ORDER_NUMBER<>'MIKE_ORDER001';

语句8：将语句7中的WHERE条件放到ON后面。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
LEFT OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER<>'MIKE_ORDER001';

从语句7和语句8查询的结果来看，显然是不相同的，语句8显示的结果是难以理解的。因此，推荐在写连接查询的时候，ON后面只跟连接条件，而对中间表限制的条件都写到WHERE子句中。

左外连接：

where：

on：

这样理解：on是通过笛卡尔积形成中间表的筛选条件，where是中间表生成最终表的筛选条件。

对于on中的MIKE_ORDER001，在形成中间表的过程，认为对于这一行，右表中没有符合要求的行，MIKE_ORDER001左外连接右表为空。

语句9：全外连接（FULL OUTER JOIN）。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
FULL OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID;
注意：MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。下图是上面SQL在Oracle下执行的结果：

语句10：左外和右外的合集，实际上查询结果和语句9是相同的。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
LEFT OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID

UNION

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
RIGHT OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID;
 

四、联合连接（UNION JOIN）：这是一种很少见的连接方式。Oracle、MySQL均不支持，其作用是：找出全外连接和内连接之间差异的所有行。这在数据分析中排错中比较常用。也可以利用数据库的集合操作来实现此功能。
语句11：联合查询（UNION JOIN）例句，还没有找到能执行的SQL环境。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
UNION JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID

语句12：语句11在DB2下的等价实现。还不知道DB2是否支持语句11呢！
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
FULL OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID
EXCEPT
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
INNER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID

语句13：语句11在Oracle下的等价实现。
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
FULL OUTER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID
MINUS
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROM ORDERS O
INNER JOIN CUSTOMERS C
ON C.ID=O.CUSTOMER_ID;
 

五、自然连接（NATURAL INNER JOIN）：说真的，这种连接查询没有存在的价值，既然是SQL2标准中定义的，就给出个例子看看吧。自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。对于每种连接类型（除了交叉连接外），均可指定NATURAL。下面给出几个例子。
语句14：
SELECT *
FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;

语句15：
SELECT *
FROM ORDERS O NATURAL LEFT OUTER JOIN CUSTOMERS C;

语句16：
SELECT *
FROM ORDERS O NATURAL RIGHT OUTER JOIN CUSTOMERS C;

语句17：
SELECT *
FROM ORDERS O NATURAL FULL OUTER JOIN CUSTOMERS C;

六、SQL查询的基本原理：两种情况介绍。
第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果。

第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。

七、ON后面的条件（ON条件）和WHERE条件的区别：
ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。

从这里可以看出，将WHERE条件移入ON后面是不恰当的。推荐的做法是：ON只进行连接操作，WHERE只过滤中间表的记录。

八、总结
连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。下面总结一下两表连接查询选择方式的依据：
        1、 查两表关联列相等的数据用内连接。
        2、 Col_L是Col_R的子集时用右外连接。
        3、 Col_R是Col_L的子集时用左外连接。
        4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
        5、 求差操作的时候用联合查询。

多个表查询的时候，这些不同的连接类型可以写到一块。例如：
SELECT T1.C1,T2.CX,T3.CY
FROM TAB1 T1
       INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
       INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
       LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
WHERE T1.X >T3.Y;
上面这个SQL查询是多表连接的一个示范。

二十八、常用SQL-->group by与having 、explain 等等

（1）explain

        在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

概要描述：
        id:选择标识符
        select_type:表示查询的类型。
        table:输出结果集的表
        partitions:匹配的分区
        type:表示表的连接类型
        possible_keys:表示查询时，可能使用的索引
        key:表示实际使用的索引
        key_len:索引字段的长度
        ref:列与索引的比较
        rows:扫描出的行数(估算的行数)
        filtered:按表条件过滤的行百分比
        Extra:执行情况的描述和说明

下面对这些字段出现的可能进行解释：

一、 id

        SELECT识别符。这是SELECT的查询序列号

        我的理解是SQL执行的顺序的标识，SQL从大到小的执行

                1. id相同时，执行顺序由上至下

                2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

                3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

-- 查看在研发部并且名字以Jef开头的员工，经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';

二、select_type

      示查询中每个select子句的类型

                (1) SIMPLE(简单SELECT，不使用UNION或子查询等)

                (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

                (3) UNION(UNION中的第二个或后面的SELECT语句)

                (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

                (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

                (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

                (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

                (8) DERIVED(派生表的SELECT, FROM子句的子查询)

                (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

三、table

        显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

四、type

        对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

        常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

        ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

        index: Full Index Scan，index与ALL区别为index类型只遍历索引树

        range:只检索给定范围的行，使用一个索引来选择行

        ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

        eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

        const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

        NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

五、possible_keys

        指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

        该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
        如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key

        key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

        如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

七、key_len

        表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

        不损失精确性的情况下，长度越短越好 

八、ref

        列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

九、rows

        估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

十、Extra

        该列包含MySQL解决查询的详细信息,有以下几种情况：

        Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

        Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

        Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

-- 测试Extra的filesort
explain select * from emp order by name;

        Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

        Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

        Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

        No tables used：Query语句中使用from dual 或不含任何from子句

-- explain select now() from dual;

总结：
        • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
        • EXPLAIN不考虑各种Cache
        • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
        • 部分统计信息是估算的，并非精确值
        • EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

通过收集统计信息不可能存在结果

（2）group by

1、group by + group_concat()的栗子

group_concat()可以将分组后每个组内的值都显示出来

group_concat()此函数返回一个字符串，是查询结果集合中指定列非NULL值的串联。如果所有列都是NULL，则此函数返回NULL。完整语法如下：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

GROUP_CONCAT 是一个聚合函数，通常跟 GROUP BY 一起使用。

2、group by +聚合函数的栗子

有什么聚合函数？

• count()：统计记录的条数
• sum()：字段值的总和
• max()：字段值的最大值
• min()：字段值的最小值
• avg()：字段值的平均值

3、group by + with rollup的栗子

with rollup用来在所有记录的最后加上一条记录，显示上面所有记录每个字段的总和，通过一个例子来说明把。

表中数据有：

mysql> select * from age;
+------+-----------+------+
| sno  | sname     | sage |
+------+-----------+------+
| 1101 | justcode1 |   20 |
| 1102 | justcode2 |   21 |
| 1103 | justcode3 |   22 |
| 1104 | justcode4 |   20 |
| 1105 | justcode5 |   21 |
| 1106 | justcode6 |   21 |
| 1107 | justcode7 |   22 |
| 1108 | justcode8 |   22 |
+------+-----------+------+
8 rows in set (0.00 sec)
--------------------- 

没有with rollup的查询:

mysql> select count(*),sage from age group by sage;
+----------+------+
| count(*) | sage |
+----------+------+
|        2 |   20 |
|        3 |   21 |
|        3 |   22 |
+----------+------+
3 rows in set (0.00 sec)
--------------------- 

带with rollup的查询：

mysql> select count(*),sage from age group by sage with rollup;
+----------+------+
| count(*) | sage |
+----------+------+
|        2 |   20 |
|        3 |   21 |
|        3 |   22 |
|        8 | NULL |
+----------+------+
4 rows in set (0.00 sec)
--------------------- 

with rollup 、with cube、grouping

CUBE 和 ROLLUP 之间的区别在于：

        CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

        ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

        grouping: 当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。

3、mysql 统计 group by 之后的 group 的个数

        如果将 count(*) 和 group by 一起使用，count(*) 统计的将会是每个 group 里面的行数，而不是 group 的个数。

        如果你想统计 group 的个数，需要将 group by 查询放到子查询里面，然后在主查询里面再使用 count(*)。

如下所示，第一个查询的 count(*) 统计的只是特定的 Category 和 Year 下的总行数，而第二个查询才是第一个查询的 group 的个数。

mysql> SELECT tag AS Category, YEAR(created) AS Year,
   COUNT(*) AS Counts FROM fyi_links GROUP BY tag,
   YEAR(created);
+----------+------+--------+
| Category | Year | Counts |
+----------+------+--------+
| DBA      | 2005 |      1 |
| DBA      | 2006 |      2 |
| DEV      | 2004 |      1 |
| DEV      | 2006 |      1 |
| SQA      | 2003 |      1 |
| SQA      | 2006 |      1 |
+----------+------+--------+
6 rows in set (0.00 sec)
 
mysql> SELECT COUNT(*) FROM (
   SELECT tag AS Category, YEAR(created) AS Year,
   COUNT(*) AS Counts FROM fyi_links GROUP BY tag,
   YEAR(created) ) groups;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

附加：

（1）提交读和可重复读的实现原理

事务ACID回顾
        InnoDB引擎下，具备事务功能，事务具备ACID（原子性、一致性、隔离性、持久性），一致性其实是目的，由原子性、隔离性和持久性共同来保证！原子性是由undo log来进行保证的（回滚的时候采用undo log），持久性由InnoDB的redo log、undo log、 binlog来保证，而隔离性指的是它有四个隔离级别，分别是：

        读未提交
        读提交
        可重复读
        串行化

        其中我们用的比较多的是 读提交（RC） 和 可重复读（RR），下面来详细介绍一下他们是如何通过MVCC多版本并发控制实现的。

MVCC
        在MySQL InnoDB存储引擎下,RC、RR基于MVCC（多版本并发控制）进行并发事务控制

        MVCC是基于”数据版本”对并发事务进行访问

下面举一个例子来说明实现原理：

         现在有三个事务，事务id分别是 trx_id = 1、2、3、4；前面三个事务都对张三这个人做了name的更新并且提交，事务4就是在两个时间段去做了“读”操作，我们先来看在“读提交”的隔离级别下，事务4的两次读操作会读出什么结果呢？

        如果是RC级别，那么 select1 = 张三，select2=张小三，在图中可以看出这个时序关系。

        如果是RR级别，那么select1= 张三，select2=张三，两次结果相同。

下面来分析底层实现原理：

undo log版本链
先来了解一下undo log版本链，他用链的形式存储了数据的变化：

其中trx_id代表导致当前数据版本的事务id，DB_ROLL_PTR储存着上一个数据版本的数据地址

这里补充一个undo log回滚的过程：

如果要回滚，那么就得

        比如插入一条记录，得把这个记录的id记录下来，回滚的时候直接删掉这个id即可。

        删除记录则要把记录的内容保留，回滚的时候插入即可。

        修改的时候则把旧值记录，回滚时直接把旧值写入。

问：

undo log不是会被删除吗？中间数据万一被删了版本链不就断了吗？

undo log版本链不是立即删除，mysql确保版本链数据不再被“引用”后再进行删除！

ReadView
ReadView就是读视图，

ReadView是“快照读”SQL执行时MVCC提取数据的依据.

快照读就是最普通的Select查询SQL语句

当前读指代执行下列语句时进行数据读取的方式（比如插入或者删除数据，必须是最新的表的状态下去做变更操作！）

Insert、Update、Delete、

Select…for update

Select…lock in share mode

只有当快照读的时候才会用到MVCC

ReadView的数据结构：

ReadView是一个数据结构，包含4个字段

m_ids:当前活跃的事务编号集合
min_trx_id:最小活跃事务编号
max_trx_id:预分配事务编号，当前最大事务编号+1
creator_trx_id:ReadView创建者的事务编号

读已提交（RC）下
读已提交（RC）：在每执行一次快照读的时候，都去生成一个ReadView，所以两次生成了两个不同的ReadView读视图

分析： 

        在事务4的第一个select语句执行时，生成了一个ReadVIew，它是用来判断读取undo log版本链中具体哪一个数据版本的，根据右侧的规则一步步进行判断即可，

举例：

        先对TRX_ID = 3进行判断：

        从undo log的最新一个版本（TRX-ID = 3）开始遍历，当前的事务id为3，不等于creator_id，也就是说，select的事务和这个数据版本不是同一个事务，那就继续向下判断（如果相等，说明select事务之前可能做了一个更新操作，之后才select 是同一个事务中的前后操作 所以肯定是可以读的！）

        继续判断：当前的事务id和min_trx_id相比，比min要大，所以还得继续判断（如果确实比min小，那么说明是在最小活跃事务之前数据提交得，是可以访问的）

        继续判断：当前事务id和max_trx_id相比，比max要小，所以还得继续判断（如果比max大，说明这个数据版本在的事务是在ReadView生成之后才开启的，不允许访问）

        继续判断：如果当前事务在min之后和max之前，就是最小活跃事务和最大活跃事务之间，并且还要判断当前事务存在于活跃事务中（m_ids：记录了还有哪些事务没有被提交），如果不在活跃事务中也就是提交了，那就可以访问

先对TRX_ID = 2进行判断：

…

先对TRX_ID = 1进行判断：

满足条件 trx_id < min_trx_id(2) 成立，说明在最小活跃事务之前就提交了已经，可以访问！

可重复读（RR）下
仅在第一次执行快照读时生成ReadView，后续快照读复用前面的ReadVIew

RR级别下使用MVCC能避免幻读吗？

能，但不完全能！

连续多次快照读，ReadView会产生复用，没有幻读问题
当两次快照读之间存在当前读，ReadView会重新生成，导致产生幻读

举例：

        在事务B中两次select快照读的中间有一个 更新语句（当前读），这种情况下，第二次的快照读生成的ReadVIew就会重新生成，而不是复用，否则在同一个事务中前后就会造成不一致！
 

MySQL默认的隔离级别是可重复读，即：事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。 那么MySQL可重复读是如何实现的呢？

使用的的一种叫MVCC的控制方式 ，即Mutil-Version Concurrency Control,多版本并发控制，类似于乐观锁的一种实现方式

实现方式：

InnoDB在每行记录后面保存两个隐藏的列来，分别保存了这个行的创建时间和行的删除时间。这里存储的并不是实际的时间值,而是系统版本号，当数据被修改时，版本号加1
在读取事务开始时，系统会给当前读事务一个版本号，事务会读取版本号<=当前版本号的数据
此时如果其他写事务修改了这条数据，那么这条数据的版本号就会加1，从而比当前读事务的版本号高，读事务自然而然的就读不到更新后的数据了

MySQL中隔离级别分为4种，提未交读、读已提交、可重复读、串行化。同时MySQL默认隔离级别为可重复读。

MVCC版本控制
如果表中数据如如下所示，同时隔离级别为可重复读那么按照下面的时间进行执行，此时你觉得事务A和事务B查询的结果会是什么呢？

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
 
insert into t(id, k) values(1,1),(2,2)

 
答案是在事务B中查询的结果为3，而事务A中为1，或许很好理解事务A的值为什么是1，但是却并不好理解事务B为什么是3,这要从MySQL的MVCC开始说起

版本控制链

        首先在Innodb中每一个事务都有一个事务ID，只要事务启动就会存在一个事务ID，叫作 transaction id。而且事务ID是按照一定规律进行递增的，当我们对某一行数据进行更新操作时实际上会将当前的事务ID，做一个记录，这个记录存在于MySQL的隐藏列中，也就是row trx_id。同时会产生一个undo log的指针来指向上一次的数据。

        例如现在将表中id为1的数据的k修改为2，且当前事务ID为99，同时在版本控制链中，上一次这一行的数据是被事务id为98的进行插入的，那么这一行数据实际上的修改过程如下。

 这里98版本k应该是1

        可以看到，当执行更新操作后，实际上会在版本控制链中进行一个记录，可以理解为将原来的数据进行拷贝一份，同时现在用row_trx_id（6tyte）记录当前事务id，同时用DB_ROLL_PTR（7byte回滚指针），来指向上一个数据。也就是说每一行数据实际上会存在多个版本，同时每个版本都有自己的row_trx_id。

read view

        read view实际上就是一个数组，在可重复读隔离级别下，事务启动的时候就会产生一个read view直到事务结束。

        read view中存放的是当前活跃事务id，也就是当前还没有提交的事务id，如下图所示，假如在事务之间还存在一个活跃事务id为50,事务A的事务Id为51，事务B为52,事务C为53。那么事务A的readview为[50,51]，事务B为[50,51,52]，事务C为[50,51,52,53]。

高低水位

        read view中最小事务id为低水位，而当前系统已经创建过的事务Id最大值加1，记作高水位。例如在事务A启动时由于read view为[50,51]那么高水位就是52,低水位为50,而事务B启动时由于read view为[50,51,52]那么高水位就是53，低水位为50。而之所以在可重读级别下能够始终看到的数据都和启动时候看的是一致的，原因就是因为高低水位加上一个当前事务id以及一个比对结果。

高低水位比对规则

如果row trx_id 等于当前事务id，则说明修改是由当前事务修改，可以读取。

1.如果row trx_id小于低水位落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

2.如果row trx_id大于等于高水位落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

3.如果落在黄色部分，那就包括两种情况

        3.1 若row trx_id在read view中，表示这个版本是由还没提交的事务生成的，不可见。

        3.2 若row trx_id不在read view中，表示这个版本是已经提交了的事务生成的，可见。

根据MVCC分析不同事务k的结果

        前面说过在如下所示的执行结果中，事务B查询的k为3，事务A查询的结果为1,我们通过MVCC进行分析一下为什么是这样。 通过前面的建表可以知道此时的id为1的k实际数据为1，假设在事务A之前还存在一个事务同时事务id为50，事务A的事务id为51,事务B的事务id为52，事务C的事务id为53。

前面说过在如下所示的执行结果中，事务B查询的k为3，事务A查询的结果为1,我们通过MVCC进行分析一下为什么是这样。

        通过前面的建表可以知道此时的id为1的k实际数据为1，假设在事务A之前还存在一个事务同时事务id为50，事务A的事务id为51,事务B的事务id为52，事务C的事务id为53。

        如下所示，此时在事务A中需要查询数据，然后在对应的版本控制链中进行查找，首先事务A的read view为[50,51]，然后根据上面所说的比对规则，然后进行查询，查找到k为3时，此时row trx_id为52，此时也就是在黄色部分也就是说在将来发生的事务中，然后再次查找下一个，得到row trx_id为53，此时也比高水位大，所以也就是将来发生的事务，下图有误，我们将最后最后一条记录的row trx_id改为40，继续查找，然后再找到row trx_id 40的发现是已经提交的事务，因为小于低水位。所以事务A查询的结果就是1。

一致性读

        所谓的一致性读就是指在可重复读隔离级别下，事务启动时看到的数据无论其他事务怎么修改，自己看到的数据都是和启动时候看到的数据时一致的。

更新逻辑

        按照我们上面说的一致性读的话，此时如果按照上图所示，在事务B中查进行了一次更改操作，此时我们再次查询的时候应该是2而不是3，这是为什么呢？虽然事务C进行加1后变成了2，但是实际上事务B此时应该是看不到的，所以在事务B中应该是2，为什么就是3呢？

        实际上在更新的时候采用了当前读的机制，也就是读最新的数据，如果不读最新的数据，那么就会导致数据丢失。所以MySQL是在更新的时候先拿到最新的数据也就是(1，2)然后在(1,2)的基础上进行加1操作，同时记录row trx_id为52。

        在事务B查询时发现row trx_id 为52，是当前线程的id，则可以读。

读已提交和可重复读区别

        在MySQL中可重复读和读已提交都是通过MVCC进行实现的，却别在于可重读是事务启动的时候就生成read view整个事务结束都一直使用这个read view，而在读已提交中则是每执行一条语句就重新生成最新的read view。
 

（2）主键和索引的区别

1、应用范畴不同：

        主键属于索引的一种。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

2、种类不同：

        根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。而，主键只是其中的一种。

3、创建方式不同：

        当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。

        由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。经常在WHERE子句中的列上面创建索引。

1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。

2. 一个表中可以有多个唯一性索引，但只能有一个主键。

3. 主键列不允许空值，而唯一性索引列允许空值。

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

        1、主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
        2、唯一性索引列允许空值，而主键列不允许为空值。
        3、主键列在创建时，已经默认为空值 + 唯一索引了。
        4、主键可以被其他表引用为外键，而唯一索引不能。
        5、一个表最多只能创建一个主键，但可以创建多个唯一索引。
        6、主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
        7、在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。
 

（3）千万数据分页查询，与用limit分页的区别

1.直接使用用limit start, count分页语句：

select * from order limit start, count

当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000， 10000开始分页的执行时间（每页取20条）， 如下：

        我们已经看出随着起始记录的增加，时间也随着增大， 这说明分页语句limit跟起始页码是有很大关系的，那么我们把起始记录改为40w看下

select * from order limit 400000, 20 3.229秒

再看我们取最后一页记录的时间

select * from order limit 800000, 20 37.44秒

显然这种时间是无法忍受的。

从中我们也能总结出两件事情：

1）limit语句的查询时间与起始记录的位置成正比

2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

2.对limit分页问题的性能优化方法

        利用表的覆盖索引来加速分页查询

        我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

        因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

        在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：

这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：

select id from order limit 800000, 20 0.2秒

相对于查询了所有列的37.44秒，提升了大概100多倍的速度

那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

SELECT * FROM order WHERE ID > =(select id from order limit 800000, 1) limit 20

查询时间为0.2秒，简直是一个质的飞跃啊，哈哈

另一种写法

SELECT * FROM order a JOIN (select id from order limit 800000, 20) b ON a.ID = b.id

查询时间也很短。

一、limit用法
        在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。

SELECT * FROM table LIMIT [offset,] rows | `rows OFFSET offset ` 
(LIMIT offset, `length`)
 

SELECT
*
FROM table
where condition1 = 0
and condition2 = 0
and condition3 = -1
and condition4 = -1
order by id asc
LIMIT 2000 OFFSET 50000

        LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

        当offset特别大时，这条语句的执行效率会明显减低，而且效率是随着offset的增大而降低的。
 

因为：
        MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，当offset特别大，然后单条数据也很大的时候，每次查询需要获取的数据就越多，自然就会很慢。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目：
mysql> SELECT * FROM table LIMIT 5;//检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n。

另外，顺便一提：sql 中 limit 与 limit，offset连用的区别

① select * from table limit 2,1;                

//含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

② select * from table limit 2 offset 1;     

//含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2,3条
 

二、Mysql的分页查询语句的性能分析
        MySql分页sql语句，如果和MSSQL的TOP语法相比，那么MySQL的LIMIT语法要显得优雅了许多。使用它来分页是再自然不过的事情了。

（数据量较小的时候）

最基本的分页方式：

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

        在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引：
举例来说，如果实际SQL类似下面语句，那么在category_id, id两列上建立复合索引比较好：

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

（数据量很大，达到万级或百万级以上的大数据的时候）

子查询的分页方式：

随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10
一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

此时，我们可以通过子查询的方式来提高分页效率，大致如下：

SELECT * FROM articles WHERE id >= 
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10

JOIN分页方式

SELECT * FROM `content` AS t1 
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

经过我的测试，join分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。
 

explain SQL语句：

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index

        为什么会这样呢？因为子查询是在索引上完成的，而普通的查询是在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。

        实际可以利用类似策略设计模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。

三、对于有大数据量的mysql表来说，使用LIMIT分页存在很严重的性能问题。

查询从第1000000之后的30条记录：

SQL代码1：平均用时6.6秒 SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

SQL代码2：平均用时0.6秒 SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM 
`cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

        因为要取出所有字段内容，第一种需要跨越大量数据块并取出，而第二种基本通过直接根据索引字段定位后，才取出相应内容，效率自然大大提升。对limit的优化，不是直接使用limit，而是首先获取到offset的id，然后直接使用limit size来获取数据。

        可以看出，越往后分页，LIMIT语句的偏移量就会越大，两者速度差距也会越明显。

        实际应用中，可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。

四、优化思想：避免数据量大时扫描过多的记录

为了保证index索引列连续，可以为每个表加一个自增字段，并且加上索引

（4）主从复制

一、什么是主从复制?

        主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的业务数据库。您看，像在mysql数据库中，支持单项、异步赋值。在赋值过程中，一个服务器充当主服务器，而另外一台服务器充当从服务器。此时主服务器会将更新信息写入到一个特定的二进制文件中。并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。当一台从服务器连接到主服务器时，从服务器会通知主服务器从服务器的日志文件中读取最后一次成功更新的位置。然后从服务器会接收从哪个时刻起发生的任何更新，然后锁住并等到主服务器通知新的更新

二、主从复制的作用（好处，或者说为什么要做主从）重点？

        做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
        架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。
        读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

         1--在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）
         2--在从主服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）
         3--当主服务器出现问题时，可以切换到从服务器。（提升性能）

三、主从复制的原理？

        1.数据库有个bin-log二进制文件，记录了所有sql语句。

        2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

        3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

        4.下面的主从配置就是围绕这个原理配置

        5.具体需要三个线程来操作：

                1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建两个线程进行处理：

                2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

        3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

        可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。

主从复制如图  帮助理解：

四、主从复制的好处？

        做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
        架构的扩展。业务量越来越大,I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的评率，提高单个机器的I/O性能。
        读写分离，使数据库能支持更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

好处一:实现服务器负载均衡

        通过服务器复制功能，可以在主服务器和从服务器之间实现负载均衡。即可以通过在主服务器和从服务器之间切分处理客户查询的负荷，从而得到更好地客户相应时间。通常情况下，数据库管理员会有两种思路。

        一是在主服务器上只实现数据的更新操作。包括数据记录的更新、删除、新建等等作业。而不关心数据的查询作业。数据库管理员将数据的查询请求全部 转发到从服务器中。这在某些应用中会比较有用。如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，即更新的用户比较少。而查询的用户数 量会非常的多。此时就可以设置一台主服务器，专门用来数据的更新。同时设置多台从服务器，用来负责用户信息的查询。将数据更新与查询分别放在不同的服务器 上进行，即可以提高数据的安全性，同时也缩短应用程序的响应时间、提高系统的性能。

        二是在主服务器上与从服务器切分查询的作业。在这种思路下，主服务器不单单要完成数据的更新、删除、插入等作业，同时也需要负担一部分查询作 业。而从服务器的话，只负责数据的查询。当主服务器比较忙时，部分查询请求会自动发送到从服务器重，以降低主服务器的工作负荷。当然，像修改数据、插入数 据、删除数据等语句仍然会发送到主服务器中，以便主服务器和从服务器数据的同步。

好处二：通过复制实现数据的异地备份

        可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份 作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

        而如果使用复制来实现对数据的备份，就可以在从服务器上对数据进行备份。此时不仅不会干扰主服务气的正常运行，而且在备份过程中主服务器可以继 续处理相关的更新作业。同时在数据复制的同时，也实现了对数据的异地备份。除非主服务器和从服务器的两块硬盘同时损坏了，否则的话数据库管理员就可以在最 短时间内恢复数据，减少企业的由此带来的损失。

好处三：提高数据库系统的可用性

        数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员也会采用两种手段。

        一是主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了一些限制。如仍然只能够进行数据的查询，而 不能够进行数据的更新、删除等操作。这主要是从数据的安全性考虑。如现在一些银行系统的升级，在升级的过程中，只能够查询余额而不能够取钱。这是同样的道理。

        二是从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等操 作。为此就需要做好数据的安全性工作。即数据的安全策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患。

五、从数据库的读的延迟问题了解吗？如何解决？

主库宕机后，数据可能丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

        半同步复制—解决数据丢失的问题
        并行复制—-解决从库复制延迟的问题（多个SQL程序并行执行）

异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

-  对于异步复制，主库将事务Binlog事件写入到Binlog文件中，此时主库只会通知一下Dump线程发送这些新的Binlog，然后主库就会继续处理提交操作，而此时不会保证这些Binlog传到任何一个从库节点上。

-  对于全同步复制，当主库提交事务之后，所有的从库节点必须收到，APPLY并且提交这些事务，然后主库线程才能继续做后续操作。这里面有一个很明显的缺点就是，主库完成一个事务的时间被拉长，性能降低。

-  对于半同步复制，是介于全同步复制和异步复制之间的一种，主库只需要等待至少一个从库节点收到并且Flush Binlog到Relay Log文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全执行并且提交的反馈，这样就节省了很多时间。

半同步复制的潜在问题
客户端事务在存储引擎层提交后，在得到从库确认的过程中，主库宕机了，此时，可能的情况有两种
-  事务还没发送到从库上
此时，客户端会收到事务提交失败的信息，客户端会重新提交该事务到新的主上，当宕机的主库重新启动后，以从库的身份重新加入到该主从结构中，会发现，该事务在从库中被提交了两次，一次是之前作为主的时候，一次是被新主同步过来的。
-  事务已经发送到从库上
此时，从库已经收到并应用了该事务，但是客户端仍然会收到事务提交失败的信息，重新提交该事务到新的主上。

无数据丢失的半同步复制
针对上述潜在问题，MySQL 5.7引入了一种新的半同步方案：Loss-Less半同步复制。针对上面这个图，"Waiting Slave dump"被调整到"Storage Commit"之前。当然，之前的半同步方案同样支持，MySQL 5.7.2引入了一个新的参数进行控制: rpl_semi_sync_master_wait_point, 这个参数有两种取值：1) AFTER_SYNC , 这个是新的半同步方案，Waiting Slave dump在Storage Commit之前。2) AFTER_COMMIT, 这个是老的半同步方案。

主从复制和主主复制区别？

        最大区别是 主从是对主操作数据，从会实时同步数据。反之对从操作，主不会同步数据，还有可能造成数据紊乱，导致主从失效。 主主则是无论对那一台操作，另一个都会同步数据。一般用作高容灾方案 

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：

        在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题。

Mysql主从复制的实现原理图大致如下(来源网络)：

        MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

实现MySQL主从复制需要进行的配置：

• 主服务器：
  • 开启二进制日志
  • 配置唯一的server-id
  • 获得master二进制日志文件名及位置
  • 创建一个用于slave和master通信的用户账号
• 从服务器：
  • 配置唯一的server-id
  • 使用master分配的用户账号读取master二进制日志
  • 启用slave服务

--------------------------------------------------------------------------------------------------------------------------------

二十九、Redis IO多路复用

三十、简单介绍Redis、适用场景、常见功能、支持数据类型

三十一、Redis 和 Memcached 区别和共同点

三十二、Redis过期数据删除策略

三十三、Redis内存淘汰机制

三十四、Redis持久化机制

三十五、缓存穿透

三十六、缓存雪崩

三十七、保证缓存和数据库数据一致性

三十八、在生成RDB期间，Redis可以同时处理写请求

三十九、缓存预热

四十、缓存降级

四十一、主从复制