数据库优化

最新推荐文章于 2024-05-17 05:52:10 发布

从别后

最新推荐文章于 2024-05-17 05:52:10 发布

阅读量291

点赞数

分类专栏： Mysql 文章标签：数据库

本文链接：https://blog.csdn.net/cs958903980/article/details/52945577

版权

Mysql 专栏收录该内容

15 篇文章 2 订阅

订阅专栏

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

硬件
代码
软件

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

表设计优化：

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

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

3.字段优化

选择字段类型的原则，是够用就好。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1字节	-128~127	0	小整数值
SMALLINT	2字节	-32768~32767	0~65535	大整数值
MEDIUMINT	3字节	-8388608~8388607	0~16777215	大整数值
INT或INTEGER	4字节	-2147483648~2147483647	0~4294967295	大整数值
BIGINT	8字节	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615	极大整数值
FLOAT	4字节	-3.402823466E+38 ~ 1.175494351E-38, 0, 1.175494351E-38 ~ 3.402823466E+38	0,1.175494351E-38 ~ 3.402823466E+38	单精度浮点数值
DOUBLE	8字节	-1.7976931348623157E+308 ~ 2.2250738585072014E-308, 0, 2.2250738585072014E-308 ~ 1.7976931348623157E+308	0,2.2250738585072014E-308 ~ 1.7976931348623157E+308	双精度浮点数值
DECIMAL	DECIMAL(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)

字符串类型

类型	大小	用途
CHAR	0~255字符	定长字符串
VARCHAR	0~65532字节	变长字符串
TINYBLOB	0~255字符	不超过255个字符的二进制字符串
TINYTEXT	0~255字符	短文本字符串
BLOB	0~65535字符	二进制形式的长文本数据
TEXT	0~65535字符	长文本数据
MEDIUMBLOB	0~16777215字符	二进制形式的中等长度文本数据
MEDIUMTEXT	0~16777215字符	中等长度文本数据
LOGNGBLOB	0~4294967295字符	二进制形式的极大文本数据
LONGTEXT	0~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优化

优化更需要优化的Query；
定位优化对象的性能瓶颈；
明确的优化目标；
从Explain 入手；
多使用profile
永远用小结果集驱动大的结果集；
尽可能在索引中完成排序；
只取出自己需要的Columns；
仅仅使用最有效的过滤条件；
少使用Join 和子查询；
http://blog.csdn.net/cs958903980/article/details/78770688
http://blog.csdn.net/cs958903980/article/details/52181754

6.索引优化