MySQL性能优化简介
chuck
影响mysql性能的相关因素
不合理的产品需求
系统架构及实现
Query优化和Schema设计
硬件环境
几个有用的参数
一、不合理的产品需求
我们需要过于精确的数据么?
同时在线人数/总发帖量:
一、不合理的产品需求
无用的功能堆积
(1)开发侧:修改有关联的代码,删除没有关联的代码;
(2)测试侧:由于功能变动,需要回归测试所有相关功能点;
(3)产品侧:不会带来任何实质上的收益,风险过大。
二、系统架构及实现
RAID策略与磁盘存储策略
选取合适的存储引擎
使用索引
数据库中存放合适的数据
应用层的cache机制
二、系统架构及实现
1. RAID策略:RAID 10/RAID 5/RAID 50
二、系统架构及实现
2.磁盘存储策略:
(1)根据业务特点,按照某种逻辑拆分表
(2)跨分区建表
二、系统架构及实现
3.选取合适的存储引擎:
二、系统架构及实现
3.选取合适的存储引擎:
MyISAM特点:
每张MyISAM表被存放在三个文件:
frm文件存放表格定义;
数据文件是MYD(MYData);
索引文件是MYI(MYIndex)。
MyISAM表是保存成文件的形式,在跨平台的数据转移快捷方便;
具有检查和修复表格的大多数工具;
MyISAM表格可以被压缩;
支持全文搜索;
不是事务安全的,也不支持外键。
二、系统架构及实现
3.选取合适的存储引擎:
MyISAM优化:
key_buffer_size : 索引缓存大小;
key_buffer_block_size : 索引缓存中的cache block size;
key_cache_division_limit : warm chain/(warm chain+hot chain);
Key_cache_age_threshold : hotwarm的阈值;
尽量不要使用NULL值;
定期optimize;
二、系统架构及实现
3.选取合适的存储引擎:
InnoDB特点:
不支持FULLTEXT类型的索引;
InnoDB中不保存表的具体行数;
对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段索引,但是在MyISAM表中,可以和其他字段一起建立联合索引;
DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除;
InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。
二、系统架构及实现
3.选取合适的存储引擎:
InnoDB优化:
innodb_buffer_pool_size : 缓存索引与实际数据;
innodb_log_buffer_size : 缓冲log数据,提高io性能;
innodb_additional_mem_pool_size : 存放表内部结构;
innodb_flush_method : fdatasync/O_DIRECT/O_DSYNC;
innodb_thread_concurrency : 并发线程处理数量;
autocommit : 是否自行提交;
二、系统架构及实现
4.使用索引
索引类型:
(1)BTree
(2)Hash
(3)Full text
二、系统架构及实现
4.使用索引
不应该索引使用原则:
(1)字段的值唯一性太差;
(2)更新非常频繁;
(3)不会出现在where中的字段;
二、系统架构及实现
5.什么样的数据不适合放在数据库中?
(1)二进制文件
(2)超长文本
(3)非关系类数据处理
二、系统架构及实现
6.应用层
使用cache,由比较成熟的容器实现;
获取的数据是否都是必需的;
查询不要过多,也不要过少;
应用层在网络状况非常好的情况下使用了永久连接;
每次查询结束后是否关闭链接;
过多的垃圾查询:SET NAMES UTF8;
三、Query语句和Schema设计优化
三、Query语句和Schema设计优化
1.Query语句优化:
方案二:
SELECT id FROM album WHERE user_id=‘xxx’ limit 10;
SELECT album_id,COUNT(*) FROM photo WHERE album_id IN (?) GROUP BY album_id;
三、Query语句和Schema设计优化
1.Query语句优化:
Table1: users表(user_id, user_name, sex)Table3: users_group表(id, group_id, user_id, level, join_time)
现在要查询某个群组下面(id = 1)用户的名称和性别,按加入时间
倒序取100-120条的记录;
三、Query语句和Schema设计优化
1.Query语句优化 :
方案二:
SELECT user_id FROM users_group WHERE users_group.group_id =‘1’ ORDER BY join_time DESC LIMIT 100,20;
SELECT users.user_id, users.user_name, users.sex FROM (--) users_group, users WHERE users_group.user_id = users.user_id;
三、Query语句和Schema设计优化
1.Query语句优化原则:
优化高并发的SQL语句
Explain和Profile
小结果集驱动大结果集
尽可能在索引中完成排序
只取自己需要的字段
尽可能避免复杂的JOIN和子查询
三、Query语句和Schema设计优化
2. Schema设计优化
(1)高效的模型设计原则
适度冗余;
大字段垂直分拆;
常用大表水平分拆。
三、Query语句和Schema设计优化
2. Schema设计优化
适度冗余 :
三、Query语句和Schema设计优化
2. Schema设计优化
大字段垂直分拆:
三、Query语句和Schema设计优化
2. Schema设计优化
常用大表水平分拆(Discuz) :
三、Query语句和Schema设计优化
2. Schema设计优化
(2)合适的数据类型
通过使用更少的数据类型来减少数据的存储空间;
常见数字/日期/字符串类型;
规范表命名。
四、硬件环境
主机IO:磁盘/内存
CPU
网络环境
五、几个有用的参数
join_buffer_size :
log_slave_updates :
old_passwords :
sort_buffer_size :
tmp_table_size :
谢谢大家
本文原创自无线技术运营空间: http://wireless.qzone.qq.com 及 http://blog.csdn.net/wireless_tech (专注无线技术运营——无线技术(操作系统/数据库/WEB前端/负载均衡/系统容灾/系统安全/短信接入/WAP接入/3G等)、无线业务运营、无线开放平台、统计分析(用户行为分析/数据挖掘)、CP合作,联系我们:1780551083@qq.com)