这是我参与8月更文挑战的第3天,继续上一篇的mysql性能优化下

1.1.1. 联合索引的查询的注意事项****

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

1.1.2. 使用OR关键字的查询****

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

1.1.3. 尽量不使用OR关键字*

在使用or时，如果条件中有一个不是索引字段，就会导致整个索引失效。此时，推荐大家使用in 或者 union

1.1.4. 查询条件中尽量不要使用函数*

在条件中使用mysql函数，可能导致索引失效。大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例： // 查询缓存不开启 SELECT username FROM user WHERE signup_date >= CURDATE() // 开启查询缓存 SELECT username FROM user WHERE signup_date >= 2015-01-12 上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

1.1.5. 当只要一行数据时使用 LIMIT 1****

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

1.1.6. 关联查询时，永远用小结果集驱动大的结果集****

其实这样的结果也非常容易理解，在 MySQL 中的 Join，只有 Nested Loop 一种 Join 方式，也就是MySQL 的 Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环的此时就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑 IO 很少，循环次数多了，总量自然也不可能很小，而且每次循环都不能避免的需要消耗 CPU ，所以 CPU 运算量也会跟着增加。反之，所需要的循环次数就会更少，总体 IO 量和 CPU 运算量也会少。而且，就算是非 Nested Loop 的 Join 算法，如 Oracle 中的 Hash Join，同样是小结果集驱动大的结果集是最优的选择。

1.1.7. 只取出自己需要的 Columns；不要select *************

任何时候在 Query 中都只取出自己需要的 Columns，尤其是在需要排序的 Query 中。对于任何 Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的 Column 越多，需要传输的数据量自然会越大，不论是从网络带宽方面考虑还是从网络传输的缓冲区来看，都是一个浪费。如果是需要排序的 Query 来说，影响就更大了。在 MySQL 中存在两种排序算法：一种是在MySQL4.1 之前的老算法，实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在我们所设定的排序区（通过参数 sort_buffer_size 设定）中进行排序，完成排序之后再次通过行指针信息取出所需要的 Columns，也就是说这种算法需要访问两次数据。第二种排序算法是从MySQL4.1 版本开始使用的改进算法，一次性将所需要的 Columns 全部取出，在排序区中进行排序后直接将数据返回给请求客户端。改行算法只需要访问一次数据，减少了大量的随机IO，极大的提高了带有排序的 Query 语句的效率。但是，这种改进后的排序算法需要一次性取出并缓存的数据比第一种算法要多很多，如果我们将并不需要的 Columns 也取出来，就会极大的浪费排序过程所需要的内存。

1.1.8. 尽量不使用子查询****

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然很灵活，但是执行效率并不高。执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。优化：可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。另外，join关联的条件，最好也是索引字段！

2. 数据库结构优化****

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

2.1. 选择合适的数据类型（）***

优化数据类型提高性能的主要原理在于以下几个方面：

通过选用更“小”的数据类型减少存储空间，使查询相同数据需要的 IO 资源降低；
通过合适的数据类型加速数据的比较数字：空间越小越好能用整数就不要用小数字符串：能用数字就用数字。（用字典方式）长度课固定就不固定日期类型：用数字（毫秒值） Timestamp：存的是毫秒值

2.2. 不要使用TEXT、BLOB类型****

会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能,如果必须要使用则独立出来一张表，用主键来对应，避免影响其它字段索引效率

2.3. 把字段定义为NOT NULL并且提默认值****

a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多 c）null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识 d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’zhangsan’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

2.4. 将字段很多的表分解成多个表****

一般一张表字段不能超过50 对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。垂直分表：字段过多，按列分表水平分表（慎用，会导致逻辑变复杂）：表内数据过多，超过1000万，需要水平分表：按行分。mycat自动分库分表

2.5. 增加中间表****

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。 A、B、C A.x,A.y,A.z B.x C.y D(A.x,A.y,A.z B.x C.y)

2.6. 适当增加冗余字段****

A(x,y,z, B.x,C.y) 设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。 注意：**** 冗余字段的值在一个表中修改了 ， 就要想办法在其他表中更新 ，否则 就会导致数据不一致的问题。*

2.7. 尽量不要设计表关联字段（外键）****

外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能下降，大数据高并发业务场景数据库使用以性能优先

2.8. 超过千万数据就要进行分表****

3. 插入数据的优化****

插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。插入数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别： www.cnblogs.com/panfeng412/…

3.1. MyISAM****

3.1.1. 禁用索引****

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。禁用索引的语句： ALTER TABLE table_name DISABLE KEYS** ** 开启索引语句： ALTER TABLE table_name ENABLE KEYS** 对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。

3.1.2. 禁用唯一性检查****

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0; 开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

3.1.3. 批量插入数据****

插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。第二种方式的插入速度比第一种方式快。

3.1.4. 使用LOAD DATA INFILE****

当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。

InnoDB****

3.1.5. 禁用唯一性检查****

1.1.1插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign_key_checks = 0; 开启：SET foreign_key_checks = 1;. 禁用外键检查****

1.1.2. 禁止自动提交****

插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。禁用：SET autocommit = 0; 开启：SET autocommit = 1;

4. 服务器优化****

4.1. 优化服务器硬件****服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。

需要从以下几个方面考虑： 1、配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。 2、配置高速磁盘，比如SSD。 3、合理分配磁盘IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。 4、配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

1.1. 优化MySQL的参数****

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。 MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：要求：必须记忆至少3个。

Mysql性能优化