精华内容
下载资源
问答
  • mysql联合索引优化(锁表)

    千次阅读 2019-11-14 10:34:43
    1.场景 通过案例,我们能发现77w数据,查100条,时间57s ...遇到这种问题,创建联合索引就行 ...注意,联合索引是偏左原则,如有3 A B C where条件后通过c过滤,而没有A和B,C是不会走索引的 ...

    1.场景

     

     

     通过案例,我们能发现77w数据,查100条,时间57s

    遇到这种问题,创建联合索引就行

     

     

    注意,联合索引是偏左原则,如有3列  A  B   C  where条件后通过c过滤,而没有A和B,C列是不会走索引的

    展开全文
  • UPDATE能走索引还会

    千次阅读 2017-08-23 09:41:02
    叶师傅有次上课过程中执行UPDATE测试案例时,发现虽然WHERE条件已有索引,有时候能利用二级索引进行更新(且只锁定相应必要的几行记录),但有时候却变成了根据主键进行更新,且会。我们先来看看下面的例子...

    导读

    执行UPDATE时,WEHRE条件列虽已有索引,但还会锁全表,肿么回事?

    问题描述

    叶师傅有次上课过程中执行UPDATE测试案例时,发现虽然WHERE条件列已有索引,有时候能利用二级索引进行更新(且只锁定相应必要的几行记录),但有时候却变成了根据主键进行更新,且会锁全表。我们先来看看下面的例子。

    测试表 t1

    CREATE TABLE `t1` (
      `c1` int(10) unsigned NOT NULL DEFAULT '0',
      `c2` int(10) unsigned NOT NULL DEFAULT '0',
      `c3` int(10) unsigned NOT NULL DEFAULT '0',
      `c4` int(10) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`c1`),
      KEY `c2` (`c2`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    表中数据

    +----+----+----+----+
    | c1 | c2 | c3 | c4 |
    +----+----+----+----+
    |  0 |  0 |  0 |  0 |
    |  1 |  1 |  1 |  0 |
    |  3 |  3 |  3 |  0 |
    |  4 |  2 |  2 |  0 |
    |  6 |  8 |  5 |  0 |
    |  7 |  6 |  6 | 10 |
    | 10 | 10 |  4 |  0 |
    +----+----+----+----+

    case1:根据二级索引UPDATE,不锁全表

    • 先看执行计划

    yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=8\G
    *************************** 1. row ***************************
               id: 1
      select_type: UPDATE
            table: t1
       partitions: NULL
             type: range
    possible_keys: c2
              key: c2
          key_len: 4
              ref: const
             rows: 2
         filtered: 100.00
            Extra: Using where
    • 启动两个session执行UPDATE测试

    session1 session 2
    mysql> begin; 
    mysql> update t1 set c4=123 where c2>=8;

    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2 Changed: 2 Warnings: 0


    mysql> begin;
    mysql> select * from t1 where c2 = 7 for update;

    ...
    1 row in set (0.00 sec)
    #直接可得到结果,不会被阻塞

    case2:根据PRIMARY KEY更新,锁全表

    yejr@imysql.com [yejr]>desc update t1 set c4=123 where c2>=6\G
    *************************** 1. row ***************************
               id: 1
      select_type: UPDATE
            table: t1
       partitions: NULL
             type: index
    possible_keys: c2
              key: PRIMARY
          key_len: 4
              ref: NULL
             rows: 7
         filtered: 100.00
            Extra: Using where

    我们能看到本次执行计划是 根据主键索引进行更新,且会锁全表

    • 同样地,启动两个session执行UPDATE测试

    session1 session 2
    mysql> begin; 
    mysql> update t1 set c4=123 where c2>=6;

    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3 Changed: 3 Warnings: 0


    mysql> begin;
    mysql> select * from t1 where c2 = 3 for update;
    #无法得到结果,被阻塞了
    mysql> rollback; 
    #执行rollback,释放锁


    ... 
    1 row in set (4.23 sec) 
    #session1释放锁后才能得到结果

    查看行锁等待情况

    yejr@imysql.com [yejr]>select * from sys.innodb_lock_waits\G
    *************************** 1. row ***************************
                    wait_started: 2017-08-15 15:20:20
                        wait_age: 00:00:17
                   wait_age_secs: 17
                    locked_table: `yejr`.`t1`
                    locked_index: PRIMARY  <--主键上加锁
                     locked_type: RECORD
                  waiting_trx_id: 268350
             waiting_trx_started: 2017-08-15 15:20:20
                 waiting_trx_age: 00:00:17
         waiting_trx_rows_locked: 2
       waiting_trx_rows_modified: 0
                     waiting_pid: 13
                   waiting_query: select * from t1 where c2 = 3 for update
                 waiting_lock_id: 268350:387:3:4
               waiting_lock_mode: X
                 blocking_trx_id: 268349
                    blocking_pid: 12
                  blocking_query:  NULL
                blocking_lock_id: 268349:387:3:4
              blocking_lock_mode: X
            blocking_trx_started: 2017-08-15 15:20:18
                blocking_trx_age: 00:00:19
        blocking_trx_rows_locked: 8  <-- 所有记录都被加锁了
      blocking_trx_rows_modified: 3  <---持有锁的事务更新了3行记录
         sql_kill_blocking_query: KILL QUERY 12
    sql_kill_blocking_connection: KILL 12

    问题分析

    好了,案例说完了,也该说原因了。

    肾好的同学可能记得我说过一个结论:当MySQL预估扫描行数超过全表总数约 20% ~ 30% 时,即便有二级索引,也会直接升级为全表扫描

    这个结论的原因并不难理解,二级索引的顺序和主键顺序一般来说是不一样的,根据二级索引的顺序回表读数据时,实际上对于主键则很可能是随机扫描,因此当需要随机扫描的数量超过一定比例时(一般是20% ~ 30%),则优化器会决定直接改成全表扫描

    上述说法出处:WHERE Clause Optimization(https://dev.mysql.com/doc/refman/5.7/en/where-optimization.html

    Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size.

    不过,上面这个结论是针对读数据的情况,UPDATE/DELETE修改数据时是否也这样呢?

    答案是肯定的,要不然上面的测试结果怎么解释......

    按照官方开发者的说法,当优化器评估根据二级索引更新行数超过约50%(从上面测试结果来看,其实20% ~ 30%就已经是这样了,不过这个比例并不是固定值,会根据各种代价计算自动调整)就会改成走主键索引,并且锁全表,这么做是既定的策略,原因和上面一样,也是为了提高检索效率。

    总结

    老调重弹,几点建议:

    • 不管检索还是更新,都尽可能利用索引;

    • 不要一次性检索或更新大批量数据,建议分城多批次;

    • 事务尽快提交,降低行锁持有时间及其影响。

    本文提到的问题,我也提了个BUG#87021,有兴趣的可以去看看。

    延伸阅读


    知识无界限,不再加原创

    喜欢就转走,铁粉加密圈

    640?wx_fmt=png


    好铁观音尽在

    「老叶茶馆」

    http://yejinrong.com

    640?wx_fmt=png

    展开全文
  • 前段时间, 自动化测试的小姐姐向我们开发人员反应, 由于我方经常debug导致锁表, 她们的用例经常失败. 该问题出现的时机基本一致,通过show OPEN TABLES where In_use > 0;以及show processlist, 确定sql. 但是奇怪...

    事件经过:

    前段时间, 自动化测试的小姐姐向我们开发人员反应, 由于我方经常debug导致锁表, 她们的用例经常失败.
    该问题出现的时机基本一致,通过show OPEN TABLES where In_use > 0;以及show processlist, 确定sql.
    但是奇怪的是这次的事故竟然是把整个表锁了, 而不是以往只是某条数据被锁住(开发和测试未单独分库, 调试时的某些数据偶尔会导致锁表情况).
    对sql进行分析后发现, 这条update后面的where竟然没有走索引!!!

    原因:

    我们使用mysql时一般使用的都是默认引擎innoDB.
    而innoDB的行锁是通过给索引上的索引项枷锁来实现的, 而不是针对记录来加锁的.
    假如在操作数据的时候, 没有索引, 是无法使用行级锁的, 此时使用的是表锁. (小伙伴们这下明白对mysql来说索引多重要了吧)

    引申

    同时引申一个有趣的现象. 假如多个线程操作的是不同数据, 也用了索引, 但是为什么还是锁表了呢?
    原因还是上面这个, 行锁是在索引上的. 假如你where条件后面分别是: code=“001” and name=“xx” 和code=“001” and name=“yy”, 索引在code上, 那么还是会出现锁表的.
    例如:
    tdb_goods 表中brand_name上有索引, 而goods_price字段无索引

    在线程A中执行
    START TRANSACTION;
    UPDATE tdb_goods SET is_show=1 WHERE brand_name='苹果' AND goods_price='3388.000';
    然后在A执行COMMIT;之前在另一个线程B中开启一个事务并执行UPDATE tdb_goods SET is_show=1 WHERE brand_name='苹果' AND goods_price='2288.000';
    此时线程Bmysql会提示:
    错误代码: 1205
    Lock wait timeout exceeded; try restarting transaction
    

    那么
    另外需要注意一点: 如果表比较小, 即使有索引, mysql也不一定会走索引而是查全表.

    展开全文
  • mysql中for update没有命中索引锁表吗?

    千次阅读 热门讨论 2019-03-24 10:36:51
    前天和同事饭后散步的时候他说使用for update如果没有命中索引就会锁表,刚好最近要写类似的代码,听了之后心有戚戚,于是乎来尝试一下。 结构: 数据: 第一个事务:SELECT * FROM student WHEREno= '325985' ...

    前天和同事饭后散步的时候他说使用for update如果没有命中索引就会锁表,刚好最近要写类似的代码,听了之后心有戚戚,于是乎来尝试一下。

    表结构:
    学生表
    数据:
    数据
    第一个事务:SELECT * FROM student WHEREno= '325985' FOR UPDATE;
    在这里插入图片描述
    第二个事务:SELECT * FROM student WHEREno= '456325' FOR UPDATE;
    在这里插入图片描述
    可以看的出来,在第一个事务中,我只锁了no为325985的记录,但在第二个事务中,no为456325却无法查询,说明没有命中索引确实会锁表

    加上索引后试试:
    在这里插入图片描述
    第一个事务:
    在这里插入图片描述
    第二个事务:
    在这里插入图片描述
    都可以查出来,说明没有锁表。

    还有一种特殊情况,就是如果where之后的条件是索引,但数据是不存在的,这种情况会锁表吗?推论:既然数据不存在,那么也无法命中索引,那么也会锁表。不过不能光想不做,让我们一起试试吧。

    加一个联合唯一索引:
    在这里插入图片描述
    第一个事务:SELECT * FROM student WHEREname= '李不飞' ANDno= '325985' AND gender = 2 AND class = 13 AND grade = 3 FOR UPDATE;

    在这里插入图片描述
    第二个事务:SELECT * FROM student WHEREname= '李飞飞' ANDno= '325985' AND gender = 2 AND class = 13 AND grade = 3 FOR UPDATE;
    在这里插入图片描述
    看来我的推论是错误的,如果这条数据不存在,是不会锁表的

    结论:如果for update没有命中索引会锁表。

    补充一点,update更新语句和for update机制一样,没有命中索引,同样会锁表。

    展开全文
  • 索引创建时在上放置一个独占,并等待所有其他扫描的、使用索引分区的并发进程结束,然后才创建附属索引。如果这个正在被读取或更新,则  CREATE INDEX ONLINE  语句等待独占。 如果  LOCK MODE  ...
  • mysql5.6创建索引导致锁表阻塞查询

    千次阅读 2017-11-01 10:15:00
    结论:添加索引时,若果有的慢查询,会导致索引添加延时等待 添加索引语句:alter table tb_name add index idx_xx(col_name); 执行添加索引的SQL: 通过show processlist; 发现有锁等待的线程: | ...
  • Bitmap位图索引与普通的B-Tree索引锁的比较 通过以下实验,来验证Bitmap位图索引较之普通的B-Tree索引锁的“高昂代价”。位图索引会带来“位图段级”,实际使用过程一定要充分了解不同索引带来的代价情况。 ...
  • mysql update到底锁不

    千次阅读 2020-01-04 23:17:12
    最近工作遇到一个需要批量update的事情,数据量大概有十几万,不算大,但因为操作的是一个使用非常频繁的业务,所以必须考虑锁表的问题,那么update到底会不会锁表呢?无图无真相,直接上图。 运行环境:mysql ...
  • MySQL的索引、事务和

    千次阅读 2021-03-08 16:57:18
    索引类似字典中的音序,用于快速找到记录的一种数据结构。 优势:可以快速检索,减少 I/O 次数,加快检索速度;根据索引分组和排序,可以加快分组和排序。 劣势:本身也是,也会占用存储空间。会降低数据的...
  • 乐观:假定大概率不会发生并发更新冲突,访问、处理数据过程中加锁,只在更新数据时再根据版本号或时间戳判断是否有冲突,有则处理,无则提交事务; 悲观:假定大概率会发生并发更新冲突,访问、处理数据前就...
  • 4.1、innodb存储引擎类型 innodb类似oracle的IOT(索引聚集-indexorganized table)...首先中是否有唯一非空索引(unique not null),如果有则该即为主键。 符合上述条件,innodb存储引擎会自动创建一
  • MySql 知识点——索引、事务

    千次阅读 2018-05-16 21:23:29
    降低更新表的速度。 注意点:小表使用全表扫描更快,中大表才使用索引。超级大表索引基本无效。 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通...
  • MySQL存储过程、函数、视图、触发器、索引的基本知识 更多精彩请访问本文源地址: https://blog.csdn.net/zixiao217 高山仰止 了解视图的使用 了解存储过程、函数的创建和使用 了解触发器的创建和使用...
  • Mysql的索引

    千次阅读 2019-02-02 10:42:49
    索引在数据库中可以说是非常重要的知识点了,在面试中也会经常会被问到的。本文力求简单讲清每个知识点,希望大家看完能有所收获。声明:如果没有说明具体的数据库和存储引擎,默认指的是MySQL中的InnoDB存储...
  • mysql innodb索引

    千次阅读 2018-08-06 01:51:56
    目前公司购买的是阿里云的rds服务,旗舰版的顶配目前是2000个G,...所以,接下来汇总下,我的一些mysql的一些理论和经验。 MVCC 并发控制协议 vs 基于的并发控制 innodb实现的是基于多版本的并发控制协议(Mu...
  • MySQL之事务、索引

    千次阅读 2016-05-20 17:24:53
    Mysql之事务、索引 事务 索引 事务概念
  • MongoDB 创建索引导致库的解决方案

    千次阅读 2017-11-10 14:56:03
    MongoDB 创建索引导致库的解决方案 背景描述 300G 的数据创建索引,执行 db.collection.ensureIndex({key:1}) 之后,打开另一个终端,任何操作都能执行。 根本原因 在数据库建立索引时,默认时 “foreground...
  • MySQL的InnoDB存储引擎行锁是加在索引上的,所以只当增删改查操作是通过索引找到指定数据行的时候,才能相应数据行的索引加锁,否则只能整个加表锁,共享读独占写。当一个事务经过索引查询数据,...
  • 但这并意味着索引越多越好,因为索引会占用内存,还需要维护,并且索引还会影响增删改速度。 所以我们创建索引时有什么能参考的属性,或者要遵守的原则呢? 1.的离散度 我们先来看一个重要的属性的离散度,...
  • mysql中 myisam,innodb默认使用的是 Btree索引,至于btree的数据结构是怎样的都重要, 只需要知道结果,既然是索引那这个数据结构最后是排好序;就像新华字典他的目录就是按照a,b,c…这样排好序的; 所以你在找...
  • 2、当更新位图所在的时,由于要在不同的索引条目之间修改bit位,比如将第一条记录从01变为02,则必须将01所在的索引条目的第一个bit位改为0,再将02所在的索引条目的第一个bit位改为1。因此,
  • MySQL分区索引

    千次阅读 2020-03-03 20:26:29
    MySQL分区索引。 讲了一下分区的本质与数据库的三种索引,以及最常见的索引,B树索引
  • ... 一、索引 ... 经常进行INSERT/UPDATE/DELETE操作就不要建立索引了,换言之:索引会降低插入、删除、修改等维护任务的速度; 索引需要占物理和数据空间; 了解过索引的最左匹配原则; ...
  • 按照索引规则,如果能使用索引行,能使用索引锁表。 (2)Insert时,行。 一、Update操作 1. 实验一 (1)创建和基础数据,id是主键,如下图: (2)在navicat中,新建...
  • 查看当前系统状态: ...使用db2inst1用户登陆数据库,在schema SYSIBMADM下视图LOCK_HELD当前和视图LOCKWAITS等待 ...实际上在schema ...实际上系统内所有的以及和视图信息也在这个schema下
  • 外键上是否需要索引

    千次阅读 2018-07-05 21:07:45
    外键上是否需要索引 其实这个问题应该算是老生常谈了。这两天看concept看到这里,于是就在说说这个问题。...其实我认为需要那么麻烦,与增加一个索引所带来的性能开销和磁盘空间开销相比,确实索引可能...
  • 索引的数据结构分析,数据库面试到索引最常见的问题分析,我总结了一下。
  • innodb mysql的索引

    千次阅读 2013-12-30 16:49:20
    InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级,否则,...
  • 索引降低并发性

    千次阅读 2007-01-01 10:46:00
    如果有2 0 0个用户打算修改不同顾客的数据,仅存储单个顾客信息的单一行进行加锁要比住整个好得多。那么,用户如何只锁定行而不是呢?当然是使用索引了。正如前面所提到的,存有要修改数据的字段使用索引...
  • 但,不当的使用会使InnoDB的行级变成,给我们带来较大的困扰(select、update、delete都可能导致)。 二、索引类型 InnoDB的索引有两类:聚集索引(Clustered Index)与普通索引(Secondary Index)。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 110,756
精华内容 44,302
关键字:

对索引列更新不锁表