mysql hint优化的示例分析

共计 7926 个字符，预计需要花费 20 分钟才能阅读完成。

这篇文章将为大家详细讲解有关 mysql hint 优化的示例分析，文章内容质量较高，因此丸趣 TV 小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

SELECT t.oldcontractno, t.startdate, t.enddate, sum(confrec.confamt)

FROM (

        SELECT ca1.contractno, f1.startdate, f1.enddate, ca1.oldcontractno

        FROM contract c1, contractinapprove ca1, framepolicy f1

        WHERE c1.contractinapproveid = ca1.contractid

        AND ca1.contractid = f1.contractid

        AND c1.contracttype =4

)t, contract c2, confrec

WHERE

(

        (substring( c2.oldcontractno, 1, locate( – , c2.oldcontractno) -1 ) = t.oldcontractno)

        OR

        (c2.oldcontractno = t.oldcontractno)

)

AND confrec.contractid = c2.contractid

AND c2.customersitecode NOT IN (JP , BD)

AND confrec.confdate BETWEEN t.startdate AND t.enddate

GROUP BY t.oldcontractno

执行计划如下所示：

+—-+————-+————+——–+———————+———————+———+——————————+———+———————————+

| id | select_type | table       | type    | possible_keys        | key                  | key_len | ref                           | rows     | Extra                            |

+—-+————-+————+——–+———————+———————+———+——————————+———+———————————+

|  1 | PRIMARY      | derived2 | ALL     | NULL                 | NULL                 | NULL     | NULL                          |       57 | Using temporary; Using filesort |

|  1 | PRIMARY      | confrec     | ALL     | contractid           | NULL                 | NULL     | NULL                          | 1007935 | Using where                      |

|  1 | PRIMARY      | c2          | eq_ref | PRIMARY              | PRIMARY              | 4        | ap_db.confrec.contractid     |        1 | Using where                      |

|  2 | DERIVED      | f1          | ALL     | FK28E4C8DF253521AD  | NULL                 | NULL     | NULL                          |      262 |                                  |

|  2 | DERIVED      | c1          | ref     | contractinapproveid | contractinapproveid | 5        | ap_db.f1.contractid           |        1 | Using where                      |

|  2 | DERIVED      | ca1         | eq_ref | PRIMARY              | PRIMARY              | 4        | ap_db.c1.contractinapproveid |        1 | Using where                      |

+—-+————-+————+——–+———————+———————+———+——————————+———+———————————+

从执行计划分析，最大的问题是 confrec 的全表扫描，但是 confrec 和 contract 的连接使用了索引。看执行计划是对 confrec 进行了全表扫描，而 contract 使用了索引，现在修改一下，confrec（百万级别）用索引,contract（3w）用全表扫描。由于 contract 的数据量小很多，效率高了，从执行计划的预估行数可以看的很明显。

          使用了 sql hint 控制了表的连接顺序解决该问题。

SELECT t.oldcontractno, t.startdate, t.enddate, sum(confrec.confamt)

FROM (

        SELECT ca1.contractno, f1.startdate, f1.enddate, ca1.oldcontractno

        FROM contract c1, contractinapprove ca1, framepolicy f1

        WHERE c1.contractinapproveid = ca1.contractid

        AND ca1.contractid = f1.contractid

        AND c1.contracttype =4

)t, contract c2 straight_join confrec

WHERE

(

        (substring( c2.oldcontractno, 1, locate( – , c2.oldcontractno) -1 ) = t.oldcontractno)

        OR

        (c2.oldcontractno = t.oldcontractno)

)

AND confrec.contractid = c2.contractid

AND c2.customersitecode NOT IN (JP , BD)

AND confrec.confdate BETWEEN t.startdate AND t.enddate

GROUP BY t.oldcontractno

修改后的执行计划：

+—-+————-+————+——–+———————+———————+———+——————————+——-+———————————+

| id | select_type | table       | type    | possible_keys        | key                  | key_len | ref                           | rows  | Extra                            |

+—-+————-+————+——–+———————+———————+———+——————————+——-+———————————+

|  1 | PRIMARY      | derived2 | ALL     | NULL                 | NULL                 | NULL     | NULL                          |     57 | Using temporary; Using filesort |

|  1 | PRIMARY      | c2          | ALL     | PRIMARY              | NULL                 | NULL     | NULL                          | 13135 | Using where                      |

|  1 | PRIMARY      | confrec     | ref     | contractid           | contractid           | 4        | ap_db.c2.contractid           |     45 | Using where                      |

|  2 | DERIVED      | f1          | ALL     | FK28E4C8DF253521AD  | NULL                 | NULL     | NULL                          |    262 |                                  |

|  2 | DERIVED      | c1          | ref     | contractinapproveid | contractinapproveid | 5        | ap_db.f1.contractid           |      1 | Using where                      |

|  2 | DERIVED      | ca1         | eq_ref | PRIMARY              | PRIMARY              | 4        | ap_db.c1.contractinapproveid |      1 | Using where                      |

+—-+————-+————+——–+———————+———————+———+——————————+——-+———————————+

看明显看到了连接行数的减少。

ref: http://hi.baidu.com/veyroner/blog/item/c72827fd9403d3f7fd037f77.html

[@more@]

普通 MySQL 运行，数据量和访问量不大的话，是足够快的，但是当数据量和访问量剧增的时候，那么就会明显发现 MySQL 很慢，甚至 down 掉，那么就要考虑优化我们的 MySQL 了。

优化无非是从三个角度入手：
第一个是从硬件，增加硬件，增加服务器
第二个就是对我们的 MySQL 服务器进行优化，增加缓存大小，开多端口，读写分开
第三个就是我们的应用优化，建立索引，优化 SQL 查询语句，建立缓存等等

我就简单的说说 SQL 查询语句的优化。因为如果我们 Web 服务器比数据库服
务器多或者性能优良的话，我们完全可以把数据库的压力转嫁到 Web 服务器上，因为如果单台 MySQL，或者 Master/Slave
架构的数据库服务器都负担比较重，那么就可以考虑把 MySQL 的运算放到 Web 服务器上去进行。当然了，如果你 Web 服务器比数据库服务器差，那就把压力
放在数据库服务器上吧，呵呵。

如果是把 MySQL 服务器的压力放在 Web 服务器上，那么很多运算就需要我们的程序去执行，比如 Web 程序中全部交给 PHP 脚
本去处理数据。单台 MySQL 服务器，查询、更新、插入、删除都在一台服务器上的话，访问量一大，你会明显发现锁表现象，当对一个表进行更新删除操作的时
候，就会拒绝其他操作，这样就会导致锁表，解决这个问题最简单直接的办法就是拿两台 MySQL 服务器，一台负责查询（select）操作，另外一台负责更
改（update/delete/insert），然后进行同步，这样能够避免锁表，如果服务器更多，那么就更好处理了，可以采用分布式数据库架构和数据
的散列存储，下面我们会简单说一下。

一、SQL 的优化和注意事项

现在我们假设我们只有一台 MySQL 服务器，所有的 select/update/insert/delete 操作都是在这上面进行的，我们同时有三台 Web 服务器，通过 DNS 轮巡来访问，那么我们如何进行我们应用程序和 SQL 的优化。

1. Where 条件
在查询中，WHERE 条件也是一个比较重要的因素，尽量少并且是合理的 where 条件是很重要的，在写每一个 where 条件的时候都要仔细考虑，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，这样就会减少后一个 where 条件的查询时间。
有时候一些 where 条件会导致索引无效，当使用了 Mysql 函数的时候，索引将无效，比如：select * from tbl1 where
left(name, 4) = hylr，那么这时候索引无效，还有就是使用 LIKE 进行搜索匹配的时候，这样的语句索引是无效的：select
* from tbl1 where name like %xxx%，但是这样索引是有效的：select * from tbl1 where
name like xxx%，所以谨慎的写你的 SQL 是很重要的。

2. 关联查询和子查询
数据库一个很重要的特点是关联查询，LEFT JOIN 和全关联，特别是多个表进行关联，因为每个关联表查询的时候，进行扫描的时候都是一个笛卡尔乘积的数量级，扫描数量很大，如果确实是需要进行关联操作，请给 where 或者 on 的条件进行索引。
关联操作也是可能交给应用去操作的，看数据量的大小，如果数据量不是非常大，比如 10 万条以下，那么就可以交给程序去处理（totododo 提出笔误，特此修正），程序分别提取左右两个表的数据，然后进行循环的扫描处理，返回结果，这个过程同样非常耗费 Web 服务器的资源，那么就需要取决于你愿意把压力放在 Web 服务器上或者数据库服务器上了。
子查询是在 mysql5 中支持的功能，比如：select * from tbl1 where id in(select id from tbl1)，那样效率是非常非常低，要尽量避免使用子查询，要是我，绝对不用，呵呵。

3.   一些耗费时间和资源的操作
SQL 语句中一些浪费的操作，比如 DISTINCT、COUNT、GROUP
BY、各种 MySQL 函数。这些操作都是比较耗资源的，我想应用最多的是 count 字句吧，如果使用 count，尽量不要 count（*），最好
count 一个字段，比如 count（id），或者 count(1)，（据 totododo 测
试效率其实是一样的），同样能够起到统计的作用。如果不是十分必要，尽量不要使用 distinct 操作，就是提取唯一值，你完全可以把这个操作交给脚本程
序去执行提取唯一值，减少 MySQL 的负担。group by
操作也是，确实需要分组的话，请谨慎的操作，如果是小批量的数据，可以考虑交给脚本程序去做。
至于 MySQL 的函数，估计很多常用，比如有人喜欢把截取字符串也交给 MySQL 去操作，或者时间转换操作，使用比较多的函数像 SUBSTR(),
CONCAT(), DATE_FORMAT(), TO_DAYS(), MAX(), MIN(), MD5()
等等，这些操作完全可以交给脚本程序去做，减轻 MySQL 的负担。

4. 合理的建立索引
索引的提升速度的一个非常重要的手段，索引在对一些经常进行 select 操作，并且值比较唯一的字段是相当有效的，比如主键的 id 字段，唯一的名字 name 字段等等。
但是索引对于唯一值比较少的字段，比如性别 gender 字段，寥寥无几的类别字段等，意义不大，因为性别是 50% 的几率，索引几乎没有意义。对于 update/delete/insert 非常频繁的表，建立索引要慎重考虑，因为这些频繁的操作同样对于索引的维护工作量也是很大的，最后反而得不偿失，这个需要自己仔细考虑。索引同样不是越多越好，适当的索引会起到很关键的作用，不适当的索引，反而减低效率维护，增加维护索引的负担。

5. 监控 sql 执行效率
在 select 语句前面使用 EXPLAIN 字句能够查看当前这个 select 字句的执行情况，包括使用了什么操作、返回多少几率、对索引的使用情况如何等等，能够有效分析 SQL 语句的执行效率和合理程度。
另外使用 MySQL 中本身的慢查询日志：slow-log，同样能够记录查询中花费时间比较多的 SQL 语句，好对相应的语句进行优化和改写。
另外在 MySQL 终端下，使用 show processlist 命令能够有效的查看当前 MySQL 在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看 SQL 执行情况，同时对一些锁表操作进行优化。

二、数据库服务器的架构和分布想法

对于服务器的架构设计，这个其实是比较重要的，一个合理的设计，能够让应用更好的运行。当然，架构的设计，取决于你的应用和你硬件的实际情况。我就简单的说说几种不同的数据库架构设计方式，权当是一个个人的想法，希望能够有帮助。

1. 单台服务器开多进程和端口
单台 MySQL 服务器，如果使用长链接等等都无法解决负载太大，连接太多的问题，不凡考虑采用一台 MySQL 上使用多个端口开启多个 MySQL 守护进程的方法来缓解压力。当然，前提是你的应用必须支持多端口，并且你的 cpu 和内存足够运行多个守护进程。
优点 是能够很好的缓解暂时服务器的压力，把不同的操作放在不同的端口，或者把不同的项目模块放在不同的端口去操作，良好的分担单个守护进程的压力。
缺点 是数据可能会产生紊乱，同时可能会导致很多未知的莫名错误。呵呵

2. 使用 Master/Slave 的服务器结构
Mysql 本身具有同步功能，完全可以利用这个功能。构建 Master/Slave 的主从服务器结构，最少只需要两台 MySQL 服务器，我们可以把
Master 服务器用户更新操作，包括 update/delete/insert，把 Slave 服务器用于查询操作，包括 select
操作，然后两机进行同步。
优点 是合理的把更新和查询的压力分担，并且能够避免锁表的问题。
缺点 是更新部实时，如果网络繁忙，可能会存在延迟的问题，并且任何一台服务器 down 掉了都很麻烦。

3. 使用分布式的散列存储
这种结构适合大数据量，并且负载比较大，然后服务器比较充足的情况。分布式存储结构，简单的可以是多台服务器，每台服务器功能是类似的，但是存储的数据不
一样，比如做一个用户系统，那么把用户 ID 在 1 -10 万以内的存储在 A 服务器，用户 ID 在 10-20 万存储在 B 服务器，20-3- 万存储在 C 服务器，以此
类推。如果每个用户访问的服务器不足，可以构建组服务器，就是每组用户拥有多台服务器，比如可以在某用户组建立两台 MySQL 服务器，一台 Master，
一台 Slave，同样分离他们的更新和查询操作，或者可以设计成双向同步。同时，你的应用程序必须支持跨数据库和跨服务器的操作能力。
优点 是服务器的负载合理的被平摊，每台服务器都是负责一部分用户，如果一台服务器 down 掉了，不会影响其他用户 ID 的用户正常访问。同时添加节点比较容易，如果又增加了 10 万用户，那么又可以增加一个节点服务器，升级很方便。
缺点 是任何一台数据库服务器 down 掉或者数据丢失，那么这部分服务器的用户将很郁闷，数据都没了，当然，这个需要良好的备份机制。

关于 mysql hint 优化的示例分析就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。