MySQL取模索引 mysql索引实战

文章目录

• 09 | 普通索引和唯一索引，应该怎么选择？

• 普通索引与唯一索引的区别
• 什么场合适合普通索引
• changebuffer 和 redo log

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

• 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 | 怎么给字符串字段加索引？

字符串加索引也是常用的场景，比如我们希望使用邮箱进行登录的场景。
一般的解决办法如下：

1. 直接全字段建立索引，优点是查询速度会比较快，并且可以进行范围查找，缺点是占用的空间很大。因为字符串往往比较大。
2. 建立字段的前缀索引，至于前缀的长度主要需要考虑区分度问题。我们可以首先统计当前字符串的种类count(distinct)，然后我们一次计算采用不同长度的前缀可以得到的区分度。选择一个比较合适的。
3. 对于一些有规律的数字，比如身份证号，学号等。前缀都是重复的。可以考虑采用倒叙存储的方法。提高区分度。
4. 额外引入哈希字段，对有规律的字符串计算哈希值，进而实现打散的目标。

但是需要注意，后三种方法都无法使用覆盖索引和范围搜索。

12 | 为什么我的MySQL会“抖”一下？

系统可能会出现突然的不稳定。这有可能是因为脏页的flush造成的。脏页是指内存中对保存的页的数据。很多数据是在内存中改写的，并没有持久化到硬盘中。因此我们需要定期进行flush刷回磁盘。

什么时候会触发flush呢？

1. redo log被写满了。我们为了释放redo log的空间，需要将删除的redo log空间中的脏页进行一下持久化。这里旧体现出了redo log 的好处。redolog是按照物理逻辑记录的数据库变化，因此flush脏页回去的过程其实也是顺序的。一定程度降低了磁盘IO的压力。
2. 内存buffer fool满了，我们需要释放一些新的空间，因此就需要刷回去一些最久没有使用的数据页。
3. mysql在不忙的时候会后台持久化
4. 关机前自己flush

我们需要做的就是根据磁盘的IO能力，设计正确的参数。包括了redolog写入的速度，和buffer区的比例。

另外一个有趣的东西，8.0前的mysql是在刷脏页的时候检查邻居是不是脏页的，也会顺道刷回的。

脏页在flush会硬盘的时候，正常情况与redo log无关的。只有在崩溃的时候会借助redo log

13 | 为什么表数据删掉一半，表文件大小不变？

因为在插入和删除的过程中，其实只是对于部分空间进行了复用。并没有整合数据页。想要真正实现压缩表，需要重建一个表。重建的过程中会涉及到一些锁的问题。因为不是DBA这里不在仔细深究了。

14 | count(*)这么慢，我该怎么办？

通过一个事务单独存储一下行数。对于Innodb引擎，按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(* )，所以我建议
你，尽量使用count()。

16/17 | “order by”是怎么工作的？

涉及到排序相关的问题。这里涉及到的知识点包括全字段排序和rowid排序。以及是否需要临时表协助进行排序。排序主要是使用的归并排序的方法。

对于随机算法也是类似的

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

对于索引的使用是由条件，如果对索引字段进行了函数操作，可能会破坏有序性，就无法借助索引树完成快速查找。比较典型的例子就是字符串会转成数字，或者不同的编码之间的转换。

19 | 为什么我只查一行的语句，也执行这么慢？

语句卡死的情况下，首先需要检查是不是锁的占用。可以在另外一个客户端中检查show process list检查是不是存在别的线程占用了当前任务需要的表。

23 | MySQL是怎么保证数据不丢的？

如果你的MySQL现在出现了性能瓶颈，而且瓶颈在IO上，可以通过哪些方法来提升性能呢？

首先需要明确在事务提交过程中存在两个落盘，redo log 和 binlog。binlog每个线程都有一个binlog cache，然后执行write操作写到binlog 文件系统，最后执行持久化落盘。redo log有一个redo log buffer，在写入到page cache以后，也是需要单独执行落盘操作。为了加速操作，系统有组提交的优化，也就是一次性提交并行的多个文件。

将redo log 持久化的时机有后台1s的轮询，redo log buffer 被写入一半，和其他并行事务commit时机。由于崩溃恢复需要redo log 的prepare状态，因此我们需要持久化prepare的redolog。同时为了尽量拖慢，以尽可能实现组提交，流程可以实现为，

24 | MySQL是怎么保证主备一致的？

如何维护MySQL的高可用性。

同时需要了解binlog 的三种形式，row， statement 和mixed形式，statement形式的row保存的是原始的sql语句，这个方案的优点是存储空间小，缺点是可能出现主备不一致的情况（调用的索引不一致）；row是记录了数据库的具体操作，删除了哪一行的数据等等；mixed是混合，在出现歧义时候采用row。正常的数据库起码使用mixed，或者推荐使用mixed。

25 | MySQL是怎么保证高可用的？

主要讲了主备延迟的问题。在主备切换中，如何保证数据的高可靠性是优先的。

26 | 备库为什么会延迟好几个小时？

主要是备库的多线程复制。如何在备库实现多线程复制？MariaDB是一个思路，在一组提交的事务有一个一样的commit_id，这些事务是不会修改同一行的，因此可以在备库上并行，可以分配给多个并行线程执行。

27 | 主库出问题了，从库怎么办？

需要连接全局事务ID，GTID。这个id是在事务提交时候生成的，是某个事务的身份证；又两部分组成，GTID = sever_UUID:gno，前面的一个是在实例启动时自动生成的，是一个全局唯一值。后一个是一个整数，初始为1，每次提交时事务时分配自动加1。

也就是说每个实例维护了一个事务ID集合，判断自己执行过哪些事务。在进行主备切换的时候，新的主库是A’，从库是B。B会将自己的GTID_set分享给主库，主库会和自己的GTID_set进行比对，寻找差集。并且依次返回在自己这里存在，但是B没有的事务的binlog。对于自己这里不存在的GTID，会返回错误，表示已经删除了。

28 | 读写分离有哪些坑？

读写分离主要是为了分担主库的压力，写的任务还是在主库，但是读取的任务可以分担到从库进行查询。一般分为两种思路，一种是从客户端的底层直接进行负载均衡，这样的好处是效率高一些，一般这样会有一个负责管理后端的组件，如Zookeeper。另外一种思路是在数据库和客户端之间存在一个proxy，由proxy决定路由分发。

读写分离问题有一个大坑是“过期读”问题，也就是由于主库和从库之间存在延迟，可能刚写入的东西无法从从库中查询得到。得到还是过期的信息。

解决这个过期读的方法有一些尝试性的办法，比如，对于要求严格的查询强制走主库；sleep方案，延迟一段时间查询；配合semi-sync方案，在从库应答收到binlog文件之后再给客户端反馈，但是需要注意semi-sync+位点判断的方法只对于一主一从有效；以及比较好的等GTID方案，给客户端返回一个带有事务ID的参数，然后查询某个从库有无，如果超时返回就从主库查询。当然这个方法的关键还是需要限流不然可能压力徒增给主库宕机。

29 | 如何判断一个数据库是不是出问题了？

检测数据库正常的思路：select 1只能检测连接正常；select * from mysql.health_check;可以检测到由于并发线程太多导致的数据库不可用现象；update mysql.health_check set t_modified=now();可以检测到因为binlog磁盘满了导致的commit被堆积的问题。另外还可以调用内部的api，查询每个IO的时间。

31 | 误删数据后除了跑路，还能怎么办？

如何处理数据库的恢复。对于误删行，我们一般可以利用Flashback借助binlog恢复出来。但是对于drop table是无法实现的。对于误删库表的情况，使用全量备份+增量日志的方法也可以恢复到任意时间点。同时还有一些避免的方法，比如是涉及一个延迟复制备库，主动与主库延迟1小时，这样可以弥补1小时内发生的问题。

对于数据库安全，预防大于处理。比如限制好权限，在进行删库删表之前，首先修改表名进行观察，然后再执行删除。

32 | 为什么还有kill不掉的语句？

一般有两种，kill+id 是断掉连接，但是还是需要执行kill query相关的程序逻辑的，比如回滚。因此会出现killed状态。

33 | 我查这么多数据，会不会把数据库内存打爆？

大表的查询的过程中sever层是一边读一边发送的。并不会吃满全部的内存。如果出现sending to client，表示服务器的网络栈被写满了。

Innodb的page cache是一个重点。在Innodb中有一个Buffer pool，可以起到加速查询的作用。如果查询的内存直接在buffer中，可以直接返回。因此我们很关注BP率，不能太低。Innodb采用的是改进以后的LRU（最长时间不用）算法，维护了两个区域，一个是young区一个是old区。在进行全表扫描时候，新读入的页会进入到old区。对于old区回进行判断，如果在1s后没有在进行读取回保持不对，否则回被放入young的头部。这样保证了这种大表读取时候，都是直接在old区，不影响太大整体的BP。

34 | 到底可不可以使用join？

对于join语句的使用需要考虑能不能使用索引优化。比如语句select * from t1 straight_join t2 on (t1.a=t2.a)这个方法中，t1被称为驱动表，t2被称为被驱动表。对于数据库来说，运行这个查询的思路是，首先得到全部的t1.a，然后借助索引t2.a，查找全部的t2的数据。因此时间复杂度不大。称为Index Nested-Loop Join（NLJ）

但是另外一种Block Nested-Loop Join（BLJ），是一种暴力的策略，分块将t1的内容存入，然后读取t2的每一行进行对比。这种方法的复杂度极高。不推荐使用。

结论：如果可以被驱动表可以使用索引的话是可以使用join的。同时为了提高效率，驱动表应该尽量使用小表。

35 | join语句怎么优化？

首先是MRR（Multi-Range Read）优化，也就是对于范围的查询，我们一次性读入多行驱动表的数据，并且在read_rnd_buffer中id进行排序，这样在被驱动表进行查询时候会变为顺序读写，提高速度。MRR优化的核心是，一般是范围查询，然后得到足够多的id之后进行排序，再去主键查询，题现顺序性的优势。

进一步可以BKA（Batched Key Access）优化，这个主要是对使用索引NLJ的优化，从驱动表中一次取出多个记录，然后排序之后再使用主键索引进行优化。

BNL对系统的影响：1. 多次扫描被驱动表，占用IO。2. 占用CPU资源，多次对比条件。3. 可能会导致Buffer Pool的热数据被淘汰（因为读写强度大，1s内被淘汰，无法正常进入young区域），影响BP率。

优化方法：

对于负作用大的BNL，可以采用增加被驱动表的索引，或者构建一个临时表预处理，在临时表上添加索引的方法。使得BNL算法也可以使用BKA优化。

36 | 为什么临时表可以重名？

临时表是具有很好的性质的。创建临时表的语句是creat temporary table t(c int)engine=memory;这里对于内存临时表，使用memory更好。

临时表的性质：

1. 临时表是线程私有的，其他线程是无法看到的。这保证了安全性。
2. 临时表是可以与普通表重名的，并且如果出现出现重名的情况会优先修改临时表。
3. 临时表在线程结束以后会被收回。
4. show table 命令不会显示临时表，show creat会显示临时表。

因此临时表特别时候优化join的情况。比如对于分库分表的场景下，我们可以在每个分库上执行范围查找。然后把分库的结果汇总到一个临时表中，在临时表中再进行筛选。

线程保存临时表的思路其实是维护了一个临时表表名的链表，每次优先检查这个链表。对于主备复制场景，如果使用了binlog_format=row，binlog中是不会存在临时表的信息的。

37 | 什么时候会使用内部临时表？

Union和group by一般会用到内部临时表。因为Union需要判断重复，这里隐含了需要一个唯一索引。这是借助创建一个临时表唯一索引实现的。group by 一般需要统计一个信息，这也是需要额外字段的。

38 | 都说InnoDB好，那还要不要使用Memory引擎？

InnoDB和Memory的区别。InnoDb是索引组织表，数据放在了主键的索引上，其他索引保存的是主键的id。Memory是堆组织表，主键的索引是数据的位置信息。因此相比与Innodb，memory不容易出现空洞，可以随意插入，所有的索引权值一样都是的，主键id是hash索引。

但是建议除了内部临时表，都使用InnoDB表。因为锁粒度问题，M引擎只支持表级锁，另外会有持久化问题，因为数据在内存中。对于内部临时表可以使用M引擎，因为不存在并发和持久化，并且是Hash索引，速度更快。

39 | 自增主键为什么不是连续的？

首先记住结论，自增主键并不一定是连续的，但是一定是递增。导致不是连续的因素有很多，比如唯一索引冲突，事务回滚，自增锁等。自增锁可能是一次性申请下来多个id的。

40 | insert语句的锁为什么这么多？

insert…select语句可能回对全表加锁；对于一边遍历一边更新数据的情况，一般需要临时表；insert的唯一键冲突很奇怪，如果唯一键冲突会上一个读锁，然后回滚。这个是可能导致死锁的。

一个比较好的语句是insert into t values(11, 1, 1) on duplicate key update d = 100插入一行数据，如果遇到唯一键约束就执行后一个逻辑。会优先考虑11的唯一键约束，然后考虑1的。

41 | 怎么最快地复制一张表？

表复制的三个方法，mysqldump，打出csv文件，物理拷贝方法。

42 | grant之后要跟着flush privileges吗？

用户赋权有关的问题。

43 | 要不要使用分区表？