DBA之路 6_MySQL_索引（下）及執(zhí)行計劃

1.1索引

MUL   輔助索引
PRI   主鍵（聚集索引）
UNI   唯一鍵
1.2索引的操作管理
建立 索引
mysql> alter table t100w add index idx_k2(k2);
Query OK, 0 rows affected (6.11 sec)
查詢索引
mysql> show index from t100w;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t100w |          1 | idx_k2   |            1 | k2          | A         |        3826 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> desc t100w;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | int(11)   | YES  |     | NULL              |                             |
| num   | int(11)   | YES  |     | NULL              |                             |
| k1    | char(2)   | YES  |     | NULL              |                             |
| k2    | char(4)   | YES  | MUL | NULL              |                             |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
創(chuàng)建唯一索引
mysql>alter table t100w add unique index index_k1(k1);

前綴索引只能設置給字符串類型的列
mysql> alter table city add index idx_name(name(3));
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table city drop index idx_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
；

創(chuàng)建聯(lián)合索引
mysql> alter table city add index idx_n_p(countrycode,population);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from city
    -> ;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city  |          0 | PRIMARY     |            1 | ID          | A         |        4188 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | CountryCode |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_n_p     |            1 | CountryCode | A         |         232 |     NULL | NULL   |      | BTREE      |         |               |
| city  |          1 | idx_n_p     |            2 | Population  | A         |        4052 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

2.執(zhí)行計劃

2.1作用

上線新的查詢語句之前，進行提前預警性能較差的語句（提前預估語句性能）
在出現(xiàn)性能問題時可以找到合理的解決思路

2.2執(zhí)行計劃獲取

desc和explain都可以獲取執(zhí)行計劃（部分），結(jié)果一樣
mysql> desc select * from city where name='ShangHai';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_name      | idx_name | 35      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

重要的列：
table：        顯示從哪個表查詢，單表無所謂，多表會有作用
type:          索引的應用級別（應用類型）
possible_key   可能會使用到的索引
key            實際用到的索引
key_len        聯(lián)合索引的覆蓋程度（針對聯(lián)合索引，可以判斷出用的索引）
rows           覆蓋的行數(shù)（查詢的行數(shù)，越少越好）
Extra          額外的信息

2.3關鍵信息介紹

type:ref
級別
1>ALL：全表掃描，遍歷，不走索引
出現(xiàn)ALL的情況：（1）沒建立索引
              （2）建了索引不走
2>Index：全索引掃描
3>range：索引范圍掃描
4>ref:輔助索引等值查詢
5>eq_ref:多表連接查詢時，on的條件列是唯一索引或主鍵
6>const,system:主鍵或唯一鍵的等值查詢

級別：從上往下，性能依次變好
ALL：全表掃描，遍歷，不走索引
出現(xiàn)ALL的情況：（1）沒建立索引
              （2）建了索引不走
六種不走索引的情況：
mysql> desc select * from t100w where k2 != 'aaa';            輔助索引出現(xiàn)!=才會出現(xiàn)all
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | idx_k2        | NULL | NULL    | NULL | 888567 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from t100w where k2 like '%xy%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 888567 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select k1 from t100w;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 888567 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from t100w;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | t100w | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 888567 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

總結(jié):（1） select * from 不會走索引
     （2）select 非索引列 from 不會走索引
     （3）!=不等于在輔助索引時是all在聚集索引是range
     （4）%a   模糊匹配在前時是ALL，在后面的時候會顯示range
Index：全索引掃描
mysql> desc select k2 from t100w;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t100w | NULL       | index | NULL          | idx_k2 | 17      | NULL | 888567 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

取索引列的所有數(shù)據(jù)
range：索引范圍掃描
輔助索引:<  >  <=  >=   like 這類可以接受
in   or   這類盡量避免
主鍵：!=   出現(xiàn)在主鍵列則會走range

mysql> desc select * from world.city where countrycode like 'C%'
mysql> desc select * from world.city where id!=3000;
mysql> desc select * from world.city where id>3000;

mysql> desc select * from world.city where countrycode in ('CHN','USA');

改寫為：
desc
select * from world.city where countrycode='CHN'
union all 
select * from world.city where countrycode='USA';
ref:輔助索引等值查詢
mysql> desc select * from city where countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
eq_ref:多表連接查詢時，on的條件列是唯一索引或主鍵
mysql> desc select a.name,b.name ,b.surfacearea 
    -> from city as a 
    -> join country as b 
    -> on a.countrycode=b.code 
    -> where a.population <100;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | CountryCode   | NULL    | NULL    | NULL                | 4188 |    33.33 | Using where |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 3       | world.a.CountryCode |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
const,system:主鍵或唯一鍵的等值查詢
mysql> desc select * from city where id='10';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Extra:Null 額外的信息

+---------------------------------------+
| Extra                                 |
+---------------------------------------+
| Using index condition; Using filesort |
+---------------------------------------+
Using filesort解決方法：
mysql> desc select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table city add index idx_pop_cou(countrycode,population);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc select * from city where countrycode='CHN' order by population;
+----+-------------+-------+------------+------+-------------------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys           | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+-------------------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | city  | NULL       | ref  | CountryCode,idx_pop_cou | idx_pop_cou | 3       | const |  363 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+-------------------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


設置聯(lián)合索引

3.Explain（desc）使用場景（面試題）

1.MySQL出現(xiàn)性能問題，總結(jié)有兩種情況

（1）應急性的慢，突然夯住
應急處理：數(shù)據(jù)庫夯?。?，資源耗盡）
處理過程：
1.show proesslist;獲取到導致數(shù)據(jù)庫夯住的語句
2.explain分析SQL語句的執(zhí)行計劃，有沒有走索引，索引的類型情況
3.建索引，改語句
（2）一段時間慢（持續(xù)性）
1.記錄慢日志，slowlog，分析slowlog
2.explain，分析SQL的執(zhí)行計劃，有沒有走索引，索引的類型情況
3.建索引，改語句

4.索引應用規(guī)范

業(yè)務
1.產(chǎn)品的功能
2.用戶的行為
“熱”查詢語句--->較慢--->slowlog
“熱數(shù)據(jù)”

4.1建立索引的原則（DBA運維規(guī)范）

4.1.0說明
為了使索引的使用效率更高，在創(chuàng)建索引時，必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引。那么索引設計原則又是怎樣的?
4.1.1(必須的) 建表時一定要有主鍵,一般是個無關列

略.回顧一下,聚集索引結(jié)構(gòu).

4.1.2選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。
4.1.3
當有where group by order by 時，要建立聯(lián)合索引
4.10*********關于聯(lián)合索引************
(1）where A, group by B. order by c ---------->(A,,B,C)
(2)只有where A B C
   1>都是等值的情況下（5.5版本以后無關索引順序，都可以走索引），
但是，我們需要把唯一值最多的一列放到最左邊
   2>如果有不等值的查詢，例如以下情況
    select where A= and B> and C=
    索引順序是ACB/CAB，語句改寫為ACB/CAB,AC的位置取決于里面的唯一值的多少
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容