面试题整理-MySQL索引

一、什么是索引 index

- 官方定义：一种帮助mysql提高查询效率的数据结构

- 索引的优点：大大加快数据查询速度

-索引的缺点：

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

2、索引需要占用磁盘空间

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

二、索引的分类

MYSQL引擎:INNODB  MYISAM
- 主键索引 唯一索引 普通索引 复合索引
- InnoDB
- a.主键索引
        设定为主键后数据库会自动建立索引，innodb为聚簇索引  主键索引索引列值不能有空

- b.单值索引 单列索引  普通索引
        即一个索引只包含单个列，一个表可以有多个单列索引 基于表中某一个列构建所有称之为普通索引   id name  age  bir   

- c.唯一索引   
        索引列的值必须唯一，但允许有空值                唯一索引索引列值可以存在null,但是只能存在一个null  

- d.复合索引
        即一个索引包含多个列                           id  (name  age  bir)  一个索引

- MYISAM
- e.Full TEXT 全文索引 (My5.7版本之前 只能用于MYISAM引擎)
        全文索引类型为FULL TEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

三、索引的基本操作

1.主键索引 自动创建
--主键索引是在建表时自动创建

--建表 主键自动创建主键索引
create table t_user(id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user;

 

2.单列索引(普通索引|单值索引)
--建表时创建
create table t_user(id varchar(20) primary key,name varchar(20),key(name));  
    '注意:随表一起建立的索引索引名同列名一致'
      
--建表后创建
create index nameindex on t_user(name);

--删除索引
drop index 索引名 on 表名

 3.唯一索引
--建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
 
--建表后创建
create unique index nameindex on t_user(name);

 4.复合索引
---建表时创建
 create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
 --建表后创建
 create index nameageindex on t_user(name,age);

四、什么情况下无法利用索引呢?

- 1. 查询语句中使用LIKE关键字  name like "三%"   张三  李四  王五
            在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

- 2.查询语句中使用多列索引
          复合索引规则
            多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

- 3.查询语句中使用OR关键字  IN关键字:无法利用索引
            查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。