一、索引

1. 创建

1.1 创建表时

# 普通索引
CREATE TABLE cnip
(
    id   int,
    name varchar(20),
    INDEX idx_name (name) # 索引名/作用列
);

# 唯一索引
CREATE TABLE cnip
(
    id   int,
    name varchar(20),
    UNIQUE INDEX idx_name (name) # 索引名/作用列
);

# 主键索引
# 通过定义主键约束的方式
CREATE TABLE cnip
(
    id   int PRIMARY KEY,
    name varchar(20)
);

# 4. 联合索引
CREATE TABLE cnip
(
    id      int,
    name    varchar(20),
    address varchar(50),
    INDEX multi_name_address (name, address)
);

# 查看索引
SHOW INDEX FROM cnip;
SHOW CREATE TABLE cnip;

1.2 表存在时

# 1. 修改表
ALTER TABLE cnip ADD INDEX idx_name(name);
ALTER TABLE cnip ADD UNIQUE INDEX idx_name(name);
ALTER TABLE cnip ADD INDEX multi_idx(name,address);

# 2. 创建索引作用于表
CREATE INDEX idx_name ON cnip(name);
CREATE UNIQUE INDEX idx_address ON cnip(address);
CREATE INDEX multi_idx ON cnip(name,address);

2. 删除索引

• 大量添加数据时，可以考虑暂时删掉索引，提高性能

# 1. 方式一
ALTER TABLE cnip DROP INDEX idx_name;
ALTER TABLE cnip DROP PRIMARY KEY; # 只针对于主键索引

# 2. 方式二
DROP INDEX idx_name ON cnip;

# 联合索引
删除表中列时，如果列为索引组成部分，则改列也会从索引中删除
如果组成索引的所有列都被删除，则整个索引都会被删除

3. MYSQL8.0新特性

3.1 降序索引

3.2 隐藏索引

• 检查某个索引是否起到查询优化作用的一种软删除方案
• 将待删除索引设置隐藏，查询优化器不再使用该索引，假如验证该索引没用，再进行彻底删除
• 隐藏索引数据结构存在，数据更新时会更新索引

# 索引默认是可见 visible
# 创建不可见索引的三种方式
CREATE TABLE cnip
(
    id      int,
    name    varchar(20),
    address varchar(50),
    INDEX idx_address (address) INVISIBLE
);

ALTER TABLE cnip
    ADD INDEX idx_name (name) INVISIBLE;

CREATE INDEX idx_name ON cnip (name) INVISIBLE;

# 修改索引可见性
ALTER TABLE cnip ALTER INDEX  idx_name INVISIBLE ;

二、设计原则

1. 适合索引

1 字段数值有唯一性限制

- 表中某个字段使唯一性的，可以创建唯一性索引，主键索引
- 通过该索引更快的来确定某条记录
- 业务上具有唯一特性的字段，即使是组合字段，也必须构建唯一索引(Alibaba )
- 唯一索引影响了insert速度，但是这个损耗可以忽略，提高查找速度明显

2 频繁作为WHERE查询条件的字段

- 某个字段在SELECT语句中的WHERE条件中经常被使用到
- 在大数据量的情况下，创建普通索引就可以大幅度提升数据查询效率

3 ORDER BY/GROUP BY 后的字段

- 索引就是让数据按照某种顺序进行存储或检索
- 如果待排序的列有多个，可以在这些列上建立组合索引

4. UPDATE/DELETE后的WHERE字段

5. DISTINCT后的字段

6. 多表JOIN操作时

- 连接表的数量尽量不要超过三张表
- 对WHERE条件创建索引
- 对于连接的字段创建索引，并且该字段在多张表中的类型必须一致

7. 列类型数据尽可能小

- 类型大小： 该类型表示的有效数据范围的大小
- 在定义表结构的时候要显示指定列的类型，以整数为例(TINYINT, MEDIUMINT,INT,BIGINT),
  占用的存储空间依次递增，能表示的整数范围依次递增
- 如果要对该列添加索引，尽可能使用较小类型

• 数据类型越小，在查询时进行的比较操作越快
• 数据类型越小，索引占用的存储空间越少，在一个数据页内就可以放进更多几率，减少磁盘I/O带来的性能损耗，更多的数据页会混村在内存中，加快读写效率
• 对主键更加适用

8. 区分度高(散列性高)的列

• 列的基数：一列数据中不重复数据的个数
• 列的基数大的列建立索引，列的基数小的不适合建立索引
• 一般列的散列度超过33%就可以

9. 使用最频繁的列放到联合索引的左侧

10. 多个字段都要创建索引时，联合索引优于单值索引

2. 不适合索引

1. where中使用不到的索引

2. 数据量小的表中(1000行)

3. 有大量重复数据的列上

4. 避免对经常更新的表创建过多索引

• 频繁更新的字段不一定要创建索引。更新数据时也要更新索引，如果索引太大，更新效率低
• 过多索引虽然提高了查询性能，但是更新时要涉及到多个索引，更新效率低

5. 不建议用无序的值作为索引

• 身份证，UUID，MD5, HASH, 无序长字符串
• 索引比较时需要转换为ASCII，并且插入时可能造成页分裂

6. 删除不再使用或很少使用的索引

7. 不要定义冗余或重复的索引

• 重复索引： PRIMARY KEY/ UNIQUE/INDEX三个索引同时作用于一个字段