学习目标：

1.理解索引（3W）

2.会创建和查看索引

3.能根据需求维护索引，优化查询

学习导航：

画板

一、什么是索引，为什么使用索引？

•索引（也叫做“键（key）”）是存储引擎用于快速查找记录的一种数据结构，用来快速查询数据库表中的特定记录。

•MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

  • 索引的优缺点：

优点：

•可以提高查询数据的速度；

•通过创建唯一索引，可以保证数据库表中每一行数据的唯一性；

•在实现数据的参考完整性方面，可以加速表和表之间的连接；

•在使用分组和排序子句进行数据查询时，可以减少分组和排序的时间；

建议：建立索引的字段通常是哪些？

1.主键（primary key 自动建立）、外键索引，加快表之间的连接速度。 2.唯一索引，保证数据的唯一性约束。 3.分组字段索引，加快分组。 3.排序字段索引，加快排序。 5.经常访问的列和查询条件索引，加快响应速度。 缺点：

•创建和维护索引需要耗费时间，并且随着数据量的增加所耗费的时间也会增加；

•索引需要占用磁盘空间；

•当对表中的数据进行增加、删除和修改操作时，索引也需要动态的维护，这样就降低了数据的维护速度；

二、索引的分类

•索引作为一种特殊的数据结构，由MySQL的存储引擎实现，不同存储引擎支持的索引类型不同。

•存储引擎InnoDB的索引实现采用BTREE（平衡二叉树，B树）。

•索引的分类主要考虑两个维度：

•主键索引：由PRIMARY KEY定义的一种特殊的唯一性索引，用于根据主键自身的唯一性标识每条记录，防止添加主键索引的字段值重复或为NULL。

•辅助索引：主键索引的性能相对来说最好，但查询优化更多的是对辅助索引建立和维护。根据创建索引的字段个数，可以将它们分为单列索引和复合索引。

6.3 使用索引（index|key）优化查询性能 - 图2

三、创建索引

1.使用Navicat工具创建索引

任务1：为orders表的ocode列创建名为ix_ocode的普通索引。

右击表—》设计—》索引—》新建索引—》编辑索引名、字段、类型—》ctrl+s保存

2.使用SQL语句创建索引

（1）在建表时创建索引：

任务2：创建goods_ bak表，并在gcode列上创建名为ix_gcode的唯一索引。 

  1. CREATE TABLE goods_bak
  2. (  gid int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3.  cid int,
  4.  gcode varchar(50) NOT NULL,
  5.  gname varchar(100) NOT NULL,
  6.  gprice decimal(10,2),
  7.  gsale_qty int,
  8.  ginfo varchar(20000),     #商品详情
  9.  UNIQUE INDEX ix_gcode(gcode)  -- 【索引类型】
  10. );

（2）使用ALTERTABLE语句创建索引：

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名(字段名 [(长度)][ASC|DESC]))

任务3：在users表的ulogin、uname和uemail三列上创建名为ix_users的复合索引。

  1. ALTER TABLE users
  2. ADD INDEX ix_users (ulogin, uname, uemail) ;

（3）使用CREATE INDEX语句创建索引：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])

任务4：在goods_bak表的gname列上创建名为ix_gname的前缀索引，取gname列的前10个字符。

  1. CREATE INDEX ix_gname
  2. ON goods_bak(gname(10)) ;

任务5：在goods_bak表的ginfo列上创建名为ix_ft_ginfo的全文索引。

  1. CREATE FULLTEXT INDEX ix_ft_ginfo
  2. ON goods_bak(ginfo);

四、查看索引

•索引创建好后，可以通过SHOW INDEX FROM/SHOW KEYS FROM语句，查看指定表的索引信息。

SHOW {INDEX | KEYS} FROM 表名 ;

任务6：使用SHOW INDEX FROM语句，查看goods_bak表的索引信息。

五、维护索引

1.删除索引

方式一：使用ALTER TABLE语句删除索引

方式二：使用drop index语句删除索引

任务7：删除goods_bak表上名为ix_ft_ginfo的索引。

任务8：删除goods_bak表中名为ix_gname的索引。

学习提示：删除表中的列时，会删除与该列相关的索引信息。若待删除的列为索引的组成部分，则该列也会从索引中删除。若组成索引的所有列都被删除，则整个索引将被删除。 2.修改索引

•随着数据的增册改会造成索引碎片的产生，当需要验证索引的有效性时，可修改索引为隐形索引。

•MySQL修改索引使用ALTER TABLE语句。

ALTER TABLE 表名 ALTER INDEX 索引名 [VISIBLE | INVISIBLE]; — MySQL 8.0新增功能 1.alter table 表 先删后建索引 
  1. ALTER TABLE users
  2. -- 语法：先删后建
  3. DROP INDEX idx_login_name_email,
  4. add INDEX idx_new(ulogin,uname);

2.

六、索引的设计原则

•高效的索引有利于快速查找数据，而设计不合理的索引可能会对数据库和应用程序的性能造成障碍。因此，创建索引时应尽量考虑符合以下原则，便于提升索引的使用效率。

（1）不要建立过多的索引。 （2）为用于搜索、排序或分组的列创建索引，而对于用作输出显示的列则不宜创建索引。 （3）使用唯一索引，并考虑数据列的基数。数据列的基数是指它所容纳的所有非重复值的个数。 （4）使用短索引，应尽量选用长度较短的数据类型。 （5）在设置前缀索引时，字段长度的设定需要通过一定的计算和测试以选取最合适的字符长度范围。 （6）利用最左前缀。

课堂小结：

1.建立索引有三种方法：（1）建表时创建索引；（2）修改表添加索引；（3）在表的字段上创建索引。

2.主键自动、外键要建立索引。

3.其他分组、排序、查询条件字段的索引主要是普通索引。

4.创建唯一索引可以保证数据的唯一性，不允许重复值。