MySQL數據庫干貨_20——MySQL中的索引【附有詳細代碼】

MySQL中的索引

索引介紹

索引是對數據庫表中的一列或多列值進行排序的一種結構，使用索引可以快速訪問數據庫表中的特定信息。索引是一種特殊的文件，它們包含著對數據表里所有記錄的位置信息。更通俗的說，數據庫索引好比是一本書前面的目錄，能加快數據庫的查詢速度。MySQL 索引的建立對于MySQL 的高效運行是很重要的，索引可以大大提高 MySQL 的檢索速度。

索引的作用

索引相當于圖書上的目錄，可以根據目錄上的頁碼快速找到所需的內容，提高性能（查詢速度）。

索引優點：

通過創建唯一性索引，可以保證數據庫表中的每一行數據的唯一性；
可以加快數據的檢索速度；
可以加速表與表之間的連接；
在使用分組和排序進行檢索的時候，可以減少查詢中分組和排序的時間；

索引缺點

創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加；
索引需要占用物理空間，數據量越大，占用空間越大；
會降低表的增刪改的效率，因為每次增刪改索引都需要進行動態維護；

什么時候需要創建索引

頻繁作為查詢條件的字段應該創建索引；
查詢中排序的字段創建索引將大大提高排序的速度（索引就是排序加快速查找）；
查詢中統計或者分組的字段；

什么時候不需要創建索引

頻繁更新的字段不適合創建索引，因為每次更新不單單是更新記錄，還會更新索引，保存索引文件；
where條件里用不到的字段，不創建索引；
表記錄太少，不需要創建索引；
經常增刪改的表；
數據重復且分布平均的字段，因此為經常查詢的和經常排序的字段建立索引。注意某些數據包含大量重復數據，因此他建立索引就沒有太大的效果，例如性別字段，只有男女，不適合建立索引；

MySQL中的索引類型

普通索引：

最基本的索引，它沒有任何限制。
唯一索引：

索引列的值必須唯一，但允許有空值，如果是組合索引，則列值的組合必須唯一。
主鍵索引：

特殊的索引，唯一的標識一條記錄，不能為空，一般用primary key來約束。
聯合索引：

在多個字段上建立索引，能夠加速查詢到速度。

普通索引

是最基本的索引，它沒有任何限制。在創建索引時，可以指定索引長度。length 為可選參數，表示索引的長度，只有字符串類型的字段才能指定索引長度，如果是 BLOB 和 TEXT 類型，必須指定 length。

創建索引時需要注意：

如果指定單列索引長度，length 必須小于這個字段所允許的最大字符個數。

查詢索引

SHOW INDEX FROM tableName;

直接創建索引

CREATE INDEX indexName ON table(columnName(length));

示例：

為 emp3 表中的 name 創建一個索引，索引名為 emp3_name_index；

create index emp3_name_index ON emp3(name);

修改表添加索引

ALTER TABLE tablename ADD INDEX index_name (columnName(length));

示例：

修改 emp3 表，為 addrees 列添加索引，索引名為 emp3_address_index；

alter table emp3 add index emp3_address_index(address);

創建表時指定索引列

CREATE TABLE table (
COLUMNName TYPE ,
PRIMARY KEY (id),
INDEX index_name (column(length))
);

示例：

創建 emp4 表，包含 emp_id,name,address 列，同時為 name 列創建索引，索引名為 emp4_name_index。

create table  emp4(emp_id int primary key auto_increment,name varchar(30),address varchar(50),index emp4_name_index(name));

刪除索引

DROP INDEX indexname ON tablename;

示例：

刪除 mep3 表中索引名為 emp3_address_index 的索引。

drop index emp3_address_index on emp3;

唯一索引

唯一索引與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。

創建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length));

示例：

為 emp 表中的 name 創建一個唯一索引，索引名為 emp_name_index。

create unique index emp_name_index on emp(name);

修改表添加唯一索引

ALTER TABLE table_name ADD UNIQUE indexName (column(length));

示例：

修改 emp 表，為 salary 列添加唯一索引，索引名為 emp_salary_index。

alter table emp add unique emp_salary_index(salary);

創建表時指定唯一索引

CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY (`id`),
UNIQUE index_name (column(length))
);

示例：

創建 emp5 表，包含 emp_id,name,address 列，同時為 name 列創建唯一索引。索引名為 emp5_name_index。

create table emp5(emp_id int primary key ,name varchar(30),address varchar(30),unique emp5_name_index(name));

主鍵索引

主鍵索引是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時創建主鍵索引。

修改表添加主鍵索引

ALTER TABLE  表名 ADD PRIMARY KEY(列名);

示例：

修改 emp 表為 employee_id 添加主鍵索引。

alter table emp add primary key(employee_id);

創建表時指定主鍵索引

CREATE TABLE `table` (
COLUMN TYPE ,
PRIMARY KEY(column)
);

示例：

創建 emp6 表，包含 emp_id,name,address 列，同時為 emp_id 列創建主鍵索引。

create table emp6(employee_id int primary key auto_increment,name varchar(20),address varchar(50));

組合索引

組合索引是指使用多個字段創建的索引，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用(最左前綴原則)。

最左前綴原則

就是最左優先。

如：我們使用表中的 name ，address ，salary 創建組合索引，那么想要組合索引生效，我們只能使用如下組合：

name/address/salary

name/address

name/

如果使用 addrees/salary 或者是 salary 則索引不會生效。

添加組合索引

ALTER TABLE tablename ADD INDEX index_name (column(length),column(length));

示例：

修改 emp6 表，為 name ，address 列創建組合索引。

alter table emp6 add index emp6_index_n_a(name,address);

創建表時創建組合索引

CREATE TABLE `table` (
COLUMN TYPE ,
INDEX index_name (column(length),column(length))
);

示例：

創建 emp7 表，包含 emp_id,name,address 列，同時為 name,address 列創建組合索引。

create table emp7(emp_id int primary key auto_increment ,name varchar(20),address varchar(30),index emp7_index_n_a(name,address));

posted @ 2023-11-03 16:25 Gjq- 閱讀(57) 評論(0) 收藏舉報來源

刷新頁面返回頂部