MySQL的使用及优化

前言

最近听了公司里的同事做的技术分享，然后觉得对自己还是挺有帮助的。都是一些日常需要注意的地方，我们目前在开发过程中，其实用不到MySQL太深的内容的。只是能适用我们日常开发的知识就可以了。所以我将自己的理解和学习总结也写出来，供大家一起分享。

大体分四部分：

• 数据库优化概述
• 数据库表设计
• 索引原理及优化
• 可扩展性设计

数据库优化概述

优化金三角

做数据库优化一般是由以下几种方式：

成本和效果成反比。

服务器硬件

增强服务器的硬件方式不同的方式：例如增加磁盘配置（SSD，PCRE），增大内存，增加CPU配置等。增强服务器硬件在一定的阶段内确实可以达到不错的效果，但是并不是长久之计，如果不注重下面的三种策略，一味的增加硬件配置，会适得其反。

系统及数据库配置

随着系统硬件的不断更新迭代，数据库的配置也是不断变化的。例如以前的机械硬盘性能并不很好，所以数据库的配置并没有设置太高。当服务器普遍的都是SSD后数据库的系统配配置也是可以随之变化的。另外随着业务的变化以及数据量的增长，数据库的配置也是随着变化的。但是这部分的配置带来的效果并不太明显，和增加服务器硬件类似。

数据库表设计及规划

数据库的表在设计之初就应该考虑好了以后的规划。不然当发现数据库产生瓶颈了再去优化，成本会很高。所以也需要开发人员能通过对业务的深刻理解来对数据库做好长远的规划。

SQL及索引优化

对SQL语句以及索引的优化可以说是成本最低的了，效果也是非常显著的。

这四部分内容，总有人觉得SQL及索引优化是最重要的，但是本人觉得最重要的是数据库表设计以及规划，如果能根据业务将表设计好了，根本是不需要进行索引优化的。如果数据库没有规划好，再好的DBA给你做SQL优化，效果也是杯水车薪的。

MySQL逻辑架构

上面这幅图是MySQL的基本逻辑架构图，主要分为四层。

连接层

通过MySQL的连接地址去访问MySQL的数据库，以及对访问信息的校验。

服务层

对SQL语句的校验，以及对SQL的优化和优化策略选择，最后发送到执行器去执行SQL。还包括MySQL的查询缓存也在这一层。

引擎层

MySQL是插件式存储引擎，最终将数据存到硬盘时不同的引擎有不同的组织方式。上面列出了一些引擎，常见InnoDB，MyISAM等，只要符合MySQL的接口规范，MySQL是支持自定义的引擎。

存储系统层

这部分主要是数据存储，将数据存到磁盘，磁盘的IO读写等过程。

数据库表设计

引擎的选择

请使用InnoDB存储引擎，慎用MyISAM引擎。

上图是InnoDB引擎和MyISAM引擎的一些区别对比。

ACID事务支持：由于我们介绍这次介绍MySQL的时候是以OLTP（on-line transaction processing：联机事务处理）为主的，而非OLAP（On-Line Analytical Processing：联机分析处理），所以事务处理是很重要的，这也就是为什么强烈要求使用InnoDB引擎的一个原因。

锁粒度：MyISAM支持的锁粒度是表级锁，表级锁的意思是指当一张数据表被锁住后，其他的对这张表的操作（DML）都要等着前一个锁释放了才可以执行。所以当并发量高时用户体验是很不好的。而InnoDB引擎的行级锁，只是对表的一部分数据进行加锁，所以能很好的支持并发，降低了对同一张表的操作冲突。

外检约束：虽然InnoDB支持外键，MyISAM不支持外键。但是也不建议在日常的使用过程中用外键，因为每次操作外键时都要去检查一下外键关联的数据。

全文索引：InnoDB引擎不支持全文索引，但是MyISAM支持。但是在数据库中建立全文索引其实并不是什么好的策略，还是建议如果需要建立全文索引的时候考虑使用搜索引擎工具如：ElasticSearch，Solr等。

崩溃安全：InnoDB支持崩溃安全，MyISAM是不支持的崩溃安全的。

什么是崩溃安全呢？

举个例子🌰：当一台服务器的上的数据库突然挂了，或是服务器崩溃了，甚至是突然断电了。这个时候如果MySQL使用的是InnoDB引擎，那么在数据库恢复后或是重新通电后，会执行崩溃恢复，就是未执行完的事务会继续执行，该回滚的回滚，该执行完的执行完，能确保数据的一致性。但是如果MySQL是使用的MyISAM引擎，那么首先MyISAM不支持事务，所以会造成数据的不一致性，而且如果在对表进行操作时断电，导致没有正确的关闭表，还会导致存储文件的损坏，在恢复通电后对这张表的任何读写操作都不能执行了。而且就算手动恢复数据也是比较麻烦的。

表设计-规划

在设计表时要遵循几个基本原则：

1. 线上业务尽量避免使用外键、存储过程、分区表、触发器等。
2. 不在数据库中存储图片、文件等大数据。
3. 尽量避免使用TEXT\BLOB等类型的大字段。
4. 拆分大字段和访问频率低的字段，分离冷热数据。
5. 不同的业务使用不同的数据库，禁止混合使用。

第一条基本原则，是为了防止随着业务的发展以后如果数据量大到一定程度了需要分表时，拆分带有这些特性的表时成本是非常大的。

第二条、第三条 、第四条都是说大字段或大文件是不建议存储到MySQL当中的，因为对这些数据的操作MySQL是有特殊的存储方式的，性能很差。如果存储了这些数据后，再有一些排序或者是聚合操作的话会直接在磁盘中建立临时文件表，普通的字段类型例如varchar类型的，在有聚合操作时是会在内存中进行临时存储的。

第五条原则是要求对业务有长远的规划，不同的业务首先要分表，其次要分库。虽然MySQL的很强大，但是单节点的能力是有限的。所以企业级的数据库都是分布式的，要为以后业务的增长数据的访问量增长做好充分的规划。

字段类型选择

VARCHAR(N)：只分配真正需要的空间

例如：使用VARCHAR(5)和VARCHAR(200)存储'hello'的空间开销是一样的，使用更短的列有什么优势吗？

虽然存储开销是一样的，但是如果对这个字段进行聚合操作（order by、group by等），这个时候是需要先将临时数据存储到内存中的，但是申请内存空间时是按照字段的定义大小来申请的，也就是说VARCHAR(200)申请的内存空间是VARCHAR(5)的40倍。还有一种情况是，当一个表的数据量很大时，要做数据迁移或是大数据分析时，是需要抽取全表数据的，这个时候读全表数据是无法靠申请内存空间来实现的，MySQL是会在磁盘中建立临时文件表。并且是按照字段定义的大小来占用磁盘空间的，如果一个200G的硬盘，但是表中的数据是50G，在抽取全表数据时会有可能将磁盘占满的。

所以，更大的定义列会消耗更多的内存，在使用内存临时表进行排序或操作时会根据定义的长度进行内存分配。

数值类型vs字符串类型vs时间类型vs枚举类型

在给字段选择类型时，尽量遵循【小而简单】的原则，但是可以根据可以读性等因素适当调整。

例如：在存储时间字段时，有的人使用int类型（4个字节），有的人使用datetime(8个字节)，虽然说占用的空间小了，但是可读性也变差了。而且就即使是类型选择的稍微不太合理，这部分也是可以通过对SQL的优化等操作来减小影响的。

还有就是例如存储性别的时候，咱们使用tinyint，而不使用枚举类型，因为如果以后又多了一种类型（😏），这种操作是需要进行改表的，成本比用tinyint类型大很多。

表字段个数vs表记录行数vs表物理文件大小

单个表的字段数到了一定程度是建议拆表的，但是具体的峰值是根据实际的业务来看的，还有就是一个表的记录行数也是不建议很多，当到达一定量时再进行聚合操作是性能很差的。当表的数据量很大时增加字段也是需要消耗成本的，需要copy表中数据然后重新建表，这样才能保证线上的数据在加字段时是热处理。表物理文件的大小也是根据实际需求来考虑是否拆分的，如果表中只是追加操作，而且查询操作很不频繁，那么拆表就可以慢慢考虑。这部分内容不做过多的讨论。

索引工作原理及优化

InnoDB表索引结构

上面的图介绍的是InnoDB的索引结构，分为两部分聚簇索引和辅助索引。

聚簇索引也是主键索引，InnoDB表都是有主键的，就算是没有给表创建主键，MySQL也会默认的创建一个主键，聚簇索引每一个叶子节点代表的是主键的键值，最末端指向的是主键所在的那行数据记录。

辅助索引也是非聚簇索引，辅助索引就是日常表中除了主键以外的其他索引，每个叶子节点都代表的是索引的字段值，最末端指向的是索引值的主键。

在创建索引时需要注意，常用的有int类型，bigInt类型。首先这些类型是占用字节数少，并且是有序的。在建立辅助索引时能节省空间，因为每个辅助索引记录后面都带着一个主键索引，如果主键是uuid或是MD5值一类的，那么在建立辅助索引后会占用很大的磁盘空间，并且在按照主键去查询的时候主键值是要加载到内存中的，所以综合考虑还是int、bigInt更好一些。

例如下图的例子。

主键之外将name字段设置为索引。索引类型是varchar并且每个索引记录后面都跟着一个主键值，这个索引其实是很耗性能的。

MyISAM表索引结构

相对于InnoDB来说，MyISAM引擎的主键也是指向主键所在的记录的，但是辅助索引就不一样了，辅助索引最终也是指向数据记录的。MyISAM引擎的在数据存储的物理位置上有一个物理位置的编号。然后无论是主键还是辅助索引都是指向这个编号的。

如下图的例子所示：

索引优化

主键原则（InnoDB）

表必须有主键。

不使用更新频繁的列。

忌用字符串列做主键。

不使用UUID/MD5等生成的随机数做主键。

推荐用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键。

表必须有主键，即使没有主键InnoDB也会自动生成一个，如果使用频繁更新的列做主键，那主键的B+树不是一个稳定的结构，很耗磁盘开销，以及主键性能大大降低，上面已经说了字符串类型做主键会占用大量磁盘空间。不适用随机数做主键，是为了防止有磁盘空洞，产生不连续的空间。

最左前缀

目前的MySQL确实是有最左前缀的规则，即a_b_c索引，查询b和c时不走联合索引，但是随着MySQL的不断发展，现在又出现了一种叫做“索引下推”的概念，虽然不是代表着b和c使用时就能走索引了，但是看趋势可能以后会出现这种优化。最左前缀内容就不做过多的介绍了。

覆盖索引

首先介绍一下，回表的概念，InnoDB引擎的表是必须有主键的，但是当存在辅助索引时，辅助索引在索引记录中存储的是主键值。当通过二级索引去查询非辅助索引包含的字段时，是先根据辅助索引查询到相应的主键值，然后再根据主键值去查询到相应的记录。这个查询两次的过程就是回表。如果一个联合索引由a、b、c三个字段组成，那么“select b,c from test where a = 100”这个SQL就不需要产生回表的，因为只查询联合索引就能得到想要的结果了。

谨慎合理添加索引

改善查询效率

避免排序

数据率重

减慢插入和更新的效率。

索引添加的目的就是为了改善查询效率，添加索引时要避免出现using filesort，出现using filesort是指，当查询操作中包含order by，无法利用索引完成排序操作时，MySQL优化器不得不选择相应的排序算法来实现，数据较少时从内存排序，否则从磁盘排序。

举个例子：

还是以上面的tb_user_test表为例，"select b,c from tb_user_test where a=100 and b=200 order by c desc;"这个SQL语句在执行的时候如果tb_user_test没有idx_a_b_c这个联合索引那么执行计划是这样的

注意Extra列的值，Using filesort 出现了，这说明MySQL将数据重新排序了。

如果将字段a和b创建了联合索引后的执行计划是这样的

还是会有Using filesort。

将字段a和b还有c创建了联合索引后的执行计划是这样的

这次没有Using filesort了，创建索引时注意避免出现重排序问题。

数据虑重是指在使用distinct或者group by的时候也是可以使用索引进行优化查询的。distinct或group by的列创建索引能提高查询效率。

索引虽然能改善查询效率，但是代价是牺牲了插入和更新的效率。

索引数据控制

单张表索引数量建议不超过5个。

单个索引中的字段建议不超过5个。

字符串适度使用前缀索引。

索引不是越多越好，能不添加的索引尽量不要添加。

索引的控制只是一些建议，并不是强制要求。

索引禁忌

不在低区分度的列上建立索引，例如：“性别”。

尽量避免%前导查询，如like "%ab"。

尽量避免负向查询，如not in /like。

避免全表扫描以及频繁的回表操作

区分度低的列创建了索引后查询速度确实提升了，但是当数据量变大后会产生大量的随机IO和回表查询。like前缀是不走索引的，索引对负向查询的支持也不好。 

其他几点需要注意的是，索引的建立要优先保证高频查询需求的效率，低频需求尽可能使用到最左前缀索引。索引也要随着业务的演进更变化，不是建完索引就完事了。

高效SQL开发

SQL优化--设计基本原则

SQL尽可能简单，线上尽可能少使用大SQL，使用简单小SQL。

尽可能少使用存储过程/触发器/函数，减少MySQL端的数学运算和逻辑判断。（不易于扩展）

使用预编译语句，降低SQL注入概率。

尽量少用select * ，只取需要的数据列。（可降低磁盘I/O，有机会只走复合索引，缓存使用降低。）

SQL优化--隐式转换

基本原则：where条件比较，字段类型和传入值必须保证：数字对数字，字符对字符。

通过下面的例子就可以看出来。

字段：`remark` varchar(50) NOT NULL COMMENT '备注，默认为空',

MySQL>SELECT  id, gift_ code FROM gift Where deal_ id = 640 AND remark=115127; 1 row in set (0.14 sec)

MySQL>SELECT  id, gift_ code FROM pool gift Where deal_ id = 640 AND remark='115127' ;1 row in set (0.005 sec)

 

当remark传入int类型的值后，查询时间0.14秒，传入字符类型后只需要0.005秒。

SQL优化--函数计算

基本原则：不在索引列进行数学运算和函数运算。

索引字段进行数学运算时，不走索引。可以放到后面对值进行运算。

例如：

通过运行时间就可以看出效果。

索引字段慎用函数运算，MySQL的优化器对函数运算识别不出来时会直接走全表扫描。

例子如下：

SQL优化--分页

传统分页

select * from table limt 10000,10;

LIMIT原理

limit 10000,10; 偏移量越大则越慢。查询的时候要一步一步遍历到第10010条记录，然后取后10条记录，前面的全部抛弃掉。

推荐分页SQL

select * from table where id>=23424 limit 11;

#10+1(每页10条)

select * from table where id>23434 limit 11;

分页方式二

select * from table where id>=(select id from table limit 10000,1) limit 10;

分页方式三

select * from table where Inner join (select id from table limit 10000,10) using (id);

分页方式四

先取id：select id from table limit 10000,10;
select * from table where id in (123,456,...);

具体示例：

MySQL> select sql_no_ cache * from post limit 10,10;10 row in set (0.01 sec)

MySQL> select sql_ no_cache *  from post limit 2000,10;10 row in set (0.13 sec)

MySQL> select sql_no_cache  *  from post limit 80000,10;10 rows in set (0.58 sec)

MySQL> select sql_no_ cache id from post limit 8000,10;10 rows in set (0.02 sec)

MySQL> select sql_no_ cache * from post WHERE id> = 323423 limit 10;10 rows in set (0.01 sec)

MySQL> select * from post WHERE id >= ( select sql_ no_ cache id from post limit8000,1 ) limit 10;10 rows in set (0.02 sec)  

可扩展性设计

不到万不得已时就不要拆分

我们无论是做分表还是分库的时候首先都不是绝对要进行拆分的，主要还是需要看数据的增长速度的，因为一旦进行拆分后，处理业务的复杂度也提升了。所以在未来业务发展不明确，以及当前数据量增长速度不影响业务时，尽量先不要进行数据拆分，避免过度设计和过早优化。

还有当真的是业务上有影响的时候应该是先考虑，升级硬件、升级网络、读写分离、索引优化等等。

数据量过大影响正常业务

1. 数据量过大时，在读取大范围数据时，会造成频繁的磁盘I/O和网络I/O。例如：数据库缓存放不下过多的数据，这样在读取大量数据时是需要频繁访问磁盘的，这样就造成了大量的磁盘I/O（所以需要垂直分表）。或者是在备份数据以及业务上建立临时表等操作都会产生大量的网络I/O（所以需要分库来分担网络压力）。
2. 对一个大表进行DDL（改表操作）时，是会进行锁表的。并且由于数据量大是会造成更长的锁表时间的，这段时间业务不能访问此表，影响很大。
3. 对一个大表进行DML（数据操作）时，若是有使用数据锁的时候，无论是行锁还是表级锁，好的情况下锁一到多行（索引区间）的数据，坏的情况下（不走索引）是会锁全表的。这样的锁在大表上就会造成锁等待，后面的数据请求需要等锁释放后才能操作。将数据水平拆分后，空间换时间，降低了访问的压力。

冷热数据垂直拆分，减少数据体量

举例：我们目前有一个用户表，里面有名称、注册时间、最后一次登录时间、地址信息等内容。其中用户每次登录后都会更新登录时间，然后业务发展的很好，用户量目前已经发展到2亿了，并且每天的活跃用户也能到1千万。这样我们的用户表就会频繁的更新最后一次登录时间字段，数据量过大造成每次更新数据已经很慢了。但是名称、注册时间、地址信息等字段是很少更新的。

 

这个时候我们应该将用户表进行垂直拆分，将这种更新频率低的字段拆到一个单独的表里，这样用户表里的数据就都算热数据了，而且体量也变轻了。

数据量增长过快，提前做好水平拆分准备

由于某个热点新闻，用户注册量持续飙升，这个时候会很快就到单表瓶颈。此时就需要考虑提前对表进行水平拆分。

安全性以及提升可用性

不同的业务数据在系统初期为了快速的迭代，可能会设计的存储在一起，但是随着业务的发展每个业务的访问量、数据量都有所不同。可以利用水平拆分，将不同的数据放到多个地方来存储，这样既增加了，数据的安全性（不会因为一个业务把整个数据库搞垮），又提高了可用性（每个库或表只存储一部分数据，分担的请求的压力）。

 

业务隔离

不同的业务使用不同的数据库实例

垂直拆分

不同的业务表拆分到不同的数据库中，可以根据不同的模块，不同的功能将表拆分到不同个数据库中。逻辑比较清晰，但是也要考虑到具体的情况，如果有关联查询时，两个表放在里不同的库中，这样就拆分的不合理了，所以拆分的时候要对业务做深入的了解。

水平拆分

水平拆分，大概有两种方式：

1. 按照数值范围拆分。按照时间范围或者数据大小进行拆分，优点：扩容简单、直接增加容量即可；缺点：请求量不均匀有可能某个时间范围内的请求量占大部分（例如一般新注册用户活跃度会高些）。
2. 按照数值取模拆分。根据主键ID进行划分，优点：数据量和请求量分布均匀；缺点：扩容麻烦，需要进行rehash。迁移数据比较麻烦

 

一个表中的数据拆分到不同表中或不同的库中。但是拆分的时候要慎重的考虑好了，要以哪个键作为唯一标识进行拆分。一但确定下来最好不要随意更改。

终极--数据拆分

水平拆分+垂直拆分

（如果对分布式事务要求不太高的可以使用WTable,底层也是做了拆分。聚合操作也比较麻烦，要对每个库进行请求，然后再进行聚合操作。）

总结

这次的知识总结的比较粗糙，以后会对每一块做深入研究。