MySQL索引

一、索引的基本概述

1、什么是索引 index

（1）官方定义：一种帮助MySQL提高查询效率的数据结构；

（2）索引的优点：加快数据的查询速度；

（3）索引的缺点：

维护索引需要耗费数据库资源；

索引需要占用磁盘空间；

当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响；

2、索引的分类

innodb引擎包含如下：

-- a.主键索引

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

主键索引索引列的值不能为空；

-- b.单值索引、单列索引、普通索引

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

-- c.唯一索引

索引列的值必须唯一，但允许有空值，但只能有一个null；

-- d.复合索引

一个索引包含多个列；

MYISAM引擎：

-- e.Full Text 全文索引（MySQL 5.7版本以前只能用于MYISAM引擎）

全文索引类型为FULL TEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR上创建。mysql只有MYISAM存储引擎支持全文索引；

3、索引的基本操作

（1）主键索引在创建表的时候自动创建

create table t_user (id varchar(20) primary key ,name varchar(20));
-- 查看索引
show index from t_user;

（2）单列索引

-- 建表时创建
-- 注意：随表一起创建的索引，  索引名同列名一致
create table t_user1 (id varchar(20) primary key ,name varchar(20),key(name));-- 建表后创建
create index nameindex on t_user1(name)-- 删除索引
drop index 索引名  on 表名;-- 查看索引
show index from t_user1;

（3）唯一索引

-- 建表时创建
-- 注意：随表一起创建的索引，  索引名同列名一致
create table t_user2 (id varchar(20) primary key ,name varchar(20),unique(name));-- 建表后创建
create unique index nameindex on t_user2(name);

（4）复合索引

-- 建表时创建
-- 注意：随表一起创建的索引，  索引名同列名一致
create table t_user3 (id varchar(20) primary key ,name varchar(20), age int,key(name,age));-- 建表后创建
create index nameageindex on t_user3(name, age);

经典面试题：

        假设有name age bir 三个字段组成的索引（如下哪些可以条件可以使用索引）

        1、索引遵循最左前缀原则

        2、mysql 索引在查询时为了能够更好利用索引，在查询过程中会动态调整查询字段顺序以便利用索引；
-- 完全匹配所有列（有name就行）
SELECT * FROM table WHERE name = 'John' AND age = 30 AND bir = '1990-01-01';-- 配索引的部分列（从最左边开始）
SELECT * FROM table WHERE name = 'John';-- 不可以使用索引
SELECT * FROM table WHERE age = 30;