索引的类型和使用

一、索引的介绍

        索引可以加快查询的效率,当不使用索引的时候,相当于逐行进行比较,直到找到符合要求的值,相当于全表扫描。

        可以把索引想象成书中的目录,例如对student表的name字段建立索引,那么在磁盘就对name字段开辟了1个存储空间,查询name字段的时候就能直接定位到这个存储空间。

 二、索引的分类

按照功能分:单列索引、组合索引、全文索引、空间索引

其中最常用的就是单列索引和组合索引，单列索引又分为普通索引、唯一索引、主键索引。

组合索引并不是一种新的索引类型,而是将两个或多个字段同时创建为普通索引或者唯一索引,下面会介绍。

 

三、单列索引 

单列索引表示只对某一列建立索引,一张表中可以有多个单列索引。

3.1、普通索引

普通索引对列中的字段没有特殊要求,不用限制唯一性等,只是纯粹的为了查询比较快

创建普通索引的四种方式:

1、创建表的时候创建索引

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键字段
    name VARCHAR(100),                  -- 姓名字段
    age INT,                            -- 年龄字段
    gender VARCHAR(10),                 -- 性别字段
    score DECIMAL(5, 2),                -- 成绩字段
    INDEX idx_name (name)               -- 在 name 字段上创建普通索引
);

INDEX idx_name (name):表示创建的索引为普通类型,因为INDEX前面没有加限制条件,创建的索引名字为idx_name,对name这一列字段创建索引

2、alter关键字创建索引

ALTER TABLE student ADD INDEX idx_score (score);

3、CREATE关键字创建索引 

CREATE INDEX idx_name ON student (name);

4、使用navicat等连接工具图形化界面操作

推荐第4种 

3.2、唯一索引 

        唯一索引和普通索引的区别在于索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须是唯一。
        唯一索引的创建方式和普通索引类似,只需要在前面加上unique关键字

 CREATE unique INDEX idx_name ON student (name);

创建唯一索引也推荐使用navicat等连接工具图形化界面操作

3.3、主键索引 

主键索引在创建表的时候mysql会自动为主键创建索引,主键索引是一种特殊的唯一索引,因为主键索引唯一但不允许为空值。主键索引在navicat的图形化界面中看不见,只能通过命令查询:

show index form 表名

四、组合索引 

        4.1、组合索引介绍

        组合索引并不是一种新的索引类型,而是将两个或多个字段同时创建为普通索引或者唯一索引
所以组合索引可以将创建的字段全部设置为普通索引,也可以将创建的字段全部设置为唯一索引

         4.2、组合索引的命中规则                

        索引是否命中是由where后面的条件决定的,不是由select后面的查询字段决定的。
        组合索引必须满足最左匹配原则:
        假设我创建了一张student表:为student表的name、age字段创建了组合索引,并且顺序也是name、age,那么我在查询的时候一定在where后面的条件带上name字段

例如:

SELECT * FROM student t WHERE age=15 AND gende='女' and score=90 and name=zhangsan

        这种在where后面的条件中带了name字段的,就会命中组合索引,因为使用的是and查询,对于mysql而言字段的顺序没有区别,就会命中组合索引最左侧的字段。如果使用的是or,就不会命中。
        所以使用了组合索引,一定要在查询的where子句后面加上最左边的索引字段,例如上面的name字段。 

 

五、使用explain关键字 

对上面的sql语句使用explain关键字

EXPLAIN	SELECT * FROM student t WHERE t.age=18 AND gender='男' and score=90 and name='zhangsan'

得到查询结果

 

 key 字段：表示查询使用的索引。如果 key 字段为 NULL，则表示没有使用索引。如果该字段显示了某个索引的名字，那么该查询就命中了这个索引。

这部分内容看完sql优化继续深入