MySQL性能优化实战：深入理解索引及其最佳实践

Java探秘者

于 2024-09-30 20:27:40 发布

阅读量290

点赞数 4

分类专栏：数据库文章标签：数据库 java intellij-idea java-ee mysql mybatis maven

本文链接：https://blog.csdn.net/2301_80237574/article/details/142612009

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL性能

1、分析-数据库查询效率低下

我们进入公司进行项目开发往往关注的是业务需求和功能的实现，但是随着项目运行的时间增加，数据量也就增加了，这时会影响到我们数据库的查询性能。所以我们要提高操作数据库的性能，有如下两种方式：

1.硬优化：就是软优化之后性能还很低，只能采取硬优化，最后的步骤了，就是公司花钱购买服务器。在硬件上进行优化。我们在这里不关注。我们关注的软优化。

2.软优化: 在操作和设计数据库方面上进行优化，例如下面讲解的索引。这是我们本课程学习的重点。（重点）

索引

什么是索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

将数据进行排序整理的过程就称为**索引

MySQL索引分类

* 主键（约束）索引
        主键约束+提高查询效率

* 唯一（约束）索引
        唯一约束+提高查询效率

* 普通索引
        仅提高查询效率

* 组合（联合）索引
        多个字段组成索引

* 全文索引
        solr、es

* hash索引
        根据key-value 效率非常高

MySQL索引语法

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,..);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

1.如果在同一张表中创建多个索引，要保证索引名是不能重复的

2.上述创建索引的方式比较麻烦，还需要指定索引名

3.采用上述方式不能添加主键索引

准备的创建表的SQL语句

create database day04;

use day04;
-- 创建学生表
CREATE TABLE student(
 id INT,
 name VARCHAR(32),
 telephone VARCHAR(11)
);

【给上述表的字段设置索引】

1.给name字段设置普通索引

CREATE INDEX name_idx ON student(name);

2.给telephone字段设置唯一索引

CREATE UNIQUE INDEX telephone_uni_idx ON student(telephone);

② 在已有表的字段上修改表时指定【了解】

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);  --默认索引名：primary

-- 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(字段); -- 默认索引名：字段名

-- 添加普通索引，索引值可以出现多次。
alter table 表名 add index(字段); -- 默认索引名：字段名

③ 创建表时指定【掌握】

-- 创建学生表
CREATE TABLE student3(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 name VARCHAR(32),
 telephone VARCHAR(11) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX(name) -- 普通索引
);

查看索引

show index from 表名;

【查看student3表的索引信息】

show index from student3;

删除索引

语法

【语法1】直接删除

-- 直接删除
drop index 索引名 on 表名;

【语法2】修改表时删除

-- 修改表时删除
alter table 表名 drop index 索引名;

索引的优缺点

优势

1）类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。IO次数越多，效率越低。2）索引底层就是排序，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间。
在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦。

索引创建原则

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

1. 字段内容可识别度不能低于70%，字段内数据唯一值的个数不能低于70%
    例如：一个表数据只有50行，那么性别和年龄哪个字段适合创建索引，明显是年龄，
    因为年龄的唯一值个数比较多，性别只有两个选项 。性别的识别度是50%。

2. 经常使用where条件搜索的字段，例如user表的id name等字段。

3. 经常使用表连接的字段（内连接、外连接），可以加快连接的速度。

4. 经常排序的字段 order by，因为索引已经是排过序的，
这样一来可以利用索引的排序，加快排序查 询速度。

* 注意: 那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。
因为索引的建立和维护都是需要耗时的 创建表时需要通过数据库去维护索引，
添加记录、更新、修改时，也需要更新索引，会间接影响数据库的 效率。