八、mysql 基礎優化-索引管理
1、課程大綱
索引介紹
索引管理
2、執行計劃獲取及分析
mysql資料庫中索引的型別介紹
BTREE:B+樹索引 （主要）
HASH:HASH索引
FULLTEXT:全文索引
RTREE:R樹索引
------
索引管理：
索引建立的在表的列上（欄位）的，
在where后面的列建立索引才會加快查詢速度，
索引分類：
- 主鍵索引
- 普通索引****
- 唯一索引
添加索引
alter table test add index index_name(name);
create index index_name on test(name);
查詢表是否有索引資訊：
DESC stu；看他的key列值
mysql> explain select * from stu;
mysql> explain select * from stu where stu_name='zhangsan';
查看到的type不同，
----------------------------------------------------------
索引及執行計劃
索引基本管理：
創建和洗掉：
alter table stu add index idx_name(stu_name);
alter table stu drop index idx_name;
或者
create index inx_name on stu(stu_name);
drop index inx_name on stu;
查詢索引設定
desc stu;
主鍵索引： 唯一、非空
走主鍵索引的查詢效率是最高的，我們盡量每個表有一個主鍵，并且將來查詢的時候計量以主鍵為條件查詢
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE `test1` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
增加自增主鍵
alter table test1 change id id int(4) primary key not null auto_increment;
前綴索引：
create index index_name on stu(stu_id(8));
聯合索引：
where a女生 and b身高165 and c身材好
index(a,b,c)
特點：前綴生效特性，
a,ab,abc，ac 可以走索引，
b bc c 不走索引，
原則：把最常用來作為條件查詢的列放在前面，
走索引：
select * from people where a='nv' and b>=165 and tizhong<=120;
select * from people where a='nv' and b>=165;
select * from people where a='nv';
select * from people where a='nv' and tizhong<=120;
alter table stu add index minx(gender,age);
唯一性索引：
create unique index index_name on test(name);
3、explain 調取陳述句的執行計劃
主要是判斷陳述句是否走索引
explain select stu_name,gender,age from stu where gender='F' and age <20;
mysql> explain select name,gender,age from test where gender='F' and age <20;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| 1 | SIMPLE | test | range | inx_test | inx_test | 7 | NULL | 1 | Using index condition |
type : 表示MySQL在表中找到所需行的方式，又稱“訪問型別”，
常見型別如下:
ALL,index, range, ref, eq_ref, const, system, NULL
從左到右，性能從最差到最好
ALL：
Full Table Scan， MySQL將遍歷全表以找到匹配的行
如果顯示ALL，說明：
查詢沒有走索引：
1、陳述句本身的問題
2、索引的問題，沒建立索引
index：Full Index Scan，index與ALL區別為index型別只遍歷索引樹
例子：
explain select count(*) from stu ;
range:索引范圍掃描，對索引的掃描開始于某一點，回傳匹配值域的行，
顯而易見的索引范圍掃描是帶有between或者where子句里帶有<,>查詢，
where 條件中有范圍查詢或模糊查詢時
> < >= <= between and in () or
like 'xx%'
當mysql使用索引去查找一系列值時，例如IN()和OR串列，也會顯示range（范圍掃描）,當然性能上面是有差異的，
ref：使用非唯一索引掃描或者唯一索引的前綴掃描，回傳匹配某個單獨值的記錄行
where stu_name='xiaoming'
explain select * from stu where stu_name='aa';
eq_ref：類似ref，區別就在使用的索引是唯一索引，對于每個索引鍵值，表中只有一條記錄匹配，簡單來說，
就是多表連接中使用primary key或者 unique key作為關聯條件
join條件使用的是primary key或者 unique key
const、system：當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些型別訪問，
如將主鍵置于where串列中，MySQL就能將該查詢轉換為一個常量
explain select * from city where id=1;
NULL：MySQL在優化程序中分解陳述句，執行時甚至不用訪問表或索引，
例如從一個索引列里選取最小值可以通過單獨索引查找完成，
mysql> explain select name,population from city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
Extra:
Using temporary
Using filesort
Using join buffer
排序 order by ,group by ,distinct,排序條件上沒有索引
explain select * from city where countrycode='CHN' order by population;
在join 的條件列上沒有建立索引
4、資料庫索引的設計原則：
一、資料庫索引的設計原則：
為了使索引的使用效率更高，在創建索引時，必須考慮在哪些欄位上創建索引和創建什么型別的索引，
那么索引設計原則又是怎樣的?(盡量使用主鍵索引和唯一性索引，)
1．選擇唯一性索引
唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄，
例如，學生表中學號是具有唯一性的欄位，為該欄位建立唯一性索引可以很快的確定某個學生的資訊，
如果使用姓名的話，可能存在同名現象，從而降低查詢速度，
主鍵索引和唯一鍵索引，在查詢中使用是效率最高的，
2．為經常需要排序、分組和聯合操作的欄位建立索引
經常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的欄位，排序操作會浪費很多時間，
如果為其建立索引，可以有效地避免排序操作，
3．為常作為查詢條件的欄位建立索引
如果某個欄位經常用來做查詢條件，那么該欄位的查詢速度會影響整個表的查詢速度，因此，
為這樣的欄位建立索引，可以提高整個表的查詢速度，
select count(DISTINCT population ) from city;
select count(*) from city;
4．盡量使用前綴來索引
如果索引欄位的值很長，最好使用值的前綴來索引，例如，TEXT和BLOG型別的欄位，進行全文檢索
會很浪費時間，如果只檢索欄位的前面的若干個字符，這樣可以提高檢索速度，
------------------------以上的是重點關注的，以下是能保證則保證的--------------------
5．限制索引的數目
索引的數目不是越多越好，每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大，
修改表時，對索引的重構和更新很麻煩，越多的索引，會使更新表變得很浪費時間，
6．盡量使用資料量少的索引
如果索引的值很長，那么查詢的速度會受到影響，例如，對一個CHAR（100）型別的欄位進行全文
檢索需要的時間肯定要比對CHAR（10）型別的欄位需要的時間要多，
7．洗掉不再使用或者很少使用的索引
表中的資料被大量更新，或者資料的使用方式被改變后，原有的一些索引可能不再需要，資料庫管理
員應當定期找出這些索引，將它們洗掉，從而減少索引對更新操作的影響，
5、索引的開發規范
不走索引的情況：
重點關注：
1) 沒有查詢條件，或者查詢條件沒有建立索引
select * from tab; 全表掃描，
select * from tab where 1=1;
在業務資料庫中，特別是資料量比較大的表，
是沒有全表掃描這種需求，
1、對用戶查看是非常痛苦的，
2、對服務器來講毀滅性的，
（1）select * from tab;
SQL改寫成以下陳述句：
selec * from tab order by price limit 10 需要在price列上建立索引
（2）
select * from tab where name='zhangsan' name列沒有索引
改：
1、換成有索引的列作為查詢條件
2、將name列建立索引
2) 查詢結果集是原表中的大部分資料，應該是30％以上，
查詢的結果集，超過了總數行數30%，優化器覺得就沒有必要走索引了，
假如：tab表 id，name id:1-100w ，id列有索引
select * from tab where id>500000;
如果業務允許，可以使用limit控制，
怎么改寫 ？
結合業務判斷，有沒有更好的方式，如果沒有更好的改寫方案
盡量不要在mysql存放這個資料了，放到redis里面，
3) 索引本身失效，統計資料不真實
索引有自我維護的能力，
對于表內容變化比較頻繁的情況下，有可能會出現索引失效，
4) 查詢條件使用函式在索引列上，或者對索引列進行運算，運算包括(+，-，*，/，! 等)
例子：
錯誤的例子：select * from test where id-1=9;
正確的例子：select * from test where id=10;
5)隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤.
由于表的欄位tu_mdn定義為varchar2(20),但在查詢時把該欄位作為number型別以where條件傳給資料庫,
這樣會導致索引失效. 錯誤的例子：select * from test where tu_mdn=13333333333;
正確的例子：select * from test where tu_mdn='13333333333';
------------------------
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| telnum | varchar(20) | YES | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>
------------------------
6)
<> ，not in 不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum <> '110';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119');
------------
mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id | name | telnum |
+------+------+---------+
| 1 | a | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';
-----
單獨的>,<,in 有可能走，也有可能不走，和結果集有關，盡量結合業務添加limit
or或in 盡量改成union
EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改寫成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'
-----------------------------------
7) like "%_" 百分號在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引掃描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引
%linux%類的搜索需求，可以使用elasticsearch
%linux培訓%
8) 單獨參考復合索引里非第一位置的索引列.
列子：
復合索引：
DROP TABLE t1
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
走索引的情況測驗：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 ;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m'; ----->部分走索引
不走索引的：
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44459.html
標籤：MySQL