PHP 加速 MySql 查询

Question

我正在使用 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);
    }

几乎立即加载。

为什么前 3 个比最后一个花费的时间长得多？我为我需要在我的表中搜索的所有变量添加了一个索引，但它仍然需要很长时间才能工作。我需要在 MySql 端做任何事情来让它重建索引吗？我已经尝试过优化表，我也多次重启 MySql 服务..

如果无法加快它们的速度，那么有没有一种简单的方法可以在用户加载页面时向其显示加载动画？

谢谢!

更新

这是快速查询的解释结果:

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');
    }

这又需要几秒钟才能加载..

解释查询:

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

Answer 1

查询 2 的问题(查询 1 看起来不错)。但是对于两个，虽然你有一个级别的索引，但是基数太高以至于 mysql 放弃了它。行数 170K，基数 159k

来自标题为 How MySQL Uses Indexes 的手册页:

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query. See Section 8.2.1.16, “How to Avoid Full Table Scans” for details.

综合指数

复合 ( Multi-column) 索引是 2 个或更多列的组合索引。可以是唯一的，也可以不是。

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

覆盖索引

覆盖索引是一种可以通过快速遍历索引 b 树来解决查询的索引，无需需要查找实际的表数据页面.这些是速度的终极必杀技。 Percona关于它的快速文章。

全文搜索(基于 OP 评论)

来自 Full-Text Search Functions 上的手册页, 这是摘录:

MySQL has support for full-text indexing and searching:

A full-text index in MySQL is an index of type FULLTEXT.

Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.

A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.

For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.