常见优化
1 )使用永久连接到数据库,避免连接的开销。如果需要初始化很多连接,而又不能用永久连接,那么可以修改变量 thread_cache_size的值。
2 )尽量不要在经常需要更新的 MyISAM表上用太过复杂的 SELECT 语句,这是为了避免在读和写之间争夺锁。
3 )如果必须对一个较长的而且是不定长的字符串字段进行频繁查找,可以考虑建立该字段的md5映射。
4 )在删除一大堆记录之后执行 OPTIMIZETABLE 语句,这样数据排列更加整齐,可以提高下次查询速度。
5 )如果更新比较频繁,定期执行 OPTIMIZETABLE 防止使用动态记录格式的 MyISAM 表产生碎片。
6 )使用 analyze 帮助优化 MySQL 的查询(只适合 MyISAM 和 BDB 表,处理过程中锁表)。
1.2. 有效使用索引
1 )索引是包含部分字段(域)的列表,索引的字段一般都需要排序,这样查找速度会更快。在 MySQL 中, MyISAM 表的索引被分离的文件保存, InnoDB 表存储在表空间中。
2 )在 MySQL 中,有四种类型的索引:主键、唯一性索引、全文索引和普通索引。
3 )使用索引的最普遍之处是与 where 语句条件匹配的行。只出现在域列表(紧跟 select 之后, from 之前的字段)都不能利用索引。
4 )在索引字段上执行 max 或 min 函数会非常快。
5 )在 order by 字段使用索引,会大大加快排序的速度,在 heap 表的 order by 不能使用索引。
6 )在跨表连接时使用属于字段作为条件,可以加快连接速度。
7 )在索引字段上支持通配符,不过在以通配符开头时不能有效使用索引。
8 )不要在枚举的字段上创建索引,重复的情况太多,没有多少效率,浪费磁盘空间。
9 )尽量使用短索引,比如在整数字段或部分域上创建索引。
10 )不要创建太多索引,否则插入和更新的速度会较慢。
11 )记住索引会使用最左边前缀的规则。
12 )大量数据导入时,可以先不要创建索引,等数据导入完成后再创建索引会加快速度。
13 )使用 explain 分析 MySQL 如何使用索引。
1.3. 加速 select
1 )尽量使用 limit ,避免全表扫描。
2 )尽量使用索引,而且是正确的使用。
3 ) order by 尽量在索引上执行,这样只要扫描索引即可,而且索引一般是经过排序的。
4 )尽量采用数字作为索引,数字的排序和比较都必字符串快很多。
5 )对于非索引排序,增加 sort_buffer_size 的值可以加快速度。
6 )必须在 where 中使用索引,否则无效。另外索引是自左向右解析的,可以部分利用索引。
7 )大部分情况使用内连接即可,有些特殊情况必须使用其他连接方式,否则有可能会降低效率。
8 ) max 、 min 或 count 操作尽量在索引上执行。
9 )当想让 SELECT 语句的优先级比插入操作还高时,用 INSERTLOW_PRIORITY 。
10 )用 SELECT HIGH_PRIORITY 来使检索记录跳过队列,也就是说即使有其他客户端正要写入数据,也会先让 SELECT执行完。
11)关于通过查询缓冲提高查询速度
12)尽量避免SELECT *命令
从表中读取越多的数据,查询会变得更慢。他增加了磁盘需要操作的时间,还是在数据库服务器与WEB服务器是独立分开的情况下。
你将会经历非常漫长的网络延迟,仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列,这是一个非常良好的习惯。
1.4. 加速 insert
1 )如果要在同一个客户端在同一时间内插入很多记录,可以使用 INSERT 语句附带有多个 VALUES 值。这种做法比使用单一值的 INSERT语句快多了(在一些情况下比较快)。
2 )如果是往一个非空的数据表里增加记录,可以调整变量 bulk_insert_buffer_size 的值使之更快。
3 )想要让 MyISAM 表更快,在 LOADDATA INFILE 和 INSERT 时都可以增加系统变量 key_buffer_size 的值。
4 )要从不同的客户端中插入大量记录,使用 INSERT DELAYED 语句也可以提高速度。
5 )对 MyISAM ,可以在 SELECT语句运行时插入记录,只要这时没有正在删除记录。
6 )想要将一个文本文件加载到数据表中,可以使用 LOAD DATA INFILE 。这通常是使用大量 INSERT语句的 20 倍。
7 )可以在锁表后,一起执行几个语句来加速 INSERT 操作。
这对性能提高的好处在于:直到所有的 INSERT语句都完成之后,索引缓存一次性刷新到磁盘中。
通常情况是,多次 INSERT语句就会有多数次索引缓存刷新到磁盘中的开销。
8 )如果能在一个语句中一次性插入多个值的话,显式的锁表操作也就没必要了。
9 )对事务表而言,用 BEGIN/COMMIT 代替 LOCKTABLES 来提高速度。
10 )锁表也会降低多次连接测试的总时间,尽管每个独立连接为了等待锁的最大等待时间也会增加。
11 )想要让 MyISAM 表更快,在 LOADDATA INFILE 和 INSERT 时都可以增加系统变量 key_buffer_size 的值。
1.5. 其他加速
1 )加速 update :建索引和推迟更新。
2 )加速 delete :删除单个记录的时间和它的索引个数几乎成正比。想更快地删除记录,可以增加索引键的缓存。
3 )如果想要删除数据表的所有记录,请使用 TRUNCATE TABLE tbl_name 而不是 DELETE FROM tbl_name 。
4 )多表删除数据时,尽量使用 mysql 的多表删除机制,这样更快、更安全。