数据库优化

对于大部分项目而言,项目的瓶颈都是数据库。因此对数据库的优化,也是重中之重。
针对数据库优化,一般分为三类:

  • 硬件
  • 代码
  • 软件

硬件优化很简单粗暴,多核自然比单核快,两台要比一台好。这个取决于公司的实力,这里就不做讨论。
代码优化是个大命题,包含代码中缓存的使用、连接池等。这个话题另开一篇进行总结。
软件优化指的是针对数据库本身的优化,主要包括表设计(存储引擎、字段)、sql优化与索引、配置文件、系统架构等。

表设计优化:

1.存储引擎
强烈建议使用Innodb类型。一般情况下认为myisam要比Innodb快,在低版本的MySQL中确实如此,但是在高版本的MySQL中,Innodb读速度并不比myisam慢,并且支持事务、外键等高级应用。在MySQL8.0版本中,官方已经决定废弃myisam引擎了。

2.设计优化
三范式设计并不是需要完全遵守的。三范式出现的年代,存储空间是很贵的,业务量确很小。如今存储空间很便宜,业务量极大,完全遵守三范式的话,很可能会导致join多个表、甚至跨库join表,这都是不允许的。适当的冗余字段,减少join操作,用空间换性能。

3.字段优化

  • 选择字段类型的原则,是够用就好。
类型大小范围(有符号)范围(无符号)用途
TINYINT1字节-128~1270小整数值
SMALLINT2字节-32768~327670~65535大整数值
MEDIUMINT3字节-8388608~83886070~16777215大整数值
INT或INTEGER4字节-2147483648~21474836470~4294967295大整数值
BIGINT8字节-9223372036854775808
~
9223372036854775807
0
~
18446744073709551615
极大整数值
FLOAT4字节-3.402823466E+38
~
1.175494351E-38,
0,
1.175494351E-38
~
3.402823466E+38
0,1.175494351E-38
~
3.402823466E+38
单精度浮点数值
DOUBLE8字节-1.7976931348623157E+308
~
2.2250738585072014E-308,
0,
2.2250738585072014E-308
~
1.7976931348623157E+308
0,2.2250738585072014E-308
~
1.7976931348623157E+308
双精度浮点数值
DECIMALDECIMAL(M,D)
如果M>D为M+2
否则为D+2依赖于M和D的值
依赖于M和D的值小数值
  • int(11) vs int(21)
    其实两种写法标识的范围是一致的,区别是前导零。假如存了一个1,那么前一种写法前导零有10个,后一种有20个。
    mysql> create table t(c1 int(11) zerofill,c2 int(21) zerofill);//zerofill 是补全零的
    Query OK, 0 rows affected (0.02 sec) 

    mysql> insert into t values(1,1);
    Query OK, 1 rows affected (0.02 sec)

    mysql> select * from t;      
    +-------------+-----------------------+     
    | c1          | c2                    |    
    +-------------+-----------------------+     
    | 00000000001 | 000000000000000000001 |     
    +-------------+-----------------------+     
    1 row in set (0.00 sec)
  • 字符串类型
类型大小用途
CHAR0~255字符定长字符串
VARCHAR0~65532字节变长字符串
TINYBLOB0~255字符不超过255个字符的二进制字符串
TINYTEXT0~255字符短文本字符串
BLOB0~65535字符二进制形式的长文本数据
TEXT0~65535字符长文本数据
MEDIUMBLOB0~16777215字符二进制形式的中等长度文本数据
MEDIUMTEXT0~16777215字符中等长度文本数据
LOGNGBLOB0~4294967295字符二进制形式的极大文本数据
LONGTEXT0~4294967295字符极大文本数据

CHAR是定长的,也就是当你输入的字符小于你指定的数目时,CHAR(8),你输入的字符小于8时,它会再后面补空值。当你输入的字符大于指定的数时,它会截取超出的字符。最大长度是255字符。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节)。

CHAR(4)存储需求VARCHAR(4)存储需求
’ ‘4个字节1个字节
‘ab’‘ab ‘4个字节‘ab ‘3个字节
‘abcd’‘abcd’4个字节‘abcd’5个字节
‘abcdefgh’‘abcd’4个字节‘abcd’5个字节

请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值不保存,并且会出现错误。
从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别:

    mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
    Query OK, 0 rows affected (0.02 sec)

    mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
    Query OK, 1 row affected (0.00 sec)

    mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
    +----------------+----------------+
    | CONCAT(v, '+') | CONCAT(c, '+') |
    +----------------+----------------+
    | ab  +          | ab+            |
    +----------------+----------------+
    1 row in set (0.00 sec)

所有MySQL版本均如此,并且它不受SQL服务器模式的影响。
对于尾部填充字符被裁剪掉或比较时将它们忽视掉的情形,如果列的索引需要唯一的值,在列内插入一个只是填充字符数不同的值将会造成复制键值错误。

CHAR BYTE是CHAR BINARY的别名。这是为了保证兼容性。

ASCII属性为CHAR列分配latin1字符集。UNICODE属性分配ucs2字符集。

如果存储引擎用的是Innodb,那么强烈建议采用VARCHAR类型。因为VARCHAR更快更省空间。理论上CHAR比VARCHAR快的根本原因是站在CPU的角度来说的,但性能是综合各种因素后的最终结果,当Innodb buffer pool小于表大小时,”磁盘读写”成为了性能的关键因素,而VARCHAR更短,因此性能反而比CHAR高。并且Innodb采用页管理数据,数据移动是先在内存里完成,再写到磁盘,因此数据即使移动也很快。

  • date
    三个字节,如2015-05-01只能存储到天数。date精确到年月日

  • time
    三个字节,只能存小时分钟,time精确到小时分钟秒

  • datetime
    八字节,可以存储年月日时分秒

  • timestamp
    四字节,可以存储年月日时分秒

4.锁机制

  • 了解锁机制是为了防止事务出现死锁。这个我会单独开一篇博客进行总结。

5.查询sql优化

6.索引优化

未完继续

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值