MySQL5.6在线DDL索引创建性能评估与测试分析

作者：告非言普 | 来源：互联网 | 2024-10-23 11:42

基本信息：mysql版本：(product)root@localhost[(none)]>select@@version;+------------+|@@version

基本信息：
mysql版本：
(product)root@localhost [(none)]> select @@version;
+------------+
| @@version |
+------------+
| 5.6.29-log |
+------------+
1 row in set (0.00 sec)
表payment的记录数:
(product)root@localhost [sakila]> select count(*) from payment;
Connection id:    24
Current database: sakila
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.00 sec)事务隔离级别：
(product)root@localhost [(none)]> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)场景一：
测试在payment_date字段利用ALGORITHM=COPY,LOCK=EXCLUSIVE方式建立索引所需时间，并监控表是否有加锁?
1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件#!/bin/bash
while : ;do
       date>>/data/mysql/mysql3376/lock.txt &&
mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt
        sleep 1
done
2.会话2执行建索引脚本
(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=COPY,LOCK=EXCLUSIVE;
Query OK, 16049 rows affected (1.08 sec)
Records: 16049 Duplicates: 0 Warnings: 0
3.查看lock.txt
cat lock.txt
Sun Apr 17 20:11:17 HKT 2016
Database        Table   In_use Name_locked
sakila payment 1       0
4.drop索引，并重启mysql，以清空buffer pool缓存数据
alter table sakila.payment drop index payment_date_ix1;
并重启mysql。小结：监控到表sakila.payment add index在执行建索引过程中有锁表，且建索引时间为1.08秒。场景二：
测试在payment_date字段利用ALGORITHM=COPY,LOCK=SHARED方式建立索引所需时间，并监控表是否有加锁?
1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件
#!/bin/bash
while : ;do
       date>>/data/mysql/mysql3376/lock.txt &&
mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt
        sleep 1
done
2.会话2执行建索引脚本
(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=COPY,LOCK=SHARED;
Query OK, 16049 rows affected (1.57 sec)
Records: 16049 Duplicates: 0 Warnings: 0
3.查看lock.txt
cat lock.txt
Sun Apr 17 20:19:40 HKT 2016
Database        Table   In_use Name_locked
sakila payment 1       0
4.drop索引，并重启mysql，以清空buffer pool缓存数据
alter table sakila.payment drop index payment_date_ix1; 小结：监控到表sakila.payment add index在执行建索引过程中有锁表，且建索引时间为1.08秒。
场景三：
测试在payment_date字段利用ALGORITHM=COPY,LOCK=NONE方式建立索引所需时间，并监控表是否有加锁?
1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件
#!/bin/bash
while : ;do
       date>>/data/mysql/mysql3376/lock.txt &&
mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt
        sleep 1
done
2.会话2执行建索引脚本
(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=COPY,LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. 小结：ALGORITHM=COPY,LOCK=NONE两个参数不能同时组合使用，copy方式必需要加锁。场景四：
测试在payment_date字段利用ALGORITHM=INPLACE,LOCK=EXCLUSIVE方式建立索引所需时间，并监控表是否有加锁?
1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件
#!/bin/bash
while : ;do
       date>>/data/mysql/mysql3376/lock.txt &&
mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt
        sleep 1
done
2.会话2执行建索引脚本
(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=EXCLUSIVE;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.查看lock.txt
cat lock.txt
Sun Apr 17 20:29:26 HKT 2016
Sun Apr 17 20:29:27 HKT 2016
Sun Apr 17 20:29:28 HKT 2016
4.drop索引，并重启mysql，以清空buffer pool缓存数据
alter table sakila.payment drop index payment_date_ix1; 小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.32秒。场景五：
测试在payment_date字段利用ALGORITHM=INPLACE,LOCK=SHARED方式建立索引所需时间，并监控表是否有加锁?
alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=SHARED;
1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件
#!/bin/bash
while : ;do
       date>>/data/mysql/mysql3376/lock.txt &&
mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt
        sleep 1
done
2.会话2执行建索引脚本
(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=SHARED;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.查看lock.txt
cat lock.txt
Sun Apr 17 20:37:07 HKT 2016
Sun Apr 17 20:37:08 HKT 2016
Sun Apr 17 20:37:09 HKT 2016
4.drop索引，并重启mysql，以清空buffer pool缓存数据
alter table sakila.payment drop index payment_date_ix1; 小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.23秒。场景六：
测试在payment_date字段利用ALGORITHM=INPLACE,LOCK=NONE方式建立索引所需时间和是否锁住表上dml操作?
1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件
#!/bin/bash
while : ;do
       date>>/data/mysql/mysql3376/lock.txt &&
mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt
        sleep 1
done
2.会话2执行建索引脚本
(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date),ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.查看lock.txt
cat lock.txt
Sun Apr 17 20:39:56 HKT 2016
Sun Apr 17 20:39:57 HKT 2016
Sun Apr 17 20:39:58 HKT 2016
4.drop索引，并重启mysql，以清空buffer pool缓存数据
alter table sakila.payment drop index payment_date_ix1 小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.17秒。;场景七：
测试在payment_date字段在默认方式建立索引所需时间和是否锁表?
1.会话1先执行每秒监控查询表payment是否有锁，并记录到txt文件
#!/bin/bash
while : ;do
       date>>/data/mysql/mysql3376/lock.txt &&
mysql -uroot -psafe_2016 -S /tmp/mysql3376.sock -e "show open tables from sakila where In_use>0">>/data/mysql/mysql3376/lock.txt
        sleep 1
done
2.会话2执行建索引脚本
(product)root@localhost [(none)]> alter table sakila.payment add index payment_date_ix1 (payment_date);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.查看lock.txt
cat lock.txt
Sun Apr 17 20:44:25 HKT 2016
Sun Apr 17 20:44:26 HKT 2016
Sun Apr 17 20:44:27 HKT 2016
4.drop索引，并重启mysql，以清空buffer pool缓存数据
alter table sakila.payment drop index payment_date_ix1; 小结：监控到表sakila.payment add index在执行建索引过程中无锁表，且建索引时间为0.21秒。总结：
1.ALGORITHM=INPLACE和默认方式在线建索引都不会锁表，而copy方式必需锁表
2.ALGORITHM=COPY,LOCK=NONE两个参数不能同时组合使用，因为copy方式必需要加锁。
3.ALGORITHM=INPLACE方式比ALGORITHM=COPY方式建索引时间至少减少70%。
4.重启mysql方式是否有比较干净清空buffer pool中缓存数据，需进一步验证确认。

推荐阅读

web
通过Web界面管理Linux日志的解决方案

本指南介绍了一种利用rsyslog、MariaDB和LogAnalyzer搭建集中式日志管理平台的方法，使用户可以通过Web界面查看和分析Linux系统的日志记录。此方案不仅适用于服务器环境，还提供了详细的步骤来确保系统的稳定性和安全性。 ... [详细]

蜡笔小新 2024-12-24 17:11:38
buffer
优化MySQL InnoDB的IO性能：配置参数详解

本文深入探讨了如何通过调整InnoDB的关键配置参数来优化MySQL的随机IO性能，涵盖了缓存、日志文件、预读机制等多个方面，帮助读者全面提升数据库系统的性能。 ... [详细]

蜡笔小新 2024-12-27 13:00:29
buffer
使用Vultr云服务器和Namesilo域名搭建个人网站

本文详细介绍了如何通过Vultr云服务器和Namesilo域名搭建一个功能齐全的个人网站，包括购买、配置服务器以及绑定域名的具体步骤。文章还提供了详细的命令行操作指南，帮助读者顺利完成建站过程。 ... [详细]

蜡笔小新 2024-12-26 16:36:34
buffer
MySQL缓存机制深度解析

本文详细探讨了MySQL的缓存机制，包括主从复制、读写分离以及缓存同步策略等内容。通过理解这些概念和技术，读者可以更好地优化数据库性能。 ... [详细]

蜡笔小新 2024-12-26 15:15:06
web
MySQL 数据库迁移指南：从本地到远程及磁盘间迁移

本文详细介绍了如何在不同场景下进行 MySQL 数据库的迁移，包括从一个硬盘迁移到另一个硬盘、从一台计算机迁移到另一台计算机，以及解决迁移过程中可能遇到的问题。 ... [详细]

蜡笔小新 2024-12-26 13:21:38
controller
深入解析JDBC源码

本文详细探讨了JDBC（Java数据库连接）的内部机制，重点分析其作为服务提供者接口（SPI）框架的应用。通过类图和代码示例，展示了JDBC如何注册驱动程序、建立数据库连接以及执行SQL查询的过程。 ... [详细]

蜡笔小新 2024-12-25 19:59:15
web
微软Exchange服务器遭遇2022年版“千年虫”漏洞

微软Exchange服务器在新年伊始遭遇了一个类似于‘千年虫’的日期处理漏洞，导致邮件传输受阻。该问题主要影响配置了FIP-FS恶意软件引擎的Exchange 2016和2019版本。 ... [详细]

蜡笔小新 2024-12-25 14:08:03
js
优化局域网SSH连接延迟问题的解决方案

本文介绍了解决局域网内SSH连接到服务器时出现长时间等待问题的方法。通过调整配置和优化网络设置，可以显著缩短SSH连接的时间。 ... [详细]

蜡笔小新 2024-12-25 11:31:48
web
Transforming the Future of Virtual Worlds

Explore how Matterverse is redefining the metaverse experience, creating immersive and meaningful virtual environments that foster genuine connections and economic opportunities. ... [详细]

蜡笔小新 2024-12-28 09:44:49
version
Java 序列化接口详解

本文深入探讨了 Java 中的 Serializable 接口，解释了其实现机制、用途及注意事项，帮助开发者更好地理解和使用序列化功能。 ... [详细]

蜡笔小新 2024-12-27 15:06:12
web
DNN Community 和 Professional 版本的主要差异

本文详细解析了 DotNetNuke (DNN) 的两种主要版本：Community 和 Professional。通过对比两者的功能和附加组件，帮助用户选择最适合其需求的版本。 ... [详细]

蜡笔小新 2024-12-27 13:14:08
text
使用 SQLiteJDBC 和 HikariCP 实现 Java 程序连接 SQLite 数据库

本文介绍了如何通过 Maven 依赖引入 SQLiteJDBC 和 HikariCP 包，从而在 Java 应用中高效地连接和操作 SQLite 数据库。文章提供了详细的代码示例，并解释了每个步骤的实现细节。 ... [详细]

蜡笔小新 2024-12-26 17:34:42
text
Python 爬虫基础教程及代码实例

根据最新发布的《互联网人才趋势报告》，尽管大量IT从业者已转向Python开发，但随着人工智能和大数据领域的迅猛发展，仍存在巨大的人才缺口。本文将详细介绍如何使用Python编写一个简单的爬虫程序，并提供完整的代码示例。 ... [详细]

蜡笔小新 2024-12-26 10:42:40
email
MySQL LAST_INSERT_ID() 函数深入解析

本文详细介绍了 MySQL 中 LAST_INSERT_ID() 函数的使用方法及其工作原理，包括如何获取最后一个插入记录的自增 ID、多行插入时的行为以及在不同客户端环境下的表现。 ... [详细]

蜡笔小新 2024-12-25 22:04:04
hash
MySQL索引详解与优化

本文深入探讨了MySQL中的索引机制，包括索引的基本概念、优势与劣势、分类及其实现原理，并详细介绍了索引的使用场景和优化技巧。通过具体示例，帮助读者更好地理解和应用索引以提升数据库性能。 ... [详细]

蜡笔小新 2024-12-25 19:52:47

告非言普

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章