广告
返回顶部
首页 > 资讯 > 数据库 >MYSQL大表加索引的实现
  • 221
分享到

MYSQL大表加索引的实现

MYSQL大表加索引MYSQL大表索引 2023-05-30 09:05:02 221人浏览 泡泡鱼
摘要

起因是这样的,有一张表存在慢sql,查询耗时最多达到12s,定位问题后发现是由于全表扫描导致,需要对字段增加索引,但是表的数据量600多万有些大,网上很多都说对大表增加索引可能会导致锁表,查阅了一些资料,可以说网上说了很

起因是这样的,有一张表存在慢sql,查询耗时最多达到12s,定位问题后发现是由于全表扫描导致,需要对字段增加索引,但是表的数据量600多万有些大,网上很多都说对大表增加索引可能会导致表,查阅了一些资料,可以说网上说了很多,但是都很笼统,听别人说不如自己去验证,于是开启了验证之旅

首先新建一张表test_page1

CREATE TABLE `test_page1`  (
  `id` int(11)  NULL,
  `username` int(252) not  NULL,
  `passWord` int(252)  NULL,
  `create_time` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci not NULL ,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

第二步,像表中干他个600w条数据这一步网上有很多教程,有通过sql直接在Mysql客户端插入数据,还有通过代码插入数据的,最初为了方便,我是想再mysql客户端直接通过存储过程插入数据,但是插入速度十分感人

MYSQL大表加索引的实现

果断放弃,毕竟600w条,不想等到猴年马月,于是就选择用代码的方式插入,其实就是多费了一些力气而已,上代码,开整

MYSQL大表加索引的实现

public class Connect {
    //    导入驱动jar包或添加Maven依赖(这里使用的是Maven,Maven依赖代码附在文末)
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //  获取数据库连接对象
    public static Connection getConn() {
        Connection conn = null;
        try {
            //  rewriteBATchedStatements=true,一次插入多条数据,只插入一次
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxx?rewriteBatchedStatements=true", "root", "xxx");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }
    //  释放资源
    public static void closeAll(AutoCloseable... autoCloseables) {
        for (AutoCloseable autoCloseable : autoCloseables) {
            if (autoCloseable != null) {
                try {
                    autoCloseable.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}
public class InsertData {
    private static ThreadPoolExecutor getDefaultThreadPool() {
        ThreadPoolExecutor result = new ThreadPoolExecutor(0, 1000, 1, TimeUnit.SECONDS, new SynchronousQueue<>());
        result.setThreadFactory(new ThreadFactory() {
            @Override
            public Thread newThread(Runnable r) {
                return new Thread(r, "deterministic runner thread");
            }
        });
        return result;
    }
    
    public static void main(String[] args) {
            for (int j = 0; j < 100; j++) {
                long start = System.currentTimeMillis();    //  获取系统当前时间,方法开始执行前记录
                Connection conn = Connect.getConn();        //  调用刚刚写好的用于获取连接数据库对象的静态工具类
                String sql = "insert into test_page1 values(null,?,?,?,NOW())";  //  要执行的sql语句
                PreparedStatement ps = null;
                getDefaultThreadPool().execute(() -> {
                    try {
                    PreparedStatement finalPs = conn.prepareStatement(sql);
                        //  不断产生sql
                        for (int i = 0; i < 20000; i++) {
                            finalPs.setString(1, Math.ceil(Math.random() * 1000000) + "");
                            finalPs.setString(2, Math.ceil(Math.random() * 1000000) + "");
                            finalPs.setString(3, UUID.randomUUID().toString());  //  UUID该类用于随机生成一串不会重复的字符串
                            finalPs.addBatch();  //  将一组参数添加到此 PreparedStatement 对象的批处理命令中。
                        }
                        int[] ints = new int[0];//   将一批命令提交给数据库来执行,如果全部命令执行成功,则返回更新计数组成的数组。
                        ints = finalPs.executeBatch();
                        //  如果数组长度不为0,则说明sql语句成功执行,即数据添加成功!
                        if (ints.length > 0) {
                            System.out.println("数据添加成功!!");
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }finally {
                        Connect.closeAll(conn, ps);  //  调用刚刚写好的静态工具类释放资源
                    }
                  });
                long end = System.currentTimeMillis();  //  再次获取系统时间
                System.out.println("所用时长:" + (end - start) / 1000 + "秒");  //  两个时间相减即为方法执行所用时长
            }
    }
}

代码之所以快,很大的原因是由与代码开启了多线程,异步插入,但在实际执行过程中,也会出现问题,比如把插入的数据量搞太大导致了OOM,这个可以修改本地的JVM,另一种就是同时插入太多,数据库连接不够了,导致报错,但这都不是重点,因为我们的重点是大表加索引。代码执行后20分钟内,插入了600w条数据。

这时候就开始我们的验证表演了。

首先,说一下网上描述的大表加索引会出现的问题

  • 如果在执行事务的时候,如果存在目标表的慢sql,这时对目标表增加索引,会导致目标表被锁,进入Waiting for table metadata lock状态,进入Waiting for table metadata lock状态后不能读也不能写
  • 加索引属于DDL操作,DDL操作执行的时候,会对表加锁

然后开始我的尝试先对表加个索引,用时15.19s

alter table test_page1 add index create_time_index(create_time)

MYSQL大表加索引的实现

然后我们开启事务,并对该表执行个慢查询,并对表新建一个索引

BEGIN;
select * from test_page1 where username = 852;
alter table test_page1 add index create_time_index(create_time)

这个慢查询有8s,足够出现问题了,很有信心

MYSQL大表加索引的实现

然而,并没有出现期望的结果,凉凉,难道网上说的都是假的,本身不存在这种情况,苦思之下,似乎找到问题我是通过dbveaer来执行的sql,同事执行两个sql是在两个tab页上执行,会不会是虽然在dbveaer的两个tab页同时执行,但是dbveaer还是一个一个排队执行的sql呢?我想大概率是这样

我又通过dbeaver新建一个数据库连接,让开启事务,并对该表执行个慢查询和对表增加索引在两个连接执行,这时执行show processlist命令,终于复现了

MYSQL大表加索引的实现

加索引命令的进程进入了Waiting for table metadata lock状态网上说Waiting for table metadata lock状态后不能读也不能写,是不是这样呢?,来执行下查询,畅通无阻,所以说网上是错误的,是可以读的,那能不能写呢,我们执行下sql

insert into test_page1(id,username,password,create_time,update_time) values(null,1,2,'6144423733',NOW());

报错了,死锁了Deadlock found when trying to get lock; try restarting transaction,这就验证了无法进行写操作

MYSQL大表加索引的实现

Waiting for table metadata lock状态会持续到什么时候呢,在验证过程中,发现了两种方式第一种,事务提交后,锁状态取消第二种,这种比较神奇,就是刚刚操作过的,对表进行插入操作,这个时候会报错,但是报错后,mysql会自动杀掉事务进程并解锁(这真的很神奇),但是事实就是这样,很糟心。

还有另外一个点要验证就是加索引属于DDL操作,DDL操作执行的时候,会对表加锁,之前我理解错了,以为加锁是表锁,会锁表的数据,但是执行ddl操作时是不会组织数据的写入的,但是另一个连接去执行DDL操作会进入等待状态,这就是多,DDL操作的确会加锁,但是他锁的不是数据而是表结构。

经过一番蛮长的论证,终于验证了什么情况下加索引会锁表,为什么有时候加索引时间会很长,加字段时间会很长,所以,大家加索引最好选择选择在一个业务低峰期加,另外,要注意优化系统,减少系统中慢sql的出现,这样会降低锁表的可能性。

另外如果表被锁住,处于Waiting for table metadata lock状态,这时候我们也可以通过杀掉线程id的方式来解锁,执行show processlist命令,找到线程id,执行kill +id,也能完成解锁。

后记

通过自己实际验证,发现网上说的大部分是正确的,但是没有那么细致,比如解锁的条件是什么,怎么解锁,锁表是锁表结果还是锁数据,实际验证之后得到了很多收获,所以技术还是要深挖

到此这篇关于MYSQL大表加索引的实现的文章就介绍到这了,更多相关MYSQL大表加索引内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

您可能感兴趣的文档:

--结束END--

本文标题: MYSQL大表加索引的实现

本文链接: https://www.lsjlt.com/news/219653.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑,方便收藏和打印~

下载Word文档
猜你喜欢
  • MYSQL大表加索引的实现
    起因是这样的,有一张表存在慢sql,查询耗时最多达到12s,定位问题后发现是由于全表扫描导致,需要对字段增加索引,但是表的数据量600多万有些大,网上很多都说对大表增加索引可能会导致锁表,查阅了一些资料,可以说网上说了很...
    99+
    2023-05-30
    MYSQL大表加索引 MYSQL大表索引
  • mysql 大表加索引或者往大表里面加字段
       通常我们往大表里面添加索引或者添加新字段,不能直接执行,执行时间很长,造成锁表操作,必须借助于pt或者gh-ost等数据库管理工具添加。     例子如下:         pt-online-schema-change -h服务器...
    99+
    2016-06-12
    mysql 大表加索引或者往大表里面加字段
  • mysql添加索引(建表之后)
    一.使用ALTER TABLE语句创建索引 语法如下: alter table table_name add index index_name (column_list) ; alter table table_name add ...
    99+
    2015-06-14
    mysql添加索引(建表之后)
  • MySQL数据库给表添加索引
    说明:当数据库中的记录数过多时,查询速度会显著变慢。此时可以给表创建索引,提高查询速度。 一、创建索引前 我现在有一张表,有1000万条记录,根据username值,查询一条记录,测试下查询时间; s...
    99+
    2023-09-18
    数据库 mysql 数学建模
  • MySQL 大表添加一列的实现
    问题参考自: https://www.zhihu.com/question/440231149 ,mysql中,一张表里有3亿数据,未分表,要求是在这个大表里添加一列数据。数据库不能停,并且还有增删改操作。请问如何操...
    99+
    2022-05-23
    MySQL 大表添加一列
  • mysql在建表语句中添加索引
    普通索引创建 创建普通索引,即不添加 UNIQUE、FULLTEXT 等任何参数。 【例】创建表名为 score 的数据表,并在该表的 id 字段上建立索引,SQL 语句如下: CREATE table score( ...
    99+
    2015-11-16
    mysql在建表语句中添加索引
  • mysql添加索引的方法(Navicat可视化加索引和sql语句加索引)
    使用索引的场景: 阿里云日志里出现了慢sql  然后发现publish_works_id字段会经常用于一些关联,所以决定把这个字段加上索引,优化sql 可视化navicat操作字段加索引,选择字段所在的表,第一步:右键->设计表 第二步:...
    99+
    2023-09-22
    mysql 数据库 sql
  • mysql的联合索引(复合索引)的实现
    联合索引 本文中联合索引的定义为(MySQL): ALTER TABLE `table_name` ADD INDEX (`col1`,`col2`,`col3`); 联合索引的优点 若多个一条SQL,需要多个...
    99+
    2022-05-29
    mysql 联合索引 mysql 复合索引
  • MySQL 索引优化实践(单表)
    目录 一、前言二、表数据准备三、常见业务无索引查询耗时测试3.1、通过订单ID / 订单编号 查询指定订单3.2、查询订单列表 四、订单常见业务索引优化实践4.1、通过唯一索引和普通索引...
    99+
    2023-10-25
    mysql 数据库
  • mysql索引的实现方法
    mysql索引的实现方法?这个问题可能是我们日常学习或工作经常见到的。希望通过这个问题能让你收获颇深。下面是小编给大家带来的参考内容,让我们一起来看看吧!MySQL索引的概念索引是一种特殊的文件(InnoD...
    99+
    2022-10-18
    mysql
  • 如何实现MySQL的索引
    MySQL中索引分三类:B+树索引、Hash索引、全文索引。InnoDB存储引擎中用的是B+树索引。要介绍B+树索引,不得不提二叉查找树、平衡二叉树和B树这三种数据结构。B+树是从它...
    99+
    2022-11-13
    实现MySQL的索引 MySQL的索引
  • Mysql索引覆盖的实现
    目录1.什么是覆盖索引2.覆盖索引为什么快3.SQL优化场景(1)无where条件(2)where条件区分度低(3)查询仅选择主键4.总结与建议1.什么是覆盖索引 通常情况下,我们创建索引的时候只关注...
    99+
    2023-03-03
    Mysql索引覆盖 mysql覆盖索引
  • mysql添加索引的方法
    这篇文章给大家分享的是有关mysql添加索引的方法的内容。小编觉得挺实用的,因此分享给大家做个参考。一起跟随小编过来看看吧。一、使用CREATE INDEX语句可以使用专门用于创建索引的 CREATE IN...
    99+
    2022-10-18
    mysql
  • MySQL主键索引和非主键索引的实现
    目录主键索引(Primary Key Index):非主键索引(Secondary Index):在mysql中,主键索引和非主键索引有不同的作用和特点: 主键索引(Primary Key Index): 主键索引是一种...
    99+
    2023-10-27
    MySQL 主键索引 MySQL 非主键索引
  • MySQL给字符串加一个高效索引的实现
    目录需求前缀索引倒序+前缀索引总结需求 在日常需求中,用户使用手机号或者邮箱登录某一个系统,是一个很常见的操作,那如何在类似手机号或者邮箱这样的字段上建立一个合理的索引呢? 前缀索引 前缀索引,就是以一个字段值的一部分作...
    99+
    2023-03-20
    MySQL字符串高效索引 MySQL字符串索引
  • mysql or走索引加索引及慢查询的作用
    目录 前言一 概述二 实验表结构声明三 Mysql不走索引归类以及详细解析1. 查询条件在索引列上使用函数操作,或者运算的情况2. 查询条件字符串和数字之间的隐式转换3. ...
    99+
    2022-11-13
    mysql or索引 mysql 慢查询的作用
  • mysql or走索引加索引及慢查询的作用
    目录 前言一 概述二 实验表结构声明三 mysql不走索引归类以及详细解析1. 查询条件在索引列上使用函数操作,或者运算的情况2. 查询条件字符串和数字之间的隐式转换3. 特殊修饰符 %%, Or 将不走索引4...
    99+
    2022-09-12
    mysql or索引 mysql 慢查询的作用
  • navicat为表添加索引的方法
    这篇文章主要介绍navicat为表添加索引的方法,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!navicat如何为表添加索引分析常用的查询场景,为字段添加索引,增加查询速度。可以添加...
    99+
    2022-10-18
    navicat 索引 ica
  • 如何实现MySQL中查看表的索引信息的语句?
    标题:如何实现MySQL中查看表的索引信息的语句?在MySQL中,索引是一种非常重要的数据结构,它能够提高查询效率、加速数据检索过程,而对于开发人员和数据库管理员来说,了解表的索引信息是非常重要的。在MySQL中,可以使用SHOW INDE...
    99+
    2023-11-08
    MySQL 索引 查看
  • MySQL索引怎么实现分页探索
    这篇文章主要讲解了“MySQL索引怎么实现分页探索”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL索引怎么实现分页探索”吧!MySQL索引优化之分页探索表结构CREATE ...
    99+
    2023-06-21
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作