MySQL 性能优化 Tips

其他小 Tips

• 针对 Innodb 表，尽量不执行 SELECT COUNT(*) 语句，因为 Innodb 表没有类似 MyISAM 那样的内部计数器来记录表记录总量，执行这个操作将会全表扫描，速度很慢。
• 尽量使用MyISAM表，除非必须使用其他类型，因为MyISAM类型的总体读写效率是相当高的，缺点是表级锁，而不是行/页级锁。
• 善用 EXPLAIN 来帮助你分析查询优化情况
• 如果需要对一个较大的且并发读写较多的数据表做 GROUP BY 等统计操作，建议使用摘要表来存储统计信息，定期更新统计表，这可能获得很大的性能改善。
• 查询时如果有 ORDER BY分句的话，注意让它的字段顺序和索引字段顺序对应，这样能加快排序速度
• 如果有一个多字段索引，则查询时，必须按照索引顺序来使用，否则该索引不会用到。例如: 索引 `idx_`(col1， col2， col3)，那么查询 SELECT ... FROM ... WHERE col1=1 AND col2=2; 使用索引，而查询 ... WHERE col2=2 AND col3=3; 或 ...WHERE col1=1 AND col3=3; 则不使用索引。
• WHERE 中的条件如果有恒量类型的(如 `field` = 1)，就尽量放在前面，这样能更快的执行过滤。
• 两个表连接时，连接字段的类型最好一致(包括字段长度)，这样的话索引速度快多了。
• 大部分情况下，字符类型的字段索引值需要一部分，例如 CREATE INDEX char_idx ON tbl1 ( name(10) );
• 尽量使用最合适的数据类型，能使用 ENUM 就不使用 TINYINT ，能使用 SMALLINT 就不使用 MEDIUMINT。这样能节省存储空间，增加数据存储量，提高搜索速度。不要担心这 样会对省级产生很大的影响，因为加入从 TINYINT 类型改变为 INT 的话，并不会改变原来的数据。
• 如果知道某个表总是频繁使用的话，可以把它放到 hot_cache 中，用以下方法:
  SET GLOBAL hot_cache。key_buffer_size=128*1024;
  CACHE INDEX `xxx` IN hot_cache;
• 把拖沓复杂，速度慢的的查询分解成多个简洁明了的查询，这样尽管查询次数多了，但是总体速度和效率却可能反而更高了，而且也减少了锁表的可能。
• 执行查询时，尽量不使用外部函数，因为这样的话就无法使用可能存在的索引，并且无论如何都会极大地降低效率。如: WHERE `create_time` > UNIX_TIMESTAMP(NOW()); 这样的查询。可以在程序中把当前的时间取得，然后直接执行构造好了的SQL语句。
• 在索引字段上使用 LIKE 查询时，左边不要使用 '%' 修饰符，这样就可以利用索引，否则无法使用索引。如 `name` LIKE 'yejr%';。
• 如果有可能，多使用存储过程，这大概能获得 22% 的性能提高。
• 如果并发访问量相对最大连接数小多了的话，最好使用永久连接，这样能节省不少连接时的系统资源损耗。
• 定期的在 MyISAM 表上执行 OPTIMIZE TABLE，这能整理随便，提高索引效率。
• 如果你主要按 col1,col2, ...顺序检索记录，请在对表大量更改后执行 ALTER TABLE ... ORDER BY col1, col2, ... 语句，这可以获得更好的性能。
• 对于频繁更改的MyISAM表，应尽量避免更新所有变长字段(VARCHAR、BLOB和TEXT)。
• 对于记录总数超过 500 万的单表，就应该赶紧考虑分表了。分表策略有多种，比如按 ID 号段，或者按时间切分，等等。
• 创建数据表时尽量指定字段不能为 NULL，并且有默认值。
• 使用 LOAD DATA，而不是使用大批量的 INSERT 语句来导入数据。
• 使数据表名和字段名尽可能的短，例如在 user 表中使用字段名 name，而不是 user_name。
• 用 DELAY_KEY_WRITE = 1 选项让 MyISAM 更快地更新索引，因为在表关闭之前它们不刷新到硬盘上。缺点是如果服务器如果突然被杀掉了，重启之后就必须运行 myisamchk 修复索引才行。
• 采用复制机制来分摊读数据的负载，把写数据只放在主服务器上，把读平均分摊到各个从服务器上，能大大提高系统负载。