MySQL SQL优化快速定位案例与优化思维导图

AustinDatabases

发布于 2025-02-21 08:10:13

11600

代码可运行

文章被收录于专栏：AustinDatabasesAustinDatabases

运行总次数：0

代码可运行

MySQL的SQL优化相对于其他的数据库来说，应该是简单的主要的原因有以下

1 业务简单：使用MySQL的业务一般为互联网业务，且在拆库，分表的基础上语句必然变得简单，更多的复杂性是在程序和架构设计的层面。

2 单线程数据查询方式：单线程的数据查询方式更希望的是短小的SQL，这样避免复杂的SQL进行上下文切换中消耗更多的CPU，根据数据库的查询设计来说，MySQL还是希望短小精悍的SQL来进行业务的处理。

3 基于B+TREE的数据物理存储方式：如要发挥最大化的MySQL的查询性能，要利用MySQL查询中的物理属性来进行有的放矢的主键查询，对MYSQL更有利。

基于这些理念和MySQL的物理实现基础，大部分懂得道理的架构师和程序员，都不会给MySQL施加如ORACLE ,SQL SERVER，PostgreSQL类似的大型SQL语句。

但.....

很多MySQL的文盲，还在MySQL上施加了复杂的SQL语句，这就需要进行查询的优化和分析了。一般针对MySQL的分析我们怎么来做。（基于MySQL 版本差异大，MYSQL 5.6 5.7 8.0 9.0 ）,这里按照向下兼容的方式，可能有些方法在高版本的MySQL已经非必须,快速查找问题的方法

我们以下面的一个语句作为一个例子；


MySQL [cloud]> explain SELECT                                                                                                                     
    ->   COUNT(*)
    -> FROM
    ->   (
    ->     SELECT
    ->       eb.slyGroupCo AS groupCo,
    ->       e.code AS entCo,
    ->       e.name AS entNa,
    ->       e.show_meth AS showMeth,
    ->       e.type + '' AS tag,
    ->       min(eb.BeginDa) AS beginda,
    ->       max(eb.EndDa) AS endda,  ,
    ->       GROUP_CONCAT(p.`Name`) AS pkgs
    ->     FROM
    ->       shopbi eb,
    ->       shop dt,
    ->       prodg p,
    ->       ent e,
    ->       bdf bd
    ->     WHERE
    ->       e.id = eb.enterpriseid
    ->       AND eb.id = dt.ShopBillID
    ->       AND dt.ProductPKGID = p.ID
    ->       AND eb.shopState = 0
    ->       AND eb.slymdCode IS NOT NULL
    ->       AND bd.DESC_ = 'S024739'
    ->       AND e.ORGID = bd.ID_
    ->       AND e.code LIKE '%ENT8853%'
    ->     GROUP BY
    ->       e.id,
    ->       eb.id
    ->     ORDER BY
    ->       max(eb.createDate) DESC
    ->   ) a;
+----+-------------+------------+--------+-----------------------------------------------------+--------------------+---------+---------------------------+-------+---------------------------------+
| id | select_type | table      | type   | possible_keys                                       | key                | key_len | ref                       | rows  | Extra                           |
+----+-------------+------------+--------+-----------------------------------------------------+--------------------+---------+---------------------------+-------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                | NULL               | NULL    | NULL                      | 23072 | NULL                            |
|  2 | DERIVED     | p          | ALL    | PRIMARY                                             | NULL               | NULL    | NULL                      |   224 | Using temporary; Using filesort |
|  2 | DERIVED     | dt         | ref    | PRIMARY,FKD55C954DBC4687E4,FKD55C954DA4BA8D24       | FKD55C954DA4BA8D24 | 98      | cloudplat.p.ID            |   103 | Using index                     |
|  2 | DERIVED     | eb         | eq_ref | PRIMARY,FKEB7193FD322835EC,INDEX_SHOPBILL_SLYMDCODE | PRIMARY            | 98      | cloudplat.dt.ShopBillID   |     1 | Using where                     |
|  2 | DERIVED     | e          | eq_ref | PRIMARY,FKD027336111B1115F                          | PRIMARY            | 98      | cloudplat.eb.EnterpriseID |     1 | Using where                     |
|  2 | DERIVED     | bd         | eq_ref | PRIMARY                                             | PRIMARY            | 182     | cloudplat.e.ORGID         |     1 | Using where                     |
+----+-------------+------------+--------+-----------------------------------------------------+--------------------+---------+---------------------------+-------+---------------------------------+
6 rows inset (0.002 sec

1 看到一个MySQL的语句，首先我先看type，尤其复杂的SQL，先看type这里有没有 ALL, 在type中ALL的字段出现，说明在这部分存在全表扫描，那么首先我们就要先分析这块的部分是否有问题。在查看了表信息后，发现对应的索引和主键都是齐全的，关键为什么走了ALL，主要的原因是一个表中的行数非常的少，且与另一个表连接是主外键关系，所以必然对于小表走了全表扫描，所以排除了这部分索引有问题。


MySQL [cloud]> select count(*) from product;
+----------+
| count(*) |
+----------+
|      261 |
+----------+
1 row in set (0.002 sec)


MySQL [cloudplat]> select count(*) from shopbi;
+----------+
| count(*) |
+----------+
|   935312 |
+----------+
1 row in set (1.174 sec)

下面想快速的对SQL进行优化，则需要使用第二种方案，针对核心表进行分析，这里一眼可以分析出，这个SQL中的核心表是eb,因为eb参与了与多个表之间的关联且通过它来进行where条件的给出是最多的。

eb表的条件
e.id = eb.enterpriseid
eb.id = dt.ShopBillID
eb.id = dt.ShopBillID
eb.shopState = 0
eb.slymdCode IS NOT NULL
max(eb.createDate) DESC


CREATE TABLE `eb` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `shopCode` varchar(32) DEFAULT NULL COMMENT 
  `EnterID` varchar(32) DEFAULT NULL COMMENT 
  `createDate` datetime DEFAULT NULL COMMENT
  `modate` datetime DEFAULT NULL COMMENT 
  `shopId` varchar(32) DEFAULT NULL COMMENT 
  `shopState` int(1) DEFAULT '0' COMMENT
  PRIMARY KEY (`id`),
  KEY `FKEB7322835EC` (`EnterpriseID`),
  KEY `FKEB7193AAD` (`createUser`),
  KEY `INDEX_SHOPBILL_SLYMDCODE` (`slymdCode`) USING BTREE,
  KEY `INDEX_SHOPBILL_SHOPID` (`shopId`) USING BTREE,
  CONSTRAINT `FKEB719335EC` FOREIGN KEY (`EnteiseID`) REFERENCES `enterprise` (`ID`),
  CONSTRAINT `FKEB71D131AAD` FOREIGN KEY (`creaUser`) REFERENCES `bdf2` (`USERNAME_`)

eb表的条件
e.id = eb.enterseid	有索引
eb.id = dt.ShopBiID	主键
eb.shate = 0	无索引
eb.slymdCode IS NOT NULL	有索引
max(eb.creatte) DESC	无索引

同时经过验证，虽然shopState无索引，但这里 shopstate过滤的数据并不多，只占本身表的10%，而90%的数据都是需要的。所以这里并不能再这个表进行更多的优化。同时经过语句的分析，发现这里有一个表的条件可以过滤更多的数据。这个表就是bd, 经过再次分析bd表,其中这里有一个关键，可以大量减少e表的扫描行数，而e表的扫描行数减少，将大幅度的减少eb表的扫描行数，而关键在于bd表的DESC_ 没有索引，而导致全表扫描与其他表进行比对。所以随即对表 bd添加索引。


| bdf2_dept | CREATE TABLE `bdf2_dept` (
  `ID_` varchar(60) NOT NULL,
  `COMP_ID_` varchar(60) DEFAULT NULL,
  `CREA_DATE_` date DEFAULT NULL,
  `DESC_` varchar(120) DEFAULT NULL,
  `NA_` varchar(60) DEFAULT NULL,
  `PAT_ID_` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row inset (0.001 sec)

MySQL [cloudplat]> select count(*) from bdf2;
+----------+
| count(*) |
+----------+
|     1506 |
+----------+
1 row inset (0.002 sec)

ALTER TABLE `cloud`.`bdf2` ADD INDEX `idx_DESC` (`DESC`);


MySQL [cloudplat]> select count(*) from ense;
+----------+
| count(*) |
+----------+
|    30787 |
+----------+
1 row in set (0.026 sec)

在添加完索引后,整体SQL运行的效率提高了 440倍，从原来的4秒，到现在的0.009秒。


MySQL [cloud]> ALTER TABLE `cloud`.`bdf2` ADD INDEX `idx_DESC` (`DESC_`);
Query OK, 0 rows affected (0.009 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [cloudplat]> explain SELECT
    ->   COUNT(*)
    -> FROM
    ->   (
    ->     SELECT
    ->       eb.slyGroupCode AS groupCode,
    ->       e.code AS entCode,
    ->       e.name AS entName,
    ->       e.show_method AS showMethod,
    ->       e.type + '' AS tag,
    ->       min(eb.BeginDate) AS begindate,
    ->       max(eb.EndDate) AS enddate,
    ->       e.dbname AS dbname,
    ->       e.domainname AS domainname,
    ->       eb.shopCode AS shopCode,
    ->       eb.shopName AS shopName,
    ->       eb.linkman AS linkman,
    ->       eb.slymdCode AS slymdCode,
    ->       eb.phone AS phone,
    ->       eb.email AS email,
    ->       eb.remark AS remark,
    ->       GROUP_CONCAT(p.`Name`) AS pkgs
    ->     FROM
    ->       sholl eb,
    ->       sholldt dt,
    ->       propkg p,
    ->       entise e,
    ->       bdf2 bd
    ->     WHERE
    ->       e.id = eb.entiseid
    ->       AND eb.id = dt.ShllID
    ->       AND dt.ProdGID = p.ID
    ->       AND eb.shate = 0
    ->       AND eb.slymdCode IS NOT NULL
    ->       AND bd.DESC_ = 'S739'
    ->       AND e.ORGID = bd.ID_
    ->       AND e.code LIKE '%EN53%'
    ->     GROUP BY
    ->       e.id,
    ->       eb.id
    ->     ORDER BY
    ->       max(eb.createDate) DESC
    ->   ) a;
+----+-------------+------------+--------+--------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+-----------------------------------------------------------+
| id | select_type | table      | type   | possible_keys                                                                  | key                | key_len | ref                       | rows | Extra                                                     |
+----+-------------+------------+--------+--------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                           | NULL               | NULL    | NULL                      |   90 | NULL                                                      |
|  2 | DERIVED     | bd         | ref    | PRIMARY,idx_DESC                                                               | idx_DESC           | 363     | const                     |    1 | Using where; Using index; Using temporary; Using filesort |
|  2 | DERIVED     | e          | ref    | PRIMARY,FKD0211B1115F                                                     | FKD06111B1115F | 99      | cloud.bd.ID_          |   15 | Using index condition; Using where                        |
|  2 | DERIVED     | eb         | ref    | PRIMARY,FKE835EC,INDEX_SHOPBILL_SLYMDCODE,idx_EnterpriseID_shopState | FKEB7193FD322835EC | 99      | cloudplat.e.ID            |    2 | Using where                                               |
|  2 | DERIVED     | dt         | ref    | PRIMARY,FKDDBC4687E4,FKD55C9A8D24                                  | FKD55C4687E4 | 98      | cloudplat.eb.id           |    3 | Using index                                               |
|  2 | DERIVED     | p          | eq_ref | PRIMARY                                                                        | PRIMARY            | 98      | cloud.dt.ProdID |    1 | NULL                                                      |
+----+-------------+------------+--------+--------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+-----------------------------------------------------------+
6 rows inset (0.002 sec)

MySQL [cloudplat]> SELECT
    ->   COUNT(*)
    -> FROM
    ->   (
    ->     SELECT
    ->       eb.slyGroupCode AS groupCode,
    ->       e.code AS entCode,
    ->       e.name AS entName,
    ->       e.show_method AS showMethod,
    ->       e.type + '' AS tag,
    ->       min(eb.BeginDate) AS begindate,
    ->       max(eb.EndDate) AS enddate,
    ->       e.dbname AS dbname,
    ->       e.domainname AS domainname,
    ->       eb.shopCode AS shopCode,
    ->       eb.shopName AS shopName,
    ->       eb.linkman AS linkman,
    ->       eb.slymdCode AS slymdCode,
    ->       eb.phone AS phone,
    ->       eb.email AS email,
    ->       eb.remark AS remark,
    ->       GROUP_CONCAT(p.`Name`) AS pkgs
    ->     FROM
    ->       shopbill eb,
    ->       shopbilldt dt,
    ->       productpkg p,
    ->       enterprise e,
    ->       bdf2_dept bd
    ->     WHERE
    ->       e.id = eb.enteseid
    ->       AND eb.id = dt.ShllID
    ->       AND dt.ProdGID = p.ID
    ->       AND eb.shate = 0
    ->       AND eb.slymdCode IS NOT NULL
    ->       AND bd.DESC_ = 'S0739'
    ->       AND e.ORGID = bd.ID_
    ->       AND e.code LIKE '%E53%'
    ->     GROUP BY
    ->       e.id,
    ->       eb.id
    ->     ORDER BY
    ->       max(eb.crate) DESC
    ->   ) a;
+----------+
| COUNT(*) |
+----------+
|       14 |
+----------+
1 row inset (0.009 sec)