mysql index是什麼意思

在mysql中，index是指“索引”，是一種特殊的資料庫結構，由資料表中的一列或多列組合而成，可以用來快速查詢資料表中有某一特定值的記錄。透過索引，查詢資料時不用讀完記錄的所有信息，而只是查詢索引列；否則，資料庫系統將讀取每筆記錄的所有資訊進行比對。因此使用索引可以很大程度上提高資料庫的查詢速度，也有效的提高了資料庫系統的效能。

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

在mysql中，index是指“索引”，是一種特殊的資料庫結構，由資料表中的一列或多列組合而成，可以用來快速查詢資料表中有某一特定值的記錄。

透過索引，查詢資料時不用讀完記錄的所有信息，而只是查詢索引列。否則，資料庫系統將讀取每筆記錄的所有資訊進行比對。

可以把索引比喻為新華字典的音序表。例如，要查「庫」字，如果不使用音序，就需要從字典的 400 頁中逐頁來找。但是，如果提取拼音出來，構成音序表，就只需要從 10 多頁的音序表中直接找到。這樣就可以大大節省時間。

因此，使用索引可以大幅提高資料庫的查詢速度，也有效的提高了資料庫系統的效能。

為什麼要使用索引

索引就是根據表中的一列或若干列依照一定順序建立的列值與記錄行之間的對應關係表，實質上是一張描述索引列的列值與原表中記錄行之間一一對應關係的有序表。

索引是 MySQL 中十分重要的資料庫對象，是資料庫效能調優技術的基礎，常用於實現資料的快速檢索。

在MySQL 中，通常有以下兩種方式存取資料庫表的行資料：

1) 順序存取

#順序存取是在資料表中實施全表掃描，從頭到尾逐行遍歷，直到在無序的行資料中找到符合條件的目標資料。

順序存取實作比較簡單，但是當表中有大量資料的時候，效率非常低。例如，在幾千萬條數據中尋找少量的數據時，使用順序存取方式將會遍歷所有的數據，花費大量的時間，顯然會影響資料庫的處理效能。

2) 索引存取

索引存取是透過遍歷索引來直接存取表中記錄行的方式。

使用這種方式的前提是對錶建立一個索引，在列上建立了索引之後，查找資料時可以直接根據該列上的索引找到對應記錄行的位置，從而快速地查找到數據。索引儲存了指定列資料值的指針，根據指定的排序順序對這些指針進行排序。

例如，在學生基本資料表 tb_students 中，如果基於 student_id 建立了索引，系統就建立了一張索引列到實際記錄的對應表。當使用者需要尋找 student_id 為 12022 的資料的時候，系統先在 student_id 索引上找到該記錄，然後透過映射表直接找到資料行，並且傳回該行資料。因為掃描索引的速度一般遠大於掃描實際資料行的速度，所以採用索引的方式可以大幅提高資料庫的工作效率。

簡而言之，不使用索引，MySQL 就必須從第一筆記錄開始讀取完整個表，直到找出相關的行。表越大，查詢資料所花費的時間就越多。如果表中查詢的欄位有索引，MySQL 就能快速到達一個位置去搜尋資料文件，而不必查看所有數據，這樣將會節省很大一部分時間。

索引的優缺點

索引有其明顯的優勢，也有其不可避免的缺點。

優點

索引的優點如下：

• 透過建立唯一索引可以保證資料庫表中每一行資料的唯一性。

• 可以為所有的 MySQL 欄位類型設定索引。

• 可以大幅加快資料的查詢速度，這是使用索引最主要的原因。

• 在實現資料的參考完整性方面可以加速表與表之間的連接。

• 在使用分組和排序子句進行資料查詢時也可以顯著減少查詢中分組和排序的時間

缺點

增加索引也有許多不利的方面，主要如下：

• #建立和維護索引組要耗費時間，並且隨著資料量的增加所耗費的時間也會增加。

• 索引需要佔磁碟空間，除了資料表佔資料空間以外，每個索引還要佔一定的實體空間。如果有大量的索引，索引檔案可能比資料檔案更快達到最大檔案尺寸。

• 當資料表中的資料增加、刪除和修改的時候，索引也要動態維護，這樣就降低了資料的維護速度。

使用索引時，需要綜合考慮索引的優點和缺點。

索引可以提高查詢速度，但會影響插入記錄的速度。因為，在有索引的表中插入記錄時，資料庫系統會依照索引進行排序，這樣就降低了插入記錄的速度，插入大量記錄時的速度影響會更加明顯。在這種情況下，最好的方法是先刪除表中的索引，然後插入數據，插入完成後，再建立索引。

索引設計原則

1）要注意，索引也是要佔用磁碟空間的，所以並不是越多越好。

2）過多的索引會影響INSERT，DELETE，UPDATE等語句的執行效率。

3）資料過少不建議建立索引。

4）對於唯一性約束，應使用對應的唯一性索引。

5）盡量不對區分度低的欄位建立索引。例如：枚舉類型的性別欄位只有男女，起不到最佳化效果。

6）建議在建立表格的時候建立索引。若表中有大量記錄，那麼將列建為索引後，表中所有記錄都會修改。

7）不經常查詢的字段，不建議建立索引。

索引類型

普通索引基本索引方式，無特別說明#唯一性索引資料唯一，允許有null主鍵索引表中只有一個主鍵，資料唯一，不允許有null複合索引可以包含兩個或多個欄位的索引方式

類型

根據不同的環境選擇對應的索引類型即可。

建立索引語法

以普通索引為例，在建立表格的時候建立索引

create table 表名(
 字段定义…
 index 索引名称(字段));

以普通索引為例，將資料表修改為索引

alter table 表名 add index 索引名称(字段);

可以使用key取代index，且索引名稱可以省略，不加索引名預設以欄位名稱作為索引名稱。

普通索引

沒啥要注意的，最基礎的索引方式。

建立一個表，並且建立普通索引，索引列為id

mysql> create table test1(id int,name varchar(5),age tinyint,index index_id(id));

查看表結構，在key下的MUL就代表著普通索引

mysql> desc test1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  | MUL | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

查看索引信息

mysql> show index from test1\G
*************************** 1. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: index_id		
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

在表中加入一些數據，用於測試

mysql> insert into test1 values(1,'张三',21),(2,'李四',22),(3,'王五',23),(4,'赵六',24);

透過

explain

執行sql查詢語句，先不通過索引，查詢姓名為趙六的記錄。

mysql> explain select * from test1 where name='赵六'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type:ALL項目表示全表掃描，rows: 4表示行數。因為趙六是表中最後一筆記錄，所以查詢遍歷了整張表。

接下來透過索引列id對姓名為趙六的欄位進行查詢。

mysql> explain select * from test1 where id=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
   partitions: NULL
         type: ref
possible_keys: index_id
          key: index_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

rows: 1表示只檢索了一行便將記錄查找出來了，高下立判。

唯一索引

在唯一索引中，索引列中資料唯一，不能出現重複的值，用來約束內容，允許有null值。

語法：

create table 表名（
 字段定义…
 unique key 索引名(字段名)）;

唯一索引常用在值不能重複的字段，身分證號，手機號碼等等。

建立一個表，並且建立唯一性索引，索引列為手機號。

mysql> create table test2(id int,name varchar(5),phone int,unique key unique_key_phone(phone));

查看表結構，key下為UNI的表示為唯一性索引

mysql> desc test2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| phone | int(11)    | YES  | UNI | NULL    |       |
+-------+------------+------+-----+---------+-------+

插入數據，並測試特性

mysql> insert into test2 values(1,'张三',1111111111);
mysql> insert into test2 values(2,'李四',null);	#可以为null
mysql> insert into test2 values(3,'王五',1111111111);	#值必须唯一
ERROR 1062 (23000): Duplicate entry '1111111111' for key 'unique_key_phone'

查看表數據

mysql> select * from test2;
+------+--------+------------+
| id   | name   | phone      |
+------+--------+------------+
|    2 | 李四   |       NULL |
|    1 | 张三   | 1111111111 |
+------+--------+------------+

主鍵索引

主鍵索引也就是設定主鍵，每個表最多只能有一個主鍵。主鍵列值必須唯一，且不允許有空值。

語法：

create table 表名（欄位primary key）;

或

create table 表名（

欄位定義…

primary key 索引名稱(字段)）；

建立一個表，並且設定為主鍵索引，索引列為id

mysql> create table test3(id int primary key,name varchar(5),age tinyint);

查看表結構，key下為PRI的為主鍵索引列

mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

插入數據，並測試特性

mysql> insert into test3 values(1,'张三',23);
mysql> insert into test3 values(null,'张三',23);	#不能为null
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into test3 values(1,'张三',23);	#值不能重复
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

查看表數據

mysql> select * from test3;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   23 |
+----+--------+------+

複合索引

######### ###複合索引可以包含兩個或多個欄位。 ######沒有特定語法，可以為表建立雙索引######建立一個表，並設定為複合主鍵，索引列為id，name###

mysql> create table test4 (id int,name varchar(5),age tinyint,primary key(id,name));

###複合主鍵特性和主鍵有些不同，只需要有一個欄位不重複即可。 ###

mysql> insert into test4 values(1,'张三',21);
mysql> insert into test4 values(1,'李四',21);
mysql> insert into test4 values(1,'张三',21);	#主键列全重复
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'

###查看表格資料###

mysql> select * from test4;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 张三   |   21 |
|  1 | 李四   |   21 |
+----+--------+------+

###【相關推薦：###mysql影片教學###】####

以上是mysql index是什麼意思的詳細內容。更多資訊請關注PHP中文網其他相關文章！