数据库的优化

数据库的优化

1. insert优化tips

insert插入一条记录花费的时间由以下几个因素决定：连接、发送查询给服务器、解析查询、插入记录、插入索引和关闭连接。

此处没有考虑初始化时打开数据表的开销，因为每次运行查询只会做一次。如果是B-tree索引，随着索引数量的增加，插入记录的速度以logN的比例下降。

可以用以下几种方法来提高插入速度：

1. 如果要在同一个客户端在同一时间内插入很多记录，可以使用insert语句附带有多个values值。这种做法比使用单一值的insert语句快多了(在一些情况下比较快)。如果是往一个非空数据表增加记录，可以调整变量bulk_insert_buffer_size的值使其更快。
2. 对实时性要求不高情况下，如要从不用的客户端插入大量记录，使用insert delayed语句也可以提高速度。
3. 对应MyISAM，可以在select语句正在运行时插入记录，只要这时候没有正在删除记录。
4. 对于MyISAM，在进行大批量插入前可以将索引关闭，等全部插入完毕后再开启索引，进行索引更新。
5. 想要将一个文本文件加载到数据表中，可以使用load data infile。速度上通常是使用大量insert语句的20倍。
6. 对于InnoDB的insert，若插入上百万，建议分批进行，批量插入3000~5000后，sleep数秒钟之后进行下一次插入，可以避免同步延迟的累积。

1. delete优化

如果编写的delete语句中没有where子句，则所有的行都被删除。当不想知道被删除的行的数目时，有一个更快的方法，即使用truncate table。

如果删除的行中包括用于auto_increment列的最大值，对于MyISAM表或InnoDB表不会被重新用。如果在autocommit模式下使用delete from tbl_name(不含where子句)删除表中的所有行，则对于所有的表类型(除InnoDB和MyISAM外)，序列重新编排。对于InnoDB表，此项操作有一些例外。

对于MyISAM和BDB表，您可以把auto_increment次级列指定到一个多列关键字中。在这种情况下，从序列的顶端被删除的值被再次使用，甚至对于MyISAM表也如此。delete语句支持以下修饰符：

如果您指定low_priority，则delete的执行被延迟，直到没有其它客户端读取本表时再执行。

对于MyISAM表，如果使用quick关键词，则在删除过程中，存储引擎不会合并索引端结点，这样可以加快部分种类的删除操作的速度。

在删除行的过程中，ignore关键词会使MySQL忽略所有的错误。(在分析阶段遇到的错误会以常规方式处理。)由于使用本选项而被忽略的错误会作为警告返回。

在MyISAM表中，被删除的记录被保留在一个带链接的清单中，后续的insert操作会重新使用旧的记录位置，要想重新使用未使用的空间并减小文件的尺寸，则可以使用optimize table语句或myisamchk应用程序重新编排表。optimize table更简便，但是myisamchk速度更快。

quick修饰符会影响到在删除操作中索引端结点是否合并。当用于被删除的行的索引值被来自后插入的行的相近的索引值代替时，delete quick最为适用。在此情况下，被删除的值留下来的空穴被重新使用。

未充满的索引块跨越某一个范围的索引值，会再次发生新的插入。当被删除的值导致出现未充满的索引块时，delete quick没有作用。在此情况下，使用quick会导致未利用的索引中出现废弃空间。

1. update优化

update更新查询的优化同select查询一样，但需要额外的写开销。写的速度依赖更新的数据大小和更新的索引的数量。所以，锁定表，同时做多个更新比一次做一个快得多。

另一个提高更新速度的办法是推迟更新并且把很多次更新放在后面一起做。如果锁表了，那么同时做很多次更新比分别做更新来得快多了。

注意，如果是在 MyISAM 表中使用了动态的记录格式，那么记录被更新为更长之后就可能会被拆分。如果经常做这个，那么偶尔做一次 optimize table 就显得非常重要了。

如题：mysql查询很快，删除很慢，数据有1000多万条，字段25个？

查询1万条数据不到1s，删除1万条数据10s多以上

用IN把多个要删除的ID用一句SQL写入，应该会一个ID一句SQL要快得多。

可能与索引有关，删除数据的速度和创建索引的数量成反比的。因此可以在删除数据之前先把索引删除，删除数据完成之后再把索引建立。

mysql数据库数据过多时sql操作会很慢

1、建立合适的表索引，用explain分析一下Sql语句有没有用到合适的索引

2、如果数据量有千万级别的话，建议分表处理

3、用show processlists 查看数据库中的链接线程，看是否出线锁表的情况

4、其它的一些针对不通表引擎的配置，比如buffer之类的配置，这个可以参照网站里面mysql优化的相关问题

配置慢查询日志，找出耗时的sql，再explain索引是否合理

1、适当合理的建立索引

2、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

3、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段

4、尽量不要使用like进行查询数据，这个会导致全表扫描

5、针对慢查询日志，进行合理的优化

6、可以采用缓存处理的数据进行缓存，避免重复查询

7、实时性要求不是很高的数据，可以做成缓存页面，下次访问的时候直接访问缓存页面

如果单表的数据超过千万级，sql操作是会变慢的，一般采取的方式是分表，这样单表的数据就会少，效率就会高，带来的问题是有些查询要分成多个查询，然后合并。