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