1.索引相關概述
索引就像書的目錄一樣，如果在字段上建立了索引，那麼以索引為查詢條件時可以加快查詢資料的速度，這是對MySQL的優化 複制
#索引的作用
限制和加速查找 複制
#常見的索引
主鍵索引，普通索引，唯一索引，聯合索引(多列) 複制
#有無索引的差別
無索引：需要從前往後一條一條的查詢
有索引：建立索引的時候，就會建立一個額外的檔案，查詢的時候，先去這個額外的檔案找，定好位置，再去原始表中直接查詢 複制
2.主鍵索引
查詢資料庫，按主鍵查詢速度是最快的，每個表隻能有一個主鍵列，可以有多個普通索引列，主鍵列要求的所有内容必須唯一 複制
#建立主鍵索引方法
#1.在建表時，增加建立主鍵索引
mysql> create table student (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> age tinyint(4) not null,
-> primary key(id),
-> key index_name(name));
Query OK, 0 rows affected (0.02 sec)
mysql> show index from student; #檢視索引
#提示：
1.primary key(id) #在id字段建立主鍵
2.key index_name(name) #在name字段建立普通索引 複制
#2.如果在建立表的時候沒有添加主鍵，使用alter添加
#alter添加文法：alter table 表名 add primary key(列名)
mysql> alter table test add primary key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec) 複制
#删除主鍵索引
#文法：alter table 表名 drop primary key;
alter table 表名 modify 列名 int,drop primary key;
mysql> alter table test drop primary key; #删除主鍵索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test; #檢視時已經沒有了
Empty set (0.00 sec)
mysql> alter table test add primary key(id); #再次添加
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test modify id int,drop primary key; #删除主鍵索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test; #檢視
Empty set (0.00 sec) 複制
3.普通索引
#作用：加速查找
#文法格式：create index 索引名字 on 表名(列名)
alter table 表名 add index 索引名字(列名)
#檢視幫助：help create index
mysql> help create index
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[index_type]
ON tbl_name (key_part,...)
[index_option]
[algorithm_option | lock_option] ...
key_part:
col_name [(length)] [ASC | DESC]
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
Normally, you create all indexes on a table at the time the table
itself is created with CREATE TABLE. See [HELP CREATE TABLE]. This
guideline is especially important for InnoDB tables, where the primary
key determines the physical layout of rows in the data file. CREATE
INDEX enables you to add indexes to existing tables.
CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.
See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY
KEY; use ALTER TABLE instead. For more information about indexes, see
https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html.
URL: https://dev.mysql.com/doc/refman/5.7/en/create-index.html 複制
#建立普通索引
#1.建立表的時候就建立普通索引
mysql> create table student (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> age tinyint(4) not null,
-> primary key(id),
-> key index_name(name));
Query OK, 0 rows affected (0.02 sec)
mysql> show index from student; #檢視索引 複制
#2.如果建表的時候沒有建立普通索引，可以使用以下方式建立
mysql> alter table student add index index_name(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index ix_age on student(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 複制
#删除普通索引
#文法：alter table 表名 drop index 索引名字；
drop index 索引名字 on 表名；
#例子：删除name和age字段索引
mysql> alter table student drop index index_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index ix_age on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 複制
4.唯一索引
#作用：加速查找和唯一限制
#文法：create unique index 索引名 on 表名(列名)
#建立唯一索引
#1.在建立表的時候就建立唯一索引
mysql> create table test (
-> id int(4) not null auto_increment,
-> name char(20) not null,
-> age varchar(4) not null,
-> primary key(id),
-> unique index ix_name(name)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show index from test; #檢視索引 複制
#2.如果建表時沒有建立，使用create進行建立
mysql> create unique index ix_age on test(age);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test; #檢視索引 複制
#删除唯一索引
#文法：drop index 索引名字 on 表名；
mysql> drop index ix_age on test;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 複制
5.聯合索引
#聯合索引是将n個列組合成一個索引
#應用場景：需要對多個列進行查詢的時候，如：where name='guo' and age='20';
#文法：create index 索引名 on 表名(列名1，列名2)
#例子：為name,dept一起建立索引
mysql> create index ix_name_dept on test(name,dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 複制
#指定前n個字元建立索引
#例如1.指定前8個字元建立索引
mysql> create index ix_dept on test(dept(8));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| test | 1 | ix_dept | 1 | dept | A | 0 | 8 | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 複制
#最左字首
#如果使用聯合索引，如将name和dept組合索引時，當查詢name字段時會使用索引，查詢name和dept時會使用索引，查詢dept時不會使用索引，因為當查左邊那個才會使用索引
create index ix_name_dept on test(name,dept);
select * from test where name='guo'; #使用索引
select * from test where name='guo' and dept = 'computer'; #使用索引
select * from test where dept = 'computer' #不使用索引 複制
6.索引注意事項
1.避免使用select *
2.count(1)或count(列) 代替count(*)
3.建立表時盡量使用char代替varchar
4.表的字段順序固定長度的字段優先
5.組合索引代替多個單列索引（經常使用多個條件查詢時）
6.盡量使用短索引 （create index ix_title on tb(title(16));特殊的資料類型 text類型）
#思考：能不能給所有的列建索引呢？
#答案是否定的，因為索引不但會占用系統空間，更新資料庫時還需要維護索引資料，是以并不是越多越好，例如數十到幾百行的小表上無需建立索引 複制
7.執行計劃explain
#使用explain判斷有沒有索引
#檢視幫助：help explain
mysql> help explain
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).
URL: https://dev.mysql.com/doc/refman/5.7/en/explain.html 複制
#檢視有沒有建立索引
mysql> explain select * from test where name='wu';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where name='wu'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec) 複制
#上面的name字段沒有建立索引，是以查詢的時候就會進行全表掃描，就會慢，如果有索引的時候就很快
#建立索引再次查找
mysql> create index ix_name on test(name); #建立索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test where name='wu'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ref
possible_keys: ix_name
key: ix_name
key_len: 20
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec) 複制
#當建立了索引之後，這次就掃描了一行
#參數詳解
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 userinfo;
特别的：如果有limit限制，則找到之後就不在繼續向下掃描
select * from userinfo where email = 'alex112@oldboy'
select * from userinfo where email = 'alex112@oldboy' limit 1;
雖然上述兩個語句都會進行全表掃描，第二句使用了limit，則找到一個後就不再繼續掃描。
INDEX ： 全索引掃描，對索引從頭到尾找一遍
select nid from userinfo;
RANGE： 對索引列進行範圍查找
select * from userinfo where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE： 合并索引，使用多個單列索引搜尋
select * from userinfo where name = 'alex' or nid in (11,22,33);
REF： 根據索引查找一個或多個值
select * from userinfo where name = 'alex112';
EQ_REF： 連接配接時使用primary key 或 unique類型
select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id;
CONST：常量
表最多有一個比對行,因為僅有一行,在這行的列值可被優化器剩餘部分認為是常數,const表很快,因為它們隻讀取一次。
select id from userinfo where id = 2 ;
SYSTEM：系統
表僅有一行(=系統表)。這是const聯接類型的一個特例。
select * from (select id from userinfo where id = 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列中索引的位圖，并且是備援的 複制