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)关闭不必要的二进制日志和慢查询日志,仅在内存足够或开发调试时打开他们
#查看慢查询是否开启
show variables like '%slow%'
#查看慢查询的条数,方便调试
show global status like '%slow%'
2)适度使用 Query Cache
3)增加 Mysql 允许的最大连接数
#查看最大连接数
show variables like 'max_connections'
4)对于 MyISAM 表适当增加 key_buffer_size
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)查看是否支持分区
show variables like '%partition%'
b)创建分区
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)后期还可以继续加
ALTER TABLE foo ADD PARTITION(
PARTITION foo_3 VALUES ;ESS THAN (TO_DAY('2011-01-01'))
)
d)删除一个分区
ALTER TABLE FOO DROP PARTITION foo_3
e)查看分区信息
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 将数据平均分散到各个分区
PARTITION BY HASH(id) PARTITION 10
g)根据什么进行分区
通常情况下,使用时间进行分区
不过还是按需求进行,如果使用主从结构,那么使用一个从服务器进行时间分区,一个使用用户进行分区,当执行查询的时候就选择正确的从服务器查询,写个 Mysql Proxy 脚本可以通过透明实现。
分表
分表是把原先的表分成几个表,进行分表查询时候,可以 union 或者做一个视图
场景一 会员数据对 5 取模,放在 5 个数据库中,如何查询会员数据?
可能根据名字查找一条数据,要查找 5 次,但是效率不比查找多数据的一个表差
<?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 更多的连接数。使用数据库连接池技术,在并发大的情况下爱,让连接进行排队和复用,一定程度上可以缓解高并发下的连接压力。