MySQL索引管理

索引分类

• 聚集索引
• 非聚集索引
  • 单列索引
    • 一个索引只包含一个列
  • 多列索引（复合索引）
    • 一个索引包含多个列
  • 唯一索引
    • 索引列的值必须唯一，允许有一个空值。

---

MyIsAm和InnoDB检索过程

innodb | myisan

• 上图有两个索引：id主键索引，name辅助索引
• InnoDB检索过程：
  • 查询id=14数据，只需要左边innodb中主键索引检索过程。
  • 查询name='Ellison'数据
    • 先在辅助索引中找name='Ellison'获取id为14
    • 再到主键索引中检索id为14的记录
• MyisAm 则不需要第二次回表查询

---

索引管理

• 创建索引
  • create [unique] index 索引名称 on 表名(列名[(length)]);
  • alter 表名 add [unique] index 索引名称 on (列名[length]);
    • 如果字段是char、varchar类型，length可以小于字段实际长度
    • 如果是blog、text等长文本类型，必须指定length
    • [unique]：中括号代表可以省略，如果加上了unique，表示创建唯一索引
    • 如果table后面只写一个字段，就是单列索引，如果写多个字段，就是复合索
      引，多个字段之间用逗号隔开
• 删除索引
  • drop index 索引名称 on 表名;
• 查看索引
  • show index from 表名;
• 索引修改
  • 可以先删除索引，再重建索引

-- 准备200万数据
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();

-- 创建索引
create index idx1 on test1 (id);
-- 创建唯一索引
create unique index idx2 on test1(name);
-- 创建索引并指定长度
create index idx3 on test1 (email(15));
-- 查看表中索引
show index from test1;
-- 删除索引
drop index idx1 on test1;

---

参考来源：【公众号】大侠学JAVA