2 索引
2.1 安裝linux下的mysql
- 去官網下載下傳mysql
- 解壓并安裝
- 壓縮檔案通過xftp解壓并且使用rpm安裝依賴
[root@hadoop soft]#tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C /home/hadoop/app/
[root@hadoop soft]cd ..
[root@hadoop hadoop]#cd app
[root@hadoop app]# rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# yum remove mysql-libs
[root@hadoop app]# rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# yum install openssl-devel
[root@hadoop app]# rpm -ivh mysql-community-devel-8.0.26-1.el7.x86_64.rpm
- 安裝用戶端
[root@hadoop app]#rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm
- 安裝服務端
[root@hadoop app]#rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm
- 啟動mysql服務
[root@hadoop app]# systemctl start mysqld
- 去日志找下随機生成的mysql的root密碼
[root@hadoop app]# vim /var/log/mysqld.log
/password
- 登入mysql
[root@hadoop app]# mysql -u root -p
Enter password:
嫌麻煩練習的時候直接跳過密碼算了：(88條消息) Linux系統安裝MySQL報錯“ Access denied for user ‘root‘@‘localhost‘ (using password: YES)“_二木成林的部落格-CSDN部落格
- 把密碼等級調低并且更改密碼
mysql>set global validate_password_policy = LOW;
mysql>set global validate_password.length = 8;
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxxx';
- 建立供遠端通路的一個使用者
mysql>drop user 'root'@'%';
mysql>flush privileges;
mysql>create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxxxx';
表示在所有的主機上都能通路
- 給建立可以遠端通路的root使用者配置設定權限
mysql>grant all on *.* to 'root'@'%';
- 用Datagrip連接配接看看
- 如果連不上看下linux的防火牆有沒有開放3306的端口，或者直接關閉防火牆看看
2.2 索引概述
- 含義：是幫助MySQL高效擷取資料的資料結構(有序)。在資料結構上實作進階查找算法引用(指向)資料。
- 比較
- 無索引，全表掃描，直至結束。性能極低。
- 有索引
- 假如是二叉查找樹:如果select * from user from age = 45，那麼45會跟36比較，走右邊，然後再跟48比較，走左邊，然後就找到45了，隻需要比對3次。效率高。
春華秋實之MySQL進階-02 索引2 索引
- 假如是二叉查找樹:如果select * from user from age = 45，那麼45會跟36比較，走右邊，然後再跟48比較，走左邊，然後就找到45了，隻需要比對3次。效率高。
- 優缺點
- 優點：
- 高效的擷取資料，降低資料庫擷取資料的磁盤IO成本。
- 通過索引對資料進行排序，降低資料排序成本，降低CPU的消耗。
- 缺點：
- 索引也是要占空間滴
- 提高查詢效率，但是降低表的更新效率，因為也要維護索引
2.3 索引結構
- 含義：索引是在存儲引擎層實作的，不同的存儲引擎有不同的索引結構。
- 分類
- 二叉樹
- 左邊節點小于自身，右側節點大于自身
- 順序插入，形成連結清單，性能降低。針對大量資料，層次深，檢索慢。
- 解決辦法：左旋右旋形成紅黑樹(平衡二叉查找樹)
- 但是還是因為隻有兩個節點導緻的在大量資料會形成較深的層次的，是以檢索慢的問題
- B-Tree(多路平衡查找樹)
- 概念:以一顆最大度數(max-degree)為5(5階)的b-tree為例，度數就是節點的子節點個數，那就表示5階的b樹每個節點最多存儲4個key，5個指針
- 看圖了解下：
- 5階表示下面有有5個節點，圖中根節點就是5個指針灰色的那邊，第一個灰色的指針表示小于20這個key，第二個灰色的指針表示在20這個key和30這個key之間，依次共5個，然後每個指針對應下面每個節點。是以得出結論，如果有n個key就有n+1個指針。
- 來個案例
春華秋實之MySQL進階-02 索引2 索引
- B+tree
- 概念：以一顆最大度數(max-degree)為4(4階)的B+Tree為例
- 看圖了解
- 與BTree的差別是，所有的資料存放都會出現在葉子的節點，并且形成了單項連結清單，上面的非葉子節點起到的作用是索引的作用
- 也來個例子
春華秋實之MySQL進階-02 索引2 索引 春華秋實之MySQL進階-02 索引2 索引
- MySQL的b+tree索引
- 概念：在原來的B+Tree上進行了優化，增加了一個指向相鄰葉子節點的連結清單指針，就形成了帶有順序的B+Tree,提高區間通路的性能。簡單說就是把單項連結清單變成了雙向連結清單
- 看圖了解：
- 每個節點存儲在頁當中，回憶下前面的InnoDB的邏輯存儲結構，表空間、段、區、頁、行，一頁預設16K，底層就是這麼存儲的。
春華秋實之MySQL進階-02 索引2 索引
- 每個節點存儲在頁當中，回憶下前面的InnoDB的邏輯存儲結構，表空間、段、區、頁、行，一頁預設16K，底層就是這麼存儲的。
- Hash索引
- 概念：哈希索引就是采用一定的hash算法，将鍵值換算成新的hash值，映射到對應的槽位上，存儲在hash表中。
- 看圖了解
- 背景：id是主鍵，要為name創造一個哈希索引的資料結構
- 第一步：先計算出每一行資料的哈希值
- 第二步：name字段的所有值根據哈希函數去計算每一個name值應該放在哈希表的哪個槽位上。例如金庸算出來是槽位值005，對應的槽位就會存儲金庸這個key，以及金庸這一行對應哈希值58dda，也就是第一步對應的值，怎麼了解呢，就了解為58dda是一個引用，指向了金庸所處的那一行的位址
春華秋實之MySQL進階-02 索引2 索引 - 楊逍和金庸算出來的槽位一樣，就叫做哈希沖突，跟java中一樣用連結清單解決就可以
- 特點：
- 隻能用于等值
- 無法排序，因為運算結果無序
- 但是查詢效率高，通常一次檢索比對就可以了，效率通常高于B+Tree索引
- 支援哈希索引的引擎
- Memory引擎，但是InnoDB中具有的自适應hash功能，就是說有時候可以将B+Tree索引在指定條件下自動建構成哈希索引
- 問：為啥InnoDB存儲引擎選擇B+Tree索引結構？
- 相對于二叉樹，層級更少，搜尋效率高
- B-tree，所有節點都會存儲資料，導緻存key和指針的存儲的少
- 相對于Hash索引，支援範圍比對和排序操作
2.4 索引分類
- 分類
- 細節：PK不能為空，unique可以為空值，PK和unique在限制時候自動建立了索引
- 根據存儲形式的分類
春華秋實之MySQL進階-02 索引2 索引
- 規則
- 如果存在主鍵，主鍵索引就是聚集索引
- 沒有主鍵，将使用第一個唯一索引作為聚集索引
- 啥都沒有咋辦，會生成一個rowid所謂隐藏的聚集索引
- 來張圖了解下
- 聚集索引葉子挂着資料，二級索引下面挂着聚集索引字段
- 查name = 'Arm'的時候，走的是二級索引，字元是按照字典序序走的索引，找到id後，再到上面的聚集索引走一遍，定位10，再把整個資料傳回出來，專業資料叫做回表查詢
- 來道題
select * from user where id = 10;
select * from user where name = 'Arm';
備注：id為主鍵，name字段建立的時候有索引
問：哪個sql語句執行效率較高？
答案：
- 第一句隻要去聚集索引中走一遍，然後擷取的資料就好啦
- 第二句去二級索引中走一遍，然後再拿着id去聚集索引中再去擷取*的資料，即回表查詢，掃描了兩個字段的索引，效率就不高
- 再來道題
問題：InnoDB主鍵索引的B+Tree高度為多高？
前提：假設一行資料為1k，一個指針占用6個位元組，主鍵為bigint，占用8個位元組。
解答：(n表示key的數量)
- n*8+(n+1)6=161024 非葉的節點 n算出來1170個key，指針就有1171個
- 1171*16 = 18736 指針數乘最大的葉子下面最大的行數，高度為2
- 18736 *1171 = 21939856 高度為3 如下圖
2.5 索引的操作文法
- 建立索引
create [unique|fulltext] index index_name on table_name(index_col_name,...)
- unique表示唯一索引，fulltext表示全文索引
- 一個索引可以關聯多個字段的，一個字段叫單列索引，多個字段叫做聯合索引
- 檢視索引
show index from table_name;
- 删除索引
drop index index_name on table_name;
- 練習
- name字段為姓名字段，該字段的值可能會重複，為該字段建立索引
- 看下現在有什麼索引先
-
mysql> show index from tb_user\G; *************************** 1. row *************************** Table: tb_user Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 24 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 1 row in set (0.00 sec) - 建立索引
-
mysql> create index idx_user_name on tb_user(name); Query OK, 0 rows affected (0.03 sec)
- phone手機号字段的值，是非空的且唯一的，給他建立唯一索引
mysql> create unique index idx_user_phone on tb_user(phone);
Query OK, 0 rows affected (0.01 sec)
- 為profession、age、status建立聯合索引
- 聯合索引字段的講究：使用最頻繁的字段放在左側。根據使用頻繁程度從重到輕，要遵循''最左字首原則''
mysql> create index idx_user_pro_age_status on tb_user(profession,age,status);
Query OK, 0 rows affected (0.01 sec)
- 為email字段建立合适的索引來提升查詢效率
mysql> create index idx_user_email on tb_user(email);
Query OK, 0 rows affected (0.01 sec)
- 删除索引
mysql> drop index idx_user_email on tb_user;
Query OK, 0 rows affected (0.01 sec)
2. 6 SQL性能分析
- SQL的執行頻率
- 看下是以增删改查什麼為主進行操作
- 檢視伺服器狀态狀态
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 24 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 644 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
+---------------+-------+
11 rows in set (0.00 sec)
- 慢查詢日志
- 含義：記錄了所有執行時間超過指定參數的所有SQL語句的日志。
- 檢視開啟情況
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec)
- MySQL的慢查詢日志預設沒有開啟，需要在My'SQL的配置檔案(/etc/my.cnf)中配置如下資訊
[root@hadoop ~]# vi /etc/my.cnf
#開啟慢查詢日志
slow_query_log=1
#超過兩秒即為慢查詢
long_query_time=0.1秒
- 重新開機下伺服器
[root@hadoop ~]# systemctl restart mysqld
- 再次查詢慢日志情況發現就開了
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
- 去查詢一百萬條資料
mysql> select count(*) from tb_sku;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (13.35 sec)
- 去檢視對應的慢查詢日志
[root@hadoop ~]# cd /var/lib/mysql
[root@hadoop mysql]# tail -f hadoop-slow.log
3. profile詳情
- 含義：可以幫助我們了解時間都耗費到哪兒去了
- 檢視profile操作,yes表示支援
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
- profile預設關閉，可以通過set開啟
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 通過profile檢視下每一條SQL的耗時情況
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 0.00025125 | select @@profiling |
| 2 | 0.00080300 | select * from tb_user |
| 3 | 0.00080375 | select * from tb_user where id = 1 |
| 4 | 0.00077725 | select * from tb_user where name = '白起' |
| 5 | 0.00065025 | select count(*) from tb_sku |
| 6 | 0.00056925 | select count(*) from tb_user |
+----------+------------+---------------------------------------------+
- 檢視指定query_id 的SQL語句各個階段的耗時情況
mysql> show profile for query 6;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000127 |開啟時間
| Executing hook on transaction | 0.000009 |執行事務時間
| starting | 0.000017 |
| checking permissions | 0.000013 |檢查權限
| Opening tables | 0.000055 |打開表
| init | 0.000011 |進行初始化的操作
| System lock | 0.000015 |
| optimizing | 0.000030 |優化操作
| statistics | 0.000030 |統計操作
| preparing | 0.000034 |
| executing | 0.000137 |執行
| end | 0.000010 |
| query end | 0.000007 |
| waiting for handler commit | 0.000015 |執行送出的操作
| closing tables | 0.000014 |
| freeing items | 0.000023 |
| cleaning up | 0.000024 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)
- 檢視執行SQL語句CPU 的耗費情況
mysql> show profile cpu for query 6;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000127 | 0.000084 | 0.000032 |
| Executing hook on transaction | 0.000009 | 0.000006 | 0.000002 |
| starting | 0.000017 | 0.000012 | 0.000005 |
| checking permissions | 0.000013 | 0.000009 | 0.000004 |
| Opening tables | 0.000055 | 0.000040 | 0.000015 |
| init | 0.000011 | 0.000008 | 0.000003 |
| System lock | 0.000015 | 0.000011 | 0.000004 |
| optimizing | 0.000030 | 0.000021 | 0.000008 |
| statistics | 0.000030 | 0.000022 | 0.000009 |
| preparing | 0.000034 | 0.000024 | 0.000009 |
| executing | 0.000137 | 0.000100 | 0.000038 |
| end | 0.000010 | 0.000006 | 0.000003 |
| query end | 0.000007 | 0.000005 | 0.000002 |
| waiting for handler commit | 0.000015 | 0.000011 | 0.000004 |
| closing tables | 0.000014 | 0.000010 | 0.000004 |
| freeing items | 0.000023 | 0.000017 | 0.000006 |
| cleaning up | 0.000024 | 0.000017 | 0.000007 |
+--------------------------------+-----------------------------------
- explain執行計劃
- 含義：擷取MySQL如何執行SELECT語句的資訊，包括在SELECT語句執行過程表中如何連接配接和連接配接的順序
- 文法:在任何select 語句中加上關鍵字explain/desc
explain select 字段清單 from 表名 where 條件；
mysql> explain select * from tb_user where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+------
| 1 | SIMPLE | tb_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+------
type連接配接的類型，possi- ble_keys可能用到的索引，key實際用到的索引，key_len索引的長度，rows掃描記錄數
- 參數說明
- id相同，執行順序從上而下，id不同，值越大，越先執行 (如圖3先執行，依次是c，sc，子查詢，s)
-
春華秋實之MySQL進階-02 索引2 索引 - select_type,表示select的類型，常見取值simple，primary，union，subquery(子查詢)
- type，表示連接配接類型，性能由好到差的連接配接類型為null(一般是不引用表，例如select 'A'),sysytem,const(根據主鍵和唯一索引一般會出現),eq_ref,ref(使用非唯一索引會出現),range,index,all(全表掃描),實操中盡量往前優化
- possibel key,顯示可能應用在這張表上的索引，一個或者多個
- key，實際用到的索引
- key_len,表示索引中使用的位元組數
- rows，認為必須要執行的行數
- filtered,表示傳回結果的行數占需讀取行數的百分比，filtered的值越大越好
- extra，額外資訊
2.7 使用規則
- 驗證索引效率
- 沒用索引
- 用到索引
- 最左字首使用法則
- 含義：主要針對聯合索引的，指的是從索引的最左列開始，并且不跳過索引中的列。如果跳過了，索引将部分失效。
- 案例
- 看下表中索引，發現profession，age，status形成了聯合索引
-
mysql> show index from tb_user; +---------+------------+-------------------------+--------------+-------------+-----------+---------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +---------+------------+-------------------------+--------------+-------------+-----------+---------- | tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL | | tb_user | 0 | idx_user_phone | 1 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_name | 1 | name | A | 24 | NULL | NULL | | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL | +---------+------------+-------------------------+--------------+-------------+-----------+---------- - 查詢條件寫聯合索引涉及的3個字段(字段順序可以變)，并explain看下索引執行情況
-
mysql> explain select * from tb_user where profession = '軟體工程' and age = 31 and status = '0'; +----+-------------+---------+------------+------+-------------------------+------------------------- | id | select_type | table | partitions | type | possible_keys | key +----+-------------+---------+------------+------+-------------------------+------------------------- | 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status +----+-------------+---------+------------+------+-------------------------+------------------------- 1 row in set, 1 warning (0.00 sec) - 如果兩個字段呢，也是走的聯合索引，前提是最左字首，并且沒有跳過字段
-
mysql> explain select * from tb_user where profession = '軟體工程' and age = 31； +----+-------------+---------+------------+------+-------------------------+------------------------- | id | select_type | table | partitions | type | possible_keys | key +----+-------------+---------+------------+------+-------------------------+------------------------- | 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status +----+-------------+---------+------------+------+-------------------------+------------------------- 1 row in set, 1 warning (0.00 sec) - 跳過最左列profession來查，就不走索引了,索引全部失效，ref=null走的全表掃描
-
mysql> explain select * from tb_user where age = 31 and status = '0'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+---- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | +----+-------------+---------+------------+------+---------------+------+---------+------+------+---- | 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | +----+-------------+---------+------------+------+---------------+------+---------+------+------+---- 1 row in set, 1 warning (0.00 sec) - 如果缺失中間字段age的索引呢，也走的聯合索引，因為最左列在，但是隻走了profession的索引，status沒走，出現了索引的部分失效
-
mysql> explain select * from tb_user where profession = '軟體工程' and status = '0'; +----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 47 | const | 4 | 10.00 | Using index condition | +----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
- 範圍查詢
- 含義：聯合索引中，出現範圍查詢(>,<),範圍查詢右側的列索引失效
- 案例:例如status就失效了，因為在範圍查詢的右側
mysql> explain select * from tb_user where profession = '軟體工程' and age > 30 and status = '0';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 49
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
1 row in set, 1 warning (0.01 sec)
- 規避方法:業務允許的範圍下，盡量使用>=，就可以走聯合索引
mysql> explain select * from tb_user where profession = '軟體工程' and age >= 30 and status = '0';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 54 | NULL | 2 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
1 row in set, 1 warning (0.00 sec)
- 索引失效
- 索引列在where中進行了運算，索引失效
mysql> explain select * from tb_user where substring(phone,10,2) = '15';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
1 row in set, 1 warning (0.00 sec)
- 字元串不加引号，索引失效
mysql> explain select * from tb_user where phone = 17799990015;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 24 | 10.00
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
1 row in set, 3 warnings (0.00 sec)
0沒有引号，索引長度為49，status失效了
mysql> explain select * from tb_user where profession = '軟體工程' and age >= 30 and status = 0;
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 49 | NULL | 2 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
- 僅僅是尾部模糊比對，索引不會失效，頭部的模糊比對會失效
這個走
mysql> explain select * from tb_user where profession like '軟體%';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 47 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
1 row in set, 1 warning (0.00 sec)
這個不走
mysql> explain select * from tb_user where profession like '%工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 11.11 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
1 row in set, 1 warning (0.00 sec)
- or 前後字段都有索引才會走
age沒有索引
mysql> explain select * from tb_user where id = 10 or age = 23;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 24 | 13.75 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
1 row in set, 1 warning (0.00 sec)
- 資料分布影響
- 如果MySQL檢測使用索引比全表更慢，則不适用索引,包含null
mysql> explain select * from tb_user where phone >= '17799990011';
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 24 | 54.17 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
- SQL提示
- 含義：就是在SQL語句中g加入一些人為提示來達到優化操作的目的
- use index :選擇給profession新增加的索引進行檢索
mysql> explain select * from tb_user use index(idx_user_profession) where profession = '軟體工程';
+----+-------------+---------+------------+------+----------------+------+---------+------+------+--
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref
+----+-------------+---------+------------+------+---------------------+---------------------+-------
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_profession | idx_user_profession | 47
+----+-------------+---------+------------+------+---------------------+---------------------+-------
1 row in set, 1 warning (0.00 sec)
- ignore index:忽略這個索引
mysql> explain select * from tb_user ignore index(idx_user_profession) where profession = '軟體工程';
+----+-------------+---------+------------+------+-------------------------+-------------------------
| id | select_type | table | partitions | type | possible_keys | key
+----+-------------+---------+------------+------+-------------------------+-------------------------
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status
+----+-------------+---------+------------+------+-------------------------+-------------------------
- force index :強制
mysql> explain select * from tb_user force index(idx_user_profession) where profession = '軟體工程';
+----+-------------+---------+------------+------+---------------------+---------------------+---------+----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref
+----+-------------+---------+------------+------+---------------------+---------------------+---------+----
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_profession | idx_user_profession | 47 |
+----+-------------+---------+------------+------+---------------------+---------------------+---------+----
- 覆寫索引
- 含義：盡量使用覆寫索引，減少select *
- extra兩者差異
- using index condition :查找使用了索引，但是需要回表查詢資料
- using where；using index：查找使用了索引，不需要回表查詢
- 具體原因：第一條資料聯合索引資料屬于二級索引葉子節點挂的就是id，是以需要的id就不要，第二條資料有一個name字段，需要根據id再去到聚集索引中回表查詢出來
- 思考：一張表，有四個字段(id,username,password,status),由于資料量大，需要對SQL語句：select id,username,password from tb_user where usename = 'itcast';進行優化，該如何進行才是最優方案
- 答案：對username和password建立聯合索引，這樣查詢這條SQL的時候就不需要進行回表查詢了
- 字首索引
- 含義：當字段類型為字元串(varchar,test等)時，使用這樣的字段當作索引會讓索引變得很大，浪費磁盤io。此時隻将字元串的一部分字首建立索引
- 文法
create index idx_xxxx on table_name(column(n));
- 字首長度
- 根據索引的選擇性來決定，選擇性是指不重複的索引值(基數)和資料表的記錄總值的比值。
mysql> select count(distinct email)/count(*) from tb_user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
| 1.0000 |
+--------------------------------+
截取郵箱前9個就發現有重複的了
mysql> select count(distinct substring(email,1,9))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,9))/count(*) |
+-----------------------------------------------+
| 0.9583 |
+-----------------------------------------------+
- 案例
mysql> create index idx_email_5 on tb_user(email(5));
- 資料結構分析
前面5位去輔助索引查詢到對用id再去聚集索引那邊找資料，由于采用的是前5位，是以會去看下email是不是全部等于where後面的條件，下一步再去看輔助索引的連結清單下一個節點中email是不是where的條件，如果是就傳回，如果不是就不要
- 單列索引和聯合索引選取的規則
- 案例
mysql> explain select id,phone,name from tb_user where phone = '17799990010' and name = '韓信';
phone和name都有單列索引，但是隻走了phone的索引，那也會産生回表查詢的，因為phone的索引是沒有包含name的字段的值
建立一個phone,name的聯合索引
mysql> create unique index idx_user_phone_name on tb_user(phone,name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次查詢走的也不是走聯合的
mysql> explain select id,phone,name from tb_user where phone = '17799990010' and name = '韓信';
是以這邊訓示一下SQL提示，指定聯合索引檢索，最後的extra中using index不需要回表查詢
mysql> explain select id,phone,name from tb_user use index(idx_user_phone_name) where phone = '17799990010' and name = '韓信';
- 結論
- 如在業務場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯合索引。
- 設計原則