mysql优化where语句

mysql优化where语句

［摘自MYSQL帮助手册］

因为工作需要对一个大表做批量更新操作。所以整理一下有关如何优化where条件的资料希望能够加快速度/。

一、相关优化技巧

1、使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接，可能要更改thread_cache_size变量的值。

笔记：这一点我深有感触。

我的代码是封装在一个一个类里面的。有一个数据库连接类我一开始的时候写了一个静态方法实现获取连接对象。然后在需要调用到的地方先创建连接对象然后再将连接对象给关闭了。这样的话是创建短连接。短连接在实际的web应用中肯定的。如果有多个客户端的连接保持不关闭的话数据库马上连接数超标。所以一般的WEB应用都保持做短连接处理。

但是现在的问题是我需要一直保持与数据库的连接所以我就将其调整为长连接了。

什么时候使用长连接呢？在我们做数据库连接池的时候就要做长连接/ 连接池里面的连接长期保持有效。而不会自已中断。这样的话后面的全部应用都可以快速连接连接而不用频繁地创建与关闭数据库连接。［见另一篇文章：长连接与短连接心得笔记］

2、总是检查所有查询确实使用已经在表中创建了的索引

可以使用EXPLAIN 来分析是否有使用到索引。

3、尝试避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题。

笔记：如果我现在在topic表上面做复杂的查询处理的话估计就会锁表。因为现在是在频繁地进行写处理操作！

4、对于没有删除的行的MyISAM表，可以在另一个查询正从表中读取的同时在末尾插入行。如果这很重要，应考虑按照避免删除行的方式使用表。另一个可能性是在删除大量行后运行OPTIMIZE TABLE

5、对于频繁更改的MyISAM表，应试图避免所有变长列(VARCHAR、BLOB和TEXT)。如果表包括单一的变长列则使用动态记录格式

笔记：如果一个BBS版块使用的是MyISAM引擎的话就最好少用变长类型了。对于 InnoDB的话就不太清楚了

6、当不需要知道何时写入数据时，使用INSERT DELAYED。这样可以加快处理，因为很多记录可以通过一次磁盘写入被写入。

笔记：这个MS有效哦。但我没有做过测试。

7、使用多行INSERT语句通过一个SQL命令来存储很多行(许多SQL服务器支持它，包括MySQL)。

笔记：这就是所谓的批处理提交写成

Insert into a values(),()  这种效率要比单条单条写要快。

8、使用LOAD DATA INFILE装载较大数量的数据。这比使用INSERT要快得多。

9、使用AUTO_INCREMENT列构成唯一值。［主键］

10、当MyISAM使用动态表格式时，偶尔使用OPTIMIZE TABLE可以避免碎片。

11、在Web服务器中，图象和其它二进制资产应该作为文件存储。也就是仅在数据库中存储的本文件的引用而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多，因此使用文件一般要快得多。

笔记：这个肯定的像图片肯定是用文件存储

12、对经常访问的不重要数据(如为没有在Web 浏览器中启用cookie的用户最后显示的标语的相关信息)使用内存表。在许多Web应用程序环境中也可以使用用户会话来处理可变状态数据。

 

二、提高INSERT语句的速度

插入一条记录需要的时间由下列因素组成。

连接（3%）

发送查询到服务器（2%）

分析查询（2%）

插入记录（1*记录大小）

插入索引（1*索引）                  看到了没 所以我觉得在线插入频繁的表不要有索引

关闭（1%）

 

加快插入的一些方法：

1、  如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，可以调节bulk_insert_buffer_size变量，使数据插入更快。

2、  如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度。

笔记：MS这个也可以加进来做优化处理哦！因为我现在是从多个客户端同时往里面写数据的所以我想可以通过使用INSERT DELAYED 来处理了！

3、  用MyISAM，如果在表中没有删除的行，能在SELECT语句正在运行的同时插入行。

4、  当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。用LOAD DATA INFILE把数据插入到表中，因为不更新任何索引，因此很快。

5、  锁定表可以加速用多个语句执行的INSERT操作：

LOCK TABLES a WRITE;

INSERT INTO a VALUES (1,23),(2,34),(4,33);

INSERT INTO a VALUES (8,26),(6,29);

UNLOCK TABLES;

这样性能会提高，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行，就不需要锁定。

对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。

［对于innoDB就是用这种办法来解决］

INSERT、UPDATE和DELETE操作在MySQL中是很快的，通过为在一行中多于大约5次连续不断地插入或更新的操作加锁，可以获得更好的整体性能。如果在一行中进行多次插入，可以执行LOCK TABLES，随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。

笔记：不知道我的更新操作是否需要做锁表操作了

三、UNDATE语句的速度

使更改更快的另一个方法是推迟更改然后在一行内进行多次更新。如果锁定表，同时做多个更新比一次做一个快得多。

四、DELETE语句的速度

删除一个记录的时间与索引数量确切成正比。为了更快速地删除记录，可以增加键高速缓冲的大小。

如果想要删除一个表的所有行，使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name。

 

结论：是否需要调整代码？同时更新300W的记录 是否需要做分页处理？