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 更多的连接数。使用数据库连接池技术，在并发大的情况下爱，让连接进行排队和复用，一定程度上可以缓解高并发下的连接压力。