MySQL— 索引

来源:http://www.cnblogs.com/OldJack/archive/2017/06/12/6994850.html
-Advertisement-
Play Games

目錄 一、索引 二、索引類型 三、索引種類 四、操作索引 五、創建索引的時機 六、命中索引 七、其它註意事項 八、LIMIT分頁 九、執行計劃 十、慢查詢日誌 一、索引 MySQL索引的建立對於MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。 打個比方，如果合理的設計且使用索引 ...


目錄

一、索引

二、索引類型

三、索引種類

四、操作索引

五、創建索引的時機

六、命中索引

七、其它註意事項

八、LIMIT分頁

九、執行計劃

十、慢查詢日誌

 

 

一、索引

MySQL索引的建立對於MySQL的高效運行是很重要的，索引可以大大提高MySQL的檢索速度。

打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那麼沒有設計和使用索引的MySQL就是一個人力三輪車。

索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。

創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。

實際上，索引也是一張表，該表保存了主鍵與索引欄位，並指向實體表的記錄。

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。

建立索引會占用磁碟空間的索引文件。

二、索引類型

Mysql目前主要有以下幾種索引類型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT

即為全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 列上可以創建全文索引。

全文索引並不是和MyISAM一起誕生的，它的出現是為瞭解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題。

2. HASH

由於HASH的唯一（幾乎100%的唯一）及類似鍵值對的形式，很適合作為索引。

HASH索引可以一次定位，不需要像樹形索引那樣逐層查找,因此具有極高的效率。但是，這種高效是有條件的，即只在“=”和“in”條件下高效，對於範圍查詢、排序及組合索引仍然效率不高。

3. BTREE

BTREE索引就是一種將索引值按一定的演算法，存入一個樹形的數據結構中（二叉樹），每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf。這是MySQL里預設和最常用的索引類型。

4. RTREE

RTREE在MySQL很少使用，僅支持geometry數據類型，支持該類型的存儲引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對於BTREE，RTREE的優勢在於範圍查找。

ps. 此段詳細內容見此片博文：Mysql幾種索引類型的區別及適用情況

三、索引種類

  • 普通索引：僅加速查詢
  • 唯一索引：加速查詢 + 列值唯一（可以有null）
  • 主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中只有一個
  • 組合索引：多列值組成一個索引，專門用於組合搜索，其效率大於索引合併
  • 全文索引：對文本的內容進行分詞，進行搜索 

ps.

索引合併，使用多個單列索引組合搜索
 覆蓋索引，select的數據列只用從索引中就能夠取得，不必讀取數據行，換句話說查詢列要被所建的索引覆蓋

四、操作索引

1. 創建索引

--創建普通索引
CREATE INDEX index_name ON table_name(col_name);

--創建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

--創建普通組合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

--創建唯一組合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

2. 通過修改表結構創建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

3. 創建表時直接指定索引

CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name (col_name)
);

4. 刪除索引

--直接刪除索引
DROP INDEX index_name ON table_name;

--修改表結構刪除索引
ALTER TABLE table_name DROP INDEX index_name;

5. 其它相關命令

- 查看表結構
    desc table_name;
 
- 查看生成表的SQL
    show create table table_name;
 
- 查看索引
    show index from  table_name;
 
- 查看執行時間
    set profiling = 1;
    SQL...
    show profiles;

五、創建索引的時機

到這裡我們已經學會了建立索引，那麼我們需要在什麼情況下建立索引呢？一般來說，在WHERE和JOIN中出現的列需要建立索引，但也不完全如此，因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。例如：

SELECT t.Name  FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州' ;

此時就需要對city和age建立索引，由於mytable_m表的userame也出現在了JOIN子句中，也有對它建立索引的必要。

剛纔提到只有某些時候的LIKE才需建立索引。因為在以通配符%和_開頭作查詢時，MySQL不會使用索引。

六、命中索引

資料庫表中添加索引後確實會讓查詢速度起飛，但前提必須是正確的使用索引來查詢，如果以錯誤的方式使用，則即使建立索引也會不奏效。
即使建立索引，索引也不會生效：

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函數
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = '[email protected]';
    特別的：當or條件中有未建立索引的列才失效，以下會走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = '[email protected]' and name = 'alex'
- 類型不一致
    如果列是字元串類型，傳入條件是必須用引號引起來，不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特別的：如果是主鍵，則還是會走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特別的：如果是主鍵或索引是整數類型，則還是會走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    當根據索引排序時候，選擇的映射如果不是索引，則不走索引
    特別的：如果對主鍵排序，則還是走索引：
        select * from tb1 order by nid desc;
 
- 組合索引最左首碼
    如果組合索引為：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

七、其它註意事項

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 創建表時儘量時 char 代替 varchar
- 表的欄位順序固定長度的欄位優先
- 組合索引代替多個單列索引（經常使用多個條件查詢時）
- 儘量使用短索引
- 使用連接（JOIN）來代替子查詢(Sub-Queries)
- 連表時註意條件類型需一致
- 索引散列值（重覆多）不適合建索引，例：性別不適合

八、LIMIT分頁

若需求是每頁顯示10條數據，如何建立分頁？

我們可以先使用LIMIT嘗試：

--第一頁
SELECT * FROM table_name LIMIT 0,10;

--第二頁
SELECT * FROM table_name LIMIT 10,10;

--第三頁
SELECT * FROM table_name LIMIT 20,10;

但是這樣做有如下弊端：

  • 每一條select語句都會從1遍歷至當前位置，若跳轉到第100頁，則會遍歷1000條記錄
  • 若記錄的id不連續，則會出錯

改善：

若已知每頁的max_id和min_id，則可以通過主鍵索引來快速定位:

--下一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);

--上一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);

--當前頁之後的某一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (頁數差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);

--當前頁之前的某一頁
SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (頁數差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P ORDER BY P.id ASC);

九、執行計劃

explain + 查詢SQL - 用於顯示SQL執行信息參數，根據參考信息可以進行SQL優化

mysql> explain select * from tb2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
id查詢順序標識
            如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
            |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
            |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
            +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
        特別的：如果使用union連接其值可能為null


    select_type
        查詢類型
            SIMPLE          簡單查詢
            PRIMARY         最外層查詢
            SUBQUERY        映射為子查詢
            DERIVED         子查詢
            UNION           聯合
            UNION RESULT    使用聯合的結果
            ...
    table
        正在訪問的表名


    type
        查詢時的訪問方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
            ALL             全表掃描，對於數據表從頭到尾找一遍
                            select * from tb1;
                            特別的：如果有limit限制，則找到之後就不再繼續向下掃描
                                   select * from tb1 where email = '[email protected]'
                                   select * from tb1 where email = '[email protected]' limit 1;
                                   雖然上述兩個語句都會進行全表掃描，第二句使用了limit，則找到一個後就不再繼續掃描。

            INDEX           全索引掃描，對索引從頭到尾找一遍
                            select nid from tb1;

            RANGE          對索引列進行範圍查找
                            select *  from tb1 where name < 'alex';
                            PS:
                                between and
                                in
                                >   >=  <   <=  操作
                                註意：!= 和 > 符號


            INDEX_MERGE     合併索引，使用多個單列索引搜索
                            select *  from tb1 where name = 'alex' or nid in (11,22,33);

            REF             根據索引查找一個或多個值
                            select *  from tb1 where name = 'seven';

            EQ_REF          連接時使用primary key 或 unique類型
                            select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;



            CONST           常量
                            表最多有一個匹配行,因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數,const表很快,因為它們只讀取一次。
                            select nid from tb1 where nid = 2 ;

            SYSTEM          系統
                            表僅有一行(=系統表)。這是const聯接類型的一個特例。
                            select * from (select nid from tb1 where nid = 1) as A;
    possible_keys
        可能使用的索引

    key
        真實使用的

    key_len
        MySQL中使用索引位元組長度

    rows
        mysql估計為了找到所需的行而要讀取的行數 ------ 只是預估值

    extra
        該列包含MySQL解決查詢的詳細信息
        “Using index”
            此值表示mysql將使用覆蓋索引，以避免訪問表。不要把覆蓋索引和index訪問類型弄混了。
        “Using where”
            這意味著mysql伺服器將在存儲引擎檢索行後再進行過濾，許多where條件里涉及索引中的列，當（並且如果）它讀取索引時，就能被存儲引擎檢驗，因此不是所有帶where子句的查詢都會顯示“Using where”。有時“Using where”的出現就是一個暗示：查詢可受益於不同的索引。
        “Using temporary”
            這意味著mysql在對查詢結果排序時會使用一個臨時表。
        “Using filesort”
            這意味著mysql會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行。mysql有兩種文件排序演算法，這兩種排序方式都可以在記憶體或者磁碟上完成，explain不會告訴你mysql將使用哪一種文件排序，也不會告訴你排序會在記憶體里還是磁碟上完成。
        “Range checked for each record(index map: N)”
            這個意味著沒有好用的索引，新的索引將在聯接的每一行上重新估算，N是顯示在possible_keys列中索引的點陣圖，並且是冗餘的。
上表詳解

十、慢查詢日誌

MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是運行10S以上的語句。預設情況下，MySQLl資料庫並不啟動慢查詢日誌，需要我們手動來設置這個參數，當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日誌會或多或少帶來一定的性能影響。慢查詢日誌支持將日誌記錄寫入文件，也支持將日誌記錄寫入資料庫表。 

1. 查看慢日誌參數：

--查詢配置命令
show variables like '%query%';

--當前配置參數
binlog_rows_query_log_events    OFF
ft_query_expansion_limit    20
have_query_cache    YES

--時間限制，超過此時間，則記錄
long_query_time    10.000000

query_alloc_block_size    8192
query_cache_limit    1048576
query_cache_min_res_unit    4096
query_cache_size    1048576
query_cache_type    OFF
query_cache_wlock_invalidate    OFF
query_prealloc_size    8192

--是否開啟慢日誌記錄
slow_query_log    OFF

--日誌文件
slow_query_log_file    D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log
--

2. 修改當前配置

set global 變數名 = 值;

--例如，修改時間限製為20s
long_query_time = 20;

ps.也可以直接打開慢日誌配置文件進行修改，但必須重啟服務才能生效

3. 查看MySQL慢日誌

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

 

"""
--verbose    版本
--debug      調試
--help       幫助
 
-v           版本
-d           調試模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反轉順序，預設文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       顯示前N條just show the top n queries
-a           不要將SQL中數字轉換成N，字元串轉換成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正則匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql機器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           總時間中不減去鎖定時間；don't subtract lock time from total time
"""

 

參考資料：

1. http://www.cnblogs.com/wupeiqi/articles/5716963.html

2. http://www.w3cschool.cn/mysql/mysql-index.html

3. http://www.cnblogs.com/yuan-shuai/p/3225417.html

4. http://www.jb51.net/article/49346.htm


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

-Advertisement-
Play Games
更多相關文章
  • Android 中 DrawerLayout + ViewPager 怎麼解決滑動衝突？
    DrawerLayout 是 Android 官方的側滑菜單控制項，而 ViewPager 相信大家都很熟悉了。今天這裡就講一下當在 DrawerLayout 中嵌套 ViewPager 時，要如何解決滑動衝突的問題，效果如下： 首先，讓我們先來解決 DrawerLayout 和 ViewPager ...
  • iOS----------developerDiskImage
    真機測試時提示Could not find Developer Disk Image.這該怎麼辦？？？？ 這是由於真機系統過高或者過低，Xcode中沒有匹配的配置包文件，我們可以通過這個路徑進入配置包的存放目錄： /Applications/Xcode.app/Contents/Developer/ ...
  • iOS 力學動畫生成器UIKit Dynamics 之碰撞效果講解
    UIKit Dynamic是iOS7 新增的一組類和方法，可賦予UIView逼真的行為和特征，不需要寫動畫效果那些繁瑣的代碼，讓開發人員能夠輕鬆地改善應用的用戶體驗。一共有6個可用於定製UIDynamicAnimator的類，這裡先只簡單介紹下碰撞的動畫效果，即UICollisionBehavior ...
  • 訂製EditText游標
    訂製EditText游標 設置背景android:background="@null" 設置游標樣式：android:textCursorDrawable="@drawable/edit_cursor_line" 去掉或設置游標下的圓點樣式：android:textSelectHandle="@dr ...
  • 【代碼筆記】iOS-removeFromSuper
    代碼： RootViewController.m ...
  • Android學習筆記- Fragment實例 底部導航欄的實現
    1.要實現的效果圖以及工程目錄結構： 先看看效果圖吧： 接著看看我們的工程的目錄結構： 2.實現流程： Step 1：寫下底部選項的一些資源文件 我們從圖上可以看到，我們底部的每一項點擊的時候都有不同的效果是吧！ 我們是通過是否selected來判定的！我們要寫的資源文件有：首先是圖片，然後是文字， ...
  • SQL SERVER 自動生成 MySQL 表結構及索引 的建表SQL
    SQL SERVER的表結構及索引轉換為MySQL的表結構及索引，其實在很多第三方工具中有提供，比如navicat、sqlyog等，但是，在處理某些數據類型、預設值及索引轉換的時候，總有些不盡人意並且需要安裝軟體，懶人開始想法子，所以基於SQL SERVER，寫了一個存儲過程，可以根據表名直接轉換為 ...
  • 微軟SQL Server認證最新信息（17年5月22日更新），感興趣的進來看看喲
    之前一直有在關註微軟認證的一些消息，由於最新的SQL Server認證加入了2016的相關內容，導致課程資料需要大部分更新，但是微軟更新相對比較慢，並且經常改版，目前發現的最新的MCP Cert Path為2017年5月22日版。所以需要不定時翻閱相關站點查看最新情況，這裡把目前最新的情況述說一下， ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類