MySQL最佳实践

MySQL是我们最常见且常用的关系型数据库之一，其相关的优化和最佳实践的tips，网上早已泛滥了，但其实用价值却无可厚非地有增无减。在此，我写泛滥的东西，确实有多此一举的嫌疑。实际，是为组内分享而整理了，所以也就顺手贴过来了。

讲MYSQL不得不介绍一个人：

Micheal Wedenius 

MySQL的创始人。这种祖宗级别的人物，还是要认识一下的。

现在这货已经是51岁高龄了，上个月还在ADC大会上在提倡开源精神了的。荷兰人，现在很胖了。(女人本性，忍不住要八卦一下体重) MySQL被金钱至上，利欲熏心的甲骨文收购后（他曾发动民众向相关委员会请愿，阻止这笔交易，结果没成功），于是转战MariaDB了。

他虽说已是个亿万富翁，但为人朴实，勤奋，是个非常不错的老头。还有，他英文还是有点儿烂的，口音很重。嘻嘻 

好，言归正转！

从以下两方面讲MYSQL最佳实践： 
1. 表结构最佳实践
2.QUERY最佳实践

表结构最佳实践： 

1.越小通常越好

TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT/DECIMAL/DOUBLE
在满足需求的前提下，尽量选择占用字节数小的数据类型。如上，能用TINYINT(1 byte)时，绝不用 SMALLINT(2byte)/MEDIUMINT(3byte).
不会有负数存储时，尽量用UNSIGNED的类型。
若可行，则尽量用INT替代FLOAT，DECIMAL等形式。如存储价格时，100*price 转成整型。
这么做的主要目的，是节省存储空间。数据所占空间越小，查询时需要走得路就越少，从而节省时间。 

2.简单就好

DATE/TIMESTAMP/DATETIME (3 byte/ 4byte/8byte)
道理同上。说明一点：
TIMESTAMP是从1970年到2038就没了的。
DATETIME则从1000到9999年为止的。所以，存储空间省还是不省？值不值得省，完全取决于业务需求了。

3.尽量避免NULL

额外BYTE消耗，增加查询复杂度 (vs.空)
只有需要区分空与NULL时，才可NULL，否则最好都有个默认的空值。
对数字型，默认值一般是0，或0.0之类的。这是有别于NULL的，好理解。
对于string型，空可表示为两种：'' 和NULL.两者是很有区别的。
假设你写col字段为空的查询：
1）无NULL的情况： 
select * from t where col＝''
2) 有NULL的情况
select * from t where col is NULL or col=''

这都算小case。当你OUTER JOIN时，你就会痛苦一点说：col为NULL是因为JOIN不上而NULL还是它没值而NULL？
当然，这都是大体的方针，guideline, 具体还得看应用场景。

4.IP用数字存储

15 bytes vs. 4bytes (INET_ATON,INET_NTOA)
省省省。MYSQL提供了ip转数字，和数字转IP的上述两个函数，所以，还是能省则省吧。

5.静态表会更快（固定长度的表）

无VARCHAR, TEXT, BLOB可变长度的string类型的字段，则称此表为静态表。否则，为动态表。
缺点：浪费点空间 （所谓静态，就是类型设多大，它就直接分配多大的地方给你，不管你用得着用不着。）
现在静态表应该很罕见吧。设计表时，在满足需求的情况下，若能静动分离，是最好的，若不能，也就算了。静态表的好处是，查询快，因为读时，读完这条记录，它知道跳多远，能读到下一条记录。动态表则不然，因为任何一条记录的长度是动态的。

6. 垂直分割

优点：降低表复杂度和字段数目,(如可分离静态和动态表)
缺点：过度分割导致多JOIN，性能更低
看业务，看数据量去平衡取舍。没有绝对的好坏，都得因地制宜。

7. 字符集选择

减少数量，而减少IO
我们大部分业务，直接GBK够了。UTF8完全是一种浪费。
你想啊，GBK任何一个字都只用2字节就够了。而UTF8因为支持了太多国家的语言，导致存储中文时，需要2－4byte. 所以除非可能国际化，不然还真没有必要。

8. 适度冗余 （空间换时间）

适度冗余最直接的目的，一般是为了减少JOIN。就是通过JOIN才能得到的另一张表内的字段，以冗余的形式，在当前表中再存储一遍。这样不需要JOIN了。当数据量大到一定程度时，这种做法是比较常见的。

9. 主键不要设得太大 (InnoDB)

这句话只适用于InnoDB. 这涉及到InnoDB数据存储形式。它是以B加树的形式存储数据文件的。即，INNODB把数据文件存成跟索引文件一样了。所以，每次你读记录的时候，都要通过主键去查询。这也是为啥InnoDB中建表时，必须要有个自增长主键的原因。

假设表T，字段(id, owner, title, c1,c2,c3,c4).假设你想在owner字段上建个索引，则对应的索引上会存储两个字段，一个是OWNER，另一个则是ID（主键），用于到数据文件中读取对应记录的。若你把ID设成BIGINT，那就意味着，你每建一个索引，对应地都要加上这个8byte长的字段，那你索引体积蹭地一下上去了。

10. 增长ID的重要性 (InnoDB)

InnoDB的数据文件本身就是索引文件，且是基于主键的索引文件。所以，这是为啥必须要有个主键的原因。你即使不设，它还是会默默地给你加个主键的。这是其一。
其二，为是得是增长呢？需要是增长的原因是，添加新的记录时，你只需要后面append就行了，若不是按顺序增长的，则插入新记录时，它首先要找到合适的位置，然后看有没有空间给它插，若没有，得让后面的往后挪，来给它腾位置。一两条就算了，若千万个都这么干时，你说，这插入得多慢？慢不说，还给你搞得遍地是碎片，多不爽。

11. CHAR vs. VARCHAR:

不讲编码，因为char还是varchar都会跟着编码走的。
其实想说的是，在大分部值的长度明确且 较短时(如存储md5值)，适合选择char(或更好的是binary in this case). 只有值的长度长短不一时，即较长的很长，较短的很短时，还是要选择用varchar的。

char是你定义几个char,它就直接分配你几个char. 
varchar(n) 的实际空间占用长度是，n char + 1-2 byte.讲到空间节省，这个确实会更节省一点。
若整个表定义中，只要有一个VARCHAR,那char与varchar基本区别不大了，除非说，你定义的是char(1).

QUERY最佳实践：

1. 不要用 “SELECT *”,否则，会读多，传输多，且增加可避免的表扫描

我就不废话了，基本人尽皆知的道规矩。

2. 不要 like ‘%item%’ but ‘item%’

前面有％，这索引就没办法利用了。所以，若想用索引加快查询速度，那前面别加%.

3. Cardinaltiy (基数) & Selectivity (选择比)

Cardinality: 不同值的个数。如表t中其有100条记录， 字段owner也有100条值，但其中10个不相同的值。这10就是这字段的Cardinality.
Selectivity: 10/100 = 10%就是这字段的selectivity.
这概念主要用来判断此字段是否适合建索引。Cardinality越大，Selectivity越高的字段，越是理想的建索引的对象。有时数据库会根据这个值来决定，是利用索引还是扫表。所以说，不是你建了索引，人家就会用的。而且，索引不可太多，多了反而会拖慢更新速度。

4. ORDER BY created DESC的优化

时间排序是应用中比较常见的需求。细想，这时间不是自增长的嘛？那跟ID自增长不是一回事儿嘛？ 所以说，在ORDER BY 时，用自增长的主键ID，会比用created，省一个FILE SORT操作。快很多的。

5. Count(1), count(*), count(owner)的区别
count(1)等同于count(*)，等同于count(任何一个NOT NULL的字段)
count(owner)：若owner是可NULL的，则数出来的数跟上面的三种情况会少的。少的正好是那些owner is null的个数。

6. Don`t JOIN ON 不同数据类型
A表user_id作为B表的外键，这种很常见。此时，需注意user_id字段的类型，在两张表里都要保持一致。这样节省不必要的开支，比如，数据库替你做类型转换等。

7. 不要用全文索引(full-text index)

当前只有MyISAM才支持全文索引。而且，不太好用，可自定义性比较差，所以完全无视它即可。若真需要做全文索引，还是考虑用Lucene, Solr, ElasticSearch, Sphinx, Groonga, Xapian等吧。个个都是行家里手，功能齐全，可定义性强，随你搞。

8. Limit n,m 慢，慎用

大部分人翻页，可能都是靠这个的。数据量大时，这显然会很慢。网上有人推荐说，第一次查出来后，记住当前页的最后一个ID，然后，在查询下一页时，把这个ID做为限制条件加进去，然后取limit pagesize。
诸如此类，若细想，应该是能想出点儿可行之策的我觉的。其实，当数据量很大时，你可以换个角度想，如继续在limit n,m上做文章能还是直接换个查询方式，如用搜索引擎等。

9. 多字段索引

这个无需多说吧，道理应该是司空见惯了。
CREATE INDEX idx_col123 ON t (col1,col2,col3);
用法则：
where col1='' and col2='' and col3=''
where col1='' and col2=''
where col1=''
where col1='' and col3='' (col1时用索引，col3时一行行验证过滤的)
你想想B Tree啥样就知道了。（mysql里应该是B+Tree, 查询时，逻辑相仿，区别不大）

10. 一個SELECT能否用多個索引？

可以。Mysql高一点的版本推出了merge optimization，支持的就是这功能。

11. JOIN vs. EXISTS 哪个更快？

1）没有定论，主要看JOIN的表大小，和one/many – to – one/many关联关系.
2) 需要明确的是：EXIST相比JOIN的优势在于 first match就返回，JOIN是能match的全部match.
3) JOIN相对于EXIST的优势在于可以根据实际情况选择执行的顺序(join order)，MySQL5.6之前，如果where中有EXISTS 执行顺序总是从外道内,现在好像变得更智能了。
4）小表JOIN大表时，用EXISTS可能更快。

A a JOIN B b ON （a.id=b.aid) WHERE a.owner='aaa' and b.cat='bbb';
执行顺序如下：
（取出A表内所有满足条件owner='aaa' 的记录）
JOIN ＝ 两个for内一一匹配
（取出B表内所有满足条件cat='bbb'的记录）

A a WHERE owner='aaa' and EXISTS
(SELECT 1 from B where cat='bbb' and a.id=aid)
执行顺序如下：
for (取出A表内所有满足条件owner='aaa' 的记录)
for (取出B表内所有满足条件cat='bbb'的记录)
check if a.id=b.aid

说明一点：里面的for，若无索引，得一条条全读一遍B表的数据。若有索引，则只需读一条对应记录即可。
根据上面执行逻辑，外加表大小和关联关系，你可以推导出用哪个更好，再测几次，看看执行计划啥的，大体就有定论了。

最后想附上MYSQL框架图，作为行军路线图，结果想想算了，这文章已经这么长了，再贴个大图，多让人头大？！哈哈！

have fun!