Mysql系列第二十二講 mysql索引管理詳解

qwer1030274531發表於2020-10-13
MySql索引

索引分類

分為聚集索引和非聚集索引。

聚集索引

每個表有且一定會有一個聚集索引，整個表的資料儲存在聚集索引中，mysql索引是採用B+樹結構儲存在檔案中，葉子節點儲存主鍵的值以及對應記錄的資料，非葉子節點不儲存記錄的資料，只儲存主鍵的值。當表中未指定主鍵時，mysql內部會自動給每條記錄新增一個隱藏的rowid欄位（預設4個位元組）作為主鍵，用rowid構建聚集索引。 http://ask.baikezh.com/hunan/

聚集索引在mysql中又叫主鍵索引。

非聚集索引（輔助索引）

也是b+樹結構，不過有一點和聚集索引不同，非聚集索引葉子節點儲存欄位（索引欄位）的值以及對應記錄主鍵的值，其他節點只儲存欄位的值（索引欄位）。

每個表可以有多個非聚集索引。

mysql中非聚集索引分為

單列索引

即一個索引只包含一個列。

多列索引（又稱複合索引）

即一個索引包含多個列。

唯一索引

索引列的值必須唯一，允許有一個空值。

資料檢索的過程
看一張圖：
在這裡插入圖片描述
上面的表中有2個索引：id作為主鍵索引，name作為輔助索引。

innodb我們用的最多，我們只看圖中左邊的innodb中資料檢索過程：

如果需要查詢id=14的資料，只需要在左邊的主鍵索引中檢索就可以了。

如果需要搜尋name='Ellison’的資料，需要2步：

  1. 先在輔助索引中檢索到name='Ellison’的資料，獲取id為14

  2. 再到主鍵索引中檢索id為14的記錄

輔助索引相對於主鍵索引多了第二步。

索引管理

建立索引

方式1： http://dxb.myzx.cn/jilin/

create [unique] index 索引名稱 on 表名(列名[(length)]);1

方式2：

alter 表名 add [unique] index 索引名稱 on (列名[(length)]);1

如果欄位是char、varchar型別，length可以小於欄位實際長度，如果是blog、text等長文字型別，必須指定length。

[unique]：中括號代表可以省略，如果加上了unique，表示建立唯一索引。

如果table後面只寫一個欄位，就是單列索引，如果寫多個欄位，就是複合索引，多個欄位之間用逗號隔開。

刪除索引 http://ask.baikezh.com/question/

drop index 索引名稱 on 表名;1

檢視索引

show index from 表名;1

索引修改

可以先刪除索引，再重建索引。

示例 http://zzdxb.baikezh.com/

準備200萬資料

/*建庫javacode2018*/DROP DATABASE IF EXISTS javacode2018;CREATE DATABASE javacode2018;USE javacode2018;/*建表test1*/DROP TABLE IF EXISTS test1;CREATE TABLE test1 (
  id     INT NOT NULL COMMENT '編號',
  name   VARCHAR(20) NOT NULL COMMENT '姓名',
  sex TINYINT NOT NULL COMMENT '性別,1：男，2：女',
  email  VARCHAR(50));/*準備資料*/DROP PROCEDURE IF EXISTS proc1;DELIMITER $CREATE PROCEDURE proc1()
  BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i <= 2000000 DO
      INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));
      SET i = i + 1;
      if i%10000=0 THEN
        COMMIT;
        START TRANSACTION;
      END IF;
    END WHILE;
    COMMIT;
  END $DELIMITER ;CALL proc1();SELECT count(*) FROM test1;1234567891011121314151617181920212223242526272829303132333435

上圖中使用儲存過程迴圈插入了200萬記錄，表中有4個欄位，除了sex列，其他列的值都是沒有重複的，表中還未建索引。 http://ask.baikezh.com/

插入的200萬資料中，id，name，email的值都是沒有重複的。

無索引我們體驗一下查詢速度 http://dxb.myzx.cn/petitmal/

mysql> select * from test1 a where a.id = 1;+----+-----------+-----+-------------------+| id | name      | sex | email             |+----+-----------+-----+-------------------+|  1 | javacode1 |   1 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (0.77 sec)1234567

上面我們按id查詢了一條記錄耗時770毫秒，我們在id上面建立個索引感受一下速度。

mysql> create index idx1 on test1 (id);Query OK, 0 rows affected (2.82 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from test1 a where a.id = 1;+----+-----------+-----+-------------------+| id | name      | sex | email             |+----+-----------+-----+-------------------+|  1 | javacode1 |   1 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (0.00 sec)1234567891011

上面的查詢是不是非常快，耗時1毫秒都不到。

我們在name上也建立個索引，感受一下查詢的神速，如下： http://zzdxb.baikezh.com/shangqiu/

mysql> create unique index idx2 on test1(name);Query OK, 0 rows affected (9.67 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from test1 where name = 'javacode1';+----+-----------+-----+-------------------+| id | name      | sex | email             |+----+-----------+-----+-------------------+|  1 | javacode1 |   1 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (0.00 sec)1234567891011

查詢快如閃電，有沒有，索引是如此的神奇。

建立索引並指定長度

通過email檢索一下資料 http://dxb.myzx.cn/lanzhou/

mysql> select * from test1 a where a.email = 'javacode1000085@163.com';+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 1000085 | javacode1000085 |   1 | javacode1000085@163.com |+---------+-----------------+-----+-------------------------+1 row in set (1.28 sec)1234567

耗時1秒多，回頭去看一下插入資料的sql，我們可以看到所有的email記錄，每條記錄的前面15個字元是不一樣的，結尾是一樣的（都是@163.com），通過前面15個字元就可以定位一個email了，那麼我們可以對email建立索引的時候指定一個長度為15，這樣相對於整個email欄位更短一些，查詢效果是一樣的，這樣一個頁中可以儲存更多的索引記錄，命令如下： http://dxb.myzx.cn

mysql> create index idx3 on test1 (email(15));Query OK, 0 rows affected (7.67 sec)Records: 0  Duplicates: 0  Warnings: 0123

然後看一下查詢效果：

mysql> select * from test1 a where a.email = 'javacode1000085@163.com';+---------+-----------------+-----+-------------------------+| id      | name            | sex | email                   |+---------+-----------------+-----+-------------------------+| 1000085 | javacode1000085 |   1 | javacode1000085@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)1234567

耗時不到1毫秒，神速。

檢視錶中的索引 http://dxb.myzx.cn/guiyang/

我們看一下test1表中的所有索引，如下：

mysql> show index from test1;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test1 |          0 | idx2     |            1 | name        | A         |     1992727 |     NULL | NULL   |      | BTREE      |         |               || test1 |          1 | idx1     |            1 | id          | A         |     1992727 |     NULL | NULL   |      | BTREE      |         |               || test1 |          1 | idx3     |            1 | email       | A         |     1992727 |       15 | NULL   | YES  | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)123456789

可以看到test1表中3個索引的詳細資訊(索引名稱、型別，欄位)。

刪除索引 http://dxb.myzx.cn/epilepsy/

我們刪除idx1，然後再列出test1表所有索引，如下：

mysql> drop index idx1 on test1;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from test1;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test1 |          0 | idx2     |            1 | name        | A         |     1992727 |     NULL | NULL   |      | BTREE      |         |               || test1 |          1 | idx3     |            1 | email       | A         |     1992727 |       15 | NULL   | YES  | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30239065/viewspace-2726498/，如需轉載，請註明出處，否則將追究法律責任。

相關文章