Mysql
MariaDB（MySQL）主鍵範圍結合全文索引時查詢慢
我有一個下面描述的表，有兩列 - 整數主鍵和標題文本 - 目前保存大約 300 萬條記錄。從下面的元數據中可以看出，整數主鍵列上有一個 BTREE 索引，標題列上有一個 FULLTEXT 索引。
MariaDB [ttsdata]> describe records; +------------------+---------------------+------+-----+---------------------+-------------------------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------------+------+-----+---------------------+-------------------------------+ | id | int(15) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(2000) | YES | MUL | | | +------------------+---------------------+------+-----+---------------------+-------------------------------+ MariaDB [ttsada]> show index from records; +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | records | 0 | PRIMARY | 1 | id | A | 2798873 | NULL | NULL | | BTREE | | | | records | 1 | title | 1 | title | NULL | 1 | NULL | NULL | YES | FULLTEXT | | | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+我想執行以下查詢：
SELECT * FROM records WHERE id > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200此查詢需要 5 秒以上才能執行。當我刪除範圍部分或全文部分時，在這兩種情況下，查詢都會在大約 100 毫秒內執行。下面是對單個查詢的分析，最後一個是我要使用的。
所以我的問題是：如何快速查詢？
下面我發布了每個單獨條件和組合條件的統計資訊。每個統計數據包含：
EXPLAIN- 執行時間與
SQL_NO_CACHE- 匹配行數
一般來說，我是 MySQL 和 DBA 的新手。我不知道如何從中得出任何結論。
神奇的
2589968數字是碰巧需要的東西。查詢 1
SELECT * FROM records WHERE id > 2589968 LIMIT 200MariaDB [ttsdata]> explain SELECT * FROM records WHERE id > 2589968 LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ | 1 | SIMPLE | records | range | PRIMARY | PRIMARY | 4 | NULL | 227183 | Using index condition | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ 1 row in set (0.005 sec) MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 2589968 LIMIT 200; ... 200 rows in set (0.108 sec) MariaDB [ttsdata]> SELECT count(*) FROM records WHERE id > 2589968; +----------+ | count(*) | +----------+ | 208905 | +----------+ 1 row in set (0.257 sec)查詢時間：0.108 秒
查詢 2
SELECT * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200MariaDB [ttsdata]> explain SELECT * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | 1 | SIMPLE | records | fulltext | title | title | 0 | | 1 | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.007 sec) MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (0.138 sec) MariaDB [ttsdata]> SELECT count(*) FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE); +----------+ | count(*) | +----------+ | 1846710 | +----------+ 1 row in set (6.056 sec)查詢時間：0.138 秒
查詢 3
SELECT * FROM records WHERE id > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200MariaDB [ttsdata]> explain SELECT * FROM records WHERE id > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | 1 | SIMPLE | records | fulltext | PRIMARY,title | title | 0 | | 1 | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.005 sec) MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (5.627 sec) MariaDB [ttsdata]> SELECT count(*) FROM records WHERE id > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +----------+ | count(*) | +----------+ | 91026 | +----------+ 1 row in set (6.817 sec)查詢時間：5.627 秒
分析查詢語句
WHERE id > 2589968MariaDB [ttsdata]> analyze SELECT * FROM records WHERE id > 2589968 LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+--------+----------+------------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+---------+-------+---------------+---------+---------+------+--------+--------+----------+------------+--------------------------+ | 1 | SIMPLE | records | range | PRIMARY | PRIMARY | 4 | NULL | 227210 | 200.00 | 100.00 | 100.00 | Using where; Using index | +------+-------------+---------+-------+---------------+---------+---------+------+--------+--------+----------+------------+--------------------------+ 1 row in set (0.007 sec)MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE)MariaDB [ttsdata]> analyze SELECT * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-----------+---------+------+------+--------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+---------+----------+---------------+-----------+---------+------+------+--------+----------+------------+-------------+ | 1 | SIMPLE | records | fulltext | title | title | 0 | | 1 | 200.00 | 100.00 | 100.00 | Using where | +------+-------------+---------+----------+---------------+-----------+---------+------+------+--------+----------+------------+-------------+ 1 row in set (0.007 sec)WHERE id > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE)MariaDB [ttsdata]> analyze SELECT id FROM records WHERE id > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+------------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+---------+----------+---------------+-------+---------+------+------+------------+----------+------------+-------------+ | 1 | SIMPLE | records | fulltext | PRIMARY,title | title | 0 | | 1 | 1755861.00 | 8.12 | 0.01 | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+------------+----------+------------+-------------+ 1 row in set (5.768 sec)使用索引提示進行解釋和分析
無索引提示
詢問：
SELECT * FROM records WHERE ID > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200
USE INDEX (PRIMARY, title)索引提示也報告了相同的結果。結果：
MariaDB [ttsdata]> explain SELECT * FROM records WHERE ID > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+----------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | records | fulltext | PRIMARY,title | title | 0 | | 1 | Using where | +------+-------------+---------+----------+---------------+-----------+---------+------+------+-------------+ 1 row in set (0.009 sec) MariaDB [ttsdata]> analyze SELECT * FROM records WHERE ID > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+------------+----------+------------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+---------+----------+---------------+-------+---------+------+------+------------+----------+------------+-------------+ | 1 | SIMPLE | records | fulltext | PRIMARY,title | title | 0 | | 1 | 1755861.00 | 8.12 | 0.01 | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+------------+----------+------------+-------------+ 1 row in set (5.763 sec)使用索引（主要）
詢問：
SELECT * FROM records USE INDEX (PRIMARY) WHERE ID > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200結果：
MariaDB [ttsdata]> explain SELECT * FROM records USE INDEX (PRIMARY) WHERE ID > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+---------+-------+---------------+---------+---------+------+--------+------------------------------------+ | 1 | SIMPLE | records | range | PRIMARY | PRIMARY | 4 | NULL | 227210 | Using index condition; Using where | +------+-------------+---------+-------+---------------+---------+---------+------+--------+------------------------------------+ 1 row in set (0.006 sec) MariaDB [ttsdata]> analyze SELECT * FROM records USE INDEX (PRIMARY) WHERE ID > 2589968 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+--------+----------+------------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+---------+-------+---------------+---------+---------+------+--------+--------+----------+------------+------------------------------------+ | 1 | SIMPLE | records | range | PRIMARY | PRIMARY | 4 | NULL | 227210 | 930.00 | 100.00 | 21.51 | Using index condition; Using where | +------+-------------+---------+-------+---------------+---------+---------+------+--------+--------+----------+------------+------------------------------------+ 1 row in set (0.014 sec)
你有：
- 208905 行其中 id > 2589968
- 1846710在哪裡匹配（標題）反對（布爾模式下的“+花”）
- 91026 WHERE id > 2589968 AND MATCH (title) AGAINST (’+flower’ IN BOOLEAN MODE)
這意味著大約 43% 的 id > 2589968 的行在其標題中包含“花”。
出於某種原因，查詢優化器決定使用全文索引來啟動。由於全文索引不包含 id 列，伺服器需要對錶執行大量查找以檢查 id 條件。只有約 5% 的行與 id 條件匹配，我想它們中的大多數都位於全文索引的“末尾”，因為您正在尋找後來添加的那些（id 列是 auto_increment）。因此，伺服器執行了很多不成功的查找，對於您的情況，從 id > 2589968 開始讀取您的聚集索引（這是主鍵）並檢查每條記錄的全文條件會更快（10 個中的 4 個應該給出成功的結果) 並在 200 場比賽后完成。
您還可以嘗試做些什麼來提高性能？
使用列 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY 而不是 id 重建表。這是列的內置 MySQL 名稱，應包含在全文索引中用作參考的文件 ID。我不確定它是否會在 MariaDB 中以相同的方式工作，實際上我沒有使用 MySQL 對其進行測試，但它可能有助於伺服器避免大量額外的查找。