mysql优化小结（插入和查询）_MySQL

bitsCN.com

1. 说到mysql，我们立刻想起它体积小、速度快、还开源的特点，所以它应用颇广。今天我们来总结一下mysql中最频繁的两个操作：插入和查询，的优化方法。

2.插入：

3.一、文本导入

4.使用LOAD DATA INFILE从文本下载数据这将比使用插入语句快20倍。

5.示例：

6.load data local infile 'C:/Users/DELL/Desktop/Description&Data/news1.txt' into table news （文件位置）

7.fields terminated by ',' （每一字段由‘，’分隔开）

8.lines terminated by '/r/n' （每一组数据由 换行符 分隔开）

9.(content,date,ip,author,subject); （解释txt中每一行由这几个字段组成）

10.二、一次插多条

11.使用带有多个VALUES列表的INSERT语句一次插入几行比使用一个单行插入语句快几倍。

12.示例：

13.INSERT INTO food VALUES

14.(NULL,'EE果冻','EE果冻厂', 1.5 ,'2007', 2 ,'北京') ,

15.(NULL,'FF咖啡','FF咖啡厂', 20 ,'2002', 5 ,'天津') ,

16.(NULL,'GG奶糖','GG奶糖', 14 ,'2003', 3 ,'广东') ; 而不是：

1.INSERT INTO food VALUES (NULL,'EE果冻','EE

果冻厂', 1.5 ,'2007', 2 ,'北京');

2.INSERT INTO food VALUES (NULL,'FF咖啡','FF

咖啡厂', 20 ,'2002', 5 ,'天津');

3.INSERT INTO food VALUES (NULL,'GG奶糖','GG

奶糖', 14 ,'2003', 3 ,'广东');

4.第一种方式减少了与数据库之间的连接等操作，所以其速度比第二种方式要快。

三、使用varchar

之所以使用varchar，而不是char，因为varchar是按实际数据的长度存储的；而char在存储完实际数据后，还把空白的空间自动补全。所以明显char插入会比varchar慢。实验说明，无论插入数据涉及的列是否建立索引，char的效率都明显低于varchar。

四、控制字段长度

这个很明显，越小的数据类型占用的空间越小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了，所以要综合考虑决定字段长度。

五、非空+默认值

NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，所以我们最好把属性尽量设置成非空，同时赋予它一个默认值，比如 0。

六、禁用事务

MySQL数据库表有两种类型，一种是支持事务处理，一种是不支持事务处理。MySQL在处理这两种表时，分别使用了不同类型的数据库引擎，因此数据库引擎在插入时效率不同，理论上说启用了事务功能后会比较慢。

示例：

Create Table(

….. /*字段说明*/

) ENGINE=InnoDB/MyISAM (带事务、不带事务)

事实证明是否禁用事务对插入数据的速度影响很大。
不过还是要多说一句，事务并非只会让我们的插入变慢。用了事务，就不可避免的要牺牲一部分速度，但是可以很大程度上保证数据的正确性。

七、禁用索引

插入记录时，MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时，这些排序会降低插入记录的速度。为了解决这种情况，在插入记录之前先禁用索引。等到记录都插入完毕后再开启索引。（虽然对于先插数据还是先建索引可能有一点争议）

示例：

1.ALTER TABLE 表名 DISABLE KEYS ; （禁用索引）
2.
1.ALTER TABLE 表名 ENABLE KEYS ; （启用索引）
1.
1.

八、禁用唯一性检查

我们知道，插入数据时MySQL会对插入的记录进行唯一性校验。这种校验也会降低插入记录的速度。可以在插入记录之前禁用唯一性检查。等到记录插入完毕后再开启。禁用唯一性检查的语句如下：

1.SET UNIQUE_CHECKS=0;

2.重新开启唯一性检查的语句如下：

1.SET UNIQUE_CHECKS=1;

九、先锁定表再插入

这将提高数据库性能，因为索引缓冲区只是在所有的插入语句完成后才对磁盘进行一次刷新。通常情况下，有多少个插入语句就会有多少次索引缓冲区刷新。如果你可以用一个插入语句实现所有行的插入，则无需使用显式锁定语句。

示例：

LOCK TABLES； （锁定表）

十、启用并行插入

可以对myisam表并行插入Concurrent_insert系统变量可以被设置用于修改concurrent-insert处理。该变量默认设置为1。如果concurrent_insert被设置为0，并行插入就被禁用。如果该变量被设置为2，在表的末端可以并行插入，即便该表的某些行已经被删除。

十一、延迟插入

如果你的客户不能或无需等待插入完成的时候，这招很有用。当你使用MySQL存储，并定期运行需要很长时间才能完成的SELECT和UPDATE语句的时候，你会发现这种情况很常见。当客户使用插入延迟，服务器立刻返回，如果表没有被其他线程调用，则行会列队等待被插入。使用插入延迟的另一个好处就是从多个客户插入的情况会被绑定并记录在同一个block中。这将比处理多个独立的插入要快得多。

查询：

一、优化数据类型

查询优化方面，数据类型是查询的基础，所以我们首先得优化我们的数据类型。实际上，数据类型方面，查询所需要的优化和插入差不多，主要也是避免null和尽量使用小的字段。

二、使用连接查询

使用连接查询效率一般都优于子查询。遇到子查询时，MySQL查询优化引擎并不是总是最有效的，这就是为什么经常将子查询转换为连接查询的原因了，优化器已经能够正确处理连接查询了，当然要注意的一点是，确保连接表(第二个表)的连接列是有索引的，在第一个表上MySQL通常会相对于第二个表的查询子集进行一次全表扫描，这是嵌套循环算法的一部分。

三、索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引可以加快表与表之间的链接，可以大大加快数据的检索速度。

但是索引会带来额外的开销，所以我们一般在经常搜索的列和经常需要连接的列上建立索引。

四、为表设置id属性

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。

因为在mysql的数据引擎下，很多操作都需要主键，所以死主键的性能和设置变得非常重要，比如，集群，分区……

五、Explain

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

六、避免select *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

所以，你应该养成一个需要什么就取什么的好的习惯。

七、调整内部变量

MySQL是如此的开放，所以可轻松地进一步调整其缺省设置以获得更优的性能及稳定性。需要优化的一些关键变量如下：

改变索引缓冲区长度(key_buffer)：

一般，该变量控制缓冲区的长度在处理索引表（读/写操作）时使用。MySQL使用手册指出该变量可以不断增加以确保索引表的最佳性能，并推荐使用与系统内存25％的大小作为该变量的值。这是MySQL十分重要的配置变量之一，如果你对优化和提高系统性能有兴趣，可以从改变 key_buffer_size变量的值开始。

改变表长(read_buffer_size)：

当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

设定打开表的数目的最大值(table_cache)：

该变量控制MySQL在任何时候打开表的最大数目，由此能控制服务器响应输入请求的能力。它跟max_connections变量密切相关，增加 table_cache值可使MySQL打开更多的表，就如增加max_connections值可增加连接数一样。当收到大量不同数据库及表的请求时，可以考虑改变这一值的大小。

对缓长查询设定一个时间限制(long_query_time)：

MySQL带有“慢查询日志”，它会自动地记录所有的在一个特定的时间范围内尚未结束的查询。这个日志对于跟踪那些低效率或者行为不端的查询以及寻找优化对象都非常有用。long_query_time变量控制这一最大时间限定，以秒为单位。

八、使用 Limit 1

当我们要一行数据时应该使用 LIMIT 1，这样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

文章到此就结束了，但是作者对于mysql优化的理解仅仅是管中窥豹，而更多的优化策略需要大家亲自去探索去实践喔。

bitsCN.com