MySQL开发者不得不知的最佳实践经验丨【绽放吧！数据库】

MySql数据库是最流行的关系型数据库管理系统之一，在 Web 应用方面，MySQL是被广泛使用的一种数据库。它虽然被Oracle 收购，但是它采用了双授权政策，分为社区版和商业版，而社区版仍然可以免费使用。由于MySQL 具备体积小、速度快、使用成本低，开发人群基础多和开源等特点，因此，被非常多的国内外公司所选择。
数据库上手不难，但是如果要想精通则非常不容易，这里面涉及到大量的知识，比如如何选择数据库引擎、数据类型如何选择、索引如何创建、表结构如何设计、性能如何优化、如何实现高可用性以及安全性等。其中，业界对于MySQL也总结了相关最佳实践经验，下面逐条分享给大家，希望对各位开发者有所帮助。首先给出示例数据表的创建脚本：

CREATE TABLE `t_order` (
  `id_order` bigint NOT NULL,
  `order_time` datetime DEFAULT NULL,
  `user_id` char(32) NOT NULL,
  `amount` decimal(16,2) DEFAULT NULL,
  `nums` int DEFAULT NULL,
  `memo` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

生成模拟数据脚本如下：

delimiter $$ 
create procedure auto_gen_torder()
BEGIN
    declare i int default 1;
    while (i <= 9000000) do
        insert into auto_gen_tordert_order values(
        i,now(),
        concat('user_',i),
        FLOOR(1 + (RAND() * 1000)),
        FLOOR(1+ rand()*10),'备注');
        set i = i + 1;
    end while;
END$$
delimiter ;

1 选择合适的数据类型

MySQL数据库支持多种类型，一般来说，可分4类：数值类型、布尔类型、日期类型和字符串(字符)类型。我们知道，数据结构对于程序来说，是骨架，也是非常重要的地位，而数据库中的数据类型构成了表结构，就是业务对应的数据结构。且业务字段的数据类型的选择对于数据库性能来说，也是非常重要的。因此，我们需要根据业务的具体特点，分析每个业务字段的特点，选择合适的数据类型，其中必须考虑到合适且有一定的扩展性。
根据官网介绍，MySQL数据库常见的类型罗列如下：

一般来说，数值类型的应该根据范围选择最小的兼容类型，比如年龄字段一般范围为1到120，因此可以选择TINYINT而不是INT，这样可以节省存储空间，也能提高性能，特别的如果存储日期类型，那么则建议用日期类型，而不是字符串类型，这样虽然方便，但是不能检测数据是否正确，可能会写入错误的数据。从性能考虑，一般对于定长的字符类型值，优先CHAR类型，然后是VARCHAR类型。因此，对于单字符来说，CHAR (1) 优于 VARCHAR (1) 。

2 选择合适的日期格式

MySQL日期格式，如DATETIME或DATE，建议使用YYYY-MM-DD日期格式，而不是DD-MM-YYYY或MM-DD-YYYY 。这样看起来更加的直观，而且同一的日期数据格式也更加利于日期处理。另外，如果只需要存储日期，则应该选择DATE数据类型，它是4个字节，虽然MySQL支持的DATA类型的一些不严格的语法格式，但是，在实际应用中，最好还是选择标准形式。日期中使用“-”做分隔符，时间用“:”做分隔符，然后中间用空格隔开。比如建议给定的值为2016-03-17 16:27:55。虽然MySQL支持使用“@”等特殊字符做分隔符。DATETIME类型用8个字节来表示日期和时间，以YYYY-MM-DD HH:MM:SS的形式来显示DATETIME类型的值。

3 选择合适的列建立索引

对于数据量大的表，特别是查询时，合适的列索引可以大大提高检索的性能，这也是数据库性能优化的一种重要的方法。一般来说，对于一个查询语句，如果某些列出现在WHERE语句之后，或者作为多个表之间进行JOIN的列，那么则需要建立索引。

-- 4.672s
SELECT max(amount) from mydb.t_order limit 1;
-- 建立索引
ALTER TABLE `t_order` ADD INDEX idx_t_order_amount ( `amount` )
-- 0.016s
SELECT max(amount) from mydb.t_order limit 1;

上述示例中，这个t_order表中有模拟数据900万条。在未建立amount索引前，查询最大值耗时4.672s，当建立索引后（当有数据后，建立索引可能会非常耗时，模拟数据中耗时近30s），然后再执行查询，由于建立索引后，数据都是排序好的，因此查询非常快，耗时0.016s。
同样的，如下的查询也是非常快的（耗时0.016s）：

SELECT 
    *
FROM
    mydb.t_order
WHERE
    amount > 52
ORDER BY amount DESC
LIMIT 1;

但是有些情况下，不建索引也可以，比如性别字段可能就只有两个值（男、女），而建索引可能意义不大，且索引会影响到更新速度。另外，MySQL查询每次只能使用一个索引，比如：select * from t_order where amount>52 and nums>2 。我们在amount和nums上分别创建单个索引的话，虽然提高了很多效率，但是在amount和nums两列上创建复合索引的话，将带来更高的效率。
一般来说，创建(amount, nums, user_id)的复合索引，那么n内部相当于分别创建了(amount, nums, user_id)、(amount, nums)、(amount)三个索引，这被称为最佳左前缀特性。因此在创建复合索引时，应该将最常用作限制条件的列放在最左边，依次递减。

-- 单索引
alter table `t_order` add index ix_amount (`amount`) 
-- 复合索引 
alter table `t_order` add index ix_date_amount(`order_time`,`amount`)

4 避免在索引列上使用函数

上一条提到，当一个表中的数据量非常大时，有百万或者千万数据记录时，如果不建立索引，那么查询将非常的慢。对于建立查询的列，有时候我们利用函数来简化某种查询操作，但在索引列上调用函数，可能会让索引失效，而不能发挥索引的优势。比如部分匹配的查询，一般建议使用Like语句，而不是其他的函数。示例如下：

-- 索引可用，但like影响性能
SELECT * from t_order WHERE user_id like 'user_999%';
-- 函数不能发挥索引特征
SELECT * from t_order WHERE left(user_id,8)='user_999';

注意：一般来说，like “%user%” 不会使用索引而like “user%”可以使用索引。

5 慎用SELECT * 语句

我们在后台查询数据时，非常喜欢用SELECT * FROM TableName，这个*语句会返回全部列数据。这里虽然书写起来非常方便，而且维护字段的时候，这个都不用重写修改，但是现实的业务表中的字段数据量一般都比较大，少则十几个列，多则近百列。如果不分析业务需求，全部返回全部字段，则可能非常影响性能，特别是字段有TEXT类型或者Blob等大数据类型的，这对于大量数据传输到前端来说，也是不合适的，且有安全性风险，可能界面上只能看到6个字段，但是却返回了30个字段。

-- 不建议 
SELECT * from t_order WHERE amount>0 order by amount limit 100;
-- 建议
SELECT id_order,amount from t_order WHERE amount>0 order by amount limit 100;

6 需要时才用Order By

我们在后台查询数据时，如果数据量不大或者说不需要后端进行排序，那么从性能角度来说，建议不要在后端使用Order By，而是到前端进行排序。这样在多用户查询的场景下，响应速度更快。

7 根据业务场景选择数据库存储引擎

MySQL数据库存储引擎支持多种，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，其性能也是不同的。从读写上来说，数据库可以分为只读场景和事务场景。对于读非常多的场景或者只读场景，则采用MyISAM 存储引擎性能更高，但是其不支持事务；而对于写场景多或者需要事务支持的，则需要选择InnoDB存储引擎。

8 多表join字段要建立索引且字段类型一致

前面介绍到，为了提高性能，多表连接时，建议在join的字段上建立索引，此时还有一个就是字段的数据类型要一致，否则不能起到索引的效果。如果A表的Col1字段类型是BIGINT，而B表的需要join的字段类型是INT，即使都建立了字段索引，但是由于数据类型不同，索引的效果也会受到影响。

9 在需要时使用Exist

在有些场景下，我们需要判断是否在特定条件下有数据，此时建议使用Exist来进行判断。一般来说，它的性能会更好一点。下面给出一段示例：

--建议
If EXISTS(SELECT id_order from t_order WHERE amount > 99)
--不建议
If (SELECT count(id_order) from t_order WHERE amount > 99) > 0

10 建议查询都带WHERE

在绝大部分查询场景下，我们都需要返回在特定条件下有数据，而不是全部数据，因为全部数据可能会导致内存占用过高，查询速度大幅下降等情况。因此，在写查询语句时，需要带上WHERE条件，即使在特殊情况下，需要查询所有（数据量极少的场景），也建议使用WHERE 1 = 1 作为查询结尾。另外，当只需返回一条数据时，则建议加上limit 1，这样数据库当搜寻到匹配记录时，就会返回数据，而不需要继续检索。

11 避免将数据库暴露到公网

目前的数据安全是非常重要的，而数据库中的数据是需要进行安全管控的。如果将数据库暴露到公网上，那么将大大提高数据库被攻入的风险。通常做法是，将数据库服务器放在内网中，被防火墙保护，而且只能通过特定内部进行IP访问。只运行Web服务器对其数据库进行访问。

12 设置合适的block size参数

设置MyISAM 表中的block size这个参数到合适的值，是MySQL性能优化的一条重要实践。它可以在.MYI文件中进行设置，即myisam_block_size。Mysql中block size默认值为1k，最大16k，最小1K。通常来说应该与操作系统block相同或者倍数关系，才能取得较好性能。

13 用EXPLAIN来分析SELECT查询

使用EXPLAIN在查询SQL语句之前，可以分析SQL查询语句的性能瓶颈。在SELECT语句之前增加EXPLAIN关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行SQL。

explain SELECT * from t_order WHERE user_id like 'user_999%';
explain SELECT * from t_order WHERE left(user_id,8)='user_999';
explain SELECT * from t_order WHERE user_id like '%user_999%';

　　select_type的值为SIMPLE，表示简单的select查询，不使用union或子查询。type的值为ALL，表示要对表进行表扫描。possible_keys 表示能使用哪个索引找到行记录，如果该列是NULL，则没有相关的索引。key 表示MySQL决定使用的索引，如果没有选择索引，值是NULL。key_len 表示MySQL决定使用索引的长度。ref 表示使用哪个列和key一起从表中选择行。rows 表示MySQL认为它执行查询时必须检查的行数。extra 表示查询的详情信息，用到where，临时表和排序。
上述的查询SQL分析结果，从中可以看出在索引列上不使用函数时，则可能采用索引idx_t_order_user_id，这也佐证了第四条建议。但是实际上它并未使用索引，行数依然和left函数一样，但LIKE会更慢。当使用LIKE %xxx%时，可能采用的索引都没有，是NULL。

14 防止SQL注入

如果为了实现灵活的查询条件，不少应用采用动态拼接SQL的方式来进行数据操作，那么这个过程可能就会产生SQL注入的风险。我们应该永远不要信任用户的输入，对用户的输入进行校验，对特殊字符进行过滤和转换；其次，尽量不要使用动态拼装SQL，而采用参数化的SQL或者直接使用存储过程进行数据查询存取，这样SQL注入会自动抛出错误。

15 慎重Where IN

在实际项目中，有有时候我们需要判断某个字段的值在特定的范围中，或者一些枚举值中，那么此时首先想到的就是where in 语句，在字段少的情况下，这个用法到不明显，但是如果数据量大，则性能比较差，而且有的数据库的IN是有个数限制的，比如Oracle大概是1000个的限制。如果是连续的范围，则可以使用>= and <=来判断。NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

select * from a where id in(select id from btable)
select * from a where exists(select id from btable where id=a.id )

16 避免在列上进行运算

这一条和不要在索引列上调用函数类似，比如在WHERE语句中，尽量避免列的相关计算操作，这样会导致索引无效而进行全表扫描，而影响性能。比如下面的示例：

-- 不能使用索引
select * from t_order where floor(amount) > 70
-- 可以使用索引
select * from t_order where amount > 70

这里我们可以用explain进行验证一下：

explain select * from t_order where floor(amount) > 70
explain select * from t_order where amount > 70
explain select * from t_order force index(`idx_t_order_amount`) where amount > 70

第三条SQL语句，用了 FORCE INDEX进行强制启用索引，此时rows明显减少，且实际使用的Key也是有值的。

总结

对于MySQL数据库而言，性能优化的方式比较多，本文根据相关资料进行了总结，其中在合适的字段上建立索引，并且保证值为非NULL,即给定默认值，这样可以提高性能。另外，对于SQL语句的查询，可以用explain进行验证，并调整进行优化，避免自己写的SQL进行全表扫描，而是可以发挥索引等特征，提高查询效率。当前了，不同版本的MySQL的特征也是不同的，优化可能也存在差异，这里还是需要通过不断实践才能更好的理解。
MySQL性能优化，explain只是给出个大致的方向，例如 key_len值小的，rows小的按理说，时间应该最短，效率最高。但是，实践中时间最少却可能有点出入。【绽放吧！数据库】有奖征文火热进行中：https://bbs.huaweicloud.com/blogs/285617

（完）