mysql总结

存储引擎

MySql支持多种存储引擎,通过navicat新建表可以看到,MySql一共支持7种存储引擎。我们平时听到最多的MyISAM与InnoDB就在其中。MyISAM是5.1版本之前的默认引擎,5.1之后InnoDB就成为了Mysql的默认引擎,一直沿用至今。那么这两种引擎之间有什么区别呢?

全文检索 事务 外键 数据类型 MVCC
InnoDB 不支持(5.6及以上支持) 支持 支持行锁 支持 聚簇索引 支持
MyISAM 支持 不支持 只支持表锁 不支持 非聚簇索引 不支持

InnoDB与MyISAM区别:

由于MyISAM不支持事务和行锁,因此针对数据的写操作,会锁住整张表,这在高并发情况下是极为不利的。因此MyISAM只适合大量查询少量插入的场景来使用。而InnoDB支持行锁,外键,并且可以通过MVCC来支持高并发,因此在高并发下情况下,InnoDB有更好的表现。

InnoDB的行锁是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁。

对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务。

InnoDB为什么推荐使用自增ID作为主键?

自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。

InnoDb与Myisam数据存储在哪里?以什么方式?

Innodb存储文件有FRM(表框架)、IDB(索引与表数据)

Myisam存储文件有FRM(表框架)、MYD(表数据)、MYI(索引数据)

InnoDb四大特性

  • 插入缓冲(insert buffer):在MySQL5.5之前,叫插入缓冲(insert buffer),只针对insert做了优化;现在对delete和update也有效,叫做写缓冲(change buffer)。
  • 二次写(double write):doublewrite buffer
  • 自适应哈希索引(ahi):由于通过二级索引查找数据行时,每次都要去查找对应的主键值后,才能去主键索引中找到数据行。而这种方式会导致进行了两次B+TREE的查找工作。因此Innodb存储引擎会监控对表上二级索引的查找,如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引。自适应哈希索引通过缓冲池的B+树构造而来,因此建立的速度很快。而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。(hash表类似于HashMap的桶)
  • 预读(read ahead):磁盘读写,并不是按需读取,而是按页读取,一次至少读一页数据(一般是4K),如果未来要读取的数据就在页中,就能够省去后续的磁盘IO,提高效率。

索引

逻辑角度

  • 普通索引:最基本的索引,没有任何限制。

  • 唯一索引:要求关键字不能重复。同时增加唯一约束。

  • 主键索引:要求关键字不能重复,也不能为NULL,同时增加主键约束,且可以被其他表应用为外键。

  • 组合索引:将几个列作为一条索引进行检索,使用最左匹配原则。

  • 全文索引:关键字的来源不是所有字段的数据,而是从字段中提取的特别关键词。

存储角度

聚簇索引(聚集索引)

特点

  • 聚簇索引就是按照每张表的主键构造一颗B+树,用来保存索引与数据。
  • 索引结构的叶子节点存储整张表的行数据。数据的物理存放顺序与索引顺序是一致的。
  • 由于聚簇索引是通过主键来将数据聚集,因此使用聚簇索引的引擎不能没有主键。如果表中没有定义主键,那么 InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引,那么InnoDB 会隐式定义一个主键,来作为聚簇索引。
  • 聚簇索引的每个叶子节点都包含了键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。
  • 聚集索引是 InnoDB引擎下主键索引的实现, InnoDB引擎中每张表只能有一个聚簇索引

检索过程

  • 由于聚簇索引中主键索引与数据放到了一起,所以通过主键索引来检索的之后,可以直接查找对应主键的行数据。
  • 通过辅助索引查数据时,首先查到索引位置对应的主键值,然后通过主键值去主键索引中,查找对应的行数据。

优点

  • 可以把相关数据保存在一起。

  • 数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快。

  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点

  • 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键。但如果不是按照主键顺序加载数据,那么加载完成后最好使用OPTIMIZE TABLE (碎片整理)命令来重新组织一下表。
  • 更新聚簇索引的代价很高,因为会强制InooDB将每个更新的数据移动到新的位置。
  • 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
  • 二级索引(辅助索引)访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
非聚簇索引(辅助索引)
  • 非聚簇索引是将索引与数据分开存储,索引结构的叶子节点指向了数据对应的物理地址。
  • 由于表数据存放在独立的位置,因此辅助索引访问数据总是需要二次查找。
  • 辅助索引叶子节点存储的不再是行的物理位置,而是主键值。
  • 非聚集索引是 MyISAM引擎下的索引实现。

检索过程

  • 由于非聚簇索引表数据单独存放,因此无论是通过主键索引还是二级索引的方式,都需要通过查找到的表行记录指针去查找对应的表数据。

优点

  • 插入速度不依赖插入顺序,插入速率快。

缺点

  • 检索效率低于聚簇索引,每次定位数据需要二次查找。

使用角度

  • 覆盖索引:索引是高效找到行的一个方法,当能通过二级索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。
  • 索引下推:索引下推具体是在复合索引的查询中,针对特定的过滤条件而进行减少回表次数而做的优化。

数据结构

MySql中储存索引的方法有两种:B+TREE、HASH,MySql默认采用的B+TREE,那么为什么MySql不用其他数据结构来存储呢?

  • 二叉树:二分查找法,会有左倾或者右倾的情况,且不适合做范围查询。

  • 平衡二叉树:避免的左倾和右倾,但是数据量大的时候,树高会很高,也就是IO次数会很多,并且存储的数据少。

  • B-Tree:相比平衡二叉树,树高是降低了,但是还是不适合范围查询,范围查询需要遍历所有数据。

  • B+Tree:将所有数据都放到叶子节点,所有的叶子结点使用链表相连(可以做范围查询),非叶子节点只放键值,每个数据叶中的有效数据就多了,可以减少IO次数。

B-TREE

  • 绝对平衡树,所有的叶子节点都为同一高度。

  • 每个节点都是一个二元数组: [key, data],所有节点都可以存储数据。key为索引key,data为除key之外的数据。

  • 区间查找可能需要返回上层节点重复遍历,IO操作繁琐。

B+TREE

  • B-TREE的变种,B -TREE能解决的问题,B+TREE也能够解决(降低树的高度,增大节点存储数据量)。

  • 非叶子节点不存储data,只存储索引,可以存放更多的索引。

  • Mysql中B+Tree,在经典B+Tree的基础上进行了优化,增加了双向指针。(经典的B+TREE只有单项指针)

  • 查询所有数据时,只需要扫描所有的叶子节点,并且所有的叶子节点相连。

  • B+TREE磁盘读写能力更强。他的根节点和支节点不保存数据区,所以根节点和支节点同样大小的情况下,保存的关键字要比B TREE要多。而叶子节点不保存子节点引用,能用于保存更多的关键字和数据。所以,B+TREE读写一次磁盘加载的关键字比B -TREE更多。

  • B+TREE查询性能稳定。B+TREE数据只保存在叶子节点,每次查询数据,查询IO次数一定是稳定的。当然这个每个人的理解都不同,因为在B TREE如果根节点命中直接返回,确实效率更高。

HASH

  • 对索引的key进行一次hash计算,计算就可以定位出数据存储的位置。
  • 很多时候HASH索引比B+TREE索引更高效。
  • 仅能满足“=”,“in”,不支持范围查找。
  • 会产生hash冲突问题。

InnoDb引擎

下图为innodb官方提供的架构图:

INNODB官方架构图

​ 下图为buffer pool结构图:

buffer poll

在MySql中我们插入的数据库存储引擎存储,最终持久化到磁盘。当我们查询存储的数据时,又通过存储引擎将数据加载到内存。为了加速数据的访问,MySql会将热点数据放入缓存中,避免每次去查询数据库。而我们的InnoDb作为MySql的一个存储引擎,内部的buffer pool就是为此而设计的。

buffer pool是如何被使用的呢?

当我们通过mysql查询一条数据时,mysql从磁盘中读取到数据所属的页,mysql会先将查询的页缓存到buffer pool中,下次查询相同数据时,直接通过buffer pool中缓存的页数据。

mysql查询数据时,如果知道放入buffer pool的那个位置呢?

free链表用来记录我们buffer pool中空闲区的位置,当我们需要向buffer pool中缓存数据时,会查询free链表头部节点的下一个节点的buffer pool空闲的位置,将数据插入到该位置,并在free链表中将此节点删除。

mysql修改数据时,innodb是如何操作的?

mysql修改数据时,buffer pool中如果存放着该数据对应的页,那么此时为了同步数据,Innodb不仅需要更新buffer pool中缓存的页,还需要修改磁盘中的页。但Innodb为了避免频繁的访问磁盘,仅仅修改了buffer pool中的页数据,又通过后台线程定时将修改的数据刷新到磁盘,此时未被持久化到磁盘中的页,我们称之为脏页。此时fulsh链表就是用来记录那些buffer pool中脏页的位置。后期我们只需要定时去刷新fulsh链表,将对应找脏页的数据,刷新到磁盘即可。

如果buffer pool满了怎么办?

buffer pool默认的大小默认为128M,我们页的数据为16k,因此buffer pool总会有满的时候,那么后续查询的数据,还会存储到buffer pool中吗?这就要说到Buffer pool的淘汰机制,lru链表记录Buffer pool最近访问数据页的地址。每当读取页数据时,都会将该页位置放入lru链表的头部,lru链表本身有长度,当链表满时,lru尾部的数据就会被淘汰。(详情了解lru算法)

  • 如果淘汰的是脏页,那么需要强制执行checkPoint,将脏页刷入磁盘。

优化点:理论上将buffer pool设置到足够大,存储的热点数据越多,效率越高。

热数据区、冷数据区?

上面的方式也会产生一个问题。当前用户查询所有数据时,大量的数据导致lru链表节点被频繁淘汰。表扫描只是偶发的行为,但该行为会导致存放的热点数据全部失效,这显然是不合理的。因此Innodb将lru链表一分为二,分为热数据区、冷数据区。Innodb将访问到的页节点位置,加入到冷数据区的头部,这个时候热数据区与冷数据区就隔离开了,那么什么时候将页位置节点加入热数据区呢?Innodb制定了规则,新加入节点的时间点 - 上一个节点加入的时间点 > 1秒,则直接加入热数据区。该方式就是为了防止全表扫描对热数据区带来的影响。因为一般频繁的全表扫描,两个节点插入的时间一般小于1秒。

什么时候Innodb将Buffer pool的脏页数据,持久化到磁盘中?如果中途MySql程序挂了呢?

InnoDB作为事务数据库采用了Write Ahead Log策略:当事务提交时,先写重做日志,再修改页。所以当数据库发生宕机导致脏页的刷新丢失时,可以通过重做日志(redo log)来完成数据的恢复。这便是事务数据库中Durability(持久性)的保证。

执行update语句时,innodb修改buffer pool数据使页成为脏页,记录到fulsh链表,修改成功之后,生成一个redo.log对象,并存放到log buffer中,如果需要持久化,那么将redo.log持久化,返回客户端修改成功。重启之后会针对redo.log对数据进行修复。redo.log持久化比较快,因为将随机IO转换为顺序IO,这也是为什么不直接修改磁盘数据的原因。

为什么redo.log有两个文件?

从官方的图可以看出redo.log有两个文件,logfile0、logfile1。logfile0默认大小为48M,当前logfile存储满后,会切换到logfile1,但是此时logfile0中可能还有未持久化到磁盘的数据,因此当切换redo.log时,Innodb会触发的检查点(Checkpoint),检查点会检查当前整个buffer pool中与文件中相同的数据,并将数据持久化到页中。

调优点:调大logfile的大小和个数,但是调大之后mysql重启恢复数据就会变慢(一般使用中我们很少考虑重启的恢复时间)。

开启事务的情况下,使用update整个时候需要持久化redo.log吗?

Innodb提供了三种处理方式:

  1. 事务提交时,不立即去持久化,交给后台线程,此方法缺点在于丢失数据多。
  2. 事务提交时,持久化(默认),性能最慢,丢失数据少。
  3. 事务提交时,将redo.log写入操作系统的缓冲区,操作系统定时将日志写入硬盘。这种情况下,即时MySql挂了只要操作系统没挂,那么事务的持久化还是能保证。直接断电或者关机的情况下,就会丢失数据。

什么是Change Buffer?

前面说了当用户修改数据后,最终会将数据持久化到磁盘中的数据页。但是我们在使用mysql时,还会为MySql建立索引。如果我们修改的数据涉及到索引页,那么也应该将索引页中的数据持久化到磁盘。一张表可能有多个索引页,同时修改这么多的索引页,Innodb又是如何来优化的呢?

根据Inoodb官方提供的架构图中,可以看到Change Buffer是属于Buffer pool的一部分。Change Buffer默认占Buffer pool中25%的空间。那么Change Buffer作用是什么?

对于一次更新操作,innodb引擎会看更新的页是否在buffer pool中,如果在,那么直接更新buffer pool,并且写入一条redo log。这样后续的读取操作可以读到最新的值。如果不在,那么就先将本次更新操作记录到Change buffer中,而不是立刻去更新,最后再记录一条redo log。如果后面有读操作,那么再将对应的数据页载入到buffer pool中,同时将change pool中的更新操作应用到该数据页,这个过程叫做merge。当然如果系统是空闲状态,也会有后台线程去做merge过程。

change buffer仅用于更新普通索引,对唯一性索引无效,因为唯一性索引肯定要读数据页做唯一性判断的。

优化点:在多写少读的情况下,可以将Change Buffer调大(比如日志系统)。

Doublewrite buffer是什么?

inndb的16k的数据页,如果写入磁盘中。操作系统中的页数据大小只有4k,因此想要写入操作系统种,那么分四次才能写完。如果在写第二次的时候,操作系统挂掉了,或者机器断电了,最后怎么判断哪些数据改了,那些数据没改呢?

因此Innodb将16k的页数据页分四次写入Doublewrite buffer,即使出问题了,也能通过redo.log还原。

Doublewrite buffer分四次写入磁盘中,中途写失败了,也通过redo.log来还原。

最后通过Doublewrite buffer分四次写入磁盘的页中,这种方式经历了两次磁盘IO,效率更低,但是为什么要这么做呢?

主要还是因为磁盘无法支持原子性写入,Doublewrite buffer主要是为了解决,多次写的数据丢失的问题。

如果从Doublewrite buffer写入到磁盘业数据失败了,也能通过Doublewrite buffer来还原。

为什么不直接将数据分四次写入磁盘,然后通过redo.log来还原呢?

redo.log中记录的是对页的物理修改。如果这个页本身已经发生了损坏,再对其进行重做是没有意义的。

Binlog

binlog是属于mysql的概念,binlog功能与redo.log类似,也是用来记录mysql的操作,那么为什么Inndb为什么还要设计redo.log呢?

因为redo.log记录的是某一页中某些数据的物理位置的数据,进行的那些修改,因此在使用redo.log时,Innodb能更快的对数据进行修复。

而Binlog记录的是执行的语句,因此恢复的效率不如redo.log。

Binlog本质上是一种逻辑日志,因此能够适用所有的存储引擎,并进行数据复制。

Binlog与redo.log一样,也是在fulsh链表后进行的持久化。

Binlog主从复制:

主从复制的场景中在Master 端会开启binlog ,然后将 binlog 发送到各个Slave 端,Slave 端重放binlog 从而达到Slave 端的数据和Master端的数据保持一致。在数据恢复场景,通过使用mysqlbinlog 工具以及对应的binlog 将数据恢复到指定的时间点。那么可以把binlog 解决的问题总结为两点,就是主从复制和数据恢复。

undo_log

undo_log用于存放不同事务版本下的不同数据,undo_log中记录事务操作前的原始数据,每次数据变更都会产生undolog记录,undo_log记录分为 insert undo_log 和 update undo_log。insert操作属于insert undo_log,只针对当前事务,在insert操作后产生undo_log记录,在事务提交后删除undo_log记录,说白了就是给当前事务自己看的。update 和 delete操作属于update undo_log,会根据隔离级别不同事务版本的数据可见性不同。当事务回滚后,通过undo_log日志内容,将数据还原为原始数据。

索引优化

优化点

  1. 联合索引遵循左前缀原则。
  2. 小表 (参与查询数据量小的表) 驱动大表。
  3. 避免使用IS NULL、IS NOT NULL 因为有时候不走索引。
  4. 索引避免进行进操作运算。例如:date('1970-01-01')
  5. 避免LIKE以%开头的查询,例如 A LIKE '%1'
  6. 索引列避免隐式转换,例如字段A是一个字符串‘1’,但是查询的时候使用的 A = 1, 虽然也能查到(内部会数值类型的1转换为‘1’),但是不会走索引。
  7. 使用OR的时候,保证OR的前后都有索引,不然该索引会失效。
  8. 查询的结果尽量保证覆盖索引,避免回表。
  9. 某些时候使用前缀索引可以提高查询效率,但是注意前缀索引不能用于覆盖索引。
  10. MySql的全文索引不支持中文索引,基本上不会使用,一般使用搜索引擎,例如LUCENE、SOLR、ES。
  11. 对字段进行的操作,都会造成索引失效。

注意点

  1. 不要过度索引。索引越多,占用空间越大,反而性能变慢。
  2. 只对WHERE子句中频繁使用的建立索引。
  3. 尽可能使用唯一索引,重复值越少,索引效果越强。
  4. 使用短索引,如果char(255)太大,应该给它指定一个前缀长度,大部分情况下前10位或20位值基本是唯一的,那么就不要对整个列进行索引。
  5. 充分利用左前缀,这是针对复合索引,因为WHERE语句如果有AND并列,只能识别一个索引(获取记录最少的那个),索引需要使用复合索引,那么创建时应该将WHERE最频繁的放置在左边。
  6. 索引存在,如果没有满足使用原则,也会导致索引无效。

优化步骤

  1. 开启查询缓存,优化查询。
  2. explain你的select查询。
  3. 通过 show status 命令了解各种sql的执行频率,定位执行效率较低的sql语句。
  4. 通过explain分析低效sql的执行计划。
  5. 通过 show profile 分析sql。
  6. 通过trace分析 优化器 如何选择执行计划;确定问题并采取相应的优化措施。

隔离级别

  • Read uncommitted (读未提交):在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
  • Read committed (读已提交):一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。
  • Repeatable read (可重复读):这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。(当前事务查询的数据,不受其他事务影响)
  • Serializable (串行化):这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。

MVCC

MVCC为了提高并发的读写性能,不用加锁就能让多个事务并发读写。InnoDB中,MVCC 是通过 readview + undolog 来实现的。

在事务中进行写入操作时,会生成一条undo_log日志。日志中记录原数据信息,以及操作数据对应的事务id。undo_log根据日志的先后生成顺序,形成了一条日志记录链表。

当用户执行查询sql时,会生成一致性视图read-view。它由执行查询时所有未提交事务id数据组和已创建的最大事务id组成。此时我们查询的数据结果需要跟read-view做比对,从undo_log日志中获取快照结果。

read-view根据生成时间不同,产生了RC,RR两种可见性。

RC:每条select创建一个新的readview ,所以导致读提交 读到的都是最新提交的。

RR:事务开始的时候创建一个readview, 一直到事务结束都用的这个readview,也就避免了不可重复读

事务读数据的原则就是:

  • 读版本号小于等于当前版本的数据(意思就是读不到在当前事务之后修改的数据 避免了不可重复读)

  • 读删除事务版本号大于等于当前版本的数据(意思就是如果这条数据在之后的事务里删了,当前事务也不能再读了)

EXPLAIN

在日常工作中我们可以通过explain这个命令,来查看某些SQL语句的执行计划,通过执行计划来定位执行中效率较低的SQL。下面是我们

参数 描述
id 选择标识符,多条语句同时执行的情况下,id值越大优先级越高,越先被执行
select_type 查询的类型。
table 输出结果集的表
partitions 匹配的分区,表分区的时候会用到
type 表的连接类型
possible_keys 查询时可能使用的索引
key 实际使用的索引
key_len 索引字段的长度,联合索引情况下,可以推算出运用了那些索引
ref 列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows 估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
filtered 按表条件过滤的行百分比
Extra 执行情况的描述和说明

以下将对explain命令中几个描述较多的参数详细介绍:

select_type

  • SIMPLE:简单SELECT,不使用UNION或子查询等
  • PRIMARY:子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
  • UNION:UNION中的第二个或后面的SELECT语句
  • DEPENDENT UNION: UNION中的第二个或后面的SELECT语句,取决于外面的查询
  • UNION RESULT: UNION的结果,union语句中第二个select开始后面所有select
  • SUBQUERY:子查询中的第一个SELECT,结果不依赖于外部查询
  • DEPENDENT SUBQUERY:子查询中的第一个SELECT,依赖于外部查询
  • DERIVED:派生表的SELECT, FROM子句的子查询
  • UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存,必须重新评估外链接的第一行

type

  • ALL:全表扫描,扫描所有叶子节点。

  • index:index与ALL区别为index类型只遍历索引树。

  • range:只搜索给定范围的行,使用一个索引来选择行。

  • ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。

  • eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件。

  • constsystem: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system。

  • NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

  • ALL 一> index 一> range 一> ref 一> eq_ref 一> const 一> system 一> NULL(从左到右,性能从差到好)。

  • 日常使用中尽量将SQL优化到range级别。

Extra

  • Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤

  • Using temporary:表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by

  • Using filesort:当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”

  • Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

  • Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。

  • Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

  • No tables used:Query语句中使用from dual 或不含任何from子句

常用命令与设置

查看查询缓存

show variables like '%query_cache%';query_cache_type=NO 表示已经开启).

开启查询缓存

1
2
3
vi /etc/my.cnf
query_cache_size = 20M
query_cache_type = ON

重启mysql

service mysql restart

查看缓存使用情况

show status like 'qcache%';

其中各个参数的意义如下:

  • Qcache_free_blocks:缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块。
  • Qcache_free_memory:缓存中的空闲内存。
  • Qcache_hits:每次查询在缓存中命中时就增大
  • Qcache_inserts:每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
  • Qcache_lowmem_prunes:缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个 数字在不断增长,就表示可能碎片非常严重,或者内存很少。(上面的 free_blocks和free_memory可以告诉您属于哪种情况)
  • Qcache_not_cached:不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
  • Qcache_queries_in_cache:当前缓存的查询(和响应)的数量。
  • Qcache_total_blocks:缓存中块的数量。

对于某些不想使用缓存的语句,可以这样使用:

select SQL_NO_CACHE count(*) from users where email = 'hello';

参考资料:https://blog.csdn.net/u010900754/article/details/106744734

https://www.cnblogs.com/ttaall/p/14339130.html

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×