MySQL高级知识（三）——索引

此部分将着重将索引，因为索引在sql调优部分占据很重要的地位。

1. 索引含义

• MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。因此索引的本质就是数据结构。索引的目的在于提高查询效率，可以类比字典，书籍的目录等这种形式。
• 可简单的理解为“排好序的快速查询数据结构”。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式只想数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
• 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
• 平常所说的索引，如果没有特别指明，都是B树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、唯一索引默认都是用B树。

通过show index from tablename可以查看表的索引情况。

如图，有两张表，一张表是emp，有两个索引，一张表是dept，只有一个主键索引。

2. 索引优缺点

2.1. 索引优点：

1. 类似大学图书馆的书目索引，提高数据的检索效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低数据的排序成本，从而降低CPU的消耗。

2.2. 索引缺点：

1. 索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间。
2. 虽然索引大大提高了查询效率，但是降低了更新表的速度，如insert、update和delete操作。因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新的索引列字段，并且在更新操作后，会更新相应字段索引的信息。
3. 索引只是提高查询效率的一个因素，如果你的MySQL有大量的数据表，就需要花时间研究建立最优秀的索引或优化查询语句。

3. 索引分类

3.1. 主键索引：

设定为主键后数据库会自动建立索引，innodb为聚簇索引。

语法：

• 使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。

CREATE TABLE customer2 (
id INT(10) UNSIGNED ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)  #主键（注释别放进去）
);

• 单独建主键索引：
  ALTER TABLE customer add PRIMARY KEY customer(customer_no);

• 删除建主键索引：
  ALTER TABLE customer drop PRIMARY KEY ;

• 修改建主键索引：
  必须先删除掉(drop)原索引，再新建(add)索引

3.2. 单值索引：

一个索引只包含单个列，一个表可以有多个单值索引。

语法：

• 随表一起建索引：

CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)  #单值索引
);

随表一起建立的索引 索引名同 列名(customer_name)

• 单独建单值索引：
  CREATE INDEX idx_customer_name ON customer(customer_name);

• 删除索引：
  DROP INDEX idx_customer_name ;

索引建立成哪种索引类型？
根据数据引擎类型自动选择的索引类型
除开 innodb 引擎主键默认为聚簇索引 外。 innodb 的索引都采用的 B+TREE
myisam 则都采用的 B-TREE索引

3.3. 唯一索引：

索引列的值必须唯一，但允许为空，主键就是唯一索引。

语法：

• 随表一起建索引：

CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),  # 主键
  KEY (customer_name), #单值索引
  UNIQUE (customer_no) #唯一索引
);

**建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。 **

• 单独建唯一索引：
  CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

• 删除索引：
  DROP INDEX idx_customer_no on customer ;

3.4. 复合索引：

一个索引列包含多个列。
在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)。当表的行数远大于索引列的数目时可以使用复合索引

语法：

• 随表一起建索引：

CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
  PRIMARY KEY(id),  									#主键
  KEY (customer_name),                            #单值索引
  UNIQUE (customer_name),                     #唯一索引
  KEY (customer_no,customer_name)       #复合索引
);

• 单独建索引：
  CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

• 删除索引：
  DROP INDEX idx_no_name on customer ;

4. 基本语法

1. 创建
   create [unique] index indexname on tablename(columnname(length));
alter table tablename add index indexname (columnname(length));
   注：如果是char、varchar类型的字段，length可以小于字段实际长度；如果是blob、text类型，必须指定length。
2. 删除索引
   drop index indexname on tablename;
3. 查看索引
   show index from tablename;
4. 使用alter命令：
   1. 添加主键索引
      ALTER TABLEtable_nameADD PRIMARY KEY (column)
   2. 添加唯一索引
      ALTER TABLEtable_nameADD UNIQUE (column)
   3. 添加全文索引
      ALTER TABLEtable_nameADD FULLTEXT (column)
   4. 添加普通索引
      ALTER TABLEtable_nameADD INDEX index_name (column)
   5. 添加组合索引
      ALTER TABLEtable_nameADD INDEX index_name (column1,column2,column3)

5. 建立索引与否的具体情况

5.1. 需建立索引的情况

1. 主键自动建立唯一索引。

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

3. 查询中与其他表关联的字段，外键关系建立索引。

4. 高并发下趋向创建组合索引。

5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

6. 查询中统计或分组字段。

5.2. 不需要创建索引的情况

1. 表记录太少。（数据量太少MySQL自己就可以搞定了）

2. 经常增删改的表。

3. 数据重复且平均分配的字段，如国籍、性别，不适合创建索引。

4. 频繁更新的字段不适合建立索引。

5. Where条件里用不到的字段不创建索引。