mysql常见调优

2014-05-06 本文已影响382人 UncleYee

MySQL作为最常用的关系型数据库之一，在一些中小型项目中使用的极为普遍。我所在公司对于数据库的选择，在关系型范围内也一直使用它，本文尝试着总结一下在使用过程中积累的一些优化技巧。在我们使用的过程中，我们觉得可以从3个维度探讨一下MySQL可能存在的优化点。

数据结构设计时，数据类型的选择
代码开发过程中，索引的维护和SQL语句的编写
数据库维护过程中，参数的调整和及时的维护

选择正确的数据类型
=================================

说到Mysql的数据类型，我们先来整体看看，了解一下各种数据类型的特性，以及它们各自的使用场景。宏观来说，Mysql的数据类型分为几个大类，大类下面又各自分为各种小类，魔鬼就藏在这些小类中，就是小类之间各种细微的差别，能给出完全不同的性能状况。
Mysql的数据大类可以分为整形，浮点型，字符串，日期，集合，完全能满足我们存储数据的需要了，下面来逐一看看这些细节。

1.1 整形

Mysql中整形有这么几类:

tinyInt
1个字节，范围：（-128-127 | 0-255）
smallInt
2 字节，范围：(-32 768，32 767) (0，65 535)
mediumInt
3 字节，范围：(-8 388 608，8 388 607) (0，16 777 215)
int
4 字节，范围： (-2 147 483 648，2 147 483 647) (0，4 294 967 295)

bigInt
8 字节，范围：(-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615)

对于大部分人来说，设计数据结构的时候，如果选择整形最顺手的就是直接 int 了对不对，其实很大一部分场景是不需要这么浪费的，例如我们常用的字段有类型，状态等，这些字段往往只有几个值，是可遍历的，用tinyInt就可以满足需求，而带来的好处就是更省存储空间，更快的查询性能。

1.2 浮点型

FLOAT 4 字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值

DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值

DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值依赖于M和D的值小数值

对于浮点类型我总是存着一份戒备心理，因为浮点类型的计算往往存在精度丢失的情况，这个问题真的很烦，所以我一般不选择浮点类型作为数据结构类型，即使是有业务需求，我也要用Long类型来替代（通过单位换算）。如果实在要用浮点类型，也优先考虑float.

1.3 字符串

MySQL提供了8个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本块或二进制字符串数据。

CHAR 0-255字节定长字符串
VARCHAR 0-255字节变长字符串
TINYBLOB 0-255字节不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节短文本字符串
BLOB 0-65 535字节二进制形式的长文本数据
TEXT 0-65 535字节长文本数据
MEDIUMBLOB 0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节极大文本数据

这里面真正常用的就是 char 和 varchar了，它们的选择也是很有趣的，一般来说，大家会优先选择 varchar，因为它是可变长度嘛，存储的时候按实际长度存储，省空间。那难道char就没有用武之地了吗，答案当然是否。在一些特定场景下，char反而比varchar更适用。例如我们存储通过MD5加密后的密码，这个数据的长度都是一样的（33位），还有身份证号，手机号等信息，这些数据的共同特点就是长度固定，这个时候用char类型就很适用了。

1.4 日期类型

DATE 3个字节 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3个字节 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1个字节 1901/2155 YYYY 年份值
DATETIME 8个字节 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 8 个字节 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

日期类型的选择的话还是看具体的业务场景吧，我个人喜欢用TIMESTAMP，因为时间这个字段业务性很强，在统计报表里面对时间维度往往会分得很细，我觉得还是存储全面的信息比较靠谱一些。

1.5 集合类型

ENUM 类型
　　ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排拆的数据时容易让人理解，比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null值，除此之外的输入将会使 MySQL在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配，MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。
SET 类型
　　SET类型与ENUM类型相似但不相同。SET类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录，MySQL 将会保留合法的元素，除去非法的元素。

这种类型估计大家就用的更少了，我在公司碰到一个牛人用过ENUM，他给的理由和上文提到的tinyInt一致，ENUM类型也适用于状态，类型等可遍历的字段，查询性能会奇高，具体测试我没有做验证，但是这个信息是Mysql官方提供的，所以可以直接用。

让你的索引用起来，写出真正优雅的SQL
===================================
索引是查询分析器的指明灯，要想SQL高效，就避不开索引的命中。

2.1 最左前缀原则

在实际的开发过程中，索引往往需要作用在多列上（例如根据部门ID+状态来查询员工信息），所以实际情况往往是尽可能对表建立复合索引而非单列索引。因为复合索引的意义相当于符合+单列，例如索引（a,b,c）就相当建了3个索引：（a),(a,b),(a,b,c). 了解了这一特性，自然而然的就引出了索引的一个最重要的使用原则：最左前缀原则。只要保证where条件中的查询域是按照索引的最左列开始，就能命中该索引。反之，对于上面提到的索引(a,b,c), （b）, (b,c)是无法命中的。
对于最左前缀原则，order操作也同样适用，而且和where部分可以配合使用。例如:
select * from table where a = 1 order by b desc, c asc 是可以完全命中索引的。

2.2 知道哪些情况会无法命中索引

or，union 子句会导致全表扫描，索引失效
!=,<> 操作符会会导致全表扫描，索引失效
in, between...and也是应该尽量避免的，同样它们也会造成全表扫描。一般来说，in子句尽量用exists子句代替。
索引列中存在null值，也会导致无法命中索引，解决办法是加上 index_col is not null
在索引列上使用函数或者运算
索引列上存在隐式转换，比如你的SELECT * FROM T WHERE Y = 5 在Y上面有一个索引，但是Y列是VARCHAR的，那么Mysql会将上面的5进行一个隐式的转换，SELECT * FROM T WHERE TO_NUMBER(Y) = 5,这个时候也是有可能用不到索引的。

2.3 其它建议

表主键尽量采用业务相关的自增长字段，而非业务主键如手机号，身份证号等。
where子句要主要条件的顺序，尽量将有效过滤条件放在最前面。例如： where sex ='男' and deptId ='xxxx' 明显效率不如where deptId ='xxxx' and sex ='男'。
如果碰到非常多表的联合查询，要考虑是不是数据结构设计的问题了。这个时候应该采用中间临时表来实现需求，将需要查询的信息通过后台任务跑到中间表，前端的查询只需要查询临时表就可以了，这样效率会高很多。这种场景常见于订单详情，日志记录等。

Mysql参数优化
==================================

参数优化一般让DBA来干，开发人员基本上不会接触到。我也没有具体实践过，这里有一篇文章，我觉得值得收藏，看看吧。链接