PHP加速MySql查询

Question

PHP加速MySql查询

5

我是使用Laravel PHP框架。假设我有以下查询：

public function order($orderby){
        \DB::connection()->disableQueryLog();

        if($orderby == "level"){
            $clan = Clans::orderBy('level', 'DESC')
            ->orderBy('exp', 'DESC')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);
        }elseif($orderby == "score"){
            $clan = Clans::orderBy('score', 'DESC')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);
        }elseif($orderby == "warwinpercent"){
            $clan = Clans::orderBy('warwinpercent', 'DESC')
            ->where('warswon', '>=', '100')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);
        }else
            $clan = Clans::paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);

        \DB::connection()->enableQueryLog();

        return view('clans.index')->with('clan', $clan);
    }

这些需要花费约10-15秒才能运行。

我有一个像这样的：

public function index(){

        $clan = Clans::orderBy('clanid', 'ASC')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);

        return view('clans.index')->with('clan', $clan);
    }

几乎瞬间加载。

为什么前三个查询比最后一个慢那么多？我为需要在表中搜索的所有变量添加了索引，但它仍然需要很长时间才能工作。我需要在 MySql 方面做些什么来重建索引吗？我已经尝试过 Optimize table，并且多次重新启动了 MySql 服务。

如果无法加速查询，那么有没有简单的方法可以在加载页面时向用户显示加载动画？

谢谢！

更新

这是快速查询的 Explain 结果：

explain extended select `id`, `clanid`, `name`, `level`, `exp`, `warwinpercent`, `warswon`, `playercount`, `score` from `clans` order by `clanid` asc limit 100 offset 0

这是针对 orderBy level 查询的解释结果。

explain extended select `id`, `clanid`, `name`, `level`, `exp`, `warwinpercent`, `warswon`, `playercount`, `score` from `clans` order by `level` desc, `exp` desc limit 100 offset 0

这里是SHOW INDEX FROM clans的结果。

更新2 我还有这段代码，用于在列name中搜索字符串。

public function clans(){
        if((isset($_GET['search'])) && $_GET['search'] != ""){

            $search = $_GET['search'];
            $result = Clans::where('name', 'LIKE', '%'.$search.'%')->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);
            $data = array(
                'result'  => $result,
                'search'   => $search
            );

            return view('search.clans')->with($data);

        }else
            return view('errors.1')->with('message', 'You entered an invalid search term');
    }

This again can take seconds to load.. (这句话可能需要几秒钟才能加载出来...)

解释查询:

explain extended select `id`, `clanid`, `name`, `level`, `exp`, `warwinpercent`, `warswon`, `playercount`, `score` from `clans` where `name` LIKE '%lol%' limit 100 offset 0

- user4942382

你能否编辑你的问题，附上原始查询和执行计划？ - Mihai

1

随意选择索引可能会进一步减慢速度。 - Drew

1

最终，你们 Laravel 的开发人员可以查看原始查询。因此，在其前面放置“explain”一词，在控制台上运行它，并发布这些结果。 - Drew

由于您没有WHERE条件，因此无法使用索引，导致您获取整个表。一种解决方案是使用某种缓存，您每隔一分钟运行一次查询，并从缓存中即时提供结果。Laravel肯定有类似的东西。 - Mihai

通常情况下，当您返回少量行时，索引非常有用。确保在WHERE子句中使用的列已经建立了索引。但是，如果您使用了WHERE子句，那么您的分页将会变得缓慢。 - Mihai

显示剩余10条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Drew · Answer 1

查询2存在问题（查询1看起来没问题）。但是对于两个查询，尽管您在level上有一个索引，但是基数太高，MySQL放弃了它。行数为170K，基数为159k。

从标题为MySQL如何使用索引的手册页面中：

对于小表或大表的报告查询处理大多数或所有行的情况，索引不那么重要。当查询需要访问大多数行时，顺序读取比通过索引工作更快。即使不需要查询所有行，顺序读取也可以最小化磁盘寻道。有关详细信息，请参见第8.2.1.16节“如何避免全表扫描”。

复合索引

复合（多列）索引是由2个或更多列组成的组合索引。可以是唯一的，也可以不是。

CREATE UNIQUE INDEX `thing_my_composite` ON thing (position,email,dob); -- forbid dupes hereafter

覆盖索引

覆盖索引是指查询可以通过快速浏览索引B树来解决，无需查找实际表数据页。这是速度的终极境界。Percona 快速介绍。

全文搜索（基于 OP 评论）

从全文搜索函数的手册页面中，这里是一个摘录：

MySQL支持全文索引和搜索：

MySQL中的全文索引是FULLTEXT类型的索引。

只能在MyISAM表中使用全文索引。（在MySQL 5.6及以上版本中，也可以在InnoDB表中使用。）只能为CHAR、VARCHAR或TEXT列创建全文索引。

在创建表时可以在CREATE TABLE语句中给出FULLTEXT索引定义，也可以使用ALTER TABLE或CREATE INDEX后添加。

对于大型数据集，将数据加载到没有FULLTEXT索引的表中，然后在此之后创建索引比将数据加载到具有现有FULLTEXT索引的表中要快得多。