MySQL深入学习笔记

时间：2025-01-21 08:18:56

本文是阅读《MySQL技术内幕-InnoDB存储引擎》第二版,《MySQL DBA修炼之道》,以及《MySQL王者晋级之路》所记录的笔记.

InnoDB体系架构

线程

  • 主线程(Master Thread):将缓冲池中的数据异步刷新到磁盘.
  • IO线程(IO Thread):处理异步IO的请求以及回调.
  • 清理线程(Purge Thread):回收已经使用但并未分配的Undo页.
  • 页清理线程(Page Cleaner Thread):负责脏页的刷新操作

内存

LRU列表用来管理已经读取的页,如果LRU列表里读取的页被修改,那么这个时候内存和磁盘的数据不一致就会产生脏页.
脏页由FlushList进行管理.FreeList管理空闲页的列表,如果后续FreeList列表空间不足,从LRU列表进行淘汰再进行分配空间.

脏页通过CheckPoint机制刷新到磁盘.

  • 缓冲池:缓冲池可以将索引页,数据页,undo页,插入缓存,自适应哈希索引,InnoDB存储的锁信息,数据字典等信息放入到内存,加速查询以及修改.缓冲池不可能完全承载文件数据,因此缓冲池当中的数据需要根据改进的LRU算法进行淘汰.
  • 重做日志缓冲:存储重做日志
  • 额外的内存池:存储内部数据结构

Check Point 机制

CheckPoint由主线程按照1s或者10s刷新一定比例的脏页到磁盘.
CheckPoint目的是为了:缩短数据库恢复时间,释放缓冲池空间,重做日志不可用时刷新磁盘.

InnoDB核心设计

以下为InnoDB特性,为数据库可靠性,高性能提供了保证.

InsertBuffer

为了解决非聚集索引的离散插入,删除或更新的问题的问题,InsertBuffer诞生.

注意官方后续将InsertBuffer改名为ChangeBuffer，增加了update/delete的支持.

众所周知,非聚集索引插入会引起随机IO,因为非聚集索引和聚集索引的顺序可能是不一致的,当一条数据插入时是数据页是顺序的但是对于非聚集索引就可能不是了,因此需要将插入操作合并,以减少随机IO.

前提条件:
索引是辅助索引.
索引是非唯一索引(因为InnoDB不能保证插入新值的唯一性).

InsertBuffer是存在于共享表空间中(ibdata1),本质是一个B+树,当一辅助索引插入到非聚集索引页当中,先判断这个页在缓冲池如果在直接插入,若不存在构造一个search key插入到B+树页子节点,search key包含space id(表空间),offset(页中偏移量).

如下情况会进行Merge Insert Buffer :

  • 辅助索引被读取到缓冲池,InsertBuffer中的该辅助索引页的相关记录会插入辅助索引页
  • MasterThread
  • 辅助索引页无可用空间

这时候随机插入变为顺序写入,性能极大提升.

注意：唯一索引的插入性能要比辅助索引的插入性能要差很多，是因为唯一索引需要把要插入值的位置读入内存中，然后进行判断是否唯一，然后再写入这个值

DoubleWrite

当一种情况写入16KB的页(一般Linux操作系统页大小为4kb)写入一半发生故障后,该页被损坏不能直接从重做日志恢复.
因此两次写保证InnoDB写入文件的可靠性.

思路:将页复制一个副本,防止写入失效时页损坏.进行恢复时,先通过的页的副本还原该页,然后进行重做日志恢复.

DoubleWriteBuffer提供2M的共享表空间,存储写入页的信息.
先将脏页写入到DoubleWriteBuffer内存中(memcpy),然后写入共享表空间中(此时为顺序写,需要进行fsync),然后在将数据页写入相应的表空间文件.

AdaptiveHash

InnoDB会根据数据的访问频率和模式(联合索引 (a, b),where a=X 与 where a=X and b=X是不同模式)来自动建立Hash索引.
但是有一些局限性就是只适合 =条件.

AIO与刷新邻近页

通过内核异步IO可以提升磁盘效率.
写入脏页时会判断临近的脏页如果存在会一起同过AIO执行.

WAL 日志先写

所有改变的数据先写入redo log,可以推迟数据写入文件的时机,保护数据的丢失.

Group Commit 组提交

组提交可以一并将redo log buffer的数据或者是binlog cache的多个事务数据数据合并写入文件中,减少底层IO操作次数,提升性能

刷脏页的时机

1.系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
认为系统“空闲”的时候。
正常关闭。这时候，MySQL 会把内存的脏页都flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

对性能的影响：

  1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
  2. 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

解决方案：

1.设置合适redolog文件大小
2.使用fio工具设置合理的innodb_io_capacity

GTID

GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：

GTID = source_id:transaction_id

GTID用于多个MySQL实例同步事务的标识,并可以在从库复制时更准确找到同步事务的位点.

日志

查询日志

查询日志记录对应主机查询的所有请求记录日志,这个一般不被开启.

set global general_log = off

慢查询日志

通过配置long_query_time慢查询时间,log_slow_queries开启慢查询.

# Time: 180927  0:17:41
# User@Host: schema_sync2[schema_sync2] @  [10.12.26.10]  Id: 24454
# Query_time: 1.472336  Lock_time: 0.000084 Rows_sent: 679  Rows_examined: 679 Logical_reads:1000 Phiscal_reads:20
select * from orders;

二进制日志

二进制存储格式有三种:
:按照语句存储,可能会导致主从数据不一致
:按记录存储,保证数据一致性,二进制日志比较安全主要是由于包含了变化之前的数据信息,可用于数据的闪回
:混合模式兼容效率与存储一致性的一种方式,当SQL中包含有SQL_FOUND_ROWS(),UUID()等函数会转为row模式

### UPDATE `tjy`.`idx_tjy_orders_apply_order_id`
### WHERE
###   @1=1527782400004279 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=480939048 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @3=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=1527782400004279 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=480939048 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @3=12701324 /* LONGINT meta=0 nullable=0 is_null=0 */
主从事务同步逻辑

  1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

  2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是io_thread和 sql_thread。其中 io_thread 负责与主库建立连接。

  3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

  4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

  5. sql_thread 读取中转日志，解析出日志里的命令，并执行

主主同步binlog日志的循环复制解决方案

  1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；

  2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的binlog；

  3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

重做日志

重做日志文件存储InnoDB存储引擎的事务重做日志,当写入事务发生崩溃未完成时,mysql 使用该日志恢复数据.
一种记录普通数据页的改动，一种记录changebuffer的改动.
配合redo log buffer进行写入,先写入缓存然后写入日志文件.一个扇区写入必定保证成功.
为了高效恢复数据库,节省磁盘空间,重做日志采用了循环写文件方式.
保证事务的ACID中持久性,innodb_flush_log_at_trx_commit需要设置为1,redo log buffer实时同步到磁盘.当然也可以设置为0,2,代表着不同的意思,根据实际生产环境需要设置.

undo日志

undo日志存储的是事务回滚的逻辑日志.
与redo日志不同的是undo日志存放在数据库中一个特殊段内,存放于共享表空间内.
用于实现MVCC机制.

日志两阶段提交

时序上 redo log 先 prepare， 再写binlog，最后再把 redo log commit,这样就保证了主从同步数据的一致性以及事务的特征.

表

索引组织表:表都是按照主键顺序进行存放.如果表没有定义主键,判断是否有非空的唯一索引,如果没有自动创建一个6字节大小的指针.
设置inno_db_file_per_table后存储空间每个表设置独立的表空间.
表名.idb:存储表数据,索引和插入缓冲位图等信息.
表名.frm:表结构关系.

段

表空间由各个段组成,常见的段有数据段,索引段,回滚段等.数据段为B+树的叶子节点,索引段为B+树非叶子节点.

区

区是由连续的页组成,每个区的大小固定为1MB.

页

默认大小为16KB,页类型分为数据页,undo页,事务数据页等.

行

MySQL是面向行存储的,每个页最大存储16KB/2-200行 .

行记录格式

  • Compact格式:页中存放的数据越多性能越高,列中NULL值不占用存储空间.
  • Redundant格式:老版本格式,CHAR类型的NULL值需要占用存储空间,而VARCHAR类型不需要占用存储空间.

以上格式中行溢出:针对行溢出数据,如果一个页无法存储两个行记录,溢出的数据会放置到Uncompressed BLOB页,数据页只会存储前768字节,并存储一个指针指向BLOB页中的数据.

之后InnoDB进行优化,之前的Compact和Redundant格式称为和Dynamic称为Barracuda.

Barracuda格式采取完全行溢出格式,行记录只保存20字节的指针,真正的数据存放在OffPage.
其中Compressed格式:会对行数据进行zlib算法的压缩.

数据页

除此之外数据页最外层包含了页头信息(FileHeader),校验页完整性写入磁盘(FileTrailer)
每个数据页包括Infimum 和Supremum ,这两个用于标记该数据页中行记录边界.
实际存储行记录的内容(UserRecord)与记录删除后空间会被加入到空闲链表(FreeSpace)内.
PageDirectory用于存放记录相对位置,将页加载到内存然后,通过PageDirectory二分查找对应记录.

分区表

支持四种方式进行分区:
:对分区列进行hash,通过对指定的分区数求余数放入到对应的分区
:根据建表语句对应的范围建立分区
:根据指定列的值,放入到不同分区
:和hash分区类似,不同之处是使用用户定义函数进行分区
:形式是以Range,List类似但是以上四种不支持非整型,但是Column支持字符串分区,DATE,DATETIME.

子分区

在Range,List分区的基础上再进行Hash,key分区.
每个子分区数量必须相同.

分区表的局限性

在OLTP中的使用限制比较多,例如使用非分区字段查询,会引起IO次数增加,从而影响性能.
分区功能不够强大,这种方案比较适合OLAP业务比如:销售记录的表,涉及到日期或
还有一点就是没有索引表的概念,当涉及到非分区列的查询可以避免全表扫描.

为什么使用应用层分表而不用分区表

  1. MySQL 在第一次打开分区表的时候，需要访问所有的分区；
  2. 在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；
  3. 在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。

共用MDL锁在DDL时候会造成更大范围的查询阻塞.如果分区有很多,第一次打开分区访问所有分区表的表结构文件,在性能上效率上会差很多.

索引

聚集索引:按照主键构造B+树,叶子节点存放整张表的行记录.
辅助索引:叶子节点包含主键信息(聚集索引建),以及索引建值信息.
联合索引:多了进行索引,按照索引定义顺序进行存放.因此就会有一些问题:a,b,c创建的索引,当条件包含a=X,a=X and b=X, a=X and b=X c=X时会使用索引.因为索引的排序是按照先排a再排b再排c.因此单独使用b,或者b,c都不是有序的.
覆盖索引:覆盖索引从辅助索引就可以查到记录.

MRR优化:索引都是按照索引的大小顺序进行排序.所以当通过辅助索引查到主键的值可能是离散的,离散的值意为着性能很差.MRR(Multi-Range Read)会按照主键顺序进行排序后再查询,减少数据随机访问.

ICP优化:除了MRR优化外还有ICP(INDEX CONDITION PUSHDOWN),取索引的同时会对where条件的进行过滤,减少回表次数.
自适应哈希索引经哈希函数映射到一个哈希表中.对于=的条件性能提升很大.关于自适应哈希索引的使用情况可以通过SHOW ENGINE INNODB STATUS进行查看.

InnoDB支持全文索引,但是局限性很严重,只支持Latin文字.

使用建议:

条件不要对索引使用表达式,如left(A, 1) = "c"
2.主键最好自增整型
3.索引基数尽量趋近于1
4.使用更短索引或者前缀索引
5.索引列适当,如果太多降低更新能
6.唯一索引查询性能更好,写入性能稍差
7.覆盖索引可以减少查询IO
8.利用索引排序

复制

复制在二进制一节有详细的介绍,这里不再赘述.

MySQL支持级联复制.

半同步复制与同步复制

半同步主库在发送一个事务提交会阻塞到至少一个半同步从库已经"接收到事务事件"为止,否则会发生超时.
半同步从库在写入事件到中继日志,刷新到磁盘后才确认接收到事务事件.

半同步复制区别与"全同步复制"的是,当至少一个从库收到事务事件时半同步复制并不等待从库的提交.
如果没有从库接收到事务事件会发生事务超时,这时候会转换到异步复制模式,如果一个异步复制的从库追赶上主库,将会切到半同步复制.

表设计

三范式原则:
第一:每一列都是不可分割的数据项
第二:所有数据都要和该数据表的主键有完全依赖关系
第三:非建属性之间无关

三范式原则使用有些局限性,实际可能更多使用的是反范式设计原则.

在设计时尽量用ER模型图:
矩形框：表示实体，在框中记入实体名
菱形框：表示联系，在框中记入联系名
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。（对于一对一联系，要在两个实体连线方向各写1； 对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

使用explian

explain在实际的开发当中有着重要的作用,可以分析当前SQL的性能.

字段 取值 解释
id - 表名SQL语句执行的顺序,如果id值相同那么执行顺序从上到下,如果不同那么值越大越先被执行
select type SIMPLE 不包含UNION以及子查询
PRIMARY 包含复杂查询,当前最外层查询
DERIVED 当前查询为衍生查询(from语句之后的子查询)
SUBQUERY 当前SQL中SELECT或WHERE包含子查询
UNION SELECT出现在UNION之后
UNION RESULT 从UNION的表中取结果的SELECT
table SQL语句查询的表
type ALL 指明当前的SQL全表扫描
index 使用索引进行全表扫描
range 索引范围扫描
ref 使用索引等值查询
eq_ref 使用唯一索引查询
system 系统优化后的查询,转化为常量
const system特例,查询中只有一行
null 性能最高的查询
possible_keys - 可能使用的索引
keys - 真正使用的索引
key_len - 索引的长度
ref - 连接查询的字段
rows - 扫描的行数
extra Using where; 使用条件过滤
Using filesort SQL中没有使用索引字段而导致使用文件排序,当SQL结果集较小会使用内存进行文件排序,如果结果集太大那么,会使用临时文件进行排序.
Using Index 使用索引
Using Index Condition 使用Index Condition Pushdown,将数据过滤放置到存储引擎层,提升查询性能
Using Temporary Union,使用衍生查询或者是order by与group by字段不一致等导致使用临时表的情况,还有一些其他可能出现情况请点击,实际要避免这类情况的发生.
Using join buffer(Blocked Nested Loop) 说明连接查询没有走索引,此时连接查询性需要全表扫能很差

配置参数

我们完全可以透过InnoDB的配置参数,来理解InnoDB设计的精髓.
这些配置其实是InnoDB设计的体现.

参数 说明
innodb_buffer_pool_size buffer_pool实际大小
innodb_buffer_pool_instances buffer_pool个数
query_cache_size 尽可能的小些,推荐设置为0
max_connections 最大的连接数
innodb_log_file_size redo日志文件大小, 不能设置太小会导致不停切换文件async checkpoint,设置太大会导致数据库恢复时间太长
innodb_adaptive_hash_index 是否开启自适应hash索引,默认开启加速等值查询速度
innodb_change_buffering 默认值是all,一般情况不需要改变,change buffer 请见InsertBuffer
innodb_read_ahead_threshold 是否开启缓冲池线性预读,默认开启ON,在缓冲池当中的连续访问会异步从磁盘预读多个页到缓冲池
innodb_thread_concurrency 最大并发线程数量,一般不推荐修改,除非上下文切换是瓶颈
innodb_read_io_threads,innodb_write_io_threads 数据页read/write IO线程数,根据部署环境可适当增大
innodb_io_capacity 设置InnoDB整个IO吞吐量,默认值200,涉及到IO相关的任务(例如:刷新脏页,写入插入缓存),SSD磁盘或者RAID磁盘都可以讲该值提高
innodb_max_dirty_pages_pct 超出脏页刷新的百分比,如果超出就刷新脏页
innodb_adaptive_flushing 根据redo log 产生的速度以及脏页的数量来控制刷新脏页的数量,默认开启
innodb_rollback_segments 回滚段个数的配置,InnoDB支持128回滚段,其中32保留用于临时表的事务.默认最大支持96K左右并发,如果设计临时表事务最大支持32K并发.
innodb_purge_threads Purge thread的个数,如果DML操作只执行在少数表或者单个表,那么可以设置的低一些防止与其它线程形成竞争.如果是很多个表的DML操作比较多可以适当增大
innodb_old_blocks_pct 0-95, 默认将读取的新页插入到LRU列表末端的37%的位置,防止一些扫描表或索引的操作将LRU中的热数据冲刷掉.
innodb_old_blocks_time 毫秒数,默认插入innodb_old_blocks_pct的隔1000毫秒后再插入LRU列表热端.
innodb_flush_neighbors 在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。 值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

补充

update是原子操作吗

实际上来说Update是原子操作.

1.如果开启了autocomit模式,那么所有的语句都在事务当中,每个语句组成一个单一的事务,当语句执行成功后事务自动进行提交.官方文档
,DELETE,锁定读 (select for update)语句一般会根据具体条件使用的索引加锁,对于非唯一索引会锁住扫描的范围(next-key),而对于唯一索引搜索唯一行会使用record lock.如果在事务当中执行,事务的隔离级别也会影响锁的范围官方文档

所以类似:UPDATE sku SET stock = stock - 1 where id = 10这样的语句是安全可靠的.

CHAR类型和VARCHAR类型

在可变长度字符集如UTF8下,CHAR类型和VARCHAR类型实际底层存储行没有区别.

MySQL不支持物化视图

MySQL支持视图,但是不存在实际存储.
物化视图可以通过触发器简单实现.

RAID

Redundant Array of Independent Array:独立磁盘冗余数组:将几个磁盘组合成一个逻辑的扇区.
增加数据容错性,增加磁盘容量.
可以极大提升性能,提供并行IO能力.

  • RAID0:将多个磁盘组成一个逻辑磁盘,并行IO,查询写入性能高.
  • RAID1:将N个磁盘组成互为镜像,容错性高.
  • RAID10和RAID01:结合了RAID0和RAID1的特性,性能和容错性高.

排序和索引

当我们使用主键查询并用索引列排序肯能会导致一个问题,就是使用文件排序:
文件排序是一个非常高昂的操作,需要尽量避免.

mysql> explain select id,status from orders where id > 1000 order by create_time;
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-----------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows     | Extra                       |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-----------------------------+
|  1 | SIMPLE      | orders | range | PRIMARY       | PRIMARY | 8       | NULL | 49403851 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+---------+---------+------+----------+-----------------------------+

上述例子中create_time,与id都有是索引列,但是MySQL确采用了文件排序.这是新手通常会犯的一个错误.

尽量使用以下两点避免该问题:

  • 索引列和order by列尽量一致
  • 对连接查询,order by 列要用首张表的列

索引的Cardinality

Cardinality用于指示索引的唯一特性,与索引字段性能有直接关系.
由于show index fromtable 中Cardinality不能准确更新当前索引的基数,其更新策略 :
1.表中1/16数据变化
2.20亿数据被修改.

可以使用count(DISTINC index_col)/count(*) 来实时检测当前唯一index列的个数.

Redis与MySQL使用

注意两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图.

count(字段)的性能

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id值都取出来，返回给server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说：

  1. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

  2. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是
    null，还要把值取出来再判断一下，不是 null 才累加。

也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。
但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是 null，按行累加。

排序问题

如果内存够，就要多利用内存，尽量减少磁盘访问。

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段（包括要查询的字段）都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据

随机排序问题

order by rand()随机排序会生成随机值存储到临时表中，用随机值进行排序。

如果你直接使用 order by rand()，这个语句需要 Using temporary 和 Using filesort，查询的执行代价往往是比较大的。避免使用该语句查询，使用伪随机算法，主键可能是不连续的但是可以在表中增加个字段叫rowid该值是连续的这样就解决了主键ID不连续的问题。

排序临时表选择问题

tmp_table_size：小于这个配置会使用内存临时表，大于这个参数配置会使用磁盘临时表。

对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，因此会选择排序字段进行排序，也就是rowid排序方式。

函数操作

函数操作不能走索引字段，常见的情况有：

1.条件字段的函数操作 如：select count(*) from log where month(t_modified)=7;
2.隐式转换，字符串转换为整数类型，本质上是MySQL调用内部的CAST函数，导致了不走索引，如：select * from log where id=11717;,本质上是select * from tradelog where CAST(tradid AS signed int) = 11717;
3.字符集(utf8与utf8mb4就是不同的字符集,MySQL内部先把 utf8 字符串转成 utf8mb4 字符集)不同的表在连接查询时,要求在被驱动表的索引字段上加函数操作,这就导致了不走索引select d.* from a , b where = and =2b表是utf8,a表是utf8mb4,查询到a表后的数据然后进行字符集的转换.select * from b where CONVERT(id USING utf8mb4)=;

注意不走索引是发生在条件判断的左侧使用函数,右侧使用函数不影响索引的使用

主从延迟可能原因

1.备库所在机器的性能要比主库所在的机器性能差
2.备库的压力大
3.大事务:DDL,删除多行或者更新多行

主从不一致查询解决方案

实际业务中会遇到事务在主库更新后会立刻查询的场景

  • 直接查主库

  • 等位点查询

  1. trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和Position；
  2. 选定一个从库在从库上执行 select master_pos_wait(File, Position, 1)；
  3. 如果返回值是 >=0 的正整数，则在这个从库执行查询语句；
  4. 否则，到主库执行查询语句。
  • 等GTID查询
  1. trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；
  2. 选定一个从库, 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
  3. 如果返回值是 0，则在这个从库执行查询语句；
  4. 否则，到主库执行查询语句。

连接查询

类型

Nested-Loop Join(INLJ),被驱动表使用索引进行查询,这种方式是最推荐的方式
Nested-Loop Join(BNL),这种方式会将驱动表的数据分块读入缓存,然后与被驱动表的每一行(全表扫)进行比较,效率上比INLJ要差很多,因为使用索引查询被驱动表要比全表查询被驱动表要快的多.这种方式相对于 Nested-Loop Join Algorithm(NLJ)算法效率上有很大的提升,因为NJL没有采用缓存的方法,一次只能比较一行,而BNL算法一次可以比较一个join_buffe,join_buffer可以包含多行数据.

使用规则

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表.

尽量使用INLJ算法不要使用BNL算法,也就是说explain 语句的时候Extra不要出现Using join buffer(Blocked Nested Loop)这个提示,不出现这个提示意味着我们用的是INLJ算法

group by 使用建议

  1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

  2. 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；

  3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size 参数，来避免用到磁盘临时表；

  4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

Memory 与 Innodb

InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。

而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。

区别

  1. InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；

  2. 当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；

  3. 数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；

  4. InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。

  5. InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

相关文章