MySQL优化那些事儿

作者：FuyunWang，原文地址：https://juejin.im/post/5a9b6342f265da23a2289281

MySQL的使用优化主要从优化库表结构、使用合适的索引、优化查询等方面考虑。

字符串类型

在MySQL中表示字符串的类型有多种,其中常见的有Char和VarChar,BLOB和TEXT以及可以实现某些优化的ENUM。我们要认识到不同的数据类型在存储和使用的区别,然后合理的使用就能实现优化。

关于MySQL中数据类型的介绍,可以参看我的另一篇博文: MySQL的常见操作

Char和VarChar

Char是定长类型,MySQL会根据定义的字符串的长度分配足够的空间,并且MySQL在存储CHAR值的时候会删除末尾所有的空格。对于经常变更的数据,一般采用CHAR来进行存储,因为CHAR类型在变化的时候不容易产生碎片。

VARCHAR是变长类型,它比CHAR更加节省空间,但是VARCHAR在数据变化的时候容易产生碎片,所以一般用于作为不经常变化的数据的数据类型。VARCHAR需要多使用一个或者两个额外字节来记录字符串的长度,如果列的最大长度小于等于255字节就用一个额外的字节来存储长度,否则使用两个字节。例如VARCHAR(10)的列需要11个字节的存储空间,VARCHAR(1000)的列需要1002个字节。

注意,在5.0或者更高的版本中,MySQL在存储或者检索VARCHAR数据类型时保留末尾空格,但是在4.1或者更老的版本中,MySQL在存储或者检索VARCHAR数据类型时和CHAR一样都是删除末尾的空格

BLOB和TEXT

BLOB即SMALLBLOB,TEXT即SMALLTEXT。BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符串的方式来存储。

使用ENUM来代替字符串类型

MySQL在存储枚举的时候非常的紧凑,会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数,并且在表的.frm文件中保存"数字-字符串"映射关系的"查找表"。

例如:create table enum_test(e enum('apple','banana','pear'));insert into enum_test(e) values('apple'),('banana'),('pear');然后我们所插入的数据在表中其实是存储为整数的。

数字类型
MySQL可以为整数类型指定宽度,如INT(1),INT(20),但是这对大多数应用是没有意义的。MySQL所指定的整数类型的宽度只是用于设置一些MySQL客户端用于显示字符的个数,对于存储和计算而言,INT(1)和INT(20)不会限制值的合法范围,这两种类型都是相同的。

日期和时间类型

DateTime和TimeStamp

DATETIME和TIMESTAMP是两种日期类型,两种类型在MySQL中存储数据的格式完全相同(都是yyyy-MM-dd HH:mm:ss),但是两者也有不同之处。

DATETIME能保存大范围的值,从1001年到9999年,精度为秒。MySQL采用8个字节来存储DATETIME数据类型所包含的值。默认情况下,MySQL以一种可排序的、无歧义的格式显示DATETIME的值。

TIMESTAMP保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX的时间戳相同。TIMESTAMP仅仅使用4个字节的存储空间,所以它能表示的时间范围也比DATETIME小,只能表示从1970年到2038年。TIMESTAMP也有DATETIME没有的特殊属性,默认情况下,如果插入时没有指定第一个TIMESTAMP列的值,MySQL则设置该列的值为当前时间。

索引优化是一个很大的方面,这里只是简单的介绍一些基本使用,过后会推出关于索引优化与设计的专题。

索引基础
在MySQL中,索引是在存储引擎层而不是服务器层实现的。Mysql中索引结构有:B-Tree索引、哈希索引、空间数据索引(R-Tree索引)、全文索引等索引结构,不同的存储引擎对于上述索引结构的实现不同,而且也不是所有的存储引擎都有这5种索引结构类型。

索引类型:

MySQL中的索引类型主要有5种:

1. 普通索引: 最基本的索引、没有任何限制。MyIASM中默认的BTREE类型的索引。如ALTER TABLE article ADD INDEX index_title_name ON title(100);、CREATE INDEX index_name ON table(column(100)),或者直接在创建表的时候定义索引index index_title_name(title(100))
2. 唯一索引: 索引列的值可以为空。与普通索引类似,不同之处在于索引列的值必须唯一。如ALTER TABLE article ADD UNIQU index_title_name ON title(100);、CREATE UNIQUE INDEX index_name ON table(column(100))或者直接在创建表的时候定义索引UNIQUE index_title_name(title(100))
3. 全文索引:主要用来查找文本中的关键字，而不是直接与索引中的值相比较。仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。如ALTER TABLE article ADD FULLTEXT index_content(content)、CREATE FULLTEXT INDEX index_content ON article(content)和FULLTEXT (content)。
4. 主键索引：它是一种特殊的唯一索引，不允许有空值。
5. 最左索引(组合索引): 组合索引可以更好的提高MySQL效率,最左索引遵循"最左索引"原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。
   索引方法:

可以使用B树索引的查询:

• 全值匹配的查询
• 匹配最左前缀的查询
• 匹配列前缀的查询
• 匹配范围值的查询
• 精确匹配左前列并且范围匹配另外一列
• 覆盖索引(只需要访问索引而无需查询数据行)

使用B树索引的限制

1. 在多列索引中,必须按照索引的最左列开始查找,否则索引无法使用
2. 在多列索引中,不能跳过索引中的列。比如一个三列组成的联合索引,不能只使用第一列和第三列进行查询而跳过第二列。
3. not in和<>操作无法使用索引
4. 如果查询中有某一个列的范围查询,则其右边所有的列都不能使用索引。

Hash索引的特点:

Hash索引是基于Hash表实现的,只有查询条件精确匹配Hash索引中的所有列时,才能使用Hash索引,Hash索引只适用于等值查询不适合模糊查询和范围查询。

对于Hash索引中的所有列,存储引擎都会为该列的每一行计算一个Hash码,Hash索引中存储的就是Hash码。

使用Hash索引的限制:

1. Hash索引必须进行二次的查找。
2. Hash索引无法进行排序。
3. Hash索引不支持部分索引查找也不支持范围查找。
4. Hash索引中Hash的计算可能存在Hash冲突。

B树索引与Hash索引在很多地方是不同的。B树索引除了能加快数据的查找速度之外还可以做到排序和分组,B树索引的叶子节点存储了索引关键字的值,可以直接通过索引查找关键字的信息从而避免了访问数据行。但是Hash索引的叶子节点中存储的是关键字信息的Hash码,我们需要将查询信息转化成Hash在表中找到对应的数据行才能查找到数据的信息。因此Hash索引不能作为覆盖索引来使用。

覆盖索引:

如果一个索引包含所有需要查询的字段的值(where语句的参数、order by的参数、group by的参数),那么我们通常称这个索引为覆盖索引。对于Memory存储引擎不能使用覆盖索引,查询过程中如果包含了太多的列(如select *)也不适合使用覆盖索引。 使用覆盖索引也有很多的优点。

• 优化缓存,减少磁盘I/O操作。
• 减少随机I/O,变随机I/O为顺序I/O。
• 可以避免对Innodb主键索引的二次查询。

InnoDB作为MySQL最为著名的存储引擎,这里要做特别的介绍。InnoDB中存储引擎支持B+树索引、全文索引和哈希索引。InnoDB存储引擎支持的哈希引擎是自适应的,InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引,不能人为干预是否在一张表中生成哈希索引。

传统意义上的索引就是指的B+树索引,这是目前关系型数据库系统中查找最为常用和有效的索引,其构造就是采用了二叉树的思想,根据键值对快速找到数据。通过B+树索引找到被查找数据行所在的页,然后数据库把页读入到内存,再在内存中进行查找,找到对应的数据。

InnoDB使用的是行锁,只有在修改行时,才会对行进行加锁。使用索引能够使得数据在查询过程中锁定更少的行,增加了数据处理的并发性,提高了数据库的性能。

1. 保证在MySQL中查找数据时,表中对应的列数独立的。独立的列在于索引列不能是表达式中的一部分,也不能是函数的参数。即不允许select id from article where id+1=5等情况的出现,否则索引将不能使用。
2. 索引很长的字符列,会让索引变得大且慢。这个时候就要采用前缀索引,就是选取列开始的部分字符作为索引,前缀索引的选择也要保证合理的索引选择性(越接近1越好)。
3. 如果不需要考虑排序和分组的需要,在联合索引中,应该将选择性最高的索引放到索引的最前列、将经常会被使用的列放到索引的最前列、宽度较小的列放到索引的最前列。
4. 使用pt-duplicate-key-checker h=127.0.0.1查找重复和冗余的索引,然后将重复冗余的索引删除。

MySQL连接状态

MySQL客户端和服务器之间的通信协议是"半双工"的,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器端发送数据,两个动作不能同时发生。对于每一个时刻,可以通过命令show full processlist来查看mysql当前连接的状态(Command列就代表当前的状态)。

MySQL的状态如下:

• Sleep: 线程正在等待客户端发送新的请求

• Query: 线程正在执行查询或者正在将结果发送给客户端。

• Locked: 在MySQL服务器层,该线程正在等待表锁.

• Sorting result: 线程正在对结果集进行排序。

Copying to tmp table [on disk]: 线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态要么是在做GROUP BY操作,要么是文件排序操作,或者是UNION操作。如果状态上有on disk的标记,那么表示MySQL正在将一个内存临时表放到磁盘上。

Analyzing and statistics: 线程正在收集存储引擎的统计信息,并生成查询的执行计划。

Sending data: 这表示线程或者在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。

MySQL连接过程

1. MySQL客户端发送一条查询给服务器
2. MySQL如果开启了查询缓存,那么MySQL服务器会优先检查查询缓存。检查的过程是通过一个对大小敏感的哈希查找实现的,如果缓存命中,那么在返回查询结果之前MySQL会检查一次用户权限,如果权限合适,那么直接返回缓存中的结果信息,查询完成,否则执行下一步。
3. 服务器进行SQL解析、预处理,然后再由优化器生成对应的执行计划。
4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
5. 将查询的结果返回给客户端。

在上面MySQL执行查询的过程中,服务器已经提供了一些SQL的优化措施,我们也需要正确理解并使用这些个优化措施

1. 使用explain+sql查询语句可以查看SQL查询的效率。
2. mysql使用基于成本的优化器。使用show status like 'last_query_cost'可以查询当前会话的last_query_cost值来得知mysql计算的当前查询的成本,mysql会进行评估并得到成本最小的执行计划。
3. MySQL自带一种"嵌套循环"能够对我们的大多数查询进行优化操作,调整关联表的关联顺讯以达到高效的查询。

优化数据访问

优化数据访问的关键在于:减少数据访问量,只检索必要访问的数据,保证向数据库发出的查询数据量只是实际需要的数据量。

为了实现数据访问量的优化,可以使用:

1. 在SQL的查询语句中,合理的使用limit控制行数。
2. 在多表关联的SQL查询中,只查询需要的表的列,尽量不要用"select *"
3. 借助第三方的缓存系统,将经常查询的数据缓存起来。
4. 如果查询是需要扫描大量的数据但只是返回少量的行,那么可以使用索引覆盖扫描,把需要数据的行放到索引中。

重构查询

1. 将大的查询分解成小的查询。特别是对于删除不需要的数据,一般来说就是分批删除少量的数据,这样可以大大减少数据库锁的持有时间。
2. 合理的分解关联查询。关联查询分解成单表查询可以减少锁的竞争;同时单表查询的结果在应用层做关联,可以实现数据库的拆分,做到高性能和可扩展。此外,通过将重复查询的数据做缓存可以提高效率。
3. MySQL的某些子查询效率很低(如使用in的子查询),我们应该使用explain语句测试当前查询的成本,然后决定是否应该使用内连接或者左(右)外连接改写mysql的in()子查询。但当我们需要返回一个表中的某些列时,多表关联查询我们可以使用exists关键字的子查询,这样效率也会更高。——在MySQL5.6版本以前需注意
4. 在使用union关键字进行sql查询时,如果有限制数据量和排序等操作,应在每一条sql语句中使用这些限制。
5. 使用主键自带的排序效果和limit关键字来代替max和min关键字实现最大和最小值。
6. MySQL在需要进行分页时,通过使用limit外加偏移量来实现,同时加上合适的order by子句,这样可以充分的利用具有索引的列。此外,在分页中,偏移量如果相差数据量过大,应该采用索引覆盖扫描。
7. 进行关联查询时,在on和using子句的列上添加索引,并且注意在关联顺序上,应该在第二章表中添加索引,提高效率。
8. 确保group by和order by子句的表达式上只涉及一个表中的列,只有这样才有可能使用索引优化这个过程。