README

一个用于 mysql 索引提示和优化的简单库（USE INDEX, FORCE INDEX, IGNORE INDEX）

要求

• php: ^7.4|^8.0|^8.1|^8.2|^8.3
• doctrine/dbal: ^3.0
• illuminate/database: ^8.0|^9.0|^10.0|^v11.0
• illuminate/support: ^8.0|^9.0|^10.0|^v11.0

安装

composer require shaburov/laravel-mysql-index-hints-scope

如何使用

扩展类 Blueprint

已向 Blueprint 类添加以下方法：dropIndexIfExists, hasIndex

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

Schema::table('test_table', function (Blueprint $table) {
    $table->dropIndexIfExists('test_index'); // index will be delete when index exists
    $table->hasIndex('test_index'); // index existence check  
});

使用 trait

use IndexHints\Hintable;

class ExampleModel extends Model
{
    use Hintable;
}

函数

如果没有索引，则不会发生错误。

useIndex(INDEX_NAME, (JOIN|GROUP_BY|ORDER_BY), TABLE_ALIAS);
forceIndex(INDEX_NAME, (JOIN|GROUP_BY|ORDER_BY), TABLE_ALIAS);
ignoreIndex((INDEX_NAME | [INDEX_NAME, INDEX_NAME]), (JOIN|GROUP_BY|ORDER_BY), TABLE_ALIAS);

consts: 
IndexHintsConstants:JOIN;
IndexHintsConstants:GROUP_BY;
IndexHintsConstants:ORDER_BY;

示例

/**
* select * from example_models 
* FORCE INDEX (test_index)
*/
ExampleModel::forceIndex('test_index');

/**
* select * from example_models 
* IGNORE INDEX (test_index)
*/

ExampleModel::ignoreIndex('test_index');

/**
 * select * from example_models 
 * USE INDEX (test_index) 
 * IGNORE INDEX (test_index) 
 * USE INDEX (test_index,example_index)
 */
ExampleModel::select('*')
            ->useIndex('test_index')
            ->ignoreIndex('test_index')
            ->useIndex(['test_index', 'example_index']); 

/**
* select * from example_models 
* USE INDEX (example_index)
* IGNORE INDEX FOR ORDER BY (test_index) 
* IGNORE INDEX FOR GROUP BY (test_index)
*/
ExampleModel::select('*')
            ->useIndex(['example_index'])
            ->ignoreIndex('test_index', 'ORDER_BY')
            ->ignoreIndex('test_index', 'GROUP_BY');

/**
*select * from example_models 
*IGNORE INDEX FOR JOIN (example_index)
*IGNORE INDEX FOR ORDER BY (example_index) 
*IGNORE INDEX FOR GROUP BY (example_index)
*/
ExampleModel::select('*')
            ->ignoreIndex('example_index', IndexHintsConstants::JOIN)
            ->ignoreIndex('example_index', IndexHintsConstants::ORDER_BY)
            ->ignoreIndex('example_index', IndexHintsConstants::GROUP_BY);


/**
* Will be exception (However, it is an error to mix USE INDEX and FORCE INDEX for the same table) 
*/
 ExampleModel::select('*')
            ->useIndex('example_index', IndexHintsConstants::JOIN)
            ->forceIndex('example_index', IndexHintsConstants::ORDER_BY)

索引提示为优化器提供有关如何在查询处理期间选择索引的信息。此处描述的索引提示与第8.9.3节“优化器提示”中描述的优化器提示不同。索引提示和优化器提示可以单独使用，也可以一起使用。

索引提示仅适用于 SELECT 和 UPDATE 语句。

索引提示位于表名之后。有关在 SELECT 语句中指定表的一般语法，请参阅第13.2.9.2节“JOIN 子句”。引用单个表（包括索引提示）的语法如下

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

官方 MySQL 文档

索引提示 https://dev.mysqlserver.cn/doc/refman/5.7/en/index-hints.html