江苏龙网

  1. 主页 > 生活百科 > >

MySQL性能优化之骨灰级高阶神技

小知识

【51CTO.com原创稿件】在程序，语言，架构更新换代频繁的今天，MySQL 恐怕是大家使用最多的存储数据库了 。
 

MySQL性能优化之骨灰级高阶神技

文章插图
 
 
图片来自 Pexels
大量信息的存储和查询都会用到 MySQL，因此它的优化就对系统性能提升就尤为重要了 。
由于 MySQL 的优化范围较广，从软件到硬件，从配置到应用，无法一一道来 。
今天就从开发者的角度介绍一下 MySQL 应用优化 。包括数据类型，数据表查询/修改，索引和查询等几个方面 。
数据类型优化
字段是用来存放数据的单元，设计好字段是设计数据库的第一步，同样会影响到系统的性能 。
设计字段有一个基本的原则，保小不保大，也就是能够用字节少的字段就不用字节数大的字段，目的是为了节省空间，提高查询效率 。
更小的字段，占用更小的磁盘空间，内存空间，更小的 IO 消耗 。下面针对使用场景，说一些字段类型选取的经验，供大家参考 。
数值类型
手机号：通常我们在存储手机号的时候，喜欢用 Varchar 类型 。
如果是 11 位的手机号，假设我们用 utf8 的编码，每位字节就需要 3 个字节，那么就需要 11*33=33 个字节来存放;如果我们使用 bigint，只需要 8 个字节就可以存放 。
IP 地址：同上，IP 地址也可以通过 int(4 字节)在存放，可以通过 INET_ATON() 函数把 IP 地址转成数字 。这里需要注意溢出的问题，需要用无符号的 int 。
年龄，枚举类型：可以用 tinyint 来存放，它只占用 1 个字节，无符号的 tinyint 可以表示 0-255 的范围，基本够用了 。
字符类型
Char 和 Varchar 是我们常用的字符类型 。char(N) 用来记录固定长度的字符，如果长度不足 N 的，用空格补齐 。
varchar(N) 用来保存可变长度的字符，它会额外增加 1-2 字节来保存字符串的长度 。
Char 和 Varchar 占用的字节数，根据数据库的编码格式不同而不同 。Latin1 占用 1 个字节，gbk 占用 2 个字节，utf8 占用 3 个字节 。
用法方面，如果存储的内容是可变长度的，例如：家庭住址，用户描述就可以用 Varchar 。
如果内容是固定长度的，例如：UUID(36 位)，或者是 MD5 加密串(32 位)，就可以使用 Char 存放 。
时间类型
Datetime 和 Timestamp 都是可以精确到秒的时间类型，但是 Datetime 占用 8 个字节，而 Timestamp 占用 4 个字节 。
所以在日常建表的时候可以有限选择 Timestamp 。不过他们有下面几个小区别，需要注意的 。
区别一：存储数据方式不一样 。
Timestamp 是转化成 utc 时间进行存储，查询时，转化为客户端时间返回的 。
区别二：两者存储时间的范围不一样 。
Timestamp 为'1970-01-01 00:00:01.000000' 到'2038-01-19 03:14:07.999999' 。
Datetime为'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999' 。
数据表查询/修改优化
说了如何高效地选择存储数据的类型以后，再来看看如何高效地读取数据 。MySQL 作为关系型数据库，在处理复杂业务的时候多会选择表与表之间的关联 。
这会导致我们在查询数据的时候，会关联其他的表，特别是一些多维度数据查询的时候，这种关联就尤为突出 。
此时，为了提高查询的效率，我们会对某些字段做冗余处理，让这些字段同时存在于多张表中 。
但是，这又会带来其他的问题，例如：如果针对冗余字段进行修改的时候，就需要对多张表进行修改，并且需要让这个修改保持在一个事物中 。
如果处理不当，会导致数据的不一致性 。这里需要根据具体情况采取查询策略，例如：需要跨多张表查询公司销售额信息 。
由于，销售信息需要连接多张表，并且对销售量和金额做求和操作，直接查询显然是不妥当的 。
可以生成后台服务，定时从相关表中取出信息，计算出结果放入一张汇总表中 。
将汇总表中需要查询的条件字段加上索引信息，提高查询的效率 。这种做法，限于查询数据实时性不强的情况 。
在高速迭代开发过程中，业务变化快，数据库会根据业务的变化进行迭代 。所以，在开发新产品初期，表结构会面临频繁地修改 。
MySQL 的 ALTERTABLE 操作性能对大表来说是个问题 。MySQL 执行修改表结构操作的方法是，用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表 。
这一操作需要花费大量时间，如果内存不足而表数据很大，并且索引较多的情况，会造成长时间的锁表 。


推荐阅读


上一篇：寓道于茶 下关沱茶惹人醉

下一篇：10个最受欢迎的 JavaScript 框架，以及它们的主要特征和功能