【轉】Mysql索引設計原則

来源:https://www.cnblogs.com/xiwang6428/archive/2017/12/27/8124460.html
-Advertisement-
Play Games

來源：https://segmentfault.com/a/1190000000473085 假設一高頻查詢如下SELECT * FROM user WHERE area='amoy' AND sex=0 ORDER BY last_login DESC limit 30;如何建立索引？描述考慮的過 ...


 

 來源：https://segmentfault.com/a/1190000000473085

假設一高頻查詢如下
SELECT * FROM user WHERE area='amoy' AND sex=0 ORDER BY last_login DESC limit 30;
如何建立索引？描述考慮的過程

user表如下：
初始化100W條數據，其中，area要通過IP查詢生成，sex為 0,1 隨機

CREATE TABLE user (
id int(10) NOT NULL AUTO_INCREMENT COMMENT '自增編號',
username varchar(30) NOT NULL DEFAULT '0' COMMENT '用戶名',
password varchar(30) NOT NULL DEFAULT '0' COMMENT '密碼',
area varchar(30) NOT NULL COMMENT '地址',
sex int(10) NOT NULL COMMENT '性別1，男；2，女。',
last_login int(10) NOT NULL COMMENT '最近一次登錄時間戳',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=892013 DEFAULT CHARSET=latin1

最終我的索引
(last_login,area)

索引原則：

1.where和order by等的欄位建立索引

2.使用唯一索引：對於last_login,area等欄位重覆的次數比較少，可以使用索引；而sex無非就兩個值：性別1，男；2，不值得索引

3.多列索引：不要為每一個列單獨建立索引，這樣並不能將mysql索引的效率最大化。使用“索引合併策略”

4.選擇合理的索引列順序：索引列的順序意味著索引首先按照最左列進行排序，然後是第二列，以此類推。如(last_login,area)會先按照 last_login 進行排序，然後才是area。

5.將選擇性最高的索引放到前面，也就是會所按照這個條件搜索到的數據最少，選擇性就越高，比如選擇性：last_login> area> sex。

6.索引不是越多越好，適合的索引可以提高查詢效率，但是會降低寫入效率，根據項目保持兩者的平衡性最好了。

總結上面，首先sex不適合建立索引，有沒有索引對於效率的提升意義不大，其次索引會按照最左列進行排序，因此將last_login放到最前面。

 

測試過程：

user表
沒有任何索引的查詢相關日誌：
SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.56s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.55s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.59s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.55s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.55s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.58s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.57s
共花費時間：5.66s

建立索引area:
ALTER TABLE user ADD INDEX index_area (area) ;
SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.06s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.10s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.11s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.20s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.07s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
共花費時間：0.66s
可見，建立area以後對性能的影響是巨大的(5.66/0.66 約為8.5758倍)
刪除索引：ALTER TABLE user DROP INDEX index_area;
刪除area索引發現時間又變成了0.57s

建立last_login索引：
SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.09s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.51s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.07s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.06s
共花費時間：0.87s
同樣能夠提升性能（5.66/0.87 約為6.5057倍）

建立sex索引：
ALTER TABLE user ADD INDEX index_sex (sex) ;
SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.89s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.88s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.86s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.88s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.87s
共花費時間：8.73s
同樣能夠提升性能（5.66s/8.73 約為0.6483倍）效率反而降低了？？求解？
建立這個sex索引還不如不建。

刪除索引：
ALTER TABLE user DROP INDEX index_sex;
發現時間又變成了0.57s左右，

建立兩個單獨的索引：
ALTER TABLE user
ADD INDEX index_area (area) ,
ADD INDEX index_last_login (last_login) ;

SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.09s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.33s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.21s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.28s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.67s

發現建立兩個單獨的索引還不如只建立一個索引
刪除索引：
發現時間又變成了0.57s左右，

建立一個的聯合索引：
ALTER TABLE user
ADD INDEX index_last_login_area (last_login,area) ,
SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.00s
額，第二條數據這是怎麼了，我測試了5次都在這附近晃悠哈！
這尼瑪，找對索引啦！就該這麼建立，查詢不出來需要的時間啦！估計就是我們需要的索引啦！！！！

刪除索引：
發現時間又變成了0.57s左右，

建立一個的聯合索引：
ALTER TABLE user
ADD INDEX index_sex_last_login_area (sex,last_login,area)
SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.18s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.17s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.81s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.01s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
sex怎麼總是你在拖後腿啊！把你調整到索引的最後一個吧！
刪除索引：
發現時間又變成了0.57s左右，

建立一個的聯合索引：
ALTER TABLE user
ADD INDEX index_last_login_area_sex (area,last_login,sex)
SELECT * FROM user WHERE area='美國ATT用戶' AND sex=0 ORDER BY last_login DESC limit 30; 0.03s
SELECT * FROM user WHERE area='泰國' AND sex=0 ORDER BY last_login DESC limit 30; 0.07s
SELECT * FROM user WHERE area='臺灣省臺灣大寬頻' AND sex=0 ORDER BY last_login DESC limit 30; 0.50s
SELECT * FROM user WHERE area='美國弗吉尼亞州' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='德國賓士汽車' AND sex=0 ORDER BY last_login DESC limit 30; 0.05s
SELECT * FROM user WHERE area='臺灣省中華電信' AND sex=0 ORDER BY last_login DESC limit 30; 0.06s
SELECT * FROM user WHERE area='南韓' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='拉美地區' AND sex=0 ORDER BY last_login DESC limit 30; 0.02s
SELECT * FROM user WHERE area='美國紐約(Prudential)' AND sex=0 ORDER BY last_login DESC limit 30; 0.04s
SELECT * FROM user WHERE area='印度尼西亞' AND sex=0 ORDER BY last_login DESC limit 30; 0.06s

綜上所述：1.建立索引不一定能夠加快查詢效率如sex這種給重覆次數特別多的列增加索引如sex這種會降低查詢效率，具體的原因有待查找
2.給重覆次數比較少的列增加u謳吟還是能夠大幅度提高效率
3.給where和orderby之後的欄位添加索引才會加快查詢效率
4.為每一個列單獨建立索引，不能將索引的效率最大化，應該使用索引合併策略，即根據查詢條件，建立聯合索引
5.聯合索引的順序問題：將選擇性高的索引放到前面
6.根據資料建立索引意味著索引按照最左列進行排序，然後事第二列，以此類推。如（last_login ,area）就會按照last_login進行排序，然後才是area
7.根據這次的這個查詢條件來說最好的索引是：ALTER TABLE userADD INDEX index_last_login_area (last_login,area)。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 學生信息管理系統資料庫設計
    表名tb_school（學校表：存儲學校的基本信息） 欄位 類型 介紹 屬性 其他 schoolid Int 學校id 主鍵 SchoolName Varchar(20) 學校名稱 非空 SchoolPrincipal Varchar(20) 學校校長 非空 SchoolAdd Varchar(20 ...
  • Tableau連接mongoDB資料庫
    Tableau支持大量的文件類型數據的連接和資料庫連接 但是Tableau不能直接連接MongoDB資料庫，需要藉助MongoDB Connector for BI 官方解釋 下載安裝完成之後，會在安裝目錄的bin目錄下出現兩個可執行文件mongodrdl.exe和mongosqld.exe 官方文 ...
  • mongoDB使用記錄
    一些mongodb學習的相關知識，記錄下來以便下次查看使用 參考：https://docs.mongodb.com/manual/reference/operator/ http://www.runoob.com/mongodb/mongodb-tutorial.html https://code. ...
  • linux系統mysql5.6安裝
    1.下載mysql版本 下載最新版本:https://www.mysql.com/downloads/ 下載歷史版本:https://downloads.mysql.com/archives/community/ 2.上傳下載的mysql軟體到/usr/local/src 3.解壓mysql軟體到/ ...
  • 配置SQL Server on Linux（1）
    1. 前言 SQL Server一般是在安裝過程中進行相關的配置，安裝完成之後，再去修改有一些配置就比較麻煩，比如更改SQL Server實例級別的排序規則。但在Linux下，安裝過程並沒有很多可以配置的步驟，安裝步驟變得很簡單，更多的配置可以通過/opt/mssql/bin/mssql-conf工 ...
  • [20171227]表的FULL_HASH_VALUE值的計算.txt
    [20171227]表的FULL_HASH_VALUE值的計算.txt--//sql_id的計算是使用MD5演算法進行哈希，生成一個128位的Hash Value，其中低32位作為HASH VALUE顯示，SQL_ID則取了後64位。--//實際上sql_id使用32進位表示，hash_value使用 ...
  • Python連接presto
    presto支持jdbc驅動連接，從外表來看，和一般的關係型資料庫連接相似。 目前發現有兩個包支持直接使用，分別是： pyhive : https://github.com/dropbox/PyHive presto python client : https://github.com/presto ...
  • mysql的“The user specified as a definer (”@’%') does not exist”問題 解決
    2017-12-27，MYSQL的存儲調用時出現了“The user specified as a definer (”test@’%') does not exist”的問題。 網上查過後，都是提示用戶許可權不夠，如：http://blog.handone.com/index.php/archive ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類