MySQL数据库优化总结

本文主要是介绍MySQL数据库优化总结，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

优化总结

　　对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。一般来说，要保证数据库的效率，要做好以下四个方面的工作：数据库设计、sql语句优化、数据库参数配置、恰当的硬件资源和操作系统。下面我们逐个阐明：

一、数据库设计

• 适度的反范式,注意是适度的

　　我们都知道三范式，基于三范式建立的模型是最有效保存数据的方式，也是最容易扩展的模式。我们在开发应用程序时，设计的数据库要最大程度的遵守三范式。三范式最大的问题在于查询时通常需要join很多表，导致查询效率很低。所以有时候基于性能考虑，我们需要有意的违反三范式，适度的做冗余，以达到提 高查询效率的目的。

• 适当建立索引

　　说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的’create index’，查询速度就可能提高百倍千倍，这可真有诱惑力。可是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。由于索引的存储结构不同于表的存储，一个表的索引所占空间比数据所占空间还大的情况经常发生。这意味着我们在写数据库的时候做了很多额外的工作，而这个工作只是为了提高读的效率。因此，我们建立一个索引，必须保证这个索引不会“亏本”。一般需要遵守这样的规则：

1. 索引的字段必须是经常作为查询条件的字段;
2. 索引的字段必须有足够的区分度;
3. 使用组合索引时，需要遵循“最左前缀”原则等。

• 对表进行水平划分

　　如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能，那么把表按月 拆分成12个，每个查询只查询一个表就够了。如果非要按照地域来分，即使把表拆的再小，查询还是要联合所有表来查，还不如不拆了。所以一个好的拆分依据是 最重要的。

• 对表进行垂直划分

　　有些表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

•  选择适当的字段类型，特别是主键

　　选择字段的一般原则是保小不保大，能用占用字节小的字段就不用大字段。为什么?省空间啊?空间是什么?空间就是效率!按4个字节和按32个字节定位一条记录，谁快谁慢太明显了。涉及到几个表做join时，效果就更明显了。值得一提的是，datetime 和 timestamp，datetime 占用8个字节，而 timestamp 只占用4 个字节，只用了一半，而timestamp表示的范围是1970—2037，对于大多数应用，尤其是记录什么考试时间，登录时间这类信息，绰绰有余啊。

• 文件、图片等大文件用文件系统存储，不用数据库

　　不用多说，铁律!!!数据库只存储路径。

• 选择合适的引擎

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

 二、SQL语句优化

Sql语句优化工具：

• 慢日志

　　如果发现系统慢了，又说不清楚是哪里慢，那么就该用这个工具了。只需要为mysql配置参数，mysql会自己记录下来慢的sql语句。打开mysql的配置文件（windows系统是my.ini ; linux系统是my.cnf），在[mysqld]下面加上以下代码：

# 慢查询日志
slow_query_log=ON 
slow_query_log_file="d:/slow.txt"
long_query_time=1

重启MySQL服务，此时我们在mysql中运行以下命令：

 　show variables like 'slow_query%';

运行结果如下：

可以看到slow_query_log是ON状态，slow.txt 也是我们指定的文件（查询时间超过1s的查询语句会记录在此文件中），当我们执行一次慢查询如：SELECT SLEEP(2);

便在指定路径下生成了 slow.txt 文件，打开该文件便可找到对应的sql语句：

 注：SHOW STATUS LIKE 'slow_queries'; 执行该语句可以查看慢查询次数

• Explain执行计划

　　慢查询日志可以帮助我们把所有查询时间过长的sql语句记录下来，在优化这些语句之前，我们应该使用explain命令查看mysql的执行计划，寻找其中的可优化点。explain命令的使用十分简单，只需要"explain + sql语句"即可。

如果什么都做不了，试试全索引扫描：索引覆盖。如果一个语句可以从索引上获取全部数据，就不需要通过索引再去读表，省了很多I/O。换句话说查询列要被所使用的索引覆盖。

三、数据库参数配置

 最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数可以调的大一些：

 四、合理的硬件资源和操作系统

　　如果你的机器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql

　　读写分离：如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。主库master只用来写入，从库slave只用来查询。

这篇关于MySQL数据库优化总结的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！