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

举报
jackwangcumt 发表于 2021/07/24 21:27:18 2021/07/24
【摘要】 MySql数据库是最流行的关系型数据库管理系统之一,由于MySQL 具备体积小、速度快、使用成本低和开源等特点,因此,被非常多的国内外公司所选择。本文总结了16条数据库优化的最佳实践经验,可以对百万级记录进行性能提升。

     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.jpg

2.jpg

3.jpg
   一般来说,数值类型的应该根据范围选择最小的兼容类型,比如年龄字段一般范围为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%';

1.jpg

2.jpg

1.jpg

  select_type的值为SIMPLE,表示简单的select查询,不使用union或子查询。type的值为ALL,表示要对表进行表扫描。possible_keys 表示能使用哪个索引找到行记录,如果该列是NULL,则没有相关的索引key 表示MySQL决定使用的索引,如果没有选择索引,值是NULLkey_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

3.jpg

2.jpg

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

总结

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

    

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200