Mysql、性能优化

优秀文章，第一时间收到！

KS

Knowledge Sharing

知识分享

现在是资源共享的时代，同样也是知识分享的时代，如果你觉得从本文能学到知识，请把知识与别人分享。

性能优化

查询系统性能，语法格式：

SHOW STATUS LIKE 'value';

value参数的几个统计参数如下：

1.分析查询语句

EXPLAIN，DESCRIBE用于分析SELECT语句的执行情况，语法规则：

EXPLAIN SELECT 语句;

DESCRIBE SELECT 语句;

返回的查询结果信息：

2.索引查询

（1）使用 LIKE 关键字查询时，若匹配字符串的第一个字符为“%”时，索引不会被使用，如果不在第一个位置，索引就会被使用

（2）多列索引是在表的多个字段上创建一个索引，只有查询条件中使用这些字段中第一个字段时，索引才会被使用

（3）查询语句只有 OR 关键字时，若OR前后两个条件的列都是索引时查询中将使用索引，若OR前后有一个条件的列不是索引，那么查询中将不会使用索引

3.优化子查询

子查询时，系统内层查询语句的查询结果建立一个临时表，然后外层查询语句再在临时表中查询记录，查询完成后撤销掉这些临时表，因此使用连接查询来代替子查询，这是由于连接查询不需要建立临时表，其速度比子查询要快

（1）将字段很多的表分解成多个表：有些表在设计了很多字段，其中有些字段使用频率低，当该表数据量大时，查询数据的速度就会被拖慢，因此将那些使用频率很低的字段放置在另外一个表中(两外一个表可以是 *_extra)

（2）增加中间表：在查询两个表中的几个字段，经常连表查询会降低数据库查询速度，可将这些字段建立一个中间表并将原来那几个表的数据插入到中间表中，之后使用中间表来进行查询和统计，以此提高查询速度

（3）增加冗余字段：表的规范化程度越高，表与表之间的关系就越多，若经常进行多表连接查询会浪费很多时间，可增加冗余字段的方式来提高查询速度

4.优化一行数据的查询

若已知查询结果只有一条，在查询语句之后添加 LIMIT 1 可以在数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据

5.使用JOIN级联查询

使用JOIN级联查询时，应该保证两表中JOIN的字段已经建立过索引且类型相同，这样MySQL内部会启动为你优化JOIN的SQL语句的机制，如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行

6.避免SELECT *查询数据

从数据库里读出越多的数据，那么查询就会变得越慢并且，如果数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。所以，应该养成一个需要什么就取什么的好的习惯

7.使用ENUM

ENUM类型是非常快和紧凑的，实际上ENUM保存的是 TINYINT，但其外表上显示为字符串。这样用这个字段来做一些选项列表变得相当的完美。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR

8.从PROCEDURE ANALYSE()取得建议

PROCEDUREANALYSE()会让MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础

9.尽可能使用NOT NULL

NULL会占用额外的空间且在进行比较的时候会是程序更复杂，其所代表的意义在不同的数据库中会有所不同坑是字符串"Empty"、"NULL"或是NULL，因此尽量使用NOT NULL

10.把IP地址存成INT UNSIGNED

把IP地址存成 INT UNSIGNED；如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。配合使用后面两个函数来转换ip信息：INET_ATON：将IP地址转换成数字型。INET_NTOA：将数字型转换成ip地址

11.拆分大的DELETE或INSERT语句

由于DELETE和 INSERT操作时会锁表的，表一锁住别的操作都无法进行，对于访问量大的网站所积累的进程/线程的延迟会让Web服务Crash，还会使这个服务器挂掉，因此限制一次对数据库数据修改的次数，对DELETE采用LIMIT，如：

DELETEFROM logs WHERE log_date

12.越小的列会越快

如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多

注：需要留够足够的扩展空间，否则日后修改表时会很困难

13.对表选择正确的存储引擎

MyISAM：适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好，甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的

InnoDB：是一个非常复杂的存储引擎，对于一些小的应用，它会比MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务

14.优化插入记录的速度

（1）禁用索引：插入记录时索引会对插入的记录进行排序，若插入大量数据时，这些排序会降低插入数据的速度，因此在插入大量数据时，先禁用索引，待插入数据完毕再开启索引

禁用索引的语句：

ALTER TABLE 表名 DISABLE KEYS;

重新开启索引的语句：

ALTER TABLE 表名 ENABLE KEYS;

（2）禁用唯一性检查：插入记录时系统会进行唯一性校验，校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，待插入数据完毕再开启

禁用唯一性检查的语句：

SET UNIQUE_CHECKS=0;

重新开启唯一性检查的语句：

SET UNIQUE_CHECKS=1;

（3）优化INSERT语句：当插入多条数据时，通过一条插入语句比分多条插入语句执行效率速度快很多，这是由于减少与数据库之间的连接等操作，因此能使用LOAD DATA INFILE语句比INSERT语句速度快

（4）在事务中进行插入处理，进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作，因此采用事务操作可提高插入数据得速度

（5）插入数据是有序的，这样可减少索引的维护成本

注：合并数据+事务+有序数据的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能

15.分析表、检查表和优化表

（1）分析表，语法格式：

ANALYZE TABLE表名1[,表名2,...];

返回的结果：

（2）检查表，语法格式：CHECK TABLE表名1[,表名2...][option];

option参数有5个参数分别是QUICK、FAST、CHANGED、MEDIUM和EXTENDED，这5个参数的执行效率依次降低。

注：option选项只对MyISAM类型的表有效，对InnoDB类型的表无效，CHECK TABLE语句在执行过程中会给表加上只读锁

（3）优化表，使用OPTIMIZE TABLE语句来优化表，该语句只对InnoDB和MyISAM类型的表有效，且该语句只能优化VARCHAR、BLOB或TEXT类型的字段，该语句可以消除删除和更新造成的磁盘碎片从而减少空间的浪费，语法格式：

OPTIMIZE TABLE 表名1[,表名2...];

16.优化MySQL服务器

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

需要从一下几个方面考虑：

1.配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。

2.配置告诉磁盘

3.合理分配磁盘io

4.配置多核处理器

优化MySQL参数：

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。常用参数如下：

key_buffer_size:表示缓冲区的大小。索引缓冲区的所有线程共享。增加索引缓冲区可以得到更好处理的索引（对所有读和多重写）。当然，这个值也不是越大越好，它的大小取决于内存大小，如果这个值太大，导致操作系统频繁换页，也会降低系统性能。

table_cache:表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多，这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。

query_cache_size:表示查询缓冲区的大小。该参数需要和query_cache_type配合使用。当query_cache_type值是时，所有的查询都不使用查询缓冲区，但是query_cache_type=0并不会导致Mysql释放query_cache_size所配置的缓冲区内存。当query_cache_type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定SQL_NO_CACHE，如SELECT SQL_NO_CACHE * FROM tb_name。当query_cache_type=2时，只有在查询语句中使用SQL_NO_CACHE关键字，查询才会使用查询缓冲区。实用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。

sort_buffer_size:表示排序缓存区的大小。这个值越大，进行排序的速度就越快。

read_buffer_size:表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。当线程从表中连续读取记录时，需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。

read_rnd_buffer_size:表示为每个线程保留的缓冲区的大小，与read_buffer_size相似，但主要用于存储按特定顺序读取出来的记录。也可以用SET SESSION read_rnd_buffer_size =n来临时设置该参数的值。如果频繁多次进行连续扫描，可以增加该值。

innodb_buffer_pool_size:表示InnoDB类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。但这个值太大会影响操作系统的性能。

max_connections:表示数据库的最大连接数。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。

innodb_flush_log_at_trx_commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为、1和2。值为时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘；值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；值为2时表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该参数的默认值为1。默认值1安全性最高，但是每次事务提交或是务外的指令都需要把日志写入（flush）硬盘，是比较费时的；值更快一点，但安全方面比较差；2值日志仍然会每秒写入到硬盘。所以即使出现故障，一般也不会丢失超过1~2秒的更新。

back_log:表示在mysql暂时停止回答新请求之前的短时间内，多少个请求可以被存在堆栈中。换句话说，改值表示对到来的Tcp/Ip连接的侦听队列的大小。只有期望在一个短时间内有很多连接，才需要增加该参数的值。操作系统在这个队列大小上也有限制。设定back_log高于操作系统的限制将是无效的。

interactive_timeout:表示服务器在关闭连接前等待行动的秒数。

sort_buffer_size:表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2097144(2MB)。

thread_cache_size:表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。

wait_timeout:表示服务器在关闭一个连接时等待行动的秒数。默认数值是28800。

17.其它技巧

（1）尽量使用count(*)计算数量:列的偏移量决定性能,列越靠后,访问的开销越大。由于count(*)的算法与列偏移量无关,所以count(*)最快,count(最后列)最慢