1.MySql数据库优化总结

1.1.数据库设计优化

  1. 适度的违反范式
    在开发应用程序时，数据库设计要最大程度的遵守三范式。当 然，三范式最大的问题在于查询时通常需要join很多表，导致查询效率很低。所以有时候基于性能考虑，我们需要有意的违反三范式。也就是适度的做冗余，以达到提高查询效率的目的。注意这里的反范式是适度的。
  2. 适当建立索引
    索引可以有效的提高查询速度，但这个提高是以插入、更新、删除的速度为代价的。由于索引的存储结构不同于表的存储，一个表的索引所占空间比可能数据所占空间还大。这意味着我们在写数据库的时候做了很多额外的工作，而这个工作只是为了提高读的效率。因此，我们建立一个索引，必须保证这个索引不会“亏本”。一般需要遵守这样的规则：
    • 对经常更新的表就避免对其进行过多的索引。
    • 对经常用于查询的字段应该创建索引。
    • 数据量小的表最好不要使用索引。
    • 相同值较多的字段上不要建立索引（比如”性别”字段）。相反的，在不同值较多的字段上可以建立索引。
  3. 对表进行水平划分
    如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这 需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。
    比如系统界面上只提供按月查询的功能，那么把表按月拆分成12个，每个查询只查询一个表就够了。
  4. 对表进行垂直划分
    有些表记录数并不多，但是字段却很多，导致表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。解决方案是：可以把一部分字段拆分到另一个表，并且该表与原表是一对一的关系。
  5. 选择合适的引擎
    在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎。比如：
    • InnoDB存储引擎支持外键、支持事务、支持全文检索；
    • MyISAM存储引擎可以被压缩、不支持事务、不支持外键；如果需要执行大量的select语句，MyISAM存储引擎是更好的选择。
    • MEMORY存储引擎主要用于那些内容稳定的表，或者作为统计操作的中间表。

1.2.SQL语句优化

  1. 尽量使用批量操作
    尽量使用MySql中的批量操作语句，这样可以避免频繁读写操作。比如：批量插入、批量更新、批量删除。
    ```sql / 使用多个insert语句效率低 / insert into dept values(null,’技术部’,’沈阳市’)； insert into dept values(null,’销售部’,’上海市’)； insert into dept values(null,’总部’,’北京市’)；

/ 使用批量insert语句效率高 / insert into dept values(null,’技术部’,’沈阳市’),(null,’销售部’,’上海市’),(null,’总部’,’北京市’);

  2. 2.  选择适当的数据类型<br />选择字段的数据类型的一般原则是尽量使用占用字节小的数据类型。<br />比如主键， 强烈建议用自增类型，既节省空间，又能满足大多数需求。 
  3. 3.  文件、图片等大文件用文件系统存储，不要使用数据库<br />数据库只存储文件路径。这是一个基本原则！ 
  4. 4.  使用连接（JOIN）来代替子查询(Sub-Queries)<br />MySQL从4.1开始支持SQL的子查询。但是，子查询可以被更有效率的连接替代。连接之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成需要多个步骤才能完成的子查询。  
  5. ```sql
  6. /* 查询部门地址在"NEW YORK"的所有员工 */
  8. /* 子查询方式效率低 */
  9. select * from emp where deptno in(select deptno from dept where loc='NEW YORK')
  11. /* 多表连接方式效率高 */
  12. select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='NEW YORK'
  1. 对查询语句进行优化
    绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。
    • 首先，最好是在相同类型的字段间进行比较的操作，可以避免转型的步骤。
    • 其次，在建有索引的字段上尽量不要使用函数进行操作。使用函数后索引将失效。
    • 在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
    • 应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

1.3.数据库参数配置优化

MySql安装之后，默认参数并不能满足所有生产场合下的需求。所以，还需要根据需求，修改Mysql服务器的系统参数，达到合理利用服务器现有资源，最大合理的提高MySQL性能。比如：

  • wait_timeout：MySQL客户端的数据库连接闲置最大时间值。
  • max_connections：MySQL的最大连接数。
  • max_user_connections：每个数据库用户的最大连接。
  • thread_concurrency：线程最大并发数。
  • default-storage-engine：设置MySQL默认存储引擎
    -- 查询MySQL的最大连接数。
show global variables like '%max_connections%';
-- 设置MySQL的最大连接数。
set global max_connections=1024;

    1.4.硬件和系统的优化

  1. 合理采用操作系统
    如果服务器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql
  2. 读写分离
    如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。
    比如：主库用来写入，从库都用来做select，那么每个数据库分担的压力小了很多。当然，要实现这种方式，需要程序特别设计，给程序开发带来了额外负担。不过现在已经有中间件来实现这些功能。