MySql索引的使用及理解

逃离我推掉我的手 2022-12-10 10:07 173阅读 0赞

索引是mysql中非常重要的一个知识点,面试中会经常会被问到.为了offer,认真理解下面知识点吧.

## 1:什么是索引? ##

**索引是一种用于快速查询和检索数据的数据结构。mysql中的索引结构有:  B+树和Hash。**

索引的作用就相当于目录的作用。我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。这样查找就会非常快.

## 2:索引的优缺点? ##

**优点:   **可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的.

**缺点:**

1.  创建索引和维护索引需要耗费许多时间
2.  占用物理存储空间 
3.

## **3:mysql中有哪些索引?** ##

**1.1、单列索引：单列索引中包含(普通,唯一,主键,前缀)这四个索引.  **一个索引只包含单个列，但一个表中可以有多个单列索引。

1.  **普通索引：**普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
2.  **唯一索引：**索引列中的值必须是唯一的，但是允许为空值，
3.  **主键索引**：是一种特殊的唯一索引，不允许有空值。
4.  **前缀索引** ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

**1.2、组合索引:** 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说

**1.3、全文索引:** 全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

**1.4、空间索引**　: 是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。　在创建空间索引时，使用SPATIAL关键字。(这个没用过,咱也不理解)

## 4:添加索引的sql语句 ##

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

# #

## 5:索引的底层数据结构 ##

**1: 哈希索引:**

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

**2:BTree树索引**

是一种很普遍的数据库索引结构。其特点是定位高效、利用率高、自我平衡，理论上，使用Btree在亿条数据与100条数据中定位记录的花销相同。

# #

## 6:为什么索引会提高查找速度? ##

这首先要从mysql的存储说起:  mysql的存储形式是页, 在没有使用索引时,

1.  定位到记录所在的页：需要遍历双向链表，找到所在的页
2.  从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了,这种查找时间复杂度为O(n)

**当添加索引后:** 就是将无序的数据变成有序(相对),通过 **“目录”** 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

# #

## 7:最左前缀原则 ##

如**User表的name和city加联合索引就是(name,city)**，而最左前缀原则指的是，**如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到**。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
    select * from user where name=xx ; // 可以命中索引
    select * from user where city=xx ; // 无法命中索引

例如组合索引（a,b,c），组合索引的生效原则是

从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

比如

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；

where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果

where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；

where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

## 8:使用索引的注意事项 ##

1.  经常需要搜索的列上，可以加快搜索的速度；
2.  在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
3.  在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
4.  对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
5.  在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；
6.  避免 where 子句中对宇段施加函数，这会造成无法命中索引。
7.  在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
8.  删除长期未使用的索引

索引的知识点还是蛮多的,用好索引会让你的sql变得飞快.

参考网址:

[https://snailclimb.gitee.io/javaguide/\#/docs/database/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95][https_snailclimb.gitee.io_javaguide_docs_database_E6_95_B0_E6_8D_AE_E5_BA_93_E7_B4_A2_E5_BC_95]

[https_snailclimb.gitee.io_javaguide_docs_database_E6_95_B0_E6_8D_AE_E5_BA_93_E7_B4_A2_E5_BC_95]: https://snailclimb.gitee.io/javaguide/#/docs/database/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B4%A2%E5%BC%95