MySQL-数据类型及性能优化

性能优化：完成某件任务所需的时间度量。

性能剖析

性能剖析是测量和分析时间花费在哪里的主要方法。性能剖析一般有两个步骤：测量任务所花费的时间；然后对结果进行统计和排序，将重要的任务排到前面。

性能剖析工具

show status、show profile、检查慢查询日志的条目、show processlist;

性能低下的原因

资源被过度使用，余量已经不足以正常工作；
资源没有被正确配置；
资源已经被损坏或者失灵；

数据类型

数字类型
tinyint smallint mediumint int bigint decimal float double

字符类型
char varchar

BLOB和TEXT类型
BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型由字符集和排序规则

日期和时间类型
DATETIME和TIMESTAMP

位数据类型
BIT和SET

范式的优缺点

范式化的更新操作比反范式化要快；
当数据比较好地范式化时，就只有很少或者没有重复数据，所以只需要更改更少的数据；
范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快；
很少由多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

范式化设计通常需要关联。

数据库设计原则

尽量比卖你过度设计；
使用小而简单的合适数据类型，除非真实数据模型中由确切的需要，否则应该尽可能地避免使用NULL值；
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列；
注意可变长字符串，其在临时表和排序时可能导致悲观的按最长长度分配内存；
尽量使用整型定义标识列；
避免使用MySQL已经遗弃的特性；
小心使用ENUM和SET。

索引优化

B-Tree：适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。使用场景：全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、只访问索引的查询；
B-tree的限制：如果不是按照索引的最左列开始查找，则无法使用索引。不能体哦爱国索引中的列；如果查询中由某个列的范围查询，则其右边所有列都无法使用索引优化查找。

哈希索引（hash index）：基于哈希表实现，只有精确匹配索引列的查询才有效。
哈希索引的限制：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的速度很快，所以大部分情况下这一点对性能的影响并不明显；哈希索引数据并不是按照索引值顺序排序存储的，所以无法用于排序；哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；哈希索引支持等值比较查询；访问哈希索引的数据非常快，除非有很多哈希冲突，当有哈希冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行；如果哈希冲突有很多的话，一些索引维护操作的代价也很高。

索引的有点：索引可以大大减少放服务器需要扫描的数据量；索引可帮助服务器避免排序和临时表；索引可以将随机I/O变为顺序I/O。

索引统计的成本：
Memory引擎根本不存在索引统计信息；
MyISAM将索引统计信息存储在磁盘中，ANALYZE TABLE需要进行一次全索引扫描来计算索引基数；
知道MySQL5.5版本，InnoDB也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。

查询优化

查询的生命周期：从客户端，到服务器，在服务器上面解析，生成执行计划，执行，并返回结果给客户端。

查询性能低下的基本原因：访问的数据太多；
分析方法：确认应员工程序是否在索引大量超过需要的数据。这意味着访问了太多的行，但有时候也有可能访问了太多的列；

确认MySQL服务器曾是否在分析大量超过需要的数据行。

查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧区优化它：
使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果。

改变库表结构；

重写这个复杂的查询。

客户端发送一条查询给服务器；
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；
服务器进行SQL解析、预处理，再由优化器生成对应的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的api来执行查询；
将结果返回给客户端。

优化方式：
重新定义关联表的顺序；
将外连接转化为内连接；
使用等价变换规则；
优化COUNT()、MIN()、MAX()；
预估并转化常量表达式；
覆盖索引扫描；
子查询优化；
提前终止查询；
等值传播；
列表IN（）的比较；