那海蓝蓝的博客_MySQL优化

MySQL update where+子查询案例分析

今天接到开发同学需求，需要修改一批数据，SQL如下：updatefinance_income_flowsetsettle_batch_id=2096,settle_status=400,settle_time='2022072020:00:00',update_time=now()whereidin(selectb.idfromsettle_studio_incomea,finance_incom

sql

执行计划

子查询

原创 2022-07-21 18:23:30 1649 阅读

记录一次生产环境MySQL索引失效的排查过程

早上收到开发同学求助，有个SQL查询耗时特别长，看了执行计划发现没有走索引，但是不知道原因在哪里，我们一起来分析一下。mysqlexplainSELECTFROMartisan_incomeWHEREparent_idIN(222645481,222583953,222181775,222180931,222081126,221678753,221616102,221591783,22121931

mysql

执行计划

原创 2022-04-08 18:19:47 511 阅读

pt-online-schema-change和MySQL原生online DDL对比

测试环境：MySQL5.7pt-online-schema-change 3.2.0第一次加索引，使用MySQL原生的online DDL。做一个超级慢的update，模拟长事务。1mysql> update test set user_id='aa' where user_id like '%123%';此时开始加索引：1mysql> alter table test add ind

java

原创 2021-04-10 15:39:53 308 阅读

MySQL自增主键和随机字符串主键insert性能对比

测试环境：MySQL 5.7创建测试表：123456789101112mysql> create table test1 -> (id bigint auto_increment, -> aaa varchar(40), -> PRIMARY key(id) -> );Query OK, 0 rows affected (0.01 sec)my

java

原创 2021-04-10 15:39:12 1104 阅读

MySQL索引之cardinality

查看一个表的索引：12345678910111213141516171819mysql> show index from rank_item;+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+----

java

原创 2021-04-10 15:25:18 733 阅读

Innodb关键特性之启动、关闭和恢复

参数innodb_fast_shutdown影响着表的存储引擎为Innodb的行为，该参数可以取值为0、1、2，默认值为1，支持全动态局设置。设置为1：关闭MySQL的时候不会做清除脏页和插入缓冲区的合并操作，也不会将脏页刷新到磁盘。设置为0：会做清除脏页和插入缓冲区的合并操作，也会将脏页全部刷新到磁盘上面去，但是这个时候关闭的速度也是最慢的。设置为2：不会做清除脏页和插入缓冲区的合并操作，也不会

java

原创 2021-04-10 15:24:52 563 阅读

Innodb关键特性之刷新邻接页

1、工作原理　　当刷新一个脏页时，innodb存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，增大写入量，减少了物理写IO，故该工作机制在传统机械磁盘下有着显著的优势。　　1、在写入次数基本不增加的情况下，增加了写入的量；　　2、加速了脏页的回收；　　3、充分利用double write每次1

java

原创 2021-04-10 15:23:12 260 阅读

Innodb关键特性之异步IO（AIO）

1、关于AIO与SIO　　为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作。　　1、异步IO：用户可以在发出一个IO请求后立即再发出另外一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成，这就是AIO。　　2、与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。2、开启异步IO　　首先OS要有异步io，且开启，然后mys

java

原创 2021-04-10 15:22:48 598 阅读

Innodb关键特性之Adaptive Hash Index

众所周知，InnoDB使用的索引结构是B+树，但其实它还支持另一种索引：自适应哈希索引。哈希表是数组+链表的形式。通过哈希函数计算每个节点数据中键所对应的哈希桶位置，如果出现哈希冲突，就使用拉链法来解决。更多内容可以参考从以上可以知道，哈希表查找最优情况下是查找一次.而InnoDB使用的是B+树，最优情况下的查找次数根据层数决定。因此为了提高查询效率，InnoDB便允许使用自适应哈希

java

原创 2021-04-10 15:22:08 687 阅读

Innodb关键特性之doublewrite

一、经典Partial page write问题介绍double write之前我们有必要了解partial page write（部分页失效）问题。 InnoDB的Page Size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以Page为单位进行操作的。我们知道，由于文件系统对一次大数据页（例如InnoDB的16KB）大多数情况下不是原子操作

java

原创 2021-04-10 15:21:31 366 阅读

Innodb关键特性之Insert Buffer

一、Insert Buffer Innodb存储引擎中，主键是唯一的标识符，如果主键是递增的，那么主键插入null会自动增长，同时插入的数据按照顺序存放。如果主键不是自增的，例如UUID这样的，那么插入和辅助索引一样，是随机的。对于非聚集索引，每次插入都要离散读，大大降低了插入的效率。 Innodb引入了Insert Buffer。对于非聚集索引的插入或者更新操作

java

原创 2021-04-10 15:20:56 242 阅读

MySQL · 性能优化· InnoDB buffer pool flush策略漫谈

背景我们知道InnoDB使用buffer pool来缓存从磁盘读取到内存的数据页。buffer pool通常由数个内存块加上一组控制结构体对象组成。内存块的个数取决于buffer pool instance的个数，不过在5.7版本中开始默认以128M（可配置）的chunk单位分配内存块，这样做的目的是为了支持buffer pool的在线动态调整大小。Buffer pool的每个内存块通过mmap的

java

原创 2021-04-10 15:20:10 495 阅读

innodb新特性之buffer pool动态调整大小

5.7 开始支持buffer pool 动态调整大小，每个 buffer_pool_instance都由同样个数的chunk组成(chunks数组), 每个chunk内存大小为 innodb_buffer_pool_chunk_size(实际会偏大5%，用于存放chuck中的block信息)。buffer pool以 innodb_buffer_pool_chunk_size为单位进行动态增大和缩

java

原创 2021-04-10 15:19:49 746 阅读

innodb新特性之buffer pool预热

背景innodb buffer pool做为innodb最重要的缓存，其缓存命中率的高低会直接影响数据库的性能。因此在数据库发生变更，比如重启、主备切换实例迁移等等，innodb buffer pool 需要一段时间预热，期间数据库的性能会受到明显影响。另外mysql 5.7以前innodb buffer pool缓存大小修改不是动态的，重启才能生效。因此innodb buffer pool的预

java

原创 2021-04-10 15:19:31 758 阅读

关于Mysql 的 ICP、MRR、BKA等特性

一、ICP( Index_Condition_Pushdown)对 where 中过滤条件的处理，根据索引使用情况分成了三种：如果WHERE条件可以使用索引，MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。ICP能减少Server层访问存储引擎的次数和引擎层访问基表的次数。session级别设置：set optimizer_switch="index_c

java

原创 2021-04-10 15:19:06 405 阅读

MySQL索引提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。MySQL数据库支持索引提示(INDEX HINT)显式的告诉优化器使用了哪个索引。有以下几种情况可能用到索引提示：1、MySQL数据库的优化器错误的选择了某个索引，导致SQL运行很慢。这个在情况比较少见。优化器在绝大部分情况下工作的非常有效和正确。2、某些SQL语句可以选择的索引非常多

java

原创 2021-04-10 15:15:34 418 阅读

为什么优化器选择了聚集索引？

通过这个以下这个案例，来说明优化器在选择索引时候的取舍。查看表结构：12345678910111213MySQL > show create table test2 \G*************************** 1. row *************************** Table: test2Create Table: CREATE TABLE `tes

java

原创 2021-04-10 15:15:09 136 阅读

MySQL count(*)之索引选择

覆盖索引对于一些统计问题，如下：123456789101112131415161718MySQL > show create table test1 \G*************************** 1. row *************************** Table: test1Create Table: CREATE TABLE `test1` ( `

java

原创 2021-04-10 15:14:34 584 阅读

MySQL单列索引和联合索引

本文通过一个案例，介绍优化器对单列索引和联合索引的选择。order表的ord_seq字段上有2个索引，单列索引（ order_seq）和联合索引（order_seq,order_type）123456MySQL > explain select * from `order` where order_seq = 1502131212577;+----+-------------+-------

java

原创 2021-04-10 15:14:00 479 阅读

MySQL SHOW INDEX 语法解析

本文对show index from tablename;语法进行分析。如：show index from `order`1、Table 表名2、Non_unique 如果索引不能包括重复值则为0，如果可以则为1。也就是平时所说的唯一索引。3、Key_name 索引名称，如果名字相同则表明是同一个索引，而并不是重复，比如上图中的第二、三条数据，索引名称都是index_fID_lastTi

java

原创 2021-04-10 15:13:35 206 阅读

MySQL为什么有时候会选错索引？

今天在生产环境中看到一个慢SQL，是个核心业务表，数据1300万+看一下表索引：123456789101112131415mysql>show index from `order`+-----------------+----------------------+--------------------+------------------------+-------------------

java

原创 2021-04-10 15:13:06 230 阅读

SQL优化之limit分页（抛弃OFFSET）

Limit和OFFSET的问题，其实就是 OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果使用书签可以记录前一次取到的数据的位置。那么下次就可以直接从记录的位置开始扫描。下面看一个案例：12345678910111213141516171819202122232425mysql>select id,product_id from product order

java

原创 2021-04-10 15:12:44 1338 阅读

SQL优化之limit分页（延迟关联）

MySQL分页查询最头疼的问题是偏移量非常大，比如limit 10000,20，就要检索出10020条记录，值返回最后20条，前边的10000行全部抛弃掉。对于检索字段非常多的情况下，效率更为糟糕。1234567891011121314151617181920212223242526272829 SELECT id, order_seq, product_id, use

java

原创 2021-04-10 15:12:13 1264 阅读

MySQL优化之覆盖索引的使用

查看测试表结构：123456789101112131415161718192021222324252627mysql> show create table im_message \G*************************** 1. row *************************** Table: im_messageCreate Table: CREATE

java

原创 2021-04-10 15:11:16 163 阅读

mysql count()的使用解析

查看表结构：123456789101112131415161718mysql> show create table coupon_use_test \G*************************** 1. row *************************** Table: coupon_use_testCreate Table: CREATE TABLE `co

java

原创 2021-04-10 15:08:57 159 阅读

mysql优化max()函数

查看表结构：1234567891011121314151617mysql> show create table coupon_use_test \G*************************** 1. row *************************** Table: coupon_use_testCreate Table: CREATE TABLE `coup

java

原创 2021-04-10 15:08:26 1220 阅读

Innodb重要参数优化

1.简单介绍InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的

java

原创 2021-04-10 15:07:28 237 阅读

计算innodb_buffer_pool使用率

两种方法：一、通过show status1234567891011121314mysql> show status like 'innodb_buffer_pool_pages_data';+-------------------------------+---------+| Variable_name | Value |+---------------

java

原创 2021-04-10 15:05:51 636 阅读

SQL优化之多表关联查询-案例一

慢SQL日志里看到一个三张表的关联查询，如下：1234SELECT COUNT(1) FROM refund_order_item i, artisan a, user u WHERE u.userid = i.user_id AND a.artisan_id = i.artisan_id;测试查询时间：12345678910mysql> SELECT COUNT(1) -&g

java

原创 2021-04-10 15:05:18 736 阅读

SQL优化之利用索引排序

近期在做数据库的慢SQL优化，从数据库慢日志看到一个SQL执行时间大概1.7s，高峰时候达到十几秒12345678910111213mysql> SELECT id, service_seq, order_item_seq, status, order_time_type -> , reserve_time, user_name, user_contact, service_ad

java

原创 2021-04-10 15:03:04 335 阅读

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

51CTO博客

瀚东的博客

MySQL update where+子查询案例分析

记录一次生产环境MySQL索引失效的排查过程

pt-online-schema-change和MySQL原生online DDL对比

MySQL自增主键和随机字符串主键insert性能对比

MySQL索引之cardinality

Innodb关键特性之启动、关闭和恢复

Innodb关键特性之刷新邻接页

Innodb关键特性之异步IO（AIO）

Innodb关键特性之Adaptive Hash Index

Innodb关键特性之doublewrite

Innodb关键特性之Insert Buffer

MySQL · 性能优化· InnoDB buffer pool flush策略漫谈

innodb新特性之buffer pool动态调整大小

innodb新特性之buffer pool预热

关于Mysql 的 ICP、MRR、BKA等特性

MySQL索引提示

为什么优化器选择了聚集索引？

MySQL count(*)之索引选择

MySQL单列索引和联合索引

MySQL SHOW INDEX 语法解析

MySQL为什么有时候会选错索引？

SQL优化之limit分页（抛弃OFFSET）

SQL优化之limit分页（延迟关联）

MySQL优化之覆盖索引的使用

mysql count()的使用解析

mysql优化max()函数

Innodb重要参数优化

计算innodb_buffer_pool使用率

SQL优化之多表关联查询-案例一

SQL优化之利用索引排序