修改mysql配置文件，優(yōu)化緩存大小和連接數(shù)連接方式，優(yōu)化sql語句 ,記得mysql好像是有工具可以查看最占用資源的sql語句，找到他，優(yōu)化他。
創(chuàng)新互聯(lián)建站是一家集網(wǎng)站建設(shè),松山企業(yè)網(wǎng)站建設(shè),松山品牌網(wǎng)站建設(shè),網(wǎng)站定制,松山網(wǎng)站建設(shè)報價,網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,松山網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。
安裝好mysql后，配制文件應(yīng)該在/usr/local/mysql/share/mysql目錄中，配制文件有幾個，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的網(wǎng)站和不同配制的服務(wù)器環(huán)境，當(dāng)然需要有不同的配制文件了。
一般的情況下，my-medium.cnf這個配制文件就能滿足我們的大多需要；一般我們會把配置文件拷貝到/etc/my.cnf 只需要修改這個配置文件就可以了，使用mysqladmin variables extended-status _u root _p 可以看到目前的參數(shù)，有3個配置參數(shù)是最重要的，即key_buffer_size,query_cache_size,table_cache。
key_buffer_size只對MyISAM表起作用，
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。一般我們設(shè)為16M,實際上稍微大一點的站點 這個數(shù)字是遠(yuǎn)遠(yuǎn)不夠的，通過檢查狀態(tài)值Key_read_requests和Key_reads,可以知道key_buffer_size設(shè)置是否合理。比例 key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。 或者如果你裝了phpmyadmin 可以通過服務(wù)器運行狀態(tài)看到,筆者推薦用phpmyadmin管理mysql，以下的狀態(tài)值都是本人通過phpmyadmin獲得的實例分析:
這個服務(wù)器已經(jīng)運行了20天
key_buffer_size _ 128M
key_read_requests _ 650759289
key_reads - 79112
比例接近1:8000 健康狀況非常好
目前公司的訂單表有100多萬條，使用訂單號查詢數(shù)據(jù)時，所需時間大多要10-30秒不等，查看了慢查詢?nèi)罩?，發(fā)現(xiàn)有的訂單查詢竟然耗時65秒
我查看了原有的查詢語句，發(fā)現(xiàn)where后面跟了or查詢，雖然3個or都索引，使用explain分析查詢結(jié)果，發(fā)現(xiàn)要掃描近70萬行，幾乎是全盤掃描一遍，只為獲取最多3條數(shù)據(jù)，效率實在是低下
這3個字段均設(shè)置了索引，但or在這個語句中，使索引失效了（主要看最后幾行）
使用union all代替or查詢，也就是說把3個字段的查詢分別做查詢，將結(jié)果使用union all連接在一起，這樣單次查詢可以用到索引，效率大大提高
先看一下分析結(jié)果
簡要的sql語句，查詢結(jié)果不超80ms
在開始演示之前，我們先介紹下兩個概念。
概念一，數(shù)據(jù)的可選擇性基數(shù)，也就是常說的cardinality值。
查詢優(yōu)化器在生成各種執(zhí)行計劃之前，得先從統(tǒng)計信息中取得相關(guān)數(shù)據(jù)，這樣才能估算每步操作所涉及到的記錄數(shù)，而這個相關(guān)數(shù)據(jù)就是cardinality。簡單來說，就是每個值在每個字段中的唯一值分布狀態(tài)。
比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數(shù)可以是100個，也可以是1個，當(dāng)然也可以是1到100之間的任何一個數(shù)字。這里唯一值越的多少，就是這個列的可選擇基數(shù)。
那看到這里我們就明白了，為什么要在基數(shù)高的字段上建立索引，而基數(shù)低的的字段建立索引反而沒有全表掃描來的快。當(dāng)然這個只是一方面，至于更深入的探討就不在我這篇探討的范圍了。
概念二，關(guān)于HINT的使用。
這里我來說下HINT是什么，在什么時候用。
HINT簡單來說就是在某些特定的場景下人工協(xié)助MySQL優(yōu)化器的工作，使她生成最優(yōu)的執(zhí)行計劃。一般來說，優(yōu)化器的執(zhí)行計劃都是最優(yōu)化的，不過在某些特定場景下，執(zhí)行計劃可能不是最優(yōu)化。
比如：表t1經(jīng)過大量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經(jīng)很不準(zhǔn)確了，這時候剛好執(zhí)行了一條SQL，那么有可能這條SQL的執(zhí)行計劃就不是最優(yōu)的。為什么說有可能呢？
來看下具體演示
譬如，以下兩條SQL，
A：
select * from t1 where f1 = 20;
B：
select * from t1 where f1 = 30;
如果f1的值剛好頻繁更新的值為30，并且沒有達(dá)到MySQL自動更新cardinality值的臨界值或者說用戶設(shè)置了手動更新又或者用戶減少了sample page等等，那么對這兩條語句來說，可能不準(zhǔn)確的就是B了。
這里順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。
那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個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實際上在rank1,rank2,rank3三列上分別有一個二級索引。
那我們來看SQL C的查詢計劃。
顯然，沒有用到任何索引，掃描的行數(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給相同的查詢，再次看看查詢計劃。
這個時候用到了index_merge,union了三個列。掃描的行數(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)
我們再看下SQL D的計劃：
不加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)
對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。
總結(jié)下，就是說表的cardinality值影響這張的查詢計劃，如果這個值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。
范圍訪問方法使用一個索引來檢索包含一個或多個索引間隔中的表行的子集。它可以使用索引中的一列或者多列，以下各節(jié)描述了優(yōu)化器使用范圍訪問的條件
對于一個單列索引，索引值間隔可以方便地由 WHERE 條件中的相應(yīng)條件表示，表示為范圍條件而不是 intervals 。
上述的 常量 指以下情況之一：
以下是在 WHERE 子句中具有范圍條件的查詢示例
一些非常量可能會在優(yōu)化器傳播階段轉(zhuǎn)換為常量
MySQL對于每個可能使用的索引，嘗試從 WHERE 子句中提取范圍條件。在提取過程中，不能用于構(gòu)建條件范圍的條件被刪除，產(chǎn)生重復(fù)范圍的條件被合并，產(chǎn)生空范圍的條件被刪除。
假設(shè)有以下語句， key1 是一個被索引的列，而 nonkey 沒有索引
提取 key1 索引的過程如下：
通常，范圍掃描使用的條件比 WHERE 子句中的限制要少()。MySQL執(zhí)行額外的檢查來過濾滿足范圍條件但是不完全滿足 WHERE 子句的行。
范圍條件提取算法可以處理任意深度嵌套的 AND/OR 構(gòu)造，并且它的輸出不取決于條件在 WHERE 子句中出現(xiàn)的順序
MySQL不支持為空間索引的 range 訪問合并多個范圍。要解決此限制，可以在相同的 SELECT 語句中使用 UNION 語句，將每個空間謂詞放在不同的 SELECT 中。
多列索引的范圍條件是單列索引的擴展，多列索引的范圍條件將索引行限制在一個或多個索引元組的間隔中。索引元組間隔是一個按照索引順序的，索引元組的集合。
假設(shè)有一個多列索引 key1(key_part1,key_part2,key_part3) ,按照索引順序，具有以下鍵值元組列表
key_part1 = 1 定義了一個間隔： (1,-inf,-inf) = (key_part1,key_part2,key_part3) (1,+inf,+inf) ，這個間隔包括上面的第4、5、6個元組并且可以被用來進行范圍訪問。
但是， key_part3 = 'abc' 沒有定義間隔并且不能被范圍訪問方法使用。
就是索引的最左前綴原則，B樹索引是有序的，多列索引是首先按照第一列進行排序，然后在第一列排序的基礎(chǔ)上，再對第二列數(shù)據(jù)進行排序，所以后面的列的順序獨立來看不是有序的，就不能單獨用后面的列來進行排序或者范圍訪問的操作。
對于 HASH 索引，只能使用包含相同值的每個間隔。這意味著只能針對以下形式的條件生成間隔：
這里， const1,const2... 是常量， cmp 是比較表達(dá)式： =,=,IS NULL ,并且條件覆蓋所有的索引部分（就是說，如果有 N 個條件，那么每個條件都需要是一個 N列 索引的一部分）。例如：以下是一個三列 HASH 索引的一個范圍條件
對于 BTREE 索引，一個間隔可以是使用 AND 組成的多個范圍條件的集合，每個條件都將索引的一部分和一個常量使用 =,=,IS NULL,,,=,=,!=,,BETWEENT,LIKE 'pattern'(pattern不以通配符開始) 進行比較。只要可以確定與條件匹配的一個索引元組，就可以使用一個間隔（ !=, 使用兩個間隔）
當(dāng)比較運算符是 =,=,IS NULL 時，優(yōu)化器嘗試使用索引的其他部分來確定間隔。如果比較運算符是 , , =, =, !=, , BETWEEN, LIKE ,優(yōu)化器使用索引，但不考慮索引中的其他列。
對于以下表達(dá)式，優(yōu)化器使用第一個 = ，也會使用第二個 = ,但是忽略其他索引部分，并且不將第三部分用作間隔構(gòu)造。
key_part1 = 'foo' AND key_part2 = 10 AND key_part3 10
單個間隔為：
創(chuàng)建的間隔中可能包括比原始條件更多的行，比如，前面這個間隔可能會包括 ('foo',11,0) 這個值， 010 ,這個值不滿足原始條件
如果覆蓋間隔中的行集合的條件使用 OR 進行組合，則他們會形成間隔的并集。
如果條件使用 AND 進行組合，他們形成一個包括間隔交集的行集合。
示例：
這個在兩列索引上的條件：
(key_part1 = 1 AND key_part2 2) OR (key_part1 5)
間隔是：
可以查看 EXPLAIN 輸出中的 key_len 部分查看使用的索引前綴的最大長度。
在某些情況下， key_len 包括已使用的索引列，但是這個列可能不是你期望的，假設(shè) key_part1 和 key_part2 可以為 NULL ,然后， key_len 顯示以下條件的兩個索引部分長度：
key_part1 = 1 AND key_part2 2
但是實際上，這個條件被轉(zhuǎn)換為：
key_part1 = 1 AND key_part2 IS NOT NULL
假設(shè)以下表達(dá)式, col_name 是一個索引的列
只要 col_name 等同于這些值中的任意一個，這個表達(dá)式結(jié)果就是 true 。這種比較是等值范圍比較（其中的“范圍”是一個單獨的值）。
優(yōu)化器按照以下方法，估算讀取相等的值來進行等值范圍比較的成本：
當(dāng)使用 index dive 時，優(yōu)化器在每個范圍的末端進行 dive 并且使用該范圍中的行數(shù)作為估算值。例如： col_name IN (10, 20, 30) 具有三個等值范圍，優(yōu)化器對每個范圍進行兩次 dive 以生成估算值。每次 dive 都會得出具有給定值的行數(shù)的估算值。
使用 index dive 提供了準(zhǔn)確的行數(shù)估算值，但是隨著表達(dá)式中要比較的值的數(shù)量增加，優(yōu)化器需要使用更長的時間來生成行數(shù)的估算值。而使用索引統(tǒng)計信息的準(zhǔn)確性不如直接使用索引，但是可以對大表進行更快的估算。
eq_range_index_dive_limit 選項可以控制優(yōu)化器選擇評估策略的值。要對 N 個等值范圍使用 index dive ，將 eq_range_index_dive_limit 設(shè)置為 N+1 ,要禁用統(tǒng)計信息，總是使用 index dive ，將 eq_range_index_dive_limit 設(shè)置為0。
在MySQL8.0以前，除了使用 eq_range_index_dive_limit ，沒有其他方法可以跳過 index dive 。在MySQL8.0中，當(dāng)滿足以下條件時，跳過 index dive :
對于 EXPLAIN FOR CONNECTION ,如果跳過了 index dive ，輸出結(jié)果有所變更：
不包括 FOR CONNECTION 的 EXPLAIN 輸出沒有變化
在執(zhí)行跳過 index dive 的查詢后， INFORMATION_SCHEMA.OPTIMIZER_TRACE 表包含一個值為 skipped_due_to_force_index 的 index_dives_for_range_access 行
優(yōu)化器可以對這種形式的查詢進行范圍掃描：
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
要使用范圍掃描，查詢必須滿足以下條件：
要控制有多少內(nèi)存可以用來進行范圍優(yōu)化，使用 range_optimizer_max_mem_size 變量
使用以下原則估算范圍掃描使用的內(nèi)存：
IN() 中的每個值被當(dāng)做使用 OR 結(jié)合的一個謂詞。如果有兩個 IN() 列表，每個列表中都是列表中的值的數(shù)量個謂詞通過 OR 結(jié)合。在這種情況下，視作 M × N 個 謂詞通過OR 結(jié)合。
重慶分公司
重慶分公司
