目錄
- 什么是索引
- 為什么要使用索引
- 索引的底層原理
- 索引的種類
- 索引的創建
- 主鍵索引
- 唯一索引
- 普通索引
- 索引的優缺點
- 不會命中索引的情況
- 組合索引最左前綴
- explain查詢性能分析
- 慢查詢日志
什么是索引
索引的本質是一個特殊的檔案，是存盤引擎快速找到記錄的一種資料結構，
類比：查字典的程序，通過拼音索引
索引的本質：通過不斷地縮小想要獲取資料地范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是說，有了這種索引機制，我們可以總是用同一種查找方式來鎖定資料，
為什么要使用索引
使用索引就是為了提高查詢效率
索引的底層原理
使用B+樹
索引的種類
-
主鍵索引：加速查找、不為空、不能重復
-
唯一索引：加速查詢、不為空的欄位不能重復
-
聯和唯一索引：unique（name, password）
-
普通索引：加速查找 index(name)
-
聯合索引：index(name, password)
索引的創建
主鍵索引
新增主鍵索引
--創建的時候增加主鍵索引
create table t1(
id int auto_increment primary key,
name varchar(32) not null default ''
)charset utf8;
--表創建完了之后再增加主鍵索引
alter table t2 add primary key(id);
洗掉主鍵索引
--洗掉主鍵索引
alter table t2 drop primary key;
唯一索引
新增唯一索引
--創建表的時候增加唯一索引
mysql> create table t2(
-> id int auto_increment primary key,
-> name varchar(32) not null default '',
-> unique(name) # 如果不設定唯一鍵的名字，默認唯一鍵的名字是作用的欄位名
-> )charset utf8;
--創建表后新增唯一索引鍵
# create unique index 索引名 on 表名(欄位名);
create unique index un_name on t2(name);
--或者
alter table t2 add unique key(name);
--或者
alter table t2 add unique index un_name(name);
洗掉唯一索引鍵
alter table t2 drop index name; # index后面是唯一鍵的名字
普通索引
新增
--創建表的時候新增普通索引
create table t3(
id int auto_increment primary key,
name varchar(32) not null default '',
index u_name(name) # index后面跟的是索引的名字，可以不寫
)charset utf8;
--創建表后創建普通索引
create index 索引名 on 表名(欄位名);
create index ix_name on t3(name);
--或者
alter table t3 add index ix_name (name);
洗掉
alter table t3 drop index u_name;
索引的優缺點
通過觀察*.ibd檔案可知：
- 索引加快了查詢速度
- 但是加了索引之后，會占用大量的磁盤空間
索引不是加的越多越好
不會命中索引的情況
- 不能在SQL陳述句中，進行四則運算，會降低sql的查詢效率
- 使用函式 如：
select * from t1 where reverse(name)='abc1213'; - 型別不一致
- 如果列是字串型別，傳入條件必須要用引號引起來，如
select * from t1 where email=1241;
- 如果列是字串型別，傳入條件必須要用引號引起來，如
- order by 當排序條件為索引，則select欄位必須也是索引欄位，否則無法命中
select email from t1 order by email desc;- 如果對主鍵排序，速度還是很快的
- count(1)或count(列)代替count(*)在mysql中沒有差別
- 組合索引最左前綴
組合索引最左前綴
什么時候會創建聯合索引？
根據公司的業務場景，在最常用的幾列上添加索引
select * from user where name='abc123' and email='[email protected]'
遇到上述業務情況，錯誤的做法：
index ix_name(name),
index ix_email(email)
正確的做法：
index ix_name_email(name, email)
如果組合索引為：ix_name_email (name, email)
那么：
where name='abc' and email='xxx' --索引命中
where name='abc' --命中索引
where email='[email protected]' --未命中索引
explain查詢性能分析
mysql> explain select * from t1 where id=2141\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
各個引數的含義：
-
id：這是select的查詢序列號
-
select_type：是select的型別
-
table：顯示這一行的資料是關于哪張表的
- type：這列最重要，顯示了連接使用了哪種類別,有無使用索引，是使用Explain命令分析性能瓶頸的關鍵項之一
結果值從好到壞依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般來說，得保證查詢至少達到range級別，最好能達到ref，否則就可能會出現性能問題， -
possible_keys：列出MySQL能使用哪個索引在該表中找到行
-
key：顯示MySQL實際使用的索引
-
key_len：索引長度
-
ref：顯示使用哪個列或常數與key一起從表中選擇行，
-
rows：掃描的長度
-
Extra：使用到了索引
索引覆寫：
select id from t1 where id=2132123;
當欄位id 添加了主鍵索引，這時候是命中索引的；但查詢了欄位id，id本身有主鍵索引，這時候會產生索引覆寫，會比主鍵索引快，
慢查詢日志
查看慢SQL的相關變數
mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | D:\mysql-5.7.28\data\DESKTOP-PAT2F9O-slow.log |
+---------------------------+-----------------------------------------------+
查看慢sql查詢時間配置
mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+-----------+
設定慢SQL日志配置
--開啟慢SQL查詢
set global slow_query_log = on;
--設定慢SQL查詢日志的路徑
set global slow_query_log_file = "D:/mysql-5.7.28/data/mysql_slow";
--設定慢SQL查詢時間
set global long_query_time = 2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/113100.html
標籤：MySQL