Mysql 数据库性能调优手段

本文章主要是从 Mysql 的引擎、各种引擎的 Cache、数据库的分区分表等手段而对数据库进行调优设置。

Mysql 数据库性能调优手段

根据需求选择使用数据库引擎

1. MyISAM 引擎采用原则

  • R/W > 100 且 update 相对较少
  • 并发不高,不需要事务
  • 表数据量小
  • 硬件资源有限

2. InnoDB 引擎采用原则

  • R/W 比较少,频繁更新大字段
  • 表数据量超过 1000w,并发高
  • 安全性和可用性要求高

3. 采用 Memory 引擎

  • 有足够的内存
  • 对数据的一致性不高,如在线人数和 session 等应用
  • 需要定期归档数据

4. MyISAM 和 InnoDB 之间的不同

  • InnoDB支持事务,MyISAM不支持;

  • InnoDB数据存储在共享表空间,MyISAM数据存储在文件中;

  • InnoDB支持行级锁,MyISAM只支持表锁;

  • InnoDB支持崩溃后的恢复,MyISAM不支持;

  • InnoDB支持外键,MyISAM不支持;

  • InnoDB不支持全文索引,MyISAM支持全文索引;

服务器调整优化策略

1)关闭不必要的二进制日志和慢查询日志,仅在内存足够或开发调试时打开他们

1
2
3
4
5
#查看慢查询是否开启
show variables like '%slow%'

#查看慢查询的条数,方便调试
show global status like '%slow%'

2)适度使用 Query Cache

3)增加 Mysql 允许的最大连接数

1
2
#查看最大连接数
show variables like 'max_connections'

4)对于 MyISAM 表适当增加 key_buffer_size

1
2
3
4
show global status like 'key_read%'
#key_cache_miss_rate = Key_reads / Key_read_requests * 100%

当 key_cache_miss_rate 值大于 1% 时就需要适当增加 key_buffer_size

5)对 MyISAM 表注注意设置 table_cache

当 table_cache 不够的时候,Mysql 会采用 LRU 算法踢掉最长时间没有使用的表
如果 table_cache 设置过小,Mysql 就会反复打开和关闭 FRM 文件,造成一定性能的损失
如果 table_cache 设置过大,Mysql 将消耗很多 CPU 资源去除了 table_cache 算法
设置 table_cache 可以参考 Opened_table 参数的值,如果这个值在增加,就要适当增加 table_cache
InnoDb 则要注意 innodb_buffer_poll_size 参数

6) 从表中删除大量数据,可运行 OPTIMIZE TABLE TABLENAME 进行碎片整理

数据库分区

所谓的分区,就是把一个数据表的文件的索引分散存储在不同的物理文件中。
分区类型有 Range、list、Hash、Key

a)查看是否支持分区

1
show variables like '%partition%'

b)创建分区

1
2
3
4
5
6
7
8
create table foo(
id INT NOT NULL ATUO_INCREMENT,
created DATETIME,
PRIMARY KEY(id,create)
)ENGINE = INNODB PARTITION BY RANGE (TO_DAYS(created)) (
PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2009-01-01')),
PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2010-01-01'))
)

C)后期还可以继续加

1
2
3
ALTER TABLE foo ADD PARTITION(
PARTITION foo_3 VALUES ;ESS THAN (TO_DAY('2011-01-01'))
)

d)删除一个分区

1
ALTER TABLE FOO DROP PARTITION foo_3

e)查看分区信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL

#
SHOW VARIABLES LIKE 'datadir'

# in InnoDb
foo#p#foo_1.ibd
foo#p#foo_2.ibd

# in MyISAM
foo#P#foo_1.MYD
foo#P#foo_1.MYD
foo#P#foo_2.MYD
foo#P#foo_2.MYD

f)使用哪种分区

主从结构,主服务器很少使用 select 那么使用 range 就没什么意义,可以使用 hash
会根据 id 将数据平均分散到各个分区

1
PARTITION BY HASH(id) PARTITION 10

g)根据什么进行分区

通常情况下,使用时间进行分区
不过还是按需求进行,如果使用主从结构,那么使用一个从服务器进行时间分区,一个使用用户进行分区,当执行查询的时候就选择正确的从服务器查询,写个 Mysql Proxy 脚本可以通过透明实现。

分表

分表是把原先的表分成几个表,进行分表查询时候,可以 union 或者做一个视图

场景一 会员数据对 5 取模,放在 5 个数据库中,如何查询会员数据?

可能根据名字查找一条数据,要查找 5 次,但是效率不比查找多数据的一个表差

1
2
3
4
5
6
7
8
9
10
11
<?php
$member_tabel = 'member'.uid%5;
$sql = 'select * from {member_tabel}';

$tables = array('member0','member1','member2','member3','member4');

foreach($tables as $table){
$sql.='select * from {$table} union';
}

$sql = substr($sql,0,-5);

场景二 在一个监控系统,搞流量,可以根据按天分表

  • 查询一天,查询当天的表
  • 查询一个星期,汇总7 天的数据到 week 表,查询 week 表
  • 查询一个月,汇总一个月的数据到 month 表,查询 month 表
  • 3 个月归档一次,超过三个月不能查询,中国移动也是最多保存半年的数据

瓶颈及应对措施

  • 增加配置中的 buffer 和 cache,增加内存和 cpu 数量

  • 使用第三方引擎或其他版本,如 MariaDB 或者 TokuDB

  • 迁移到其他数据库,如 PostgreSQL 或者 Oracle

  • 对数据库进行分区分表操作,减少表体积

  • 使用 Nosql 等辅助解决方案,如 Memcached Redis

  • 使用中间件做数据拆分和分布部署,这方面的典型案例有阿里巴巴对外开源的数据中间件 Cobar

  • 使用数据库连接池技术。Mysql 是线程模型,可以支持更多的并发连接数,能支持远比 oracle 和 postgreSQL 更多的连接数。使用数据库连接池技术,在并发大的情况下爱,让连接进行排队和复用,一定程度上可以缓解高并发下的连接压力。

文章对你有用?给博主一个支持
0%