MySQL最佳实践_MySQL

bitsCN.com

MySQL最佳实践

 

从以下两方面讲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. 一