【由浅入深学习MySQL】之索引进阶

ゞ浴缸里的玫瑰 2024-03-17 09:32 108阅读 0赞

**本系列为：MySQL数据库详解，为千锋资深教学老师独家创作**

**致力于为大家讲解清晰MySQL数据库相关知识点，含有丰富的代码案例及讲解。如果感觉对大家有帮助的话，可以【关注】持续追更~**

**文末有本文重点总结，技术类问题，也欢迎大家和我们沟通交流！**  
![在这里插入图片描述][ebb5d1d920b14aa4b98587f6e2a7f4b4.png]

## 前言 ##

从今天开始本系列文章就带各位小伙伴学习数据库技术。**数据库技术是Java开发中必不可少的一部分知识内容。也是非常重要的技术。本系列教程由浅入深, 全面讲解数据库体系。 非常适合零基础的小伙伴来学习。**

--------------------

全文大约【**1404】** 字，不说废话，只讲可以让你学到技术、明白原理的纯干货！本文带有丰富案例及配图视频，让你更好的理解和运用文中的技术概念，并可以给你带来具有足够启迪的思考…

## 一. 索引 ##

在上一章节中我们讲解了[索引的基本入门][Link 1]。那么在这一节中我们来探讨下**索引的深层原理**。各位小伙伴准备好了吗，我们开始喽！

### 1. 索引的设计原则 ###

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

**创建索引的原则：**

> 对查询频次较高，且数据量比较大的表建立索引。
> 
> 使用唯一索引，区分度越高，使用索引的效率越高。
> 
> 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
> 
> 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
> 
> 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

### 2.联合索引的使用 ###

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，对列name列、address和列phone列建一个联合索引。

ALTER TABLE user ADD INDEX index_three(name,address,phone);

联合索引index\_three实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。

SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
    SELECT * FROM user WHERE name = '张三' AND address = '北京';
    SELECT * FROM user WHERE name = '张三';

上面三个查询语句执行时会依照最左前缀匹配原则，检索时分别会使用下面索引。进行数据匹配。

(name,address,phone)  
    (name,address)  
    (name)

索引的字段可以是任意顺序的，举个栗子：

-- 优化器会帮助我们调整顺序，下面的SQL语句都可以命中索引
    SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';

Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。

联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。

-- 联合索引中最左边的列不包含在条件查询中，下面的SQL语句就不会命中索引
    SELECT * FROM user WHERE address = '北京' AND phone = '12345';

--------------------

## 二. 结语 ##

**最后在这里对本文核心要点进行总结:**

1.  熟练记住并理解创建索引的原则和联合索引的使用，本章节主要就是讲索引的使用原则和联合索引的使用。

--------------------

## 三. 配套视频 ##

如果你不习惯阅读技术文章，或是对文中的技术概念不能很好地理解，可以来看看视频教程。[**与本文配套的Java学习视频，戳蓝字一键直达**  
][Java_]

[ebb5d1d920b14aa4b98587f6e2a7f4b4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/17/e90b871b5adb424b94c1d619449416e8.png
[Link 1]: https://juejin.cn/post/7234772762295402553
[Java_]: https://www.bilibili.com/video/BV1qb4y1Y722?t=0.0