【数据库】MySQL性能优化简介

 

 

 

 

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 : hotwarm的阈值；

尽量不要使用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合作，联系我们：[e-mail]）