文章目录
- 09 | 普通索引和唯一索引,应该怎么选择?
- 10 | MySQL为什么有时候会选错索引?
- 11 | 怎么给字符串字段加索引?
- 12 | 为什么我的MySQL会“抖”一下?
- 13 | 为什么表数据删掉一半,表文件大小不变?
- 14 | count(*)这么慢,我该怎么办?
- 16/17 | “order by”是怎么工作的?
- 18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
- 19 | 为什么我只查一行的语句,也执行这么慢?
- 23 | MySQL是怎么保证数据不丢的?
- 24 | MySQL是怎么保证主备一致的?
- 25 | MySQL是怎么保证高可用的?
- 26 | 备库为什么会延迟好几个小时?
- 27 | 主库出问题了,从库怎么办?
- 28 | 读写分离有哪些坑?
- 29 | 如何判断一个数据库是不是出问题了?
- 31 | 误删数据后除了跑路,还能怎么办?
- 32 | 为什么还有kill不掉的语句?
- 33 | 我查这么多数据,会不会把数据库内存打爆?
- 34 | 到底可不可以使用join?
- 35 | join语句怎么优化?
- 36 | 为什么临时表可以重名?
- 37 | 什么时候会使用内部临时表?
- 38 | 都说InnoDB好,那还要不要使用Memory引擎?
- 39 | 自增主键为什么不是连续的?
- 40 | insert语句的锁为什么这么多?
- 41 | 怎么最快地复制一张表?
- 42 | grant之后要跟着flush privileges吗?
- 43 | 要不要使用分区表?
本文的内容将会更加偏向与实战。主要是记录一些mysql问题的解决方法和经验。可能不会过分的涉及背后的原理。
09 | 普通索引和唯一索引,应该怎么选择?
如果业务需求已经可以满足,优先选择普通索引。
普通索引与唯一索引的区别
在进行插入的时候,普通索引不需要额外的检查。直接在change buffer中写入更新的信息。等到需要读取相关信息的时候,再将数据页读入并且写入更新。
而唯一索引需要首先从磁盘中读入数据页进行重复性判断(实际上是完成约束),然后才能直接向内存中写入。
因此普通索引的效率很高。
什么场合适合普通索引
典型的就是保存历史记录,最新的记录可以是唯一索引的,但是我们还需要维护一个历史记录的表格。这个表格中唯一性已经得到了满足,因此我们可以大胆的使用普通索引。
同时我们需要注意普通索引适用于写多读少的场景。因为在每次读取时候,系统会从磁盘中拿出数据页并且将change buffer的数据添加上去。
changebuffer 和 redo log
如果要简单地对比这两个机制在提升更新性能上的收益的话,redo log 主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的IO消耗。
10 | MySQL为什么有时候会选错索引?
mysql选择索引的依据
mysql选择索引的核心目标是最小使用资源,因此具体的标准是多方面的,包括了选择一个基数(cardinality)大的索引,计算可能需要遍历的行数,以及是否会用到临时表,是否排序,是否回表等因素。
基数(cardinality)是衡量一个索引区分度大不大的一个标准。mysql是采用采样估算的方法,取N页的数据,然后计算有多少不同的索引。然后按照这个数据计算全局的基数。当然这个数值是会更新的,每当行数据的变化达到1/M分之一的时候,数据库就会重新进行一次统计,计算索引新的基数。
数据库还会考虑大概需要遍历的行数,以及是否回表等信息
这些都可以使用analyze table t
的指令得到比较完整精确的索引信息
解决选错索引的方法
首先如果我们明确的知道该选择哪个索引的话,我们可以使用指定索引的方法。force index(a)
。除此以外还可以诱导数据库做出一些正确的选择。
11 | 怎么给字符串字段加索引?
字符串加索引也是常用的场景,比如我们希望使用邮箱进行登录的场景。
一般的解决办法如下:
- 直接全字段建立索引,优点是查询速度会比较快,并且可以进行范围查找,缺点是占用的空间很大。因为字符串往往比较大。
- 建立字段的前缀索引,至于前缀的长度主要需要考虑区分度问题。我们可以首先统计当前字符串的种类
count(distinct)
,然后我们一次计算采用不同长度的前缀可以得到的区分度。选择一个比较合适的。 - 对于一些有规律的数字,比如身份证号,学号等。前缀都是重复的。可以考虑采用倒叙存储的方法。提高区分度。
- 额外引入哈希字段,对有规律的字符串计算哈希值,进而实现打散的目标。
但是需要注意,后三种方法都无法使用覆盖索引和范围搜索。
12 | 为什么我的MySQL会“抖”一下?
系统可能会出现突然的不稳定。这有可能是因为脏页的flush造成的。脏页是指内存中对保存的页的数据。很多数据是在内存中改写的,并没有持久化到硬盘中。因此我们需要定期进行flush刷回磁盘。
什么时候会触发flush呢?
- redo log被写满了。我们为了释放redo log的空间,需要将删除的redo log空间中的脏页进行一下持久化。这里旧体现出了redo log 的好处。redolog是按照物理逻辑记录的数据库变化,因此flush脏页回去的过程其实也是顺序的。一定程度降低了磁盘IO的压力。
- 内存buffer fool满了,我们需要释放一些新的空间,因此就需要刷回去一些最久没有使用的数据页。
- mysql在不忙的时候会后台持久化
- 关机前自己flush
我们需要做的就是根据磁盘的IO能力,设计正确的参数。包括了redolog写入的速度,和buffer区的比例。
另外一个有趣的东西,8.0前的mysql是在刷脏页的时候检查邻居是不是脏页的,也会顺道刷回的。
脏页在flush会硬盘的时候,正常情况与redo log无关的。只有在崩溃的时候会借助redo log