爬虫实战11—分布式数据库架构分析、优化及要点

文章说明：本文是在学习一个网络爬虫课程时所做笔记，文章如有不对的地方，欢迎指出，积极讨论。

一、数据库常见概念

（一）锁

1.表级锁：表锁是开销最小的锁策略，或锁定整张被访问到的表。写之前要获得写锁，会阻塞其它所有的读写操作；读锁属于共享锁，读相互之间不阻塞，也就是说在排队序列中，写的操作会被插入到读之前。

2.行级锁：行锁可以最大限度支持并发处理，但同时增大了锁开销。行级锁只在存储层实现。

（二）事务

1.Atomicity：原子性。一个事务被视为一个不可分割的最小单元。

2.Consistency：一致性。数据库总是从一个一致性状态转换到另一个一致性状态，例如执行过程崩溃，数据库的状态并不会发生变化。

3.Isolation：隔离性。一个事务所做的修改在最终提交以前，对其他事务是不可见的。

4.Durability：持久性。一旦事务提交，则其所有的修改就会永久保存到数据库，此时即使数据库系统崩溃，数据也不会丢失。

MongoDB不完全保证ACID。Redis不支持原子性，带来安全问题。

（三）死锁

指的是多个事务在同一资源上相互占用，并请求锁定对方占用的资源。

（四）AUTOCOMMIT

MySQL默认采用自动提交，也就是默认把每个没有显示声明为事务的查询，当成一个一个独立的事务执行提交。

SHOW VARIABLES LIKE  ‘AUTOCOMMIT’

如果设置为AUTOCOMMIT = 0，那么所有查询都在同一个事务中，必须使用commit提交或者rollback回滚。

二、MySQL的数据存储结构

（一）系统框架及InnoDB存储框架

InnoDB存储框架

（1）一张表存储在一个或多个文件里

（2）表包含多个segment，索引、数据、回滚记录等都是独立的segment

（3）每个segment包含多个Extent

（4）Extent包含64个Page

（5）每个page包含若干Row

（6）每个Row包含了数据域

（二）InnoDB

（1）主键索引既存储索引值，又在叶子中存储行的数据

（2）如果没有主键，则会unique key 做主键

（3）如果没有unique，则系统生成一个内部的rowid做主键 （用户不可见）

（4）次索引需要同时存主键ID

（5）像InnoDB中，主键的索引结果中，既存储了主键值，又存储了行数据，这种结构称为“聚簇索引”

（6）对主键查询有极高的性能，但是二级索引必须包含主键列，因此如果主键列很大，其它的索引都会很大

（7）支持事务，行级锁，颗粒度小，并发好，但是加锁过程更复杂（多个查询请求过来，可以并发查询）

（8）崩溃后可以安全恢复（通过日志恢复）

InnoDB的兼容性比Myisam差。

（三）Myisam

（1）不支持事务，不能安全恢复  （最大的问题，做交易时，一旦交易出错，就会导致记录不一致）

（2）表级锁，读的时候所有读到的表加共享锁，写的时候加排他锁

（3）加锁效率高，并发支持效率低

（4）支持前文索引（基于分词），可以支持复杂字段

（5）可以延迟更新索引键，如何设置了delay_key_write，每次修改完成时不会立即将修改的索引数据写入磁盘，而是会写入缓冲区，因此能提高性能，但是崩溃的时候索引会被损坏

（6）设计简单，数据紧密格式存储，某些场景下性能很好

（四）选择合适引擎

（1）默认应选择InnoDB

（2）一般不应使用混合引擎存储 （理论上可以使用混合引擎，但是因为前面整个查询过程导致数据库复杂性增加，在单机里用是不好的，但在集群里使用比较快）

（3）如果需要事务，InnoDB是最稳定的；如果主要是SELECT或者INSERT，那么Myisam也许更合适，例如日志类型的应用

（4）需要热备份的话，应该用InnoDB  （关机备份没区别）

（5）重要数据应该InnoDB存储，Myisam崩溃后损坏概率比InnoDB大得多

（6）大多数情况下，InnoDB远远比Myisam要快

（7）只读的表，优先考虑Myisam，会快很多

（五）CHAR / VARCHAR

（1）CHAR会分配固定长度的空间，使得结构更加固定，尤其是在update的时候，没有额外的开销，对于很长的字符串，用CHAR会造成空间的浪费

（2）VARCHAR可以分配变长的空间，因为空间更加紧凑，根据实际需要来，但是当一个页满的情况下，update更新会造成很大的负担，Myisam会把行拆成不同的片段拆成，而InnoDB则会分页

（3）VARCHAR(5)比VARCHAR(200)：如果都存储hello这个字符串，空间开销一样，但是更长的列会消耗更多的内存，尽量按照实际需求来设计

（六）Schema设计要点

1.避免太多的列：存储引擎API需要在服务器层与存储引擎层通过行缓冲格式拷贝数据，然后在服务器层讲缓冲内容解码成各个列。从行缓冲中奖编码过的列转换成行数据结构的代价是非常高的，它依赖列的数量

2.太多的关联：MySQL限制了最多关联61张表

3.避免使用NULL：对于索引列使用NULL，会带来存储以及大量优化问题 （优化器处理非常麻烦，但也不是绝对的，在InnoDB下使用NULL，情况也没有那么糟糕）

4.汇总表：把一些历史汇总数据，离线或定时汇总，最后总的结果是汇总结果加上当前的结果。例如个人的消费总额，可以把过去每个月的汇总，然后加上本月从1号到现在的sum，这样的计算量会极大的减小。代价是写的速度会变慢，每个人的数据都需要定期被更新，但是读的性能得到了极大的提升。在数据库设计的时候，类似的冗余数据、统计结果的缓存往往是必要的。

（七）B + Tree

B-Tree索引，用来排序、范围查找、全值或者前缀查找，只有Memory引擎支持HASH索引，但是我们按照爬虫的方式，把URL进行HASH计算后把HASH值存储，也类似于伪HASH索引。（把URLmd5。Redis特别适合。InnoDB本身不支持hash索引）

（八）多列索引

单列索引，多个单列索引进行查询的时候，效率是比较低的。

1.多列索引可以看成是把多个列拼接在一起后，再排序的数组，所以对于and过滤是有用的，而对于or过滤是无用的；

2.多列索引的顺序对最终的索引有影响，索引首先是按照最左列进行排序。

三、MySQL的查询过程

（一）查询流程

1.客户端发送一条查询给服务器；

2.服务器检查缓存，如果命中则直接返回；

3.服务器进行SQL解析，预处理；

4.交给优化器生成执行计划；

5.根据优化器生成的执行计划，调用存储引擎API执行查询；

6.结果返回

（二）通信协议

（1）半双工的方式通信，意味着任意时刻，要么服务端发送数据，要么服务端接收请求（双工意味着发送和接收可以同时进行）

（2）服务端必须接收完整请求，客户端也必须接收完整结构

（3）很多时候，客户端驱动会先读完数据，缓存到自己的缓存，然后应用层代码从本地驱动缓存读取，这样可能会导致性能降低，可以使用unbuffered_read来直接从服务端读取，这样能减少本地驱动层的内存缓存使用，针对特别大的数据结果集会有用

（三）查询缓存

1.缓存是通过一个大小敏感的HASH查找实现的，必须完整匹配

2.如果命中，仍然要检查权限，权限本身也是缓存的

3.如果都通过，会直接返回结果

（四）优化处理

1.对关键字做SQL解析，生成一棵“解析树”

2.优化器会尝试多种查询执行方式，来预测执行计划的成本，因此语句要尽量帮助正确预测，否则可能会使用最糟糕的查询方案来执行，有很多的因素，比如并发、统计信息错误、索引预判错误等，导致优化预判不准确

3.优化器是非常复杂的组件，一般会对关联表的顺序、排序方法、MIN()、MAX()、COUNT()等表达式做优化，比如Myisam维护了一个变量来存放表的行数，因此COUNT(*)就能直接返回，而不用一个一个去累加

错误的优化（bad case）：

1.SELECT * FROM film WHEREfilm_id IN (SELECT film_id FROM film_actor WHERE actor_id = 1)

当我们要查询多条数据时，会用到IN操作，我们认为的是会先执行子查询，而实际MySQL是相反的，会扫描表，然后每次调用子查询。考虑使用EXISTS或者关联查询。

2.SELECT MIN(actor_id)FROM actors WHERE first_name = “BECKHAM”

这样的扫描会全盘扫描，因为first_name没有建立索引，然后再取回最小的actor_id。

SELECT actor_idFROM actors USE INDEX(PRIMARY) WHERE first_name = “BECKHAM” LIMIT 1

因为actor_id是主键，默认是按照从小到大排序的，因此只要按照主键有限查询。

（五）执行及返回

1.执行阶段只是简单根据执行计划给出的指令逐步执行

2.如果查询结果可以被缓存，那么在执行完成后结果会放到查询缓存（更新后会消失，直到重新得到缓存）

3.执行过程中，临时数据集会放到临时结果表，例如关联查询得到的结果，会缓存到一个文件里，然后等到其它关联结果出来后，开始进一步合并、过滤

4.结果返回给客户端是一个增量的、逐步返回的过程，比如select被命中的结果，如果不需要排序，那么从命中的第一条结果就开始返回给客户端

四、深翻页及优化

（一）深翻页查询过程

数据库查询，会优先通过WHERE条件过滤，如果没有设置LIMIT参数，会全部扫描全部数据；一般情况，如果不是GROUP BY、SUM、COUNT这一类的聚合操作，当LIMIT限制达到后就立即返回不再查询，如果没有LIMIT限制，查询结果会被缓存下来，然后再写入一个缓存文件，然后会对这样的缓存文件再进行归并。

（二）深翻页优化

1.在查询的时候尽量避免这样的深翻页，一般人类的行为不会需要进行深翻页；

2.如果不得不做深翻页，比如skip前800个结果，尽量考虑增加冗余列，例如用冗余列来标记当前结果的ID好，然后通过where来帮助过滤，或者用时间来做偏移量的分割等，总之，使用参数来帮助过滤。