存储优化下数据库s的使用和论坛

1、最新更新通知 加ixuexi66资料整理不易个人学习倒卖 Android开发高手课首页|优化（下）：数据库的使用和优化14 |2019-01-15讲述：时长 15:49大小 14.50M我们先来复习一下前面讲到的方法的使用场景：少量的 Key Value 数据可以直接使用SharedPreferences，稍微复杂一些的数据类型也可以通过序列化成 JSON 或者 ProtocolBuffers 保存，并且在开发中获取或者修改数据也很简单。不过这几种方法可以覆盖所有的还可以接受，但如果是几万条的数据做增删改查呢？场景吗？数据量在几百上千条这个量级时它们的性能呢？而且如何实现快速地对某几个人的对于

2、大数据的场景，我们需要考虑稳定性、性能和可扩展性，这个时候就要轮到今天的“主角”数据库登场了。讲优化一定绕不开数据库，而数据库这个主题又非常大，我也知道不少同学学数据库的过程是从入门到放弃。那么考虑到我们大多是从事移动开发的工作，今天我就来讲讲移动端数据库的使用和优化。的那些事儿虽然市面上有很多的数据库，但受限于库体积和空间，适合移动端使用的还真不多。当然使用最广泛的还是我们今天的主角，但同样还是有一些其他不错的选择，例如创业团队的Realm、的LevelDB等。在国内那么多的移动团队中，对的研究可以算是最深入的。这其实是业务诉求导向的，用户只会在本地保存，一旦出现数据损坏或者丢失，对用户来说

3、都是不可挽回的。另一方面，有很大一批的重度用户，他们有几千个人、几千个群聊天，曾经做过一个统计，有几百万用户的数据库竟然大于 1GB。对于这批用户，如何保证他们可以正常地使用是一个非常大的。所以当时专门开展了一个重度用户优化的专项。一开始的时候我们集中在使的实现也用上的优化，例如表结构、索引等。但很快就发现由于系统版本的不同，有所差异，经常会出现一些兼容性问题，并且也考虑到加密的诉求，我们决定单独引入自己的版本。“源码在手，天下我有”，从此开启了一条研究数据库的“不归路”。那时我们投入了几个人专门去深入研究的源码，从的 PRAGMA 编译选项、Cursor 实现优化，到源码的优化，最后打造出从

4、到线上的整个体系。在 2017 年，我们开源了项目是否靠谱，就看这个项目对版本，虽然我现在已经离开了的数据库WCDB。这里多说两句，看一个开源本身有多重要。开源坚持内部与外部使用同一个团队，但还是欢迎有需要的同学使用 WCDB。在开始学习前我要提醒你，的优化同样也很难通过一两篇文章就把每个细节都讲清楚。今天的内容我选择了一些比较重要的知识点，并且为你准备了大量的参考资料，遇到陌生或者不懂的地方需要结合参考资料反复学习。1. ORM坦白说可能很多 BAT 的高级开发工程师都全了解的内部机制，也不能正确地写出高效的 SQL 语句。大部分应用为了提高开发效率，会引入 ORM 框架。ORM（Objec

5、t Relational Mapping）也就是对象关系，用面向对象的概念把数据库中表和对象关联起来，可以让我们不用关心数据库底层的实现。最新更新通知 加ixuexi66资料整理不易个人学习倒卖Android 中最常用的 ORM 框架有开源greenDAO和ORM 框架会带来什么问题呢？的Room，那使用使用 ORM 框架真的非常简单，但是简易性是需要牺牲部分执行效率为代价的，具体的损耗跟 ORM 框架写得好不好很有关系。但可能更大的问题是让很多的开发者的思维，最后可能连简单的 SQL 语句都写了。那我们的应用是否应该引入 ORM 框架呢？可能程序员天生追求偷懒，为了提高开发效率，应用的确应该

6、引入 ORM 框架。但是这不能是我们可以不去学习数据库基础知识的理由，只有理解底层的一些机制，我们才能更加得心应手地解决疑难的问题。考虑到可以更好的与 Android Jetpack 的组件互动，WCDB 选择 Room 作为 ORM 框架。2. 进程与线程并发如果我们在项目中有使用经常会出现的一个问题。，那么下面这个DatabaseLockedException就是代码1234android.database.DatabaseLockedException: database is locked Database.dbopenDatabase.openDatabaseDatabase.ope

7、nDatabaseatat atandroid.database.android.database. android.database.DatabaseLockedException 归根到底是因为并发导致，而的并发有两个维度，一个是多进程并发，一个是多线程并发。下面我们分别来讲一下它们的关键点。多进程并发默认是支持多进程并发操作的，它通过文件锁来多进程的并发。锁的粒度并没有非常细，它文章。的是整个 DB 文件，内部有 5 个状态，具体你可以参考下面的文档：locking源码分析：锁机制简介 机制简单来说，多进程可以同时获取 SHARED 锁来数据，但是只有一个进程可以获取EXCLUSIVE

8、锁来写数据库。对于 iOS 来说可能没有多进程locking_mode 的默认值改为 EXCLUSIVE。数据库的场景，可以把代码1 PRAGMA locking_mode = EXCLUSIVE在 EXCLUSIVE 模式下，数据库连接在断开前都文件的锁，从而避免不必要的，提高数据库的速度。多线程并发相比多进程，多线程的数据库启下面的配置，当然系统可能会更加常见。支持多线程并发模式，需要开会默认开启多线程Multi-thread 模式。代码1 PRAGMA_THREADSAFE =2跟多进程的锁机制一样，为了实现简单，锁的粒度都是数据库文件级别，并没有实现表级甚至行级的锁。还有需要说明的是，

9、同一个句柄同一时间只有一个线程在操作，这个时候我们需要打开连接池 Connection Pool。如果使用 WCDB 在初始化的时候可以指定连接池的大小，在中我们设置的大小是 4。代码12345publicstaticDatabase openDatabase (String path, Database.CursorFactory factory,int flags,DatabaseErrorHandler errorHandler, int poolSize)最新一手资源 更新通知 加微信 ixuexi66资料整理不易 仅供个人学习 请勿倒卖跟多进程类似，多线程可以同时读取数据库数据，但是

10、写数据库依然是互斥的。SQLite 提供了 Busy Retry 的方案，即发生阻塞时会触发 Busy Handler，此时可以让线程休眠一段时间后，重新尝试操作，你可以参考微信 iOS SQLite 源码优化实践这篇文章。为了进一步提高并发性能，我们还可以打开WAL（Write-Ahead Logging）模式。WAL 模式会将修改的数据单独写到一个 WAL 文件中，同时也会引入了 WAL 日志文件锁。通过 WAL 模式读和写可以完全地并发执行，不会互相阻塞。复制代码1 PRAGMA schema.journal_mode = WAL但是需要注意的是，写之间是仍然不能并发。如果出现多个写并发

11、的情况，依然有可能会出现 SQLiteDatabaseLockedException。这个时候我们可以让应用中捕获这个异常，然后等待一段时间再重试。复制代码1 catch (SQLiteDatabaseLockedException e) 2 if (sqliteLockedExceptionTimes < (tryTimes - 1) 3 try 4 Thread.sleep(100);5 catch (InterruptedException e1) 6 7 8 sqliteLockedExceptionTimes+；9 总的来说通过连接池与 WAL 模式，我们可以很大程度上增加 S

12、QLite 的读写并发，大大减少由于并发导致的等待耗时，建议大家在应用中可以尝试开启。3. 查询优化说到数据库的查询优化，你第一个想到的肯定是建索引，那我就先来讲讲 SQLite 的索引优化。索引优化正确使用索引在大部分的场景可以大大降低查询速度，微信的数据库优化也是通过索引开始。下面是索引使用非常简单的一个例子，我们先从索引表找到数据对应的 rowid，然后再从原数据表直接通过 rowid 查询结果。关于 SQLite 索引的原理网上有很多文章，在这里我推荐一些参考资料给你：SQLite 索引的原理官方文档：Query PlanningMySQL 索引背后的数据结构及算法原理这里的关键在于如

13、何正确的建立索引，很多时候我们以为已经建立了索引，但事实上并没有真正生效。例如使用了 BETWEEN、LIKE、OR 这些操作符、使用表达式或者 case when 等。更详细的规则可参考官方文档The SQLite Query Optimizer Overview，下面是一个通过优化转换达到使用索引目的的例子。复制代码1 BETWEEN： myfiedl 索引无法生效2 SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;3 转换成： myfiedl 索引可以生效4 SELECT * FROM mytable WHERE myfield

14、 >= 10 AND myfield <= 20;建立索引是有代价的，需要一直维护索引表的更新。比如对于一个很小的表来说就没必要建索引；如果一个表经常是执行插入更新操作，那么也需要节制的建立索引。总的来说有几个原则：建立正确的索引。这里不仅需要确保索引在查询中真正生效，我们还希望可以选择最高效的索引。如果一个表建立太多的索引，那么在查询的时候 SQLite 可能不会选择最好的来执行。最新 更新通知 加 ixuexi66资料整理不易 个人学习 倒卖单列索引、多列索引与复合索引的选择。索引要综合数据表中不同的 与排序语句一起考虑，如果 结果集过大，还是希望可以通过复合索引直接在索引表返

15、回 结 果。索引字段的选择。整型类型索引效率会远高于字符串索引，而对于主键 会默认帮我们建立索引，所以主键尽量不要用复杂字段。总的来说索引优化是 优化中最简单同时也是最有效的，但是它并不是简单的建一个索引就可以了，有的时候我们需要进一步调整 语句甚至是表的结构，这样才能达到最好的效果。页大小与缓存大小在 I/O 文件系统中，我讲过数据库就像一个小文件系统一样，事实上它内部也有页和缓存的概念。对于 的 DB 文件来说，页（page）是最小的 ，如下图所示每个表对应的数据在整个 DB 文件中都是通过一个一个的页 ，属于同一个表不同的页以 B 树（B- tree）的方式组织索引，每一个表都是一棵 B

16、 树。跟文件系统的页缓存（Page Cache）一样， 会将读过的页缓存起来，用来加快下一次 速度。页大小默认是 1024Byte，缓存大小默认是 1000 页。 的编译参数你可以查看官方文档PRAGMA Statements。复制代码1 PRAGMA page_size = 10242 PRAGMA cache_size = 1000每个页永远只存放一个表或者一组索引的数据，即不可能同一个页存放多个表或索引的数 据，表在整个 DB 文件的第一个页就是这棵 B 树的根页。继续以上图为例，如果想查询rowID 为 N+2 的数据，我们首先要从 sqlite_master 查找出 table 的

17、root page 的位置，然后读取 root page、page4 这两个页，所以一共会需要 3 次 I/O。从上表可以看到，增大 page size 并不能不断地提升性能，在拐点以后可能还会有副作用。我们可以通过 PRAGMA 改变默认 page size 的大小，也可以再创建 DB 文件的时候进行设置。但是需要注意如果存在老的数据，需要调用 vacuum 对数据表对应的节点重新计算分配大小。在微信的内部测试中，如果使用 4KB 的 page size 性能提升可以在 5%10%。但是考虑到历史数据的迁移成本，最终还是使用 1024Byte。所以这里建议大家在新建数据库的时 最新 更新通知

18、 加 ixuexi66资料整理不易 个人学习 倒卖候，就提前选择 4KB 作为默认的 page size 以获得更好的性能。其他优化关于 的使用优化还有很多很多，下面我简单提几个点。慎用“select*”，需要使用多少列，就选取多少列。正确地使用事务。预编译与参数绑定，缓存被编译后的 SQL 语句。对于 blob 或超大的 Text 列，可能会超出一个页的大小，导致出现超大页。建议将这些列单独拆表，或者放到表字段的后面。定期整理或者 无用或可删除的数据，例如 数据库会删除比较久远的数据，如果用户 到这部分数据，重新从 拉取即可。在日常的开发中，我们都应该对这些知识有所了解，再来复习一下上面学到

19、的 优化方法。通过引进 ORM，可以大大的提升我们的开发效率。通过 WAL 模式和连接池， 可以提高 的并发性能。通过正确的建立索引，可以提升 的 速度。通过调整默认的页大小和缓存大小，可以提升 的整体性能。的其他特性除了 的优化经验，我在 的工作中还积累了很多使用的经验，下面我挑选了几个比较重要的经验把它 给你。1. 损坏与恢复中 的损耗率在 1/200001/10000 左右，虽然看起来很低，不过意考虑到微信的体量，这个问题还是不容忽视的。特别是如果某些大佬的 丢失，我们团队都会承受超大的 。创新是为了解决焦虑，技术都是逼出来的。对于 损坏与恢复的研究，可以说是微信投入比较大的一块。关于

20、数据库的损耗与修复，以及 在这里的优化成果， 你可以参考下面这些资料。How To Corrupt An Database File 数据库修复实践 移动端数据库组件 WCDB 系列（二） 数据库修复三板斧WCDB Android 数据库修复2. 加密与安全数据库的安全主要有两个方面，一个是防注入，一个是加密。防注入可以通过静态安全扫描的方式，而 般会使用 SQLCipher 支持。的加 都是以页为 ，默认会使用 AES 算法加密，加 / 的耗时跟选用的密钥长度有关。下面是WCDB Android Benchmark的数据，详细的信息请查看 里的说明，从结论来说对 Create 来说影响会高达

21、到 10 倍。关于 WCDB 加 的使用，你可以参考 移动数据库组件 WCDB（四） Android 特性篇。3. 全文搜索的全文搜索也是一个技术导向的项目，最开始的时候性能并不是很理想，经常会被人“ ”。经过几个版本的优化迭代，目前看效果还是非常不错的。最新 更新通知 加 ixuexi66资料整理不易 个人学习 倒卖关于全文搜索，你可以参考这些资料： FTS3 and FTS4 Extensions 全文搜索优化 移动客户端多音字搜索关于 的这些特性，我们需要根据 的项目情况综合考虑。假如某个数据库的数据并不重要，这个时候万分之一的数据损坏率我们并 关心。同样是否需要使用数据库加密，也要根据

22、 的数据是不是敏感内容。的首先 说，正确使用索引，正确使用事务。对于大型项目来说，参与的开发 可能有几十几百人，开发 水平参差不齐，很难保证每个人都可以正确而高效地使用 ， 所以这次时候需要建立完善的 体系。1. 本地测试作为一名靠谱的开发工程师，我们每写一个 SQL 语句，都应该先在本地测试。我们可以通过 EXPLAIN QUERY PLAN 测试 SQL 语句的 计划，是 扫描还是使用了索引，以 及具体使用了哪个索引等。代码123> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 AND b>2; QUERY PLAN|-SEARCH

23、 TABLE t1 USING INDEX i2 (a=? AND b>?)关于命令行与 EXPLAIN QUERY PLAN 的使用，可以参考Command Line ShellFor以及EXPLAIN QUERY PLAN。2. 耗时本地测试过于依赖开发的自觉性，所以很多时候我们依然需要建立线上大数据的监控。因为块。集成了的源码，所以可以非常方便地增加想要的模WCDB 增加了Trace的模块，有以下三个接口：我们可以通过这些接口数据库 busy、损耗以及执行耗时。耗时比较长的 SQL 语句，需要进一步检查是 SQL 语句写得不好，还是需要建立索引。最新更新通知 加ixuexi66资料

24、整理不易个人学习倒卖3. 智能对于结果的只是我们演进的第二阶段，在这个阶段我们依然需要人工介入分负责。析，而且需要比较有经验的我们希望 SQL 语句的分析可以做到智能化，是完全不需要门槛的。开源的 Matrix 里面就有一个智能化分析语句的工具：MatrixLint 使用质量检测。它根据分析 SQL 语句的语法树，结合我们日常数据库使用的经验，抽象出索引使用不当、select*等六大问题。可能有同学会感叹为什么的人可以想到这样的方式，事实上这个思路在 MySQL 中是非常常见的做法。美团也开源了它们内部的 SQL 优化工具 SQLAdvisor，你可以参考这些资料：SQL在美团的应用美团点评 SQL 优化工具 SQLAdvisor 开源总结数据库是一个开发的基本功，清楚的底层机制对我们的工作会有很大的指导意义。掌握了数据库并发的机制，在某些时候我们可以更好地决策应该拆数据表还是拆数据库。新建一个数据库好处是可以其他库并发或者损坏的情况，而坏处是数据库初始化耗时以及内存的占用。一般来说，单独的业务都会使用数据库，例如专门的数据库、数据库、聊天数据库。但是数据库也不宜太多，我们可以有一个公共数据库，用来存放一些相对不是太大的数据。在了解数据库损坏的原理和概率以后，我们可以根据数据的重要程度决定是否要引入恢复机制。我还讲了如何实现数据库加密以及对