提高数据库并发性能概要

最新推荐文章于 2024-05-10 00:21:14 发布

转载最新推荐文章于 2024-05-10 00:21:14 发布 · 860 阅读

文章标签：

#数据库 #sql #优化 #sql server #算法 #table

Lock 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一系列针对数据库性能优化的方法，包括细粒度锁表、拆表、多数据源使用、乐观锁机制、利用临时表、缓存策略、数据库集群部署、读写分离策略、SQL语句优化及正确使用索引等。

1 细粒度锁表

减少大事物操作 (一个包含很多SQL语句、牵涉很多表的大事物操作锁住的资源也会多）；
尽量不使用表级排他锁，而是用行级锁；又因为锁一般是由数据库根据你的 SQL自动分配，所以要注意SQL的写法。比如
```
select * into B from A
```
该操作会创建B表，并对B表加表锁。

2 拆表

与降低锁的粒度异曲同工。

2.1 物理拆分方式

水平拆分: 把不同行记录分开放进不同表中。比如男性放入一张表中，女性放入另一张表。
垂直拆分: 把不同列分别放入不同表中。比如主用户信息放入一张表，附加信息放入另一张表。
混合拆分（水平拆分+垂直拆分）

2.2 逻辑拆分方式

按范围拆分，比如所有zipcode列的值 between 70000 and 79999的记录拆成一个表.
分组拆分，比如country列为冰岛、挪威、瑞典、丹麦这些北欧国家的记录拆成一个表
哈希拆分：参见数据库的哈希设计：http://hi.baidu.com/dapplehou/blog/item/471a277ff8eafc0529388ac4.html
混合拆分，以上几种拆分方式的综合。
可建立查找表以方便对拆分的子表进行登记、查询、定位。

3 多数据源(多库）

本质是拆库。与拆表异曲同工。数据库路由（定位），对于程序员来说就是数据源路由(查找、定位），需要定义多个数据源，并采取合适算法进行定位。可参考 http://hi.baidu.com/dapplehou/blog/item/6ba8034f5114e038aec3abdb.html 多库部分.

什么是合适算法？那要根据库的切分方式、性能需要以及业务需要来定夺。

4 乐观锁

可以降低数据库长事物。只在提交的那一刹那判断自己提交的数据是否已经被别人修改过了，如是，则认为自己要提交的数据是过期数据，取消提交，否则，提交成功。避免长事物带来的等待。通常采用版本号来判断是否过期数据。过程如下：

A、B两人同时读取同一条银行账户记录，记录版本号为1。
A修改此记录：增加100元，版本号加1。
B修改此记录：减少50元，版本号加1。
A提交。假设提交SQL如下：
```
update account set money=money+50,version=version+1 where id=10 and version=1
```
提交成功。id=10的这条记录version为2.
B提交。提交SQL如下：
```
update account set money=money-50,version=version+1 where id=10 and version=1
```
因为version已经等于2了，所以这条更新语句会影响0行。这样，谁先提交，谁成功。后来者的不会因为先到者读取后迟迟不提交而等待等待。减少了站着茅坑不拉屎的情况。提高了并发性能。

5 临时表

使用局部临时表提高并发性能。局部临时表特点是多用户操作互不干扰。一个局部临时表只对一个session可见、有效。多用户并发实际是各操作各自的临时表，不会产生数据表锁的问题。提高并发效率。

SQL Server创建临时表语句：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

Oracle:

CREATE GLOBAL TEMPORARY TABLE MyTempTable

把主表（频繁使用又数据量大）中的部分数据放入局部临时表，然后在临时表里进行计算、统计等工作，然后同步到主表中去（同步过程可用乐观锁机制），减少了主表在并发时锁等待的开销。

临时表性能提升原理和拆分表原理相同：通过把一个大集合拆成小集合，然后在小集合上进行计算。

6 缓存

原理同临时表。先在内存里折腾(计算、update等），然后把折腾的结果一次性的同步给db(同步过程可使用乐观锁机制）.
算法优化。
有的操作，虽然没有更新过程，但频繁的复杂查询也会使数据库性能降低。这种情况下，可采取把一些查询后的结果且不轻易变的数据保存在缓存中供后续使用。

例如：不同用户登录后的菜单显示可能要根据不同部门、不同权限以及委托、兼职等复杂逻辑来确定菜单的显示项，数据库的查询可能非常消耗性能，这种情况下，可缓存所有菜单形成菜单池，再采用亨元模式，缓存每个用户的可见菜单，这样减少了数据库查询次数，避免了显示菜单时每次都要查询数据库的性能损耗。

亨元模式可参见：http://hi.baidu.com/dapplehou/blog/item/aeca1d958268e1047bf48089.html

7 数据库集群

与多库(拆库）区别：集群是每个库上都有一份相同的数据；拆库是每个库只有全集的部分数据。

多数据库冗余可以减少单个数据库的负载，总体上提高了性能。它有3个技术关键点：

数据库查询路由
数据库同步
负载均衡算法

8 读写分离策略

在读操作远多于写操作的大型应用中，可采用读写分离策略。用来读的数据库叫从数据库，用来写或者事务性查询的叫主数据库。主数据库的数据变化会通过某些策略（比如数据库复制）同步到从数据库，从而保证主从数据库数据一致性。其本质是空间换时间，即：通过增加数据库冗余，降低数据库对锁的复杂调度。但另一方面，增加了应用架构的复杂度（这种复杂性可以通过使用第三方中间件来降低）。

9 SQL优化

SQL语句是数据库资源消耗的主要来源，它往往都是可以进一步优化的，SQL优化在时间成本和风险上代价低。

我们写的SQL语句都会被查询优化器自动优化，它的优化原则是这样的：

1. 选择运算优先运行。（即优先通过条件过滤记录集）
2. 投影运算与选择运算同时进行。
3. 将笛卡尔积与随后的选择运算合并为连接运算
4. 投影运算与其他运算同时进行
5. 寻找公共子表达式并将结果加以存储
6. 对文件进行预处理

由于有了以上优化，所以下面两种SQL的写法效率一样

select * from A,B where A.id=B.id and A.name='Jake'
select * from A,B were A.name='Jake' and A.id=B.id

随着数据库的进化，原来效率低的语句现在不一定低，需要实际测试。

10 正确使用索引

索引列
1. 经常查询的列
2. 经常排序的列
3. 经常连接的列
非索引列
1. 不经常使用的列
2. 重复记录太多的列(比如性别）
3. Image text类型的列