收藏    

前言：面试被面试官问索引吊打，只知道一个主键索引，惭愧

1.什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。如果用生活中的例子，索引就是字典的目录，如果用代码世界来说，索引的作用就和数组的下标类似。

2.建立索引的原因是作用

原因：

如果不建立索引，假如在数据库里面有成千上万条消息中我要找到其中id为19999999的用户，那么数据库就会从上往下一条一条遍历直到19999999条的时候才找到结果，这样的效率明显低下。

作用：

由前面的言论，可以了解到索引的作用是加快查找的速度，提升查询的效率

3.索引的优缺点

优点：

加快查询的速度和效率

缺点：

1.建立索引需要消耗空间

2.建立索引和维护索引需要消耗时间，加大工作的效率，大量的索引维护的时间成本并不低

3.当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度

4.是否所有的数据库表都需要实现索引，实现索引就一定好，索引的使用时机？

给每个表都添加索引不一定是好的事情，上面说到索引能增加查询的速度，但是也说到索引是占据空间的，如果表的字段，数据量都较小的情况下，那么增加索引反而可能反而是一种对查询的负担（开销更大），因为遍历索引也需要增加数据库的工作量。再者，如果字段中的属性是几乎相同的值(比如性别，年龄等)那么最好不要添加索引，索引应该添加在字段中值尽量不相等的地方。

4.MYSQL索引的类型

4.1 mysql的存储引擎

要了解mysql的索引，首先要知道mysql的存储引擎，因为索引的实现是在存储引擎中

MyISAM存储引擎：只支持BTREE索引，采用的是聚簇索引（MySql的默认事务型引擎）

InnoDB存储引擎：只支持BTREE索引，采用的是非聚簇索引

4.2 索引的种类（单例索引、组合索引、全文索引、空间索引）

单例索引：一个表中的一列索引为单列索引，一个表中可以存在多个单列索引

单列索引包括：普通索引、唯一索引、主键索引

普通索引：是mysql的基本索引，没有限制索引的条件，但是可以加快查询的速度

唯一索引：这列索引限制索引的值是唯一的，但是可以为null (unique)

主键索引：与主键的含义一样，唯一且非null

组合索引：

顾名思义，是由多个字段组合而成的索引，且触发索引的条件是在查询条件中含有这些条件的左边字段才会触发

全文索引：

我看不懂但是我大为震撼，大概的作用与like模糊查询差不多，就是通过一些范围的文字等进行的查询行为，但是说是like其实不准确，因该说是比模糊查询更加模糊的查询，就类似于你在百度上输入一句歌词查询一首歌，但是你的歌词并不输入完全正确，比如说你输入错了一个字却能找到正确的歌名，全文索引做的是类似于一个精确度的查询。（百度了一下貌似是搜索引擎中比较重要的一种实现方式），是MyISAM引擎专属的一种索引

5.MySQL索引的创建和使用

5.0 索引的基本语法

创建索引1：

CREATE [UNIQUE] INDEX indexName On tablename(columnname(length))

添加索引2：

ALTER tablename ADD [UNIQUE] INDEX [indexName] On (columnname(length))

查看索引:

SHOW INDEX FROM TABLENAME

删除索引

DROP OMDEX FROM TABLENAME

5.1普通索引（KEY/INDEX），唯一索引（UNIQUE KEY），主键索引（PRIMARY KEY）

创建一张teacher表，为字段分别加上标题三个索引（代码附带解释），然后查看表的索引如下图片

CREATE TABLE IF NOT EXISTS teacher (
#comment用来写注释
  id INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  username VARCHAR(20),
  #defalut + 值 ， 表示如果默认无数据的情况下，那么就是男！默认这个人是男
  gender VARCHAR (10) NOT NULL DEFAULT '男' COMMENT '性别',
  phone VARCHAR(20),
  #设置主键索引
  PRIMARY KEY (id),
  #设置普通索引
  INDEX (username),
  #设置唯一索引，unique_key为索引起的名称，phone是对字段phone添加唯一索引
  UNIQUE unique_key(phone)
  #ENGINE INNODB 表示使用的引擎是innodb ,default是存储结构（Btree），最后是字符集
)ENGINE INNODB DEFAULT CHARSET=utf8

可以看到控制台红色部分分别是索引名称，添加索引的字段和索引的类型，类型全部都是B树，上面说到原因是使用了Innodb存储引擎，存储的结构是BTree

5.2组合索引

创建student表，对字段id，username，age创建一个组合索引

#演示创建组合索引的student表

CREATE TABLE IF NOT EXISTS student (
#comment用来写注释
  id INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  username VARCHAR(20),
  #defalut + 值 ， 表示如果默认无数据的情况下，那么就是男！默认这个人是男
  gender VARCHAR (10) NOT NULL DEFAULT '男' COMMENT '性别',
  phone VARCHAR(20),
  age INT NOT NULL ,
  #创建组合索引，组合索引就是在多个字段上创建一个索引
  INDEX multiIIndex(id,username,age) 
  #ENGINE INNODB 表示使用的引擎是innodb ,default是存储结构（Btree），最后是字符集
)ENGINE INNODB DEFAULT CHARSET=utf8

通过： INDEX 组合索引的名称(字段1,字段2...) 完成组合索引的创建

可以看到组合索引是有编号的,id为1,username为2.....

组合索引的使用：

如果要在查询的时候触发组合所索引，那么就必须要满足查询条件包括最左前缀，也就是要包括编号为1的条件才能触发，例如上面创建的组合索引是(id,username,age) ，那么查询语句就应该包括(id)这个字段和任意编号字段比如:

select * from student where id = 1 AND username ="xxx"；

或者:

select * from student where id = 1 AND age=xx；

但是如果是usernaem+age组合就没法使用到组合索引！切记，一定要带上第一个索引

12、13、123都可以 ，23 、234、34都不可以（如果组合索引有四个字段的话）

5.4全文索引（FullText）：MyISAM引擎

创建员工表演示全文索引，由于全文索引支持的是MyISAM引擎，所以ENGINE不能再设置为Innodb

#演示创建组合索引的employee表
CREATE TABLE IF NOT EXISTS employee(
#comment用来写注释
  id INT NOT NULL  COMMENT '学号',
  username VARCHAR(20),
  #defalut + 值 ， 表示如果默认无数据的情况下，那么就是男！默认这个人是男
  gender VARCHAR (10) NOT NULL DEFAULT '男' COMMENT '性别',
  phone VARCHAR(20),
  age INT NOT NULL ,
  record VARCHAR(255) NOT NULL,
  
  #创建全文索引
 
  FULLTEXT INDEX fulltextIndex(record)
  #ENGINE MyISAM 表示使用的引擎是MyISAM ,default是存储结构（Btree），最后是字符集
)ENGINE MYISAM DEFAULT CHARSET=utf8

可以看到全文索引的类型变成了FullText类型，是MyISAM引擎特有的

注意：全文搜索只能设置在MyISAM引擎上的CHAR,VARCHAR,TEXT类型的字段上，使用全文搜索还得通过借助MATCH函数

全文搜索的使用：

例如：

在employee表下有如下两条数据

#使用全文索引
SELECT * FROM employee WHERE MATCH(record)AGAINST('wangwu')