查詢執(zhí)行引擎

MySQL只是簡(jiǎn)單地根據(jù)執(zhí)行計(jì)劃給出的指令住不住想, 基本上是通過(guò)調(diào)用存儲(chǔ)引擎實(shí)現(xiàn)的接口來(lái)完成, 這些接口類似搭積木一樣能夠完成查詢的大部分操作.

返回結(jié)果給客戶端

MySQL將結(jié)果返回給客戶端是個(gè)增量, 逐步返回的過(guò)程. 一旦服務(wù)器處理完最后一個(gè)關(guān)聯(lián)表, 開始生成第一條結(jié)果時(shí), MySQL就可以開始向客戶端逐步返回結(jié)果集了.

MySQL查詢優(yōu)化器的局限性

關(guān)聯(lián)子查詢

老版的MySQL子查詢實(shí)現(xiàn)的非常糟糕, 新版本的MySQL基本沒(méi)有問(wèn)題了.
因此最好通過(guò)EXPLAIN命令來(lái)實(shí)際的看效率是否高

image.png

如果效率不高, 改為關(guān)聯(lián)的方式

image.png

如何用好關(guān)聯(lián)子查詢

請(qǐng)通過(guò)實(shí)際測(cè)試來(lái)看

UNION的限制

如果需要多個(gè)表取出數(shù)據(jù)union后再limit，可以先limit再取出, 可以提高性能.

并行執(zhí)行

Mysql不支持多核來(lái)并行執(zhí)行查詢

哈希關(guān)聯(lián)

老版本MySQL不支持哈希關(guān)聯(lián)，所有的關(guān)聯(lián)都是循環(huán)關(guān)聯(lián)，可以通過(guò)建立一個(gè)哈希索引列模擬哈希關(guān)聯(lián).

松散索引掃描

Mysql在5.0之后的，松散索引掃描的一些限制通過(guò)"索引條件下推"的方式解決

最大值和最小值優(yōu)化

image.png

mysql> explain select min(actor_id) from actor where first_name='PENELOPE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

LIMIT 1優(yōu)化后, 當(dāng)MySQL讀到第一個(gè)滿足條件的記錄就停止.

image.png

不過(guò)我實(shí)驗(yàn)下來(lái)沒(méi)有區(qū)別, 可能sql版本不同吧:

mysql> explain select min(actor_id) from actor where first_name='PENELOPE';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql> explain select actor_id from actor use index(primary) where first_name='PENELOPE' limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql> explain select actor_id from actor where first_name='PENELOPE' limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

對(duì)同一張表查詢和更新

MySQL不允許對(duì)同一張表同時(shí)進(jìn)行查詢和更新

image.png

可以通過(guò)生成臨時(shí)表來(lái)處理, 子查詢?cè)赨PDATE語(yǔ)句打開表之前已經(jīng)完成:

image.png

查詢優(yōu)化器的提示（hint）

略

優(yōu)化特定類型的查詢

count()可以統(tǒng)計(jì)某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)
統(tǒng)計(jì)某個(gè)列時(shí)，代表不是NULL 的列
統(tǒng)計(jì)行數(shù)count(*), * 代表忽略所有的列 直接統(tǒng)計(jì)行數(shù)，意義清晰，性能會(huì)更好

使用近似值

如果count()查詢太慢, 可以考慮用explain拿到近似值

更復(fù)雜的優(yōu)化

count() 需要掃描大量的行，優(yōu)化需要增加匯總表或者類似redis這樣的外部緩存系統(tǒng), 極客時(shí)間在第14課講到了使用redis可能導(dǎo)致不一致問(wèn)題.

優(yōu)化關(guān)聯(lián)查詢

• 確保ON或USING子句中的列上有索引, 一般只需要在關(guān)聯(lián)順序中的第二個(gè)表上創(chuàng)建索引, 否則帶來(lái)額外負(fù)擔(dān)
• 確保GROUP BY, ORDER BY的表達(dá)式只涉及一個(gè)表中的類, 這樣MySQL才能使用索引做優(yōu)化
• 升級(jí)MySQL時(shí)要仔細(xì)評(píng)估, 舊的的查詢語(yǔ)句可能會(huì)變慢甚至結(jié)果都發(fā)生變化

優(yōu)化子查詢

老版本的MySQL中盡可能用關(guān)聯(lián)查詢代替子查詢, 不過(guò)高版本MySQL已經(jīng)沒(méi)有問(wèn)題了

優(yōu)化GROUP BY和DISTINCT

首先有限使用索引優(yōu)化,
如果無(wú)法使用索引時(shí)，group by 使用兩種策略來(lái)完成：臨時(shí)表或文件排序來(lái)分組，可以通過(guò) SQL_BIG_RESULT 和 SQL_SMALL_RESULT來(lái)讓優(yōu)化器選擇希望的方式

若對(duì)關(guān)聯(lián)查詢分組, 最好使用標(biāo)識(shí)列, 比如下面的語(yǔ)句可以優(yōu)化為:

優(yōu)化前

優(yōu)化后

這個(gè)查詢?cè)诓粫?huì)有同名的演員的前提下, 改寫后的結(jié)果不受影響.

• 不過(guò)我發(fā)現(xiàn)mysql8.0反而速度降低了

mysql> explain select actor.first_name, actor.last_name, count(*) cnt from film_actor inner join actor using(actor_id) group by actor.first_name, actor.last_name;
mysql> show status like 'Last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 617.732404 |
+-----------------+——————+

mysql> explain select actor.first_name, actor.last_name, c.cnt from actor inner join (select actor_id, count(*) as cnt from film_actor group by actor_id) as c using(actor_id);

mysql> show status like 'Last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 2481.148000 |
+-----------------+-------------+

優(yōu)化limit分頁(yè)

當(dāng)一次需要偏移量很大時(shí)，盡可能使用索引覆蓋掃描, 而不是查詢所有的列. 然后根據(jù)需要做一次關(guān)聯(lián)操作返回所需的列:

image.png

image.png

優(yōu)化UNION查詢

1. 如果不需要消除重復(fù)行, 盡量用UNION ALL.
2. 將where, limit, order by等子句"下推"到UNION的各個(gè)子查詢中.

用戶自定義變量

略, 有不少技巧, 當(dāng)工具書查詢

案例學(xué)習(xí)

使用MySQL構(gòu)建一個(gè)隊(duì)列表

我把我的經(jīng)驗(yàn)和書中內(nèi)容結(jié)合一下:

1. 要找到未處理的記錄, 一般不會(huì)用MySQL的sleep, 而是使用定時(shí)job

2. 要標(biāo)記正在處理的記錄, 不至于讓多個(gè)消費(fèi)者重復(fù)處理一個(gè)記錄:

   
   
   image.png

   • 書中的方案是不要使用select for update鎖表, 而是將該表的owner設(shè)置為正在處理這個(gè)記錄的連接ID:

     
     
     image.png
     
     

     先更新?tīng)顟B(tài), 再取數(shù)據(jù), 根據(jù)owner來(lái)拿到自己要處理的數(shù)據(jù). 若該連接在處理時(shí)退出了, 只需要定期運(yùn)行update語(yǔ)句將其更新為原始狀態(tài)即可. 可以用show processlist, 獲取當(dāng)前正在工作的線程, 用where條件避免使用到這些剛開始處理的線程:

     
     
     image.png

• 不過(guò)我覺(jué)得該方案太復(fù)雜了, 我們一般可以使用樂(lè)觀鎖, 避免1條記錄被多個(gè)消費(fèi)者消費(fèi), 在表上加個(gè)version字段

1. 先查詢一批待處理的記錄
2. 對(duì)每條記錄, 使用樂(lè)觀鎖技術(shù)更新?tīng)顟B(tài)為處理中:

update unset_mails set status='claimed', version=version+1 where id=10 and version=0;

3. 如果更新成功, 則可以做該任務(wù). 做完后, 將狀態(tài)翻轉(zhuǎn)為處理成功即可.
4. 如果更新沒(méi)成功, 說(shuō)明有其他消費(fèi)者同時(shí)搶到了該任務(wù), 那就跳過(guò)即可.
5. 最后, 如果消費(fèi)者在處理時(shí)由于某種情況退出, 導(dǎo)致記錄一直處于處理中的, 可以定期得將它們翻成待處理即可(一般不會(huì)考慮是否有進(jìn)程仍在處理的情況, 因?yàn)闃I(yè)務(wù)上可以做個(gè)估計(jì), 比如超時(shí)了10分鐘, 說(shuō)明肯定是消費(fèi)進(jìn)程掛了).