日本综合一区二区|亚洲中文天堂综合|日韩欧美自拍一区|男女精品天堂一区|欧美自拍第6页亚洲成人精品一区|亚洲黄色天堂一区二区成人|超碰91偷拍第一页|日韩av夜夜嗨中文字幕|久久蜜综合视频官网|精美人妻一区二区三区

mysql數(shù)據(jù)庫(kù)優(yōu)化 (一) in與not in

查詢一張表的數(shù)據(jù)是否存在于另一張表中

創(chuàng)新互聯(lián)是一家專業(yè)提供金堂縣企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站建設(shè)、成都做網(wǎng)站、H5開(kāi)發(fā)、小程序制作等業(yè)務(wù)。10年已為金堂縣眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計(jì)公司優(yōu)惠進(jìn)行中。

第一寫(xiě)法就是用in或者not in

例如

優(yōu)缺點(diǎn)

mysql 里 delete in 語(yǔ)句暴慢無(wú)比 優(yōu)化

十萬(wàn)級(jí)別查詢量 志強(qiáng)e5 cpu 單核100% 超3分鐘才能跑完。

優(yōu)化后10秒內(nèi)可以跑完。

思路 通過(guò)臨時(shí)表創(chuàng)建索引用 空間換時(shí)間避免頻繁讀取原表信息

mysql刪除原則

not exist 比not in執(zhí)行效率高 （線上項(xiàng)目保持正確性，沒(méi)有嘗試網(wǎng)上有人推薦使用 not exist 由于改動(dòng)大沒(méi)有嘗試）

truncate 比 delete執(zhí)行效率高

mysql 有哪些常見(jiàn)的優(yōu)化策略

在開(kāi)始演示之前，我們先介紹下兩個(gè)概念。

概念一，數(shù)據(jù)的可選擇性基數(shù)，也就是常說(shuō)的cardinality值。

查詢優(yōu)化器在生成各種執(zhí)行計(jì)劃之前，得先從統(tǒng)計(jì)信息中取得相關(guān)數(shù)據(jù)，這樣才能估算每步操作所涉及到的記錄數(shù)，而這個(gè)相關(guān)數(shù)據(jù)就是cardinality。簡(jiǎn)單來(lái)說(shuō)，就是每個(gè)值在每個(gè)字段中的唯一值分布狀態(tài)。

比如表t1有100行記錄，其中一列為f1。f1中唯一值的個(gè)數(shù)可以是100個(gè)，也可以是1個(gè)，當(dāng)然也可以是1到100之間的任何一個(gè)數(shù)字。這里唯一值越的多少，就是這個(gè)列的可選擇基數(shù)。

那看到這里我們就明白了，為什么要在基數(shù)高的字段上建立索引，而基數(shù)低的的字段建立索引反而沒(méi)有全表掃描來(lái)的快。當(dāng)然這個(gè)只是一方面，至于更深入的探討就不在我這篇探討的范圍了。

概念二，關(guān)于HINT的使用。

這里我來(lái)說(shuō)下HINT是什么，在什么時(shí)候用。

HINT簡(jiǎn)單來(lái)說(shuō)就是在某些特定的場(chǎng)景下人工協(xié)助MySQL優(yōu)化器的工作，使她生成最優(yōu)的執(zhí)行計(jì)劃。一般來(lái)說(shuō)，優(yōu)化器的執(zhí)行計(jì)劃都是最優(yōu)化的，不過(guò)在某些特定場(chǎng)景下，執(zhí)行計(jì)劃可能不是最優(yōu)化。

比如：表t1經(jīng)過(guò)大量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經(jīng)很不準(zhǔn)確了，這時(shí)候剛好執(zhí)行了一條SQL，那么有可能這條SQL的執(zhí)行計(jì)劃就不是最優(yōu)的。為什么說(shuō)有可能呢？

來(lái)看下具體演示

譬如，以下兩條SQL，

A：

select * from t1 where f1 = 20;

B：

select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30，并且沒(méi)有達(dá)到MySQL自動(dòng)更新cardinality值的臨界值或者說(shuō)用戶設(shè)置了手動(dòng)更新又或者用戶減少了sample page等等，那么對(duì)這兩條語(yǔ)句來(lái)說(shuō)，可能不準(zhǔn)確的就是B了。

這里順帶說(shuō)下，MySQL提供了自動(dòng)更新和手動(dòng)更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊(cè)。

那回到正題上，MySQL 8.0 帶來(lái)了幾個(gè)HINT，我今天就舉個(gè)index_merge的例子。

示例表結(jié)構(gòu)：

mysql desc t1;+------------+--------------+------+-----+---------+----------------+| Field ? ? ?| Type ? ? ? ? | Null | Key | Default | Extra ? ? ? ? ?|+------------+--------------+------+-----+---------+----------------+| id ? ? ? ? | int(11) ? ? ?| NO ? | PRI | NULL ? ?| auto_increment || rank1 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| rank2 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| log_time ? | datetime ? ? | YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| prefix_uid | varchar(100) | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|| desc1 ? ? ?| text ? ? ? ? | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|| rank3 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

表記錄數(shù)：

mysql select count(*) from t1;+----------+| count(*) |+----------+| ? ?32768 |+----------+1 row in set (0.01 sec)

這里我們兩條經(jīng)典的SQL：

SQL C：

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D：

select * from t1 where rank1 =100 ?and rank2 =100 ?and rank3 =100;

表t1實(shí)際上在rank1,rank2,rank3三列上分別有一個(gè)二級(jí)索引。

那我們來(lái)看SQL C的查詢計(jì)劃。

顯然，沒(méi)有用到任何索引，掃描的行數(shù)為32034，cost為3243.65。

mysql explain ?format=json select * from t1 ?where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "3243.65" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "ALL", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"rows_examined_per_scan": 32034, ? ? ?"rows_produced_per_join": 115, ? ? ?"filtered": "0.36", ? ? ?"cost_info": { ? ? ? ?"read_cost": "3232.07", ? ? ? ?"eval_cost": "11.58", ? ? ? ?"prefix_cost": "3243.65", ? ? ? ?"data_read_per_join": "49K" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

我們加上hint給相同的查詢，再次看看查詢計(jì)劃。

這個(gè)時(shí)候用到了index_merge,union了三個(gè)列。掃描的行數(shù)為1103，cost為441.09，明顯比之前的快了好幾倍。

mysql explain ?format=json select /*+ index_merge(t1) */ * from t1 ?where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "441.09" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "index_merge", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "union(idx_rank1,idx_rank2,idx_rank3)", ? ? ?"key_length": "5,5,5", ? ? ?"rows_examined_per_scan": 1103, ? ? ?"rows_produced_per_join": 1103, ? ? ?"filtered": "100.00", ? ? ?"cost_info": { ? ? ? ?"read_cost": "330.79", ? ? ? ?"eval_cost": "110.30", ? ? ? ?"prefix_cost": "441.09", ? ? ? ?"data_read_per_join": "473K" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

我們?cè)倏聪耂QL D的計(jì)劃：

不加HINT，

mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "534.34" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "ref", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "idx_rank1", ? ? ?"used_key_parts": [ ? ? ? ?"rank1" ? ? ?], ? ? ?"key_length": "5", ? ? ?"ref": [ ? ? ? ?"const" ? ? ?], ? ? ?"rows_examined_per_scan": 555, ? ? ?"rows_produced_per_join": 0, ? ? ?"filtered": "0.07", ? ? ?"cost_info": { ? ? ? ?"read_cost": "478.84", ? ? ? ?"eval_cost": "0.04", ? ? ? ?"prefix_cost": "534.34", ? ? ? ?"data_read_per_join": "176" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

加了HINT，

mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "5.23" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "index_merge", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "intersect(idx_rank1,idx_rank2,idx_rank3)", ? ? ?"key_length": "5,5,5", ? ? ?"rows_examined_per_scan": 1, ? ? ?"rows_produced_per_join": 1, ? ? ?"filtered": "100.00", ? ? ?"cost_info": { ? ? ? ?"read_cost": "5.13", ? ? ? ?"eval_cost": "0.10", ? ? ? ?"prefix_cost": "5.23", ? ? ? ?"data_read_per_join": "440" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

對(duì)比下以上兩個(gè)，加了HINT的比不加HINT的cost小了100倍。

總結(jié)下，就是說(shuō)表的cardinality值影響這張的查詢計(jì)劃，如果這個(gè)值沒(méi)有正常更新的話，就需要手工加HINT了。相信MySQL未來(lái)的版本會(huì)帶來(lái)更多的HINT。

mysql 千萬(wàn)級(jí)別的 in 查詢優(yōu)化

這個(gè)主鍵ID其實(shí)已經(jīng)是有建立了索引的了，而在IN查詢當(dāng)中并沒(méi)有用到而已，其實(shí)你可以試試IN里的id少些時(shí)，是會(huì)用到索引的，但當(dāng)IN里的id占據(jù)全表的大部分?jǐn)?shù)據(jù)量時(shí)，mysql采用的時(shí)全表掃描。在這個(gè)時(shí)候可以考慮：1.split返回臨時(shí)表進(jìn)行表連接，2.使用緩存遍歷

怎么代替mysql的in函數(shù)優(yōu)化速度

你這涉及到union all,or 和in 及索引字段的，，，，

1、對(duì)于索引列來(lái)最好使用union all，因復(fù)雜的查詢【包含運(yùn)算等】將使or、in放棄索引而全表掃描，除非你能確定or、in會(huì)使用索引。

2、對(duì)于只有非索引字段來(lái)說(shuō)你就老老實(shí)實(shí)的用or 或者in，因?yàn)?非索引字段本來(lái)要全表掃描而union all 只成倍增加表掃描的次數(shù)。

3、對(duì)于及有索引字段【索引字段有效】又包含非索引字段來(lái)時(shí)，按理你也使用or 、in或者union all 都可以，但是我推薦使用or、in。

上面都說(shuō)的是單表的情況，所以你這個(gè)問(wèn)題你給出語(yǔ)句信息，

并不能簡(jiǎn)單的說(shuō)誰(shuí)比in快的，，，，要看索引字段情況的

mysql的子查詢中有統(tǒng)計(jì)語(yǔ)句 我該如何優(yōu)化

子查詢優(yōu)化策略

對(duì)于不同類型的子查詢，優(yōu)化器會(huì)選擇不同的策略。

1. 對(duì)于 IN、=ANY 子查詢，優(yōu)化器有如下策略選擇：

semijoin

Materialization

exists

2. 對(duì)于 NOT IN、ALL 子查詢，優(yōu)化器有如下策略選擇：

Materialization

exists

3. 對(duì)于 derived 派生表，優(yōu)化器有如下策略選擇：

derived_merge，將派生表合并到外部查詢中（5.7 引入 ）；

將派生表物化為內(nèi)部臨時(shí)表，再用于外部查詢。

注意：update 和 delete 語(yǔ)句中子查詢不能使用 semijoin、materialization 優(yōu)化策略

當(dāng)前題目：mysql中in怎么優(yōu)化,mysql中in的用法
標(biāo)題鏈接：http://www.dlmjj.cn/article/hsjhdd.html