《高性能MySQL》摘要

零、存储引擎

1. MyISAM表上的dml会导致锁表

2. MyISAM会压缩索引，以变得更小。但会消耗更多CPU。如果是IO密集型的应用，则可能会带来很大好处

3. MyISAM不支持聚簇索引。对于定长行，索引里会存储行号。在查找数据时，在表头根据行号跳过所需的字节就可以找到数据

4. MyISAM内存只缓存索引，数据则依赖于操作系统缓存。因此访问数据需要一次系统调用，这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中最大开销的场景。

innodb_stats_persistent参数控制统计信息是否持久化，设为OFF的话就只能保存在内存里


一、 数据类型


1. 一般情况下，应该选用可以正确存储数据的最小数据类型。更小的数据类型通常意味着效率更高，它们占用更少的磁盘、内存和CPU缓存，处理数据时需要的CPU周期更少。

2. 简单更好，简单数据类型操作耗用更少的CPU周期。比如处理INT会比处理字符串更快捷。因为字符集和校对规则（排序规则）使字符比较比整形比较更复杂。

3. 尽量避免NULL,尽量指定为NOT NULL。可为NULL的列使得索引、索引统计和值比较都变得复杂。可为NULL的列会占用更多的存储空间，MySQL也需要做特别处理。另外可为NULL的列在每个索引记录上也会多占用一个额外的字节。例外的是，InnoDB存储引擎使用单独的位存储NULL值，所以对于稀疏数据（大多数为NULL）有很好的空间效率。

4. DECIMAL的精确运算是由MySQL内部实现的，而浮点运算是CPU原生支持的，所以浮点运算效率明显会更高。可以考虑用BIGINT代替DECIMAL，把金额等乘以相应的倍数来去掉小数点即可

5. CHAR会将字符串后面的空格截取，使用时要注意

6. 通常MySQL会分配固定大小的内存块来保存内部值，更长的列会消耗更多的内存。尤其是使用内存临时表进行排序或操作时会特别糟糕。在使用磁盘临时表时同样如此。因此，可以定义为VARCHAR(5)就不要定义为VARCHAR(200)

7. 尽量不要用TEXT和BLOB，会有更大消耗

8. MySQL内部会将ENUM的值根据位置保存为整数，所以尽量不要用数字作为ENUM枚举常亮，会导致混乱

9. MySQL能存储的最小时间粒度为秒

10. DATETIME使用8字节存储空间，可以存储1001年 - 9999年，精度为秒

11. TIMESTAMP使用4字节，但只能存储1970年 - 2038年。

12. 一旦选定了一种数据类型，最好在所有的关联表中采用同样的数据类型，并且要精确匹配。否则会发生隐式转换导致效率低下。InnoDB在类型不匹配时，不能创建外键。

13. ID最好采用INTEGER，效率高并且可以很容易采用AUTO_INCREMENT

14. 避免使用MySQL已经遗弃的特性，比如指定浮点数的精度或整数的显示宽度

二、SCHEMA设计


1. 太多的列会导致高CPU，因为MySQL存储引擎API工作时需要在服务器层和存储引擎之间通过行缓冲格式来拷贝数据，然后在服务器层将缓冲数据解析成各个列。将行缓冲数据从编码过的列转换成行数据结构的操作代价是非常高的。

2. 如果应用用到计数器表，在更新计数器时可能会遇到并发争用问题。这时候可以将计数保存在多行里，每次随机选择一行更新。计数值就用多行相加的结果来得出

3. 大部分修改表结构中，MySQL都是通过构建一张新表，然后从旧表中copy数据到新表，然后删除旧表实现的。耗时很长。

4. 修改列的默认值可以通过 ALTER COLUMN 而不是 ALTER TABLE语法来完成，这个操作不用重建表，因而非常快


三、索引


1. 如果查询中有某个列使用范围查询，则其有变的列都无法使用索引优化查找。所以索引列的顺序非常重要。日期字段上经常会有这个问题。

2. 如果索引列很大，消耗大量存储、内存，效率变低。这时候可以通过在列上建哈希索引来避免这个问题。如果存储引擎不支持哈希索引，可以通过使用CRC32对列值做哈希，哈希值放在另一列上。再对这个哈希值列做索引。这个列的数据可以通过TRIGGER做维护

3. “三星索引”：索引将相关记录放到一起获得一星；索引中的数据顺序和查找中的排列顺序一致则获得二星；索引中的列包含了需要查询的所有列则获得三星

4. InnoDB存储引擎中，非主键索引会包含主键数据，所以主键要尽可能小。否则普通索引会很大（主键都是聚簇索引？）

5. 需要索引的列过大的话，可以索引开始的部分字符，这样可以减小索引，提升效率。但要仔细评估调整前缀的长度以获得足够的区分度

6. 索引合并（INDEX MERGE）大多数时候说明索引建得不合理。当出现对多个索引做相交操作时（有多个AND条件），通常意味着需要包含多个列的多列索引，而不是多个单列索引。当出现对多个索引做联合操作时（有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当有些索引选择性不高，返回大量数据的时候。更重要的是，优化器不会把这些计算到“COST”里，优化器只关心随机读取。这会使得该执行计划的查询成本被低估，还不如走全表扫描。这样不但会消耗更多的内存和CPU，影响并发性。通常来说，还不如将查询改为UNION的方式往往更好。

7. MySQL的聚簇索引相当于Oracle的索引组织表。数据和索引保存在同一个结构中，因此一个表只能有一个聚簇索引。InnoDB使用主键作为聚簇索引，当表上没有主键时，会使用非空唯一索引代替。如果也没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

8. 聚簇索引的优点：
 可以把相关数据保存在一起，比如某个用户的交易记录都在物理上保存在一块，这样获取相关数据就非常高效；
 数据访问更快，因为数据和索引都保存在同一结构中；
 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。


9. 聚簇索引的缺点：
 如果数据都在内存里，聚簇索引就没什么优势了；
 插入速度严重依赖于与插入顺序，按主键顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按主键顺序加载数据，那加载完成之后最好使用OPTIMIZE TABLE重新组织下表；
 更新聚簇索引列的代价很高，会强制InnoDB将每个被更新的行移动到新位置；
 表插入新行或主键更新导致移动行的时候，可能面临“页分裂（page split）”的问题。当行的主键值要求必须将这一行数据插入到某个已满的页中时，存储引擎会将该页分裂为两个页面来容纳改行，导致表     占用更多的存储空间。所以需要避免随机值做聚簇索引
 聚簇索引可能导致全表扫描变慢。尤其是行比较稀疏，或者页分裂导致数据存储不连续的时候
 二级索引（非聚簇索引）变大，因为叶子节点包含了引用行的主键列
 二级索引访问需要两次索引查找，而不是一次（先根据二级索引找到主键，再根据主键获取到数据）
    
10. InnoDB的聚簇索引如果是覆盖索引（在EXPLAIN的Extra列显示 Using index）的话特别有用，因为二级索引在叶子节点中包含了主键值，如果主键值包含所有查询的列，就可以避免对主键索引的二次查询。

11. 如果查询是使用通配符开头的LIKE进行的，MySQL不会使用索引上的这个字段来过滤数据。比如索引index1包含col1,col2，当查询条件为：where col1='xx' and col2 like '%yy%'，则不会在索引上过滤掉不包含'yy'的数据，需要先访问数据行，再从中查找出符合col2条件的数据。5.6版本包含“索引条件推送”(index condition pushdown)的重大改进，可以大大改进现有的查询方式。

12. 索引条件推送:

13. 当EXPLAIN的type列显示为index时，说明使用了索引排序。当多表关联时，只有ORDER BY子句引用的字段全部为第一个表的字段时，才能使用索引排序。而且ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则MySQL都需要执行排序操作，而无法利用索引排序（除非最左前缀列是常量）

14. InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁（所有情况？）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22818880/viewspace-2073766/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22818880/viewspace-2073766/