帶有 OR 條件的 SELECT 的正確索引

我想獲取子字元串在名字/姓氏中的所有行。然後我想先通過完全匹配來排序，限制為 5（基本上是分頁） Query 工作得很好，直到表變得很大，所以我想知道我是否可以更好地調整它。

SELECT id, first_name, last_name, (first_name='nir' OR last_name='nir') exact_match
FROM users
WHERE first_name like '%nir%'
     OR last_name like '%nir%'
Order by exact_match desc, last_name, first_name 
limit 5 offset 0;

create table users( 
id int , 
first_name varchar(100), 
last_name varchar(100), 
primary key(id), 
key first_name(first_name),
key last_name(last_name));

insert into users values(1,'anir','asd');
insert into users values(2,'ansir','asnird');
insert into users values(3,'nir','asnird');
insert into users values(4,'nixr','nir');
insert into users values(5,'nsixr','nsir');

由於前綴萬用字元，很難對這個查詢進行很大改進。

沒有任何更改，查詢執行計劃顯示它不會使用您的索引：

EXPLAIN SELECT id, first_name, last_name, (first_name='nir' OR last_name='nir') exact_match 
FROM users 
WHERE first_name like '%nir%' OR last_name like '%nir%' 
ORDER BY exact_match desc, last_name, first_name  
LIMIT 5 OFFSET 0\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: users
  partitions: NULL
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 5
    filtered: 36.00
       Extra: Using where; Using filesort

您可以通過創建複合索引來稍微改進它：

ALTER TABLE users ADD INDEX idx_names (first_name, last_name);

這給出了以下查詢執行計劃：

*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: users
  partitions: NULL
        type: index
possible_keys: NULL
         key: idx_names
     key_len: 206
         ref: NULL
        rows: 5
    filtered: 36.00
       Extra: Using where; Using index; Using filesort

如您所見，這將使用複合索引，但是，如果我理解正確，它只會在首先使用 where 條件過濾表之後才會這樣做。

妥協怎麼樣——假設使用者詢問名字或姓氏的*開頭。*這將有效地用於“巨大”表：

( SELECT id, first_name, last_name
   FROM tbl
   WHERE first_name LIKE 'nir%' )
UNION DISTINCT
( SELECT id, first_name, last_name
   FROM tbl
   WHERE last_name LIKE 'nir%' )
ORDER BY (first_name = 'nir' OR last_name = 'nir') DESC,
   last_name,
   first_name
LIMIT 5, 0

並折騰你擁有的兩個索引，用兩個複合索引替換它們：

INDEX(last_name, first_name, id),
INDEX(first_name, last_name, id)

另一種方法——做上述；如果你沒有得到 5 個名字，那麼回到你較慢的查詢。這將有助於系統的整體性能。但這會導致個別搜尋要麼快（我的查詢）要麼慢（需要兩個查詢）。

引用自：https://dba.stackexchange.com/questions/297740