MySQL 性能优化

    做点东西要用到mysql故近来在学习mysql，和学习其他的数据库一样,总觉得建表容易，但是要让数据库良好的运行却不那么简单. 下面是一些mysql性能优化方面的知识．看了一下，受益非浅.

注意：Index（Name，Age）表示在Name，Age两列上建立联合索引

由于索引对数据库的查询性能有着至关重要的影响，下面是我的一些总结和体会：

• 一个查询一次只能使用一个索引：select name from user where name='plantegg' and age>35 , 如果Index（name); Index(age)的话，MySQL查询优化器会自动选择一个索引来使用；

• MySQL选择哪个索引，可以这样来看：mysql> show index from photo;
  +-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table | Non_unique | Key_name               | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
  | photo |          0 | PRIMARY                |            1 | photo_id      | A         |      237871 |     NULL | NULL   |      | BTREE      |         |
  | photo |          1 | index_random           |            1 | random        | A         |      237871 |     NULL | NULL   | YES  | BTREE      |         |
  | photo |          1 | FK_photo_profile_id    |            1 | profile_id    | A         |      237871 |     NULL | NULL   |      | BTREE      |         |
  | photo |          1 | FK_photo_temp_photo_id |            1 | temp_photo_id | A         |      237871 |     NULL | NULL   | YES  | BTREE      |         |
  | photo |          1 | FK_photo_album_id      |            1 | album_id      | A         |      237871 |     NULL | NULL   | YES  | BTREE      |         |
  +-------+------------+------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
• Cardinality越大表示索引候选分得越细（默认都是BTree索引）；
• 你也可以试试Force Index强制使用某个索引看看速度是不是MySQL是不是查询起来更快（如果真是这样的话你需要Analyze yourTable 了，MySQL重新计算你的Cardinality以帮助他正确地选择INDEX）
• 仔细分析Explain的结果：重点留意Extra，Key，Rows，Select_type的结果！
• 小心查询中的Group by 、order by之类的，基本上这样的查询在Explain的时候都会出现： Using where; Using temporary; Using filesort
• 联合索引要小心使用，Index（Name，Age)时,如果where name='pp' 能使用索引，where age=25时不能使用索引；where name='pp' and age>25 能使用索引；    where name ='pp'  order by  age  能使用索引；  where  name>'pp'  order by age  不能使用索引，但是 where  name>'pp'  order by name,age  能使用索引,请仔细留意差异  ;  order by name asc age desc 将不能使用索引！
• 索引只有被加入到内存里的时候对你的查询才有帮助，如果索引太大根本无法放入内存这样的索引失去了意义！访问索引的时候还需要Random  Aceess  Disk这比不用索引还慢！
• select  的时候能不用select * 就不要用，也就是需要哪些列只拿那些列（Hibernate那些对性能没有啥好处的）,比如：在Index（Name)的时候，select * from user where name like 'pp%' 和 select name from user where name like 'pp%' 两者性能千差万别，如果有10000条符合记录的结果的话（User表总共有10亿条记录）前一个查询可能需要2分钟（假设你的系统每秒100 IOPS的样子）后一个查询可能只需要0.01秒！因为前一个查询要从硬盘上取出散布在到处的这10000条记录，后一个查询直接从内存中的索引上拿Name就够了！后一个查询你explain的时候在Extra中会看到Using Index。
  
• 永远要警惕对磁盘的随机访问，顺序读写和随机访问的性能差别是N个数量级的（顺序读写的时候你的ＯＳ、Ｄｉｓｈ　Cache 这个时候大显身手）对这个问题如果感兴趣的话建议你去用C写个测试程序，随机读写的时候不断地fseek,相应地同样的功能你不要fseek而是通过顺序读写到内存中，在内存自己扔掉那些应该由磁盘去fseek的地方，应该明白我的意思吧！
• 5.0.27后，MYSQL就支持set profling=1了，这样可以详细分析你的SQL语句每一步骤的时间消耗了
• 如果order by 的时候有 limit + 索引配合的话，你会有意外惊喜的
• 如果你能够看到一个查询能像MySQL查询优化器那样在大脑中分解他的话，我想对你来说没啥难题了，:-)
  

 

    Analyze Table

MySQL 的Optimizer（优化元件）在优化SQL语句时，首先需要收集一些相关信息，其中就包括表的cardinality（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。
我们可以使用SHOW INDEX语句来查看索引的散列程度：

SHOW INDEX FROM PLAYERS;

TABLE   KEY_NAME COLUMN_NAME CARDINALITY
------- -------- ----------- -----------
PLAYERS PRIMARY PLAYERNO             14

因为此时PLAYER表中不同的PLAYERNO数量远远多于14，索引基本失效。
下面我们通过Analyze Table语句来修复索引：

ANALYZE TABLE PLAYERS;
SHOW INDEX FROM PLAYERS;
结果是：
TABLE   KEY_NAME COLUMN_NAME CARDINALITY
------- -------- ----------- -----------
PLAYERS PRIMARY PLAYERNO           1000

此时索引已经修复，查询效率大大提高。

需要注意的是，如果开启了binlog，那么Analyze Table的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入。

Checksum Table

数据在传输时，可能会发生变化，也有可能因为其它原因损坏，为了保证数据的一致，我们可以计算checksum（校验值）。
使用MyISAM引擎的表会把checksum存储起来，称为live checksum，当数据发生变化时，checksum会相应变化。
在执行Checksum Table时，可以在最后指定选项qiuck或是extended；qiuck表示返回存储的checksum值，而extended会重新计算checksum，如果没有指定选项，则默认使用extended。

Optimize Table

经常更新数据的磁盘需要整理碎片，数据库也是这样，Optimize Table语句对MyISAM和InnoDB类型的表都有效。
如果表经常更新，就应当定期运行Optimize Table语句，保证效率。
与Analyze Table一样，Optimize Table也可以使用local来取消写入binlog。

Check Table

数据库经常可能遇到错误，譬如数据写入磁盘时发生错误，或是索引没有同步更新，或是数据库未关闭MySQL就停止了。
遇到这些情况，数据就可能发生错误：
Incorrect key file for table: ' '. Try to repair it.
此时，我们可以使用Check Table语句来检查表及其对应的索引。
譬如我们运行
CHECK TABLE PLAYERS;

结果是
TABLE          OP    MSG_TYPE MSG_TEXT
-------------- ----- -------- --------
TENNIS.PLAYERS check status   OK

MySQL会保存表最近一次检查的时间，每次运行check table都会存储这些信息：

执行
SELECT    TABLE_NAME, CHECK_TIME
FROM      INFORMATION_SCHEMA.TABLES
WHERE     TABLE_NAME = 'PLAYERS'
AND       TABLE_SCHEMA = 'TENNIS';

结果是

TABLE_NAME   CHECK_TIME
----------   -------------------
PLAYERS      2006-08-21 16:44:25

Check Table还可以指定其它选项：
UPGRADE：用来测试在更早版本的MySQL中建立的表是否与当前版本兼容。
QUICK：速度最快的选项，在检查各列的数据时，不会检查链接（link）的正确与否，如果没有遇到什么问题，可以使用这个选项。
FAST：只检查表是否正常关闭，如果在系统掉电之后没有遇到严重问题，可以使用这个选项。
CHANGED：只检查上次检查时间之后更新的数据。
MEDIUM：默认的选项，会检查索引文件和数据文件之间的链接正确性。
EXTENDED：最慢的选项，会进行全面的检查。

Repair Table

用于修复表，只对MyISAM和ARCHIVE类型的表有效。
这条语句同样可以指定选项：
QUICK：最快的选项，只修复索引树。
EXTENDED：最慢的选项，需要逐行重建索引。
USE_FRM：只有当MYI文件丢失时才使用这个选项，全面重建整个索引。

与Analyze Table一样，Repair Table也可以使用local来取消写入binlog。