資料庫mul

1. 面試中常問：mysql資料庫做哪些優化也提高mysql性能

在鍵租謹開始演示之前，我們先介紹下兩個概念。

概念一，數據的可選擇性基數，也就是常說的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 = 2G*************************** 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_proced_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 = 2G*************************** 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_proced_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 =100G*************************** 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_proced_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 =100G*************************** 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_proced_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。

2. MySQL中的MUL完全解釋mysql中mul全稱

MySQL中的MUL完全解釋
MySQL是一種流行的關系型資料庫管理系統，被廣泛用於Web應用程序的後端和其他數據驅動應用程序。其中，MUL是MySQL中的一種數據類型，具有特殊的意義和用途。本文將詳細解釋MUL在MySQL中的作用和用法，並附上相關代碼示例。
MUL的含義
MUL是MySQL中欄位的一個屬性，表示可為空null、可重復出現multiple的欄位。也就是說，該欄位可以有多個值，且這些值可以是null。MUL通常用於關聯表或者其他需要存儲多個值的情況，如標簽、分類等。
MUL的用法
MUL屬性一般用於與其他表關聯的情況，例如，我們有一張訂單表，每個訂單可以有多個商品，而每個商品又有自己的屬性。我們可以將商品的屬性單獨存儲到另一張表中，以便更方便地管理和查詢。此時，我們可以使用MUL屬性來存儲訂單和商品之間的關系。
例如，我們創建一個名為orders的訂單表：
CREATE TABLE orders (
id INT(11) NOT NULL AUTO_INCREMENT,
order_num VARCHAR(255) NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY (id)
);
同時，我們創建一個名為procts的商品表：
CREATE TABLE procts (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sku VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id)
);
為了建立訂單和商品之間的關系，我們需要使用一個中間表來保存它們之間的關系。我們可以創建一個名為order_procts的表：
CREATE TABLE order_procts (
order_id INT(11) NOT NULL,
proct_id INT(11) NOT NULL,
PRIMARY KEY (order_id, proct_id),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (proct_id) REFERENCES procts(id)
);
在order_procts表中，order_id和proct_id欄位都被定義為MUL屬性，因為一個訂單可以有多個商品，一個商品也可以被多個訂單使用。使用MUL屬性，我們可以輕松地查詢哪些商品被哪些訂單購買，以及每個訂單包括哪些商品。
例如，我們可以使用以下查詢語句來查找訂單ID為1的所有商品：
SELECT procts.name FROM procts
JOIN order_procts ON order_procts.proct_id = procts.id
WHERE order_procts.order_id = 1;
當然，這只是一個簡單的例子。在實際應用中，MUL屬性通常用於處理更復雜的數據結構和關系。
總結
MUL是MySQL中一個非常實用的數據類型，可以幫助我們處理多對多的關系，以及其他需要存儲多個值的情況。在使用MUL屬性時，需要格外小心，確保設計良好的數據結構和索引，否則可能會導致性能和可維護性的問題。為了充分利用MUL屬性，我們需要深入了解和理解MySQL的數據結構和查詢語言，以及相關的最佳實踐。