MySQL优化概述

MySQL优化主要涉及到的有以下几点：

1.数据库(表)设计合理

表的设计要符合3NF，有时我们需要适当的逆范式。

2.SQL语句的优化(创建索引以及一些常用的优化小技巧)

索引包括：主键索引(primary key)、唯一索引(unique)、普通索引(index)、全文索引(fulltext)、复合索引(多列合在一起)，在创建索引的时候需注意一下几点：

• 较为频繁地作为查询条件的字段应该创建为索引

• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件(如：性别字段)

• 更新非常频繁的字段不适合创建索引

• 不会出现where子句中的字段不应该创建索引

你所创建的索引是否被使用到可以使用explain分析查看，在以下几种情况下有可能使用到你有创建的索引：

• 对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用如有这样的创建SQL语句：

  alter table emp add index myindex(name,age);

  这时如果有如下的查询语句，索引就会被使用：

  select * from emp where name="IECspace" and age=20;select * from emp where name="IECspace";

  而下面的查询语句不会使用到索引：

  select * from emp where age=20;

• 对于使用like的查询，查询不是使用%开头，会使用索引，反之不使用

在下列情况下不使用索引：

• 如果条件中有or，即使其中有条件带有索引，索引也不会被使用

• 对于多列索引，如果没有使用到第一列，则不会使用索引

• like查询以%开头不使用索引

• 如果列类型时字符串，那么一定要在条件中将数据使用引号引起来，否则不使用索引

• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

此外，如果你想检测一下创建的索引是否有效，可以使用 show status like "handler_read%";结果中Handler_read_key越大表示越有效，Handler_read_rnd_next越小越好

常见的SQL优化手法包括

• 优化group by语句：默认情况下，MySQL对所有的group by col1,col2进行排序，这与在查询中指定order by col1,col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

• 有些情况下，可以使用连接来代替子查询，因为使用join，MySQL不需要在内存中创建临时表

• 如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到独立的索引，如果没有索引，则应该考虑增加索引

3.选择合适的存储引擎：

• MyISAM(不支持外键、不支持事务，支持全文索引)：默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高。就选择MyISAM，其优势是访问的速度快。

• Innodb(不支持全文索引)：提供了具有提交、回滚和崩溃回复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且占用更多的磁盘空间。

4.选择合适的数据类型：

• 在精度要求高的应用中，建议使用定点数(decimal)来存储数据，以保证结果的准确性

• 对于存储引擎是MyISAM的数据库，如果经常做删除和修改记录的操作，要定时执行optimize table table_name;功能对表进行碎片整理

5.表的垂直分割与水平分割：垂直分割：不好表达意思，举个例子，有一个在线答题的系统，把题目保存在一张表中，而把这个题目的答案保存在另一张表中，这就是一种垂直分割的思想；水平分割：我认为就是将一张大数据量的表根据一定的标准均分成n张小表，然后按照查询条件到指定的小表中查询，这样就大大减少了遍历的数据量。

6.文件、图片等大文件用文件系统存储

7.数据库参数配置：

• 最重要的参数就是内存，我们主要用的Innodb引擎，所以innodb_additional_mem_pool_size = 64M innodb_buffer_pool_size = 1G两个调的很大

• 对于myisam，需要调整key_buffer_size，当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定该调整哪些参数

8.合理的硬件资源和操作系统