针对大型 myisam 表的 MySQL 优化

Question

OS=centos 6.7  [Dedicated server]
memory=15G      
cpu=Intel(R) Xeon(R) CPU E5-2403
mysql= V 5.1.73    

这是一个 MyISAM 表，包含大约 500 万行数据。每 5-6 分钟插入大约 3000 个用户的数据(例如上传和下载速率、 session 状态等)。

表信息:描述“radacct”

我的.cnf

从mysql慢查询日志中，最耗时的查询之一如下

Query_time: 7.941773  Lock_time: 0.155912 Rows_sent: 1 Rows_examined: 5377
use freeradius;
SET timestamp=1461582118;
SELECT sum(acctinputoctets) as upload,
       sum(acctoutputoctets) as     download
    FROM radacct a 
    INNER JOIN (SELECT acctuniqueid, MIN( radacctid )     radacctid 
                   FROM radacct 
                   WHERE username='batman215'
                     and acctstarttime  between '2016-02-03 12:10:47'
                                            and '2016-04-25 16:46:01' 
                   GROUP BY   acctuniqueid) b 
        ON a.acctuniqueid = b.acctuniqueid 
       AND a.radacctid =    b.radacctid;

解释查询输出

当有许多用户试图查看他们消耗的带宽时，由于高负载和 IO，服务器无法满足请求。我可以做些什么来进一步优化数据库吗？

表“radacct”中的索引

不使用\G解释查询

谢谢

Answer 1

让我们从您的内部查询开始解决这个问题，即:

        SELECT acctuniqueid, 
               MIN( radacctid ) radacctid 
          FROM radacct 
         WHERE username='batman215'
           and acctstarttime between '2016-02-03 12:10:47'
                                 and '2016-04-25 16:46:01' 
      GROUP BY   acctuniqueid

您正在寻找 username 的相等匹配和 acctstarttime 的范围匹配。然后，您将使用 acctuniqueid 对 radacctid 进行分组并提取极值 (MIN())。

因此，要加速这个子查询，就需要下面的复合索引。

(username, acctstarttime, acctuniqueid, radacctid)

这是如何运作的？将索引(这些是 BTREE 索引)视为其中值的排序列表。

1. 查询引擎随机访问列表 - 快速，O(log(n)) - 找到匹配 username 的第一个条目和你的 BETWEEN 范围。
2. 然后按顺序扫描列表，一个条目一个条目，直到它到达BETWEEN 范围的高端。这称为索引范围扫描。
3. 扫描时，它会按顺序查找 acctuniqueid 的每个新值，然后取 radacctid 的最低值——按顺序第一个，然后向前跳到 accuniqueid 的下一个值。这称为松散索引扫描，而且它的成本低得惊人。

所以，添加那个复合索引。这可能会对您的查询性能产生重大影响。

您的外部查询如下所示。

SELECT sum(acctinputoctets) as upload,
       sum(acctoutputoctets) as     download
  FROM radacct a  
 INNER JOIN (  /*an aggregate 
                * yielding acctuniqueid and raddactid
                * naturally ordered on those two columns
                */
           ) b ON a.acctuniqueid = b.acctuniqueid
              AND a.radacctid =     b.radacctid

为此你需要复合覆盖索引

(acctuniqueid, radacctid, acctinputoctets, acctoutputoctets)

这部分查询也满足索引魔法。

1. 索引中的前两列允许根据内部查询的结果查找您需要的每一行。
2. 然后查询引擎可以扫描将其他两列的值相加的索引。

(这称为覆盖索引，因为它包含一些列，这些列的存在只是因为我们需要它们的值，而不是因为我们希望它们被索引。其他一些 DBMS 品牌和模型允许额外的列包含在索引中而不使它们可搜索。这有点便宜，尤其是在 INSERT 操作上。MySQL 不这样做。)

因此，您的第一个行动项目:添加这两个复合索引并重试您的查询。

从您的问题来看，您似乎在表中放置了很多单列索引，希望它们能加快处理速度。这是数据库设计中臭名昭著的反模式。尊重，你应该摆脱任何你不知道你需要的索引。它们对查询没有帮助，而且会减慢 INSERTS 的速度。这是您的第二个行动项目。

第三，去读这个http://use-the-index-luke.com/这非常有帮助。

专业提示:您看到我如何格式化您的查询了吗？当您必须理解一个查询的其他方面时，制定一个清晰显示表、列、ON 条件和查询的其他方面的个人格式约定非常重要。