MySQL 中常用 SQL 的优化

最新推荐文章于 2022-02-11 16:15:44 发布
最新推荐文章于 2022-02-11 16:15:44 发布
阅读量246 收藏
点赞数
分类专栏： 数据库 老傅浅结 MySQL 文章标签： mysql sql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ffj0721/article/details/87192293
版权

个人博客：DoubleFJ の Blog

前言

之前介绍了 MySQL 中怎么样通过索引来优化查询。日常开发中，除了使用查询外，我们还会使用一些其他的常用 SQL，比如 INSERT、GROUP BY 等。对于这些 SQL 语句，我们该怎么样进行优化呢？接下来将针对这些 SQL 语句介绍一些优化的方法。

大批量插入数据

当用 load 命令导入数据的时候，适当的设置可以提高导入的速度。

对于 MyISAM 存储引擎的表，可以通过以下方式快速的导入大量的数据。

ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;

DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

下面例子中，用 LOAD 语句导入数据耗时 115.12 秒：

mysql> load data infile '/home/mysql/film_test.txt' into table film_test2;
Query OK,529056 rows affected(1 min 55.12 sec)
Records: 529056  Deleted: 0  Skipped: 0  Warning: 0

而用 alter table tbl_name disable keys 方式总耗时 6.34+12.25 = 18.59 秒，提高了 6 倍多。

mysql> alter table film_test2 disable keys;
Query OK,0 rows affected(0.00 sec)

mysql> load data infile '/home/mysql/film_test.txt' into table film_test2;
Query OK,529056 rows affected(6.34 sec)
Records: 529056  Deleted: 0  Skipped: 0  Warnings: 0

mysql> alter table film_test2 enable keys;
Query OK,0 rows affected(12.25 sec)

上面是对 MyISAM 表进行数据导入时的优化措施，对于 InnoDB 类型的表，这种方式并不能提高导入数据的效率，可以有以下几种方式提高 InnoDB 表的导入效率。

（1）因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。

例如，下面文本 film_test3.txt 是按表 film_tst4 的主键存储的，那么导入的时候共耗时 27.92 秒。

mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(22.92 sec)
Records: 1587168  Deleted: 0  Skipped: 0  Warning: 0

而下面的 film_test4.txt 是没有任何顺序的文本，那么导入的时候共耗时 31.16 秒。

mysql> load data infile '/home/mysql/film_test4.txt' into table film_test4;
Query OK,1587168 rows affected(31.16 sec)
Records: 1587168  Deleted: 0  Skipped: 0  Warning: 0

从上面的例子可以看出当被导入的文件按表主键顺序存储的时候比不按主键顺序存储的时候快 1.12 倍。

（2）在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECK=1，恢复唯一性校验，可以提高导入的效率。

例如，当 UNIQUE_CHECKS=1 时：

mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(22.92 sec)
Records: 1587168  Deleted: 0  Skipped: 0  Warning: 0

当 SET UNIQUE_CHECKS=0 时：

mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(19.92 sec)
Records: 1587168  Deleted: 0  Skipped: 0  Warning: 0

可见比 UNIQUE_CHECKS=0 的时候比 SET UNIQUE_CHECKS=1 的时候要快一些。

（3）如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

例如，当 AUTOCOMMIT=1 时：

mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(22.92 sec)
Records: 1587168  Deleted: 0  Skipped: 0  Warning: 0

当 AUTOCOMMIT=0 时：

mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK,1587168 rows affected(20.87 sec)
Records: 1587168  Deleted: 0  Skipped: 0  Warning: 0

对比一下可以知道，当 AUTOCOMMIT=0 时比 AUTOCOMMIT=1 时导入数据要快一些。

优化 INSERT 语句

当进行数据 INSERT 的时候，可以考虑采用以下几种优化方式。

  • 如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语句快（在一些情况中几倍）。下面是一次插入多值的一个例子：
insert into test values(1,2),(1,3),(1,4)...
  • 如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
  • 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；
  • 如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用；
  • 当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

优化 GROUP BY 语句

默认情况下，MySQL 对所有 GROUP BY col1，col2…的字段进行排序。这与在查询中指定 ORDER BY col1，col2…类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序，如下面的例子：

mysql> explain select id, sum(moneys) from sales2 group by id\G;
    **************************** 1. row *************************************
                 id: 1
        select_type: SIMPLE
              table: sales2
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 1000
              Extra: Using temporary; Using filesort
    1 row in set(0.00 sec)

mysql> explain select id, sum(moneys) from sales2 group by id order by null\G;
    **************************** 1. row *************************************
                 id: 1
        select_type: SIMPLE
              table: sales2
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 1000
              Extra: Using temporary

从上面的例子可以看出第一个 SQL 语句需要进行“filesort”，而第二个 SQL 由于 ORDER BY NULL 不需要进行“filesort”，而 filesort 往往非常耗费时间。

优化 ORDER BY 语句

在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者都是降序。

例如，下列 SQL 可以使用索引：

SELECT * FROM t1 ORDER BY key_part1,key_part2,...;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是在以下几种情况下则不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;  --order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;  --用于查询行的关键字与 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2;  --对不同的关键字使用 ORDER BY

优化嵌套查询

MySQL 4.1 开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。

在下面的例子中，要从 sales2 表中找到那些在 company2 表中不存在的所有公司的信息：

mysql> explain select * from sales2 where company_id not in (select id from company2)\G;
    **************************** 1. row *************************************
                 id: 1
        select_type: PRIMARY
              table: sales2
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 1000
              Extra: Using where
    **************************** 2. row *************************************
                 id: 2
        select_type: DEPENDENT SUBQUERY
              table: company2
               type: index_subquery
      possible_keys: ind_company2_id
                key: ind_company2_id
            key_len: 5
                ref: func
               rows: 2
              Extra: Using index

   2 rows in set(0.00 sec)

如果使用连接（JOIN）来完成这个查询工作，速度将会快很多。尤其是当 company2 表中对 id 建有索引的话，性能将会更好，具体查询如下：

mysql> explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is null\G;
    **************************** 1. row *************************************
                 id: 1
        select_type: SIMPLE
              table: sales2
               type: ref
      possible_keys: ind_sales2_companyid_moneys
                key: ind_sales2_companyid_moneys
            key_len: 5
                ref: const
               rows: 1
              Extra: Using where
    **************************** 2. row *************************************
                 id: 1
        select_type: SIMPLE
              table: company2
               type: ref
      possible_keys: ind_company2_id
                key: ind_company2_id
            key_len: 5
                ref: sakila.sales2.company_id
               rows: 1
              Extra: 
   2 rows in set(0.00 sec)

从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。

连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

优化 OR 条件

对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

例如，首先使用 show index 命令查看表 sales2 的索引，可知它有 3 个索引，在 id、year 两个字段上分别有 1 个独立的索引，在 company_id 和 year 字段上有 1 个复合索引。

mysql> show index from sales2\G;
    **************************** 1. row *************************************
              Table: sales2
         Non_unique: 1
           Key_name: ind_sales2_id
       Seq_in_index: 1
        Column_name: id
          Collation: A
        Cardinality: 1000
           Sub_part: NULL
             Packed: NULL
               Null: YES
         Index_type: BTREE
            Comment:
    **************************** 2. row *************************************
              Table: sales2
         Non_unique: 1
           Key_name: ind_sales2_year
       Seq_in_index: 1
        Column_name: year
          Collation: A
        Cardinality: 250
           Sub_part: NULL
             Packed: NULL
               Null: YES
         Index_type: BTREE
            Comment:
    **************************** 3. row *************************************
              Table: sales2
         Non_unique: 1
           Key_name: ind_sales2_companyid_moneys
       Seq_in_index: 1
        Column_name: company_id
          Collation: A
        Cardinality: 1000
           Sub_part: NULL
             Packed: NULL
               Null: YES
         Index_type: BTREE
            Comment:
    **************************** 4. row *************************************
              Table: sales2
         Non_unique: 1
           Key_name: ind_sales2_companyid_moneys
       Seq_in_index: 2
        Column_name: year
          Collation: A
        Cardinality: 1000
           Sub_part: NULL
             Packed: NULL
               Null: YES
         Index_type: BTREE
            Comment:
    4 rows in set(0.00 sec)

然后在两个独立索引上面做 OR 操作，具体如下：

mysql> explain select * from sales2 where id = 2 or year = 1998\G;
    **************************** 1. row *************************************
                 id: 1
        select_type: SIMPLE
              table: sales2
               type: index_merge
      possible_keys: ind_sales2_id,ind_sales2_year
                key: ind_sales2_id,ind_sales2_year
            key_len: 5,2
                ref: NULL
               rows: 2
              Extra: Using union(ind_sales2_id,ind_sales2_year); Using where
    1 row in set(0.00 sec)

可以发现查询正确的用到了索引，并且从执行计划的描述中，发现 MySQL 在处理含有 OR 子句的查询时，实际是对 OR 的各个字段分别查询后的结果进行了 UNION。

但是当在建有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候，却不能用到索引，具体结果如下：

mysql> explain select * from sales2 where company_id = 3 or moneys = 100\G;
    **************************** 1. row *************************************
                 id: 1
        select_type: SIMPLE
              table: sales2
               type: ALL
      possible_keys: ind_sales2_companyid_moneys
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 1000
              Extra: Using where
    1 row in set(0.00 sec)

本文大多摘自《深入浅出MySQL》。

DoubleFJ
关注
专栏目录
MySql基础知识总结（SQL优化篇）
学Java，找哪吒
07-12 4万+
1、建表语句 + 联合索引 CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp .
mysql int支持小数吗_MYSQL支持的数据类型-数值类型
weixin_39945871的博客
01-19 3714
一、数值类型分类MYSQL支持所有标准SQL中的数值类型，其中包括严格数值类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISON),并在此基础上扩展增加了TINYINT、MEDIUMINT、BIGINT这三种整型，并增加了BIT类型，用来存放位数据其中整数类型包括：TINYINT、SMALLINT、MED...
MySQL decimal、float、double用法说明
COCO56的博客
08-12 2357
文章目录1. 按2. 三者精度区别3. 用法介绍4. DECIMAL的存储需求5. 实例 1. 按 本文主要介绍Decimal的相关知识，对于float和double，由于本身就比较常见，且容易理解，因此不做太多介绍，在本文中进行介绍时，主要是与Decimal进行对比，让读者在比较中更加容易理解Decimal。 2. 三者精度区别 类型名称 存储需求 有效位 float 4字节 6（本人实际测试数值） double 8字节 16（本人实际测试数值） decimail(M,D) 具体字
mysql数据类型double,double在数据库怎么定义 mysql中double类型在sql中用什么类型
weixin_33737110的博客
03-18 1万+
sql定义一个double变量怎么定义？局部变量 声明：DECLARE @local_variable data_type @local_variable 是变量的名称。变量名必须以 at 符 (@) 开头。data_type 是任何由系统提供的或用户定义的数据类型。变量不能是 text、ntext 或 image 数据类型。 示例： use master decla故事若不曲折，怎能教人成长，可...
mysql double 使用_mysql使用double的坑
weixin_33167456的博客
01-20 2490
碰到一个很奇葩的问题，mysql默认是四舍五入的，但是，使用double，有时候不四舍五入。犯罪现场还原表结构CREATE TABLE `test` (`salary_decimal` decimal(11,2) DEFAULT NULL,`salary_double` double(11,2) DEFAULT NULL) ENGINE=InnoDB ;表数据salary_decimal 和 sa...
Mysql sql语句 将字符串转为Double类型
weixin_43726822的博客
06-12 2万+
第一阶段，认识 sql里面的数字类型，float,decimal, double 三者的区别介绍 float:浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位） double:双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位） decimal:数字型，128bit，不存在精度损失，常用于银行帐目计算。（28个有效位） M...
mysql 调试和优化
maidou_2011的专栏
07-21 682
mysql 调试和优化  1.调试 mysql 命令方法: mysql>explain select * from t1 \G 或: mysql>desc select * from t1 \G 主要查看以下属性:  (1)type 是否用到索引  (2)key 索引名称  (3)rows 查询影响的行数(越少说明优化的越好)  2.用 mysql 命令获得一些服务优
浅谈MySQL中优化sql语句查询常用的30种方法
12-15
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 3.应尽量避免在 where ...
MySQL数据库优化SQL篇PPT课件.pptx
10-07
本PPT课件主要讲述了MySQL数据库优化的重要知识点，特别是SQL优化方面的内容。从执行计划、SELECT语句、IN和EXIST语句、LIMIT语句、RAND函数、Order by、Group by、Distinct和Count等方面对MySQL数据库优化进行了...
浅谈MySQL中优化sql语句查询常用的30种方法.doc
03-18
浅谈 MySQL 中优化 SQL 语句查询常用的 30 种方法 MySQL 是一种广泛使用的关系数据库管理系统，优化 SQL 语句查询是提高数据库性能的关键。下面是浅谈 MySQL 中优化 SQL 语句查询常用的 30 种方法： 一、避免全表...
mysql：列类型之float、double
火焰
02-11 1万+
环境： window10 vs2022 .net 6 mysql 8.0.25 DBeaver 参考： 《MSDN：浮点数值类型（C# 引用）》 《mysql：11.1.4 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE》 1. float和double类型 在mysql中，float和double都是浮点数类型： float 占4个字节，精度是6位； double 占8个字节，精度是16位； 它们的性质和c#中浮点数的性质是一样
mysql 尽量不要使用float double类型
weixin_34150830的博客
01-31 5218
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql使用double的坑
卖克的专栏
06-24 1万+
　　碰到一个很奇葩的问题，mysql默认是四舍五入的，但是，使用double，有时候不四舍五入。 犯罪现场还原 表结构 CREATE TABLE `test` ( `salary_decimal` decimal(11,2) DEFAULT NULL, `salary_double` double(11,2) DEFAULT NULL ) ENGINE=InnoDB ; 表数据 ...
MySQL CAST与CONVERT 函数的用法
weixin_30821731的博客
02-28 2508
MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。两者具体的语法如下： CAST(value as type); CONVERT(value, type); 就是CAST(xxx AS 类型), CONVERT(xxx,类型)。 可以转换的类型是有限制的。这个类型可以是以下值其中的一个： 二进制，同带binary前缀...
mysql 创建表字段Double类型长度
热门推荐
rongwenbin的专栏
07-23 8万+
mysql> create table MyClass(     > id int(4) not null primary key auto_increment,     > name char(20) not null,     > sex int(4) not null default '0',     > degree double(16,2)); double(16,2)
mysql使用double类型方法
有 prepare , No out
11-11 4691
1 mysql> create table tb_book(id int primary key auto_increment, l_name varchar(22 ), l_price double(22,4)) ; 2 mysql> create table tb_book(id int primary key auto_increment, l_name varchar
MySQL 中分布式事务的使用
DoubleFJ の Blog
02-12 5075
个人博客：DoubleFJ の Blog 前言 MySQL 从 5.0.3 开始支持分布式事务，当前分布式事务只支持 InnoDB 存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。 分布式事务的原理 在 MySQL 中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。 资源管理器（RM）用于提供通向事务资源的途经。...
MySQL数据库常用SQL语句25条学习汇总
资源摘要信息:"mysql常用 SQL语句汇总，共 25 条" 知识点一：SQL语言简介 SQL（Structured Query Language）是一种用于存取、管理和操作数据库系统的标准计算机语言。它能够执行各种操作，如更新、插入、删除、查询...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值