MySQL最佳实践从以下两方面讲MYSQL最佳实践:1.表结构最佳实践2.QUERY最佳实践表结构最佳实践:1.越小通常越好TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT/DECIMAL/DOUBLE在满足需求的前提下,尽量选择占用字节数小
Syn
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. 一??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表的数据。若有索引,则只需读一条对应记录即可。
根据上面执行逻辑,外加表大小和关联关系,你可以推导出用哪个更好,再测几次,看看执行计划啥的,大体就有定论了。