当前位置:Gxlcms > mysql > MySQL最佳实践_MySQL

MySQL最佳实践_MySQL

时间:2021-07-01 10:21:17 帮助过:47人阅读

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. 一

人气教程排行