---- ### 索引列 **mysql 索引字段 是 分布越不均勻，就越適合作為索引嗎，比如 唯一的字段 相較于 status 幾種枚舉 字段更適合？** 在 MySQL 中，索引字段的選擇 對查詢性能有重要影響。關于字段的分布是否越不均勻越適合作為索引，需要具體分析。以下是詳細解答： 1. 索引選擇性（Selectivity） 索引選擇性 是指索引列中不同值的數量與總行數的比例。選擇性越高，索引的效果越好。選擇性可以通過以下公式計算： 復制 選擇性 = 不同值的數量 / 總行數 高選擇性：字段的值分布不均勻，不同值的數量較多（如唯一字段）。 低選擇性：字段的值分布均勻，不同值的數量較少（如枚舉字段）。 2. 高選擇性字段適合作為索引 高選擇性字段（如唯一字段）通常更適合作為索引，因為： 過濾效果好：通過索引可以快速定位到少量數據，減少掃描的行數。 查詢性能高：索引的 B+ 樹結構可以高效地支持等值查詢和范圍查詢。 示例： 唯一字段：如用戶 ID、訂單號等，每個值只出現一次，選擇性最高。 高選擇性字段：如電子郵件、手機號等，雖然不唯一，但不同值的數量較多。 3. 低選擇性字段不適合作為索引 低選擇性字段（如枚舉字段）通常不適合作為索引，因為： 過濾效果差：通過索引可能仍然需要掃描大量數據。 索引效率低：索引的 B+ 樹結構無法顯著減少掃描的行數。 示例： 枚舉字段：如狀態（status）、性別（gender）等，不同值的數量較少。 布爾字段：如是否刪除（is_deleted），只有兩個值。 4. 低選擇性字段的優化 如果低選擇性字段需要作為查詢條件，可以考慮以下優化方法： 復合索引 將低選擇性字段與其他高選擇性字段組合成復合索引。例如： sql 復制 CREATE INDEX idx_status_user_id ON orders(status, user_id); 在查詢時，先通過 status 過濾，再通過 user_id 精確定位。 覆蓋索引 如果查詢只需要返回索引列，可以使用覆蓋索引，避免回表操作。例如： sql 復制 CREATE INDEX idx_status ON orders(status); SELECT status FROM orders WHERE status = 'pending'; 分區表 如果數據量非常大，可以使用分區表，將數據按低選擇性字段分區。例如： sql 復制 CREATE TABLE orders ( id INT PRIMARY KEY, status ENUM('pending', 'completed', 'cancelled') ) PARTITION BY LIST COLUMNS(status) ( PARTITION p0 VALUES IN ('pending'), PARTITION p1 VALUES IN ('completed'), PARTITION p2 VALUES IN ('cancelled') ); 5. 總結 高選擇性字段（如唯一字段）更適合作為索引，因為過濾效果好，查詢性能高。 低選擇性字段（如枚舉字段）通常不適合作為索引，因為過濾效果差，索引效率低。 如果低選擇性字段需要作為查詢條件，可以通過 復合索引、覆蓋索引 或 分區表 進行優化。 如果您有具體的表結構或查詢場景，可以告訴我，我會為您提供更詳細的建議！?? **但是這樣 不就導致 索引文件很多嗎，status 這種反而一個索引能對應更多數據。這對我來說，有點反認知？** 您的疑問非常有道理！確實，低選擇性字段（如 status） 的一個索引可以覆蓋更多的數據行，而 高選擇性字段（如唯一字段） 的索引可能需要更多的索引條目。這看起來似乎低選擇性字段更適合作為索引，但實際情況并非如此。以下是詳細解釋，幫助您更好地理解這個問題： 1. 索引的作用 索引的主要作用是 快速定位數據，而不是簡單地覆蓋更多數據。索引通過 B+ 樹結構，將數據的有序性和查詢條件結合起來，減少需要掃描的數據量。 2. 高選擇性字段的優勢 高選擇性字段（如唯一字段）的優勢在于： 精確過濾：通過索引可以快速定位到少量數據，甚至單條數據。 減少掃描行數：索引的 B+ 樹結構可以高效地支持等值查詢和范圍查詢，顯著減少需要掃描的行數。 示例： 假設有一個 100 萬行的表，user_id 是唯一字段，status 是枚舉字段（如 pending、completed、cancelled）。 查詢 user_id = 123： 通過 user_id 索引，可以直接定位到 1 行數據。 查詢 status = 'pending'： 假設 status 的值分布均勻，pending 占 1/3，即約 33 萬行。 通過 status 索引，仍然需要掃描 33 萬行。 顯然，user_id 索引的過濾效果更好。 3. 低選擇性字段的問題 低選擇性字段（如 status）的問題在于： 過濾效果差：即使使用索引，仍然需要掃描大量數據。 索引效率低：索引的 B+ 樹結構無法顯著減少需要掃描的行數。 回表開銷：如果查詢需要返回非索引列，還需要進行回表操作，進一步增加開銷。 示例： 繼續上面的例子，查詢 status = 'pending'： 即使使用 status 索引，仍然需要掃描 33 萬行。 如果需要返回其他列（如 order_date），還需要回表讀取 33 萬行數據。 4. 索引文件的大小 雖然低選擇性字段的索引條目可能更少，但這并不意味著索引文件更小。索引文件的大小取決于： 索引列的長度：如果索引列是長字符串，索引文件會更大。 索引的結構：B+ 樹的結構需要存儲額外的指針和元數據。 示例： 假設 status 是 VARCHAR(20)，而 user_id 是 INT： status 索引的每個條目需要存儲 20 字節的字符串。 user_id 索引的每個條目只需要存儲 4 字節的整數。 即使 status 索引的條目更少，索引文件的大小可能并不比 user_id 索引小。 5. 低選擇性字段的優化 如果低選擇性字段需要作為查詢條件，可以通過以下方式優化： 復合索引 將低選擇性字段與其他高選擇性字段組合成復合索引。例如： sql 復制 CREATE INDEX idx_status_user_id ON orders(status, user_id); 在查詢時，先通過 status 過濾，再通過 user_id 精確定位。 覆蓋索引 如果查詢只需要返回索引列，可以使用覆蓋索引，避免回表操作。例如： sql 復制 CREATE INDEX idx_status ON orders(status); SELECT status FROM orders WHERE status = 'pending'; 分區表 如果數據量非常大，可以使用分區表，將數據按低選擇性字段分區。例如： sql 復制 CREATE TABLE orders ( id INT PRIMARY KEY, status ENUM('pending', 'completed', 'cancelled') ) PARTITION BY LIST COLUMNS(status) ( PARTITION p0 VALUES IN ('pending'), PARTITION p1 VALUES IN ('completed'), PARTITION p2 VALUES IN ('cancelled') ); 6. 總結 高選擇性字段（如唯一字段）更適合作為索引，因為過濾效果好，查詢性能高。 低選擇性字段（如枚舉字段）通常不適合作為索引，因為過濾效果差，索引效率低。 索引的作用是快速定位數據，而不是簡單地覆蓋更多數據。 如果低選擇性字段需要作為查詢條件，可以通過 復合索引、覆蓋索引 或 分區表 進行優化。 如果您有具體的表結構或查詢場景，可以告訴我，我會為您提供更詳細的建議！?? ---- ## mysql表字段設計 **VARCHAR與CHAR如何選擇** 參考：[MySQL數據庫中CHAR與VARCHAR之爭 - 站長之家](http://www.chinaz.com/program/2011/0503/176896.shtml) 總結： **使用VARCHAR理由** 1. 字段不經常更新 2. 字段比較長，且長度不均（比如用戶留言，有的人長有的人短） 3. 不用再檢索列 **使用CHAR的理由** 1. 字段不是很長，且長度都比較均勻（比如用戶名） 2. …… * * * * * ### 條目表 和 json字段 的使用 > 不會再更改的信息，可以放在json字段中，否則的話，還是要用條目表。和不被查詢，即不與其它數據有關聯的，也可以用json字段 ```sql -- ---------------------------- -- Table structure for `fxz_dinne_card_mould_item` -- ---------------------------- DROP TABLE IF EXISTS `fxz_dinne_card_mould_item`; CREATE TABLE `fxz_dinne_card_mould_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `card_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應套餐卡模板ID', `service_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應服務ID', `num` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '總次數', `price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '價值-用于和商家結算（指導價格）', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='套餐卡模板服務條目 表 （其實可以像訂單表一樣，無需這個表也可以）'; -- ---------------------------- -- Table structure for `fxz_dinne_card_order` -- ---------------------------- DROP TABLE IF EXISTS `fxz_dinne_card_order`; CREATE TABLE `fxz_dinne_card_order` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '用戶ID', -- 用這些信息為用戶生成訂單，這些信息，套餐卡模板可能會改變，但是改變不能夠影響用戶的訂單，所以這些信息必須此時固化到訂單信息中，仔細注意著點，這很重要！ -- 不會再更改的信息，可以放在json字段中，否則的話，還是要用條目表 `dinne_info_json` text NULL COMMENT '套餐卡模板信息', `server_info_json` text NULL COMMENT '套餐卡模板服務條目信息', `create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間', `pay_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '支付完成時間', `amount` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '應付金額', `status` tinyint(4) NOT NULL DEFAULT 0 COMMENT '狀態，0-待支付，1-已支付', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='套餐卡訂單 表'; ``` * * * * * ### 冗余設計 與 業務邏輯 > 很多時候，冗余設計可以讓業務邏輯更方便的實現 ```php /* START ######################## 用戶可用卡信息 ######################## */ $cardServer = []; // 使用時，我們并不關心，是用了哪一張套餐卡，我們只管里面的條目夠用就行，換句話說，套餐卡是可以跨卡使用的 $on = 'dinne_card_item.service_id = platform_service.id'; $cardServer = Model()->table('dinne_card_item,platform_service')->join('left')->on($on)->field('platform_service.id,platform_service.name,SUM(dinne_card_item.surplus) AS surplus')->where(['dinne_card_item.status' => 0, 'dinne_card_item.user_id' => $userInfo['member_id']])->group('dinne_card_item.service_id')->select(); // 注意這里的 SUM(dinne_card_item.surplus) AS surplus ``` ```sql -- ---------------------------- -- Table structure for `fxz_dinne_card_item` -- ---------------------------- DROP TABLE IF EXISTS `fxz_dinne_card_item`; CREATE TABLE `fxz_dinne_card_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `card_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應套餐卡ID', -- 卡就是服務 `service_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應服務ID', `user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬用戶', `num` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '總次數', `use_num` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '已使用次數', -- 很多時候，冗余設計可以讓業務邏輯更方便的實現 `surplus` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '剩余次數', `update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '最后更新（使用）時間', `price` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '價值-用于和商家結算（指導價格）', `status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態，0-正常，1-已用完，2-已過期', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='套餐卡服務條目 表 套餐卡內包含的服務（只用于標準服務）'; ``` * * * * * ### 要注意的一些問題 **注意文本字段** ``` -- 文本的要設置為允許為空，因為它不能設置默認值（實際上默認值就是NULL），如果插入數據時沒有值會報錯的（沒有值時就用缺省值，而缺省值就是默認值） `content` longtext NULL COMMENT 'content', `pic` text NULL COMMENT '相冊，使用,分割多圖', ``` **注意NULL和唯一索引問題** > 唯一索引不約束 null ``` -- 這個用戶郵箱和手機是唯一的，但是有的人開始并沒有綁定郵箱或手機，所以也不能為空串，不然唯一沖突，所以只能允許為空null了，并且默認為null `email` char(32) NULL DEFAULT NULL COMMENT '用戶郵箱', …… UNIQUE KEY `email` (`email`) USING BTREE, ``` **默認值問題** 一定要為字段設置默認值，始終要設置默認值，除非是想面提到的文本字段不能設置默認值的情況 **并且php中獲取參數，也要有默認值：** ``` $request->param('status/d', 0) ``` 不然獲取到的值可能為null，而數據庫字段NOT NULL那么就會出錯。 請嚴格準守這些規則，任何時候，否則看這樣雖然起來不會有什問題，但是在生產環境中就會出現很多很多的問題。 * * * * * ### 數據庫名詞解析 數據庫是軟件，功能是提供存儲和查詢的服務，一般稱為存取服務。 數據庫軟件通常是以一個實例的方式呈現。 一個數據庫就是一個實例。 一個實例就是一個數據庫軟件。 一個實例上可以創建多個數據庫（這個數據庫不是上面提高的軟件的意思，而是業務數據庫）。 一個數據庫內有多張表。 一個實例可以有多個用戶賬號，這些賬號就是實例的管理用戶，也是連接賬號。 * * * * * ### 邊緣業務邏輯表 對于**邊緣業務邏輯**（臨時的需求），比如臨時的新功能（不是系統主功能，沒有也沒關系），記錄用戶有沒有查看過我們新的歡迎頁面（用cookie是不行的，用戶清除就又要看一次了），有沒有打開某個功能頁面等等這樣臨時的需求，可能只用那么一兩次的業務。這種業務表可以使用臨時邊緣表來做。用 `edgetem_` 做表前綴。 * * * * * ### 業務與字段 表設計經驗 訂單條目表，存商品的名稱，圖片，規格，是防止商品更改了。當時數據和以后數據可能不統一。 而一些關聯關系中，只存原始ID，這是說明，數據統一性比較強，數據從原始數據處獲取，原始數據更改了，所有地方都不一樣，統一了數據，這種有時候要求，原始數據不能隨意經常更改，刪除，易造成業務不穩定（如需改變，請直接新增，盡量避免刪除和改動數據），這類數據往往是系統設置的一些信息。 用id統一性，如果不統一，那么就會出現，洗車卡可以扣減打蠟的服務了，因為我們判斷用戶是否有這個服務對應的卡是按照服務id來的，如果不統一，后來系統服務名稱發生改變后，就會出現用戶卡和系統卡兩個地方名稱不一致的問題。 * * * * * ### 擴展信息表 防止一個表字段太多，有時可以拆分到另一個擴展信息表里面去。 * * * * * ### 外鍵設計技巧與規范 為了實現靈活的sql，滿足業務要求，以及后期發展，所以開始在設計表外鍵關系時就一定要遵守規范。 #### 一對一 一個用戶只有一個身份證，用戶表，身份證表，顯然用戶表為主，身份證表為從。 外鍵設計在身份證表中，user_id，而不要將外鍵身份證ID設計在用戶表中。 所以遵循的規則就是，外鍵要設計在從表上。 #### 一對多 一個用戶有多張銀行卡，用戶表，銀行卡表。用戶表為主，銀行卡表為從。 外鍵設計在銀行卡表中，user_id，這樣就能輕易實現一對多的關系了。 規訓規則還是外鍵設計在從表上，而不是在主表上設計逗號分隔的銀行卡ID。 （其實和一對一的設計方式是一樣的，一對一的關系我們不在外鍵中約束，而是在業務邏輯中約束。） #### 多對多 一個用戶屬于多個用戶組，一個用戶組可以包含多個用戶。 用戶組表為主，用戶表為從。（但此時主從關系就不在重要了。） **有兩種方案：** 1. 使用關聯表，并且用戶id和用戶組id組成唯一聯合索引。 2. 使用逗號分隔ID列表的方式。這個外鍵可以放在用戶表中，也可以放在用戶組表中。（通常取決于更短小的分隔串） 方案一是標準的方案，但是要多增加一張表。方案二更簡單，但是有局限性，因為要保證短小的分隔串，比如一個用戶所屬的用戶組數量應該有限，不能太多太多。如果滿足這個條件，那么用這種方式也許是最方便的。但是檢索時不太靈活，比如根據用戶組檢索下面的用戶，只能使用like模糊查詢了，并且是實現還是有很大不方便。所以如果對檢索有要求的，最好是還是按照標準方案來。 **總結：** 方案一：標準，只需要多增加一張表。 方案二：對檢索要求不高，關聯數量邏輯上是有限的，并且不是很多，無需多增加一張表。 * * * * * 參考： **多對多時一定要使用關聯表嗎** 非得用關聯表時，才使用關聯表。 如果明確知道關聯數據不是很多，那就用一個大字段(text)，用`,`分割ID就可以了。 但是如果這種關聯關系，是具有擴展性的，業務關系決定了關聯數據可能是無限多的（比如員工和公司的關聯，不能在員工或者公司行中使用一個大字段存一個員工的所有公司，或者一個公司所有的員工，這是不現實的），并且數據關聯性查詢比較頻繁，那么就使用關聯表。 >[danger] 有復雜的查詢的，要用關聯表，不然很麻煩，in是 多個查單個，FIND_IN_SET是單個查 多個，但是遇到多個查多個就不好辦了（比如根據多個年級查課程，而課程關聯的也是多個年級），所以有復雜的查詢的一定要用關聯表。 * * * * * ### 其它 [ASCII 、UTF-8、Unicode都是個啥啊，為啥會亂碼啊？](https://www.toutiao.com/a6508698036997194253/) > 整形不考慮編碼 [58趕集的mysql軍規](http://mp.weixin.qq.com/s/tMKlpUOP0UX6cYmRMUlSBw) > 不用外鍵，因為由程序業務保證約束更合理。這樣整個程序邏輯更清晰，而不是把邏輯分散到數據庫中了。 [think-orm](https://github.com/top-think/think-orm) 數據庫操作還是都保持統一比較好。 [58到家MySQL軍規升級版](http://mp.weixin.qq.com/s/YfCORbcCX1hymXBCrZbAZg) [MySql的索引原理](https://mp.weixin.qq.com/s/ssyg8zd2pMgEhLYPj5YrcQ) [MySQL不為人知的主鍵與唯一索引約束](https://mp.weixin.qq.com/s/IE31GSDP0Ndjzc8kFj4Ukw) [mysql36條軍規.pdf_微盤下載](http://vdisk.weibo.com/s/muWOT) [mysql中utf8和utf8mb4區別 - 彼揚 - 博客園](https://www.cnblogs.com/beyang/p/7580814.html) [mysql中utf8不是真正的utf-8, utf8mb4才是真正的utf-8 - 不忘初心，方得始終 - CSDN博客](https://blog.csdn.net/cn_yefeng/article/details/80825335) [(譯)用MySQL的朋友們請不要使用"utf8"，請使用"utf8mb4" - 簡書](https://www.jianshu.com/p/ab9aa8d4df7d?openInApp=1) [譯 | 永遠不要在MySQL中使用utf8，改用utf8mb4 - 為程序員服務](http://ju.outofmemory.cn/entry/359647) * * * * * update time：2018-10-26 23:51:47