MySQL 添加删除索引及如何使用索引优化 MySQL 数据库性能

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
最近更新 2022年01月24日

MySQL 添加删除索引及如何使用索引优化 MySQL 数据库性能

一.MySQL 索引意义及原理?

在海量数据和高访问的情况下,MySQL 数据库是否添加索引,对访问速度的影响是巨大的。当数据和访问量起来后,你会明显的感觉到数据库极速变慢,或者直接挂掉,这时对 MySQL 的优化就非常必要了,而给数据库合理建立索引,是优化 MySQL 中最容易也是最重要的方法之一。

索引的意义在于提高查询效率,建立索引我们可以顺藤摸瓜,就像我们查字典一样,当我们要查「MySQL」这个单词时,我们要先看目录找到「M」在多少页,然后在「M」下找「Y」,以此类推逐步找到 MySQL 这给词。索引的原理跟查字典的原理是相通的。

在创建索引时,我们要充分考虑哪些字段会用于查询,为这个字段创建一个或多个索引。索引其实也是一种表,表种保存着索引字段以及一个能将每条记录指向被索引表的指针。

索引的使用并非越多越好,索引会影响 INSERTUPDATE 的执行效率,因为在执行写入时,索引会同步更新。当然好处是 SELECT 执行效率得到大幅提升,在常规数据库操作中,查询与写入的比例一般为 10:1 ,索引不用担心写入变慢带来的影响,在必要的字段建立索引,提高查询速度是相当有意义的。

二. MySQL 索引语法

1.创建索引

-- 直接新建索引
 CREATE INDEX index_name ON kalacloud_table_name(Col1(length)) 

-- 修改表结构新建索引
 ALTER kalacloud_table_name ADD INDEX [index_name] ON (Col1(length))  

-- 创建表的时候 建立索引
 CREATE TABLE kalacloud_table_name(ID INT NOT NULL,Col1 VARCHAR(16) NOT NULL,INDEX [index_name] (Col1(length)) ) 

2.组合索引,单个索引项中包含多个字段

MySQL 多索引允许最多 16 个字段。经验谈,最好不要超过 5 个字段,不然写入时会非常慢。

CREATE INDEX index_name ON kalacloud_table_name (Col1, COL2, COl3);

3.唯一索引

-- 创建唯一索引
CREATE UNIQUE INDEX indexName ON kalacloud_table_name(Col1(length))

-- 修改表结构
ALTER kalacloud_table_name ADD UNIQUE [indexName] ON (Col1(length)) 

-- 创建表的时候直接指定
CREATE TABLE kalacloud_table_name(ID INT NOT NULL,Col1 VARCHAR(16) NOT NULL, UNIQUE [indexName] (Col1(length)));

3.删除索引

ALTER TABLE kalacloud_table_name DROP INDEX index_name;

4.重命名索引

ALTER TABLE kalacloud_table_name RENAME INDEX index_name TO new_index_name;

5.显示索引

SHOW INDEX FROM kalacloud_table_name;

三.MySQL 索引优化实战 - 添加索引后速度到底快了多少?

在这个示例中,我们会使用 MySQL/MariaDB 创建一个海量数据的表,然后对比建立索引前与建立索引后的查询时间。在测试中,我们可以清晰的看到查询效率的巨大提升。

我们先创建一个用于演示的库「kalacloud_demo」和表「kalacloud_Employees」。

CREATE DATABASE kalacloud_demo;
USE kalacloud_demo;
CREATE TABLE kalacloud_Employees (
  `Name` VARCHAR(50)  NOT NULL,
  `Email` VARCHAR(255)  NOT NULL,
  `City` VARCHAR(50)  NOT NULL,
  `State` VARCHAR(50)  NOT NULL,
  `Wage` double  NOT NULL,
  `DOB` Date,
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

接下来,我们要往刚刚创建好的表中批量添加百万条数据。如果手动写几条进去,大家很难体会到索引的意义,上百万条,有索引和没有索引,查询时间的差别非常明显。

我们用 TestDataGenerator 代码来生成 200 万条模拟数据。

MySQL 索引模拟数据

四. 创建索引前后查询时间对比,效率优化立竿见影

我们在刚刚创建的百万数据的表中进行多种查询,对比索引建立前后,查询时间的变化。在这三组测试中,你能很明显的感知到索引对查询效率的优化有多么巨大。

第一组测试 - 未创建索引的查询时间

请注意各个查询的查询时间

未创建索引的查询时间

上述查询的执行时间在 0.72 秒到 0.64 秒之间。现在,让我们创建索引,进行测试,然后比较时间。刚刚我们查询了 CountryCityDOBWage 几个字段,我们一个一个来看。

第二组测试 - Country 创建索引后的查询速度对比

先创建 Country 字段的索引:

创建索引后的查询速度对比

然后,我们查询与第一组测试相通的 query ,看看查询时间的变化

创建索引后的查询速度对比

我们可以从返回结果中看到,建立索引前,平均查询时间为 0.7 秒,建立索引后,平均查询时间为 0.02 秒。这对于大并发量的数据库来说,这是非常巨大的性能提升。

第三组测试 - DOB 及 Wage 字段创建索引后的查询速度对比

接着我们来试试给 DOBWage 后的测试结果会怎么样

创建索引

DOB 及 Wage 字段创建索引后的查询速度对比

日期查询和过滤时间从未建立索引前的0.67 提升至 0.15

DOB 及 Wage 字段创建索引后的查询速度对比

创建索引后,可以看到查询时间的提升立竿见影,有些 query 查询时间已经快到 0.00 秒了。

五. MySQL 索引的优缺点

索引的优点

  • 索引可以减少查询所需扫描的数据量,大大提高查询效率,特别是 SELECTUNIQUE 查询。
  • 利用索引对数据存储的特性,可使查询语句减少排序和创建临时表。
  • 索引可以将随机 I/O 变为顺序 I/O 。

索引的缺点

  • 索引会减慢 INSERTUPDATEDELETE 执行时间,因为每次 UPDATEINSERTDELETE执行时,MySQL 都必须重新更新索引。
  • 索引在数据库中实际上是一个实体表,所以他会占用一部分物理磁盘空间。

六. 总结

当我们的数据库数据量开始爆发增长时,优化 MySQL 性能是我们的必修课,而适时的建立索引对数据库查询效率有着立竿见影的提升。另外「慢查询日志」也是优化 MySQL 性能的必备手段之一,有兴趣可以读一下我写的另一篇教程 - 《如何使用 MySQL 慢查询日志进行性能优化 - Profiling、mysqldumpslow 实例详解

最后顺便推荐一下我和小伙伴一起开发的卡拉云。只要你会写 SQL 无需会任何前端,也可以快速搭建后台管理工具,比如用户权限管理系统、销售管理系统、学生信息系统、财务对账系统,还可以搭建一套属于自己的数据查询看板,一键分享给协作组的同学使用,再也不用担心自己沦为人肉查询机。

卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,欢迎免费试用卡拉云

卡拉云可一键接入常见的数据库及 API

卡拉云可一键接入常见的数据库及 API

卡拉云可根据公司工作流需求,轻松搭建数据看板或其他内部工具,并且可一键分享给组内的小伙伴。

使用卡拉云轻松搭建企业内部工具

下图为使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台,仅需要简单拖拽即可快速生成前端组件,只要会写 SQL,便可搭建一套趁手的数据库工具。欢迎免费试用卡拉云

使用卡拉云在 5 分钟内搭建的「优惠券发放核销」后台

更多数据库相关教程可访问 卡拉云 查看。如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云联合创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习: