MySQL实战9-45

本文详细探讨了MySQL中的索引选择与优化,包括普通索引和唯一索引的区别,change buffer的工作原理,以及如何选择合适的索引以提高性能。文中还介绍了count(*)操作、全字段排序、order by的实现方式以及如何处理幻读问题。此外,讨论了主从复制的同步策略,以及如何处理数据误删和主库故障。最后,提到了一些性能优化技巧,如避免全表扫描、正确使用join和临时表,以及分区和分表策略。
摘要由CSDN通过智能技术生成

1、普通索引和唯一索引,应该怎么选择?查询过程:普通索引来查找到满足条件的第一个记录  后,需要查找下一个记录,直到碰到第一个不满足条件的记录。对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。性能差距微乎其微。InnoDB 的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在 InnoDB 中,每个数据页的大小默认是 16KB。普通索引正好在最后一条的几率很低

change buffer:当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。change buffer 用的是 buffer pool 里的内存。唯一索引要判断唯一性,需要读取数据进内存,所以用不到change buffer。将数据从磁盘读入内存涉及随机 IO 的访问,是数据库里面成本最高的操作之一。普通索引使用change buffer 因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的。将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作。所以读多写少的数据用change buffer会频繁触发 merge 过程。起了副作用。

merge 流程:从磁盘读入数据页到内存(老版本的数据页);从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个),依次应用,得到新版数据页;写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。到这里 merge 过程就结束了。这时候,数据页和内存中 change buffer 对应的磁盘位置都还没有修改,属于脏页

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

PRIMARY KEY(主键索引)、UNIQUE(唯一索引,允许有空值。如果是组合索引,则列值的组合必须唯一)、INDEX(普通索引)、FULLTEXT(全文索引)

查看索引
show index from tb_wz_all;

2、 MySQL选错索引问题

选错索引肯定是在判断扫描行数的时候出问题了。

MySQL 在执行语句之前,不能精确地知道满足这个条件的记录有多少条,只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越好。而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。也就是说,这个基数越大,索引的区分度越好。基数通过采样统计估算

1、使用 force index(a) 来让优化器强制使用索引 a;2、第二种方法就是,我们可以考虑修改语句,引导 MySQL 使用我们期望的索引。3、第三种方法是,在有些场景下,我们可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。4、analyze table t 命令修正不对的统计信息。

set long_query_time=0;将慢查询日志的阈值设置为 0。

3、怎么给字符串字段加索引?

直接创建完整索引,这样可能比较占用空间;

创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;

倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;

创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。

4、SQL 语句为什么某一次变“慢”了

可能就是在刷脏页(flush):当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。不论是脏页还是干净页,都在内存中。

1、redo log 写满了。这时候系统会停止所有更新操作,把 checkpoint 往前推进,redo log 留出空间可以继续写,推进的部分对应的所有脏页都 flush 到磁盘上。

2、查询语句在内存不够,需要内存的时候可能要求淘汰一个脏页。

flush的时候,整个系统就不能再接受更新了,所以要关注脏页比例,不要让它经常接近 75%。。相邻的数据页也还是脏页的话,也会被放到一起刷,硬盘时代可以减少随机IO。MySQL 8.0 中,innodb_flush_neighbors 参数的默认值已经是 0 了,表示不刷邻居,1则会刷邻居

5、表数据删掉一半,表文件大小不变?

drop table 命令回收表空间,delete 命令是不能回收表空间的,在数据文件当中只是标记为删除,如果需要做空间收缩,为了把表中存在的空洞去掉:重建表:

 MySQL 5.6 版本开始引入的 Online DDL,重建表的流程:建立一个临时文件,扫描表 A 主键的所有数据页;用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中;临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件,对应的就是图中 state3 的状态;用临时文件替换表 A 的数据文件。语句: alter table t engine=InnoDB

Online DDL 最耗时的过程就是拷贝数据到临时表的过程,这个步骤的执行期间可以接受增删改操作。重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗 IO 和 CPU 资源的。因此,如果是线上服务,你要很小心地控制操作时间。如果想要比较安全的操作的话,我推荐你使用 GitHub 开源的 gh-ost 来做。


DDL 过程如果是 Online 的,就一定是 inplace 的;反过来未必,也就是说 inplace 的 DDL,有可能不是 Online 的。截止到 MySQL 8.0,添加全文索引(FULLTEXT index)和空间索引 (SPATIAL index) 就属于这种情况。

6、count(*)过程

MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高;而 InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数(MVCC机制的限制:每一行记录都要判断自己是否对这个会话可见,因此对于 count(*) 请求来说,InnoDB 只好把数据一行一行地读出依次判断,可见的行才能够用于计算“基于这个查询”的表的总行数。优化:In

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值