python爬虫进阶（十一）：分布式数据库架构分析、优化及要点

说明：本篇主要是概念性的东西，主要是课程内容。

一、数据库常见概念

1、锁

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

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

例如：InnoDB支持行级锁，更安全，myisam只支持表级锁。

2、事务

事务是数据库中的原子操作，不管要操作的数据在一个或多个数据库中。事务中所有语句的结果要么全部提交（操作正常），要么全部回滚（操作失败，不改变原来状态）。

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

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

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

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

3、死锁

死锁是指的多个事务在同一资源上相互占用，并请求锁定对方占用的资源，例如以下两个事务：

如果正好第一行被执行完，并导致行被锁，那么彼此第二行都无法运行。InnoDB有很强的机制来检测，但我们必须意识到这样的问题是可能出现的。

4、AUTOCOMMIT

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

show variables like ‘AUTOCOMMIT’

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

二、MySQL的数据存储结构

1、InnoDB存储框架

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

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

（3）每个segment包含多个extent

（4）extent包含64个page

（5）每个page包含若干row

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

2、InnoDB

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

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

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

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

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

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

（7）支持事务，行级锁，颗粒度小，并发好，但是加锁过程更复杂

（8）崩溃后可以安全恢复

3、myisam

（1）不支持事务，不能安全恢复

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

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

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

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

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

4、选择合适引擎

（1）默认应选择InnoDB

（2）一般不应使用混合引擎存储

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

（4）需要热备份的话，应该用InnoDB

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

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

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

5、char / varchar

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

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

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

6、Schema设计要点

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

（2）太多的关联：MySQL限制了最多关联为61张表

（3）避免使用null：对于索引列使用null，会带来存储以及大量优化问题

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

7、B+ Tree

8、多列索引

多列索引可以看成是把多个列拼接在一起后，再排序的数组。

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

多列索引的顺序对最终的索引有影响，索引首先是按照最左列进行排序（上面例子从左到右是lastname、firstname）。

三、MySQL的查询过程

1、查询流程

》》》客户端发送一条查询给服务器

》》》服务器检查缓存（第一次查询后都会缓存，除非数据更新），如果命中则直接返回

》》》服务器进行SQL解析，预处理

》》》交给优化器生成执行计划

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

》》》结果返回

2、通信协议

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

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

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

3、查询缓存

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

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

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

4、优化处理

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

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

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

5、错误的优化

当我们要查询多条数据时，会用到 in 操作，然而上面这句并不会先执行划线部分的子查询，而是先执行主查询，然后每次调用子查询。

6、执行及返回

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

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

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

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

四、深翻页及优化

1、深翻页查询过程

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

2、深翻页优化

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

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