mysql优化小结

1. 说到mysql，我们立刻想起它体积

 　　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 'rn' (每一组数据由 换行符 分隔开)

　　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