MySQL SQL调优系列之索引知识学习笔记

末蓝、 2021-08-19 21:09 380阅读 0赞

继我的上篇博客：[Oracle索引知识学习笔记][Oracle]，再记录一篇MySQL的索引知识学习笔记，本博客是我在学习尚硅谷的学习教程后，做的笔记，当然我不是为了所谓宣传，仅仅是学习记录的笔记。本来可以不分享出来，不过，分享出来的笔记不仅可以给网上的学习者参考学习，同时写在csdn比较方便，可以支持图片上传，也方便自己以后查找复习

### 文章目录 ###

*   *  一、索引的概念
     *  二、索引分类
     *  三、索引用法
     *  四 、索引架构简介
     *  五、索引适用的情况
     *  六、索引不适用的情况

## 一、索引的概念 ##

MySQL官方给出的索引定义：索引(Index)是帮助MySQL高效获取数据的数据结构。所以说索引就是排好序的快速查找数据结构

## 二、索引分类 ##

MySQL的索引可以分为几种：

*  单值索引：单值索引就是只包含一个列的索引
 *  唯一索引：唯一索引要求索引列的必须是唯一的，比如说主键或者unique索引，但是还是允许空值
 *  复合索引：复合索引就是包含多个列组成的索引

## 三、索引用法 ##

*  创建索引

create [unique] index indexName on tableName(columnName(length));

比如给dept表创建索引idx\_dept\_id

create index idx_dept_id on dept(id);

*  修改索引  
    ps:修改索引也可以用于新增索引

语法：

alter tablename add [unique] index indexname on columnName(length);

*  删除索引

drop index indexname on tablename;

*  查看索引

show index from tablename ;

如果要显示换行可以加上\\G，不过就不能加上分号

show index from tablename \G

*  附录：  
    来自尚硅谷老师总结的新增索引的方法：

# 添加一个主键，主键是唯一的，不能为Null
    alter table tablename add PRIMARY KEY(columnName);
    #创建唯一索引，要求索引唯一，不过允允许空值
    alter table tablename add unique index indexname on(columnName);
    # 创建普通的索引，索引数据可以不唯一
    alter table tablename add index indexname on(columnName);
    # 指定索引为FullText，用于全文索引
    alter table tablename add FULLTEXT indexname on(columnName);

## 四 、索引架构简介 ##

MySQL的索引结构可以分为：

*  BTree索引
 *  Hash索引
 *  full-text全文索引
 *  R-Tree索引

引用尚硅谷MySQL教程的图，本博客只介绍BTree索引的  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]  
从图可以看出BTree索引的结构其实就是一棵B+树，并不一定是二叉树，也有可能是一棵多叉树，MySQL也是分为段区块这种结构的，如图，浅蓝色部分就是一个磁盘块，蓝色部分表示数据项，而黄色部分表示指针

假如我要查找29这个值，就是遍历下来，分别查找磁盘块1、磁盘块3、磁盘块8，根据指针查找下来，速度非常块，假如有几百万数据的话，能走索引的情况，是非常快的，性能对比可想而知

> 注意：只有叶子节点(最下面的节点)是存储要查找的数据的，非叶子节点存储的数据只是用于指针索引的数据项而已

## 五、索引适用的情况 ##

*  1、频繁用于查询的条件适合建立索引
 *  2、主键默认要加上Primary索引
 *  3、和其它表有外键关系的列建立索引
 *  4、where条件后面的条件列可以建上索引
 *  5、用于排序order by的列可以加上索引
 *  6、用于分组group by的列可以加上索引

> 注意：需要经常修改的列不适合建立索引，因为更新数据的同时也会重新构建索引，比较耗性能；在高并发的情况，更倾向于建立组合索引，因为一般来说，查询很少有一个条件，一般是多个条件，复合索引比较适合

## 六、索引不适用的情况 ##

*  1、需要经常增删改的表
 *  2、表的记录很少的情况，加了索引效果不明显
 *  3、如果某个数据列包含很多重复的数据，比如用户信息表的性别这个列，一般只有两种情况，所以加了索引，是没有太大的实际效果的

> 注意：索引应该加在经常查询或者排序的列，数据重复而且分布很平均的情况，是不适合加索引的

> 引用尚硅谷老师的归纳：![在这里插入图片描述][20190915104143868.png]

[Oracle]: https://smilenicky.blog.csdn.net/article/details/90706124
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/ee41f8820de6423bb974bbc106294890.png
[20190915104143868.png]: /images/20210813/e25eba3b0ac04965b8c05e3d6db764ff.png