可阅读《高性能MySQL》一书
优化点:
- 数据类型
- 索引
- SQL
- 服务器设置
- 操作系统和硬件
只介绍前三个
优化数据类型
MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择。
-
更小的通常更好
一般情况下,应尽量使用正确存储数据的最小数据类型,更小的数据类型通常更快,因为占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。
-
简单就好
简单数据类型的操作通常需要更少的CPU周期。
-
尽量避免NULL
如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引、索引统计和值比较都更复杂。
通常把可为NULL的列改为NOT NULL带来的性能提升比较小,所以(调优时)没有必要首先在现有 schema中查找并修改掉这种情况,除非确定这会导致问题。但是,如果计划在列上建索引,就应该尽量避免设计成可为NULL的列。
整数类型
如果存储整数类型,可以使用下面几种类型:
- tinyint 占1个字节
- smallint 占2个字节
- mediumint 占3个字节
- int 占4个字节
- bigint 占8个字节
为整数类型指点宽度是没有意义的,Mysql不会限制值得合法范围,所以int(1)和int(11)是相同的。
实数类型
实数类型是带有小数部分的数字。。。。也可使用Decimal存储比bigInt还大的整数
Mysql既支持精确类型,也支持不精确的类型。
- float和double类型支持使用标准的浮点运算进行近似计算。
- decimal类型用于存储精确的小数。
CPU不支持对decimal的直接计算,在Mysql5.0及以上的版本中,Mysql自身实现了decimal的高精度计算。
在Mysql5.0及以上的版本中,decimal类型允许最多65个数字。
浮点类型在存储同样范围的值时,通常比 DECIMAL使用更少的空间。 float使用4个字节存储,double使用8个字节存储,相比float有更高的精度和更大的范围。
因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL。例如存储财务数据。但在数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。
字符串类型
VARCHAR
存储可变长字符串。
需要使用1或者2个字节记录字符串的长度。
在Update时可能使行变得比原来更长,需要做额外的工作,不同的存储引擎处理的方式不一样,MYISAM会拆成不同的片段存储,InnoDB会分裂页来使行可以放进页内。
下面这些情况下使用VARCHAR是合适的:字符串列的最大长度比平均长度大很多;列的更新很少,所以碎片不是问题,使用了像UTF-8这样复杂的字符集,每个字符都使用不同的字节数进行存储。
在5.0或者更高版本,MySQL在存储和检索时会保留末尾空格。
CHAR
char类型是定长的,MySQL总是根据定义的字符串长度分配足够的空间。
**当存储CHAR值时,Mysql会删除所有末尾的空格。**不会删除开头的空格,与存储引擎无关,是在服务层进行的。
CHAR适合存储很短的字符串,或者所有值都接近同一个长度。
CHAR类型不容易产生碎片。
使用VARCHAR(5)和VARCHAR(200)存储’hello’的空间开销是一样的。
事实证明,使用更短的列有很大的优势,更长的列会消耗更多的内存,因为Mysql通常会分配固定大小的内存块来保存内部值。
所以最好的策略是只分配真正需要的空间。
BLOB和TEXT类型
为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。
与其他类型不同,MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当 BLOB和 TEXT值太大时,InnoDB 会使用专门的“外部”存储区域来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值。
BLOB和TEXT家族之间仅有的不同是BLOB类型存储的是二进制数据,没有排序规则或字符集,而TEXT类型有字符集和排序规则。
有时间可以使用枚举替代常用的字符串类型
日期和时间类型
Mysql最小时间粒度为秒。
Mysql提供两种时间类型:DateTime 和TimesTamp。
DATETIME
能保存大范围的值,从1001年到9999年,精确为秒。使用8个字节存储空间。
TIMESTAMP
只能表示1970年到2038年,只使用4个字节存储空间。存储的值与时区有关。
创建高性能索引
索引的优点
- 索引大大减少了服务器需要扫描的数据量。
- 索引可以帮助服务器避免排序和临时表。
- 索引可以将随机 I/O 变为顺序 I/O。
SQL优化
参考文章
- 在日常工作中怎么做MySQL优化的? - 掘金 (juejin.cn)