恢复

     完全恢复

 将备份作为输入执行。

 将备份后执行的日志进行重做。

    
不完全恢复(跳过误操作语句,再恢复后
 面执行的语句,完成我们的恢复。)

 基于时间点的操作。跳过故障发生时间。

 基于位置的恢复。找到出错语句的位置号,并跳过位置区间。

 

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别
char是一种固定长度的类型,varchar则是一种可变长度的类型

(2)、varchar(50)中50的涵义
最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order
by col采用fixed_length计算col长度(memory引擎也一样)

(3)、int(20)中20的涵义
是指显示字符的长度
但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001
~~~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
20表示最大显示宽度为20,但仍占4字节存储,存储范围不变;

(4)、mysql为什么这么设计
对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;

2.2 原子性和持久性的实现

redo log 称为重做日志(也叫事务日志),用来保证事务的原子性和持久性. 
redo恢复提交事务修改的页操作,redo是物理日志,页的物理修改操作.

当提交一个事务时,实际上它干了如下2件事:
一: InnoDB存储引擎把事务写入日志缓冲(log
buffer),日志缓冲把事务刷新到事务日志.
二: InnoDB存储引擎把事务写入缓冲池(Buffer pool).

这里有个问题, 事务日志也是写磁盘日志,为什么不需要双写技术?
因为事务日志块的大小和磁盘扇区的大小一样,都是512字节,因此事务日志的写入可以保证原子性,不需要doublewrite技术

重做日志缓冲是由每个为512字节大小的日志块组成的. 日志块分为三部分:
日志头(12字节),日志内容(492字节),日志尾(8字节).

>>
查询日志记录了所有对MySQL数据库请求的信息,无论这些请求是否得到了正确的执行。默认文件名为:主机名.log

日志

错误日志:记录了当 mysqld 启动和停止时,以及服务器在
运行过程中发生任何严重错误时的相关信息。

二进制文件:记录了所有的 DDL(数据定义语言)语句和
DML(数据操纵语言)
语句,不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。(定期删除日志,默认关闭)。

查询日志:记录了客户端的所有语句,格式为纯文本格式,可以直接进行读取。(log
日志中记录了所有数据库的操作,对于访问频繁的系统,此日志对系统性能的影响较大,建议关闭,默认关闭)。

慢查询日志:慢查询日志记录了包含所有执行时间超过参数long_query_time(单位:秒)所设置值的
SQL
语句的日志。(纯文本格式)MySQL日志文件之错误日志和慢查询日志详解。

日志文件小结:

 系统故障时,建议首先查看错误日志,以帮助用户迅速定位故障原因。

 记录数据的变更、数据的备份、数据的复制等操作时,打开二进制日志。默认不记录此日志,建议通过–log-bin
选项将此日志打开。

 如果希望记录数据库发生的任何操作,包括 SELECT,则需要用–log
将查询日志打开,
此日志默认关闭,一般情况下建议不要打开此日志,以免影响系统整体性能。

 查看系统的性能问题, 希望找到有性能问题的SQL语 句,需要 用
–log-slow-queries 打开慢查询日志。对于大量的慢查询日志,建议使用
mysqldumpslow 工具 来进行汇总查看。

开放性问题:

一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录
1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a
.tid;

3. 索引有什么用

  • 作用:索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B树)中,使数据库可以快速有效地查找与键值关联的行。

  • 设计良好的索引可以减少磁盘 I/O
    操作,并且消耗的系统资源也较少,从而可以提高查询性能。

  • 一般来说,应该在这些列 上创建索引,例如:
    在经常需要搜索的列上,可以加快搜索的速度;
    在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
    在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度;
    在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; 
    在经常需要排序的列上创
    建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
    在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

  • 索引的缺点:
    第一,创建索引和维护索引要耗费时间,这种时间随着数据
    量的增加而增加。 
    第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。 
    第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

>>
如果当前数据库是复制中的slave角色,则它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入,要设置log-slave-update。如果需要搭建master=>slave=>slave架构的复制,则必须设置该参数。

数据类型

MySQL数据类型-菜鸟教程 

MYSQL中数据类型介绍

整数: int(m)里的m是表示数据显示宽度,浮点数,定点数。

字符串:char(n)4.0 n 代表字节,5.0 n 代表字符 (UTF-8=3zj,GBK=2zj)

 char 固定的字符数,空格补上;检索速度快。

 varchar 字符数+1个字节(n<=255)或2个字节(n>255)

 text 字符数+2个字节;不能有默认值;索引要指定前多少个字符;文本方式存储

 blob 二进制方式存储

MySQL的复制原理以及流程

基本原理流程,3个线程以及之间的关联;

  1. 主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;
  2. 从:io线程——在使用start slave 之后,负责从master上拉取 binlog
    内容,放进 自己的relay log中;
  3. 从:sql执行线程——执行relay log中的语句;

8.开放性问题:

一个6亿的表a,一个3亿的表b,通过外间tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a
.tid;

 

9.mysql数据库引擎MyISAM和InnoDB的区别

图片 1

 

10.MySql 表中允许有多少种
TRIGGERS?

在 MySql 表中允许有六种触发器,如下:
·BEFORE INSERT
·AFTER INSERT
·BEFORE UPDATE
·AFTER UPDATE
·BEFORE DELETE
·AFTER DELETE

 

>> 如果建表时没有指定主键,唯一索引,可以指定任何一个列为分区列

索引类型:

 B-Tree索引 索引列的顺序影响者是否使用索引。

 哈希索引

 无法用于排序。

 只支持全部匹配。

 只支持等值比较。

 有很多哈希冲突时,效率不太高。

 空间数据索引(R-Tree)无需前缀查询,从所有维度查询数据。

 全文检索 查找文本中的关键词,类似于搜索引擎做的事情。

备份计划,mysqldump以及xtranbackup的实现原理

(1)、备份计划;
利用空闲间隔
长期全量备份
每天增量备份
删除1个月前的备份数据

(2)、备份恢复时间;
(3)、xtrabackup实现原理
在InnoDB内部会维护一个redo日志文件,我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时,InnoDB会检查数据文件和事务日志,并执行两个步骤:它应用(前滚)已经提交的事务日志到数据文件,并将修改过但没有提交的数据进行回滚操作。

2.1 事务的隔离性由存储引擎的锁来实现

  数据库事务会导致脏读、不可重复读和幻影读等问题。
  1)脏读:事务还没提交,他的修改已经被其他事务看到。
  2)不可重复读:同一事务中两个相同SQL读取的内容可能不同。两次读取之间其他事务提交了修改可能会造成读取数据不一致。
  3)幻影数据:同一个事务突然发现他以前没发现的数据。和不可重复读很类似,不过修改数据改成增加数据。

InnoDB提供了四种不同级别的机制保证数据隔离性。
不同于MyISAM使用表级别的锁,InnoDB采用更细粒度的行级别锁,提高了数据表的性能。InnoDB的锁通过锁定索引来实现,如果查询条件中有主键则锁定主键,如果有索引则先锁定对应索引然后再锁定对应的主键(可能造成死锁),如果连索引都没有则会锁定整个数据表。

4种隔离级别: 
1) READ UNCOMMITTED(未提交读)
事务中的修改,即使没有提交,对其它事务也是可见的. 脏读(Dirty Read).
2) READ COMMITTED(提交读)
一个事务开始时,只能”看见”已经提交的事务所做的修改.
这个级别有时候也叫不可重复读(nonrepeatable read).
3) REPEATABLE READ(可重复读)
该级别保证了同一事务中多次读取到的同样记录的结果是一致的.
但理论上,该事务级别还是无法解决另外一个幻读的问题(Phantom Read). 
幻读:
当某个事务读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录.当之前的事务再次读取该范围时,会产生幻行.(Phantom
Row).
幻读的问题理应由更高的隔离级别来解决,但mysql和其它数据库不一样,它同样在可重复读的隔离级别解决了这个问题. 
mysql的可重复读的隔离级别解决了”不可重复读”和“幻读”2个问题. 
而oracle数据库,可能需要在“SERIALIZABLE”事务隔离级别下才能解决幻读问题.
mysql默认的隔离级别也是:REPEATABLE READ(可重复读)
4) SERIALIZABLE (可串行化)
强制事务串行执行,避免了上面说到的 脏读,不可重复读,幻读 三个的问题.

>> SELECT…FOR
UPDATE对读取的行记录加一个X锁,其他事务不能对已锁定的行加上任何锁。SELECT…LOCK
IN SHARE
MODE对读取的行记录加一个S锁,其他事务可以向被锁定的行加S锁,但是如果加X锁,则会被阻塞。

视图 

MySQL数据库视图

MySQL –
视图算法

视图最简单的实现方法是把select语句的结果存放到临时表中。具有性能问题,优化器很难优化临时表上的查询。

 合并算法 :select语句与外部查询视图的select语句进行合并,然后执行。

 临时表算法 :先执行视图的select语句,后执行外部查询的语句。

视图在某些情况下可以提升性能,并和其他提升性能的方式叠加使用。

 视图不可以跨表进行修改数据,

 创建有条件限制的视图时,加上“WITH CHECK OPTION”命令。

mysql中myisam与innodb的区别

2.事务的实现原理

事务的作用: 事务会把数据库从一种一致的状态转换为另一种一致状态。

事务的机制通常被概括为“ACID”原则即原子性(A)、一致性(C)、隔离性(I)和持久性(D)。

  1. 原子性:构成事务的的所有操作必须是一个逻辑单元,要么全部执行,要么全部不执行。
  2. 一致性:数据库在事务执行前后状态都必须是稳定的。
  3. 隔离性:事务之间不会相互影响。
  4. 持久性:事务执行成功后必须全部写入磁盘。

>>
最多可以为一个表建立6个触发器,即分别为INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。

三范式

三范式定义(范式和反范式)

1NF:每个数据项都是最小单元,不可分割,确定行列之后只能对应一个数据。

2NF:每一个非主属性完全依赖于候选码(属性组的值能唯一的标识一个元组,但是其子集不可以)。

3NF:每一个非主属性既不传递依赖于,也不部分依赖于(主码=候选码为多个市,从中选出一个作为主码)。

BCNF主属性(候选码中的某一个属性)内部也不能部分或传递依赖于码。

4NF :没有多值依赖。

你是如何监控你们的数据库的?你们的慢日志都是怎么查询的?

监控的工具有很多,例如zabbix,lepus,我这里用的是lepus

2.3 一致性的实现

undo log 用来保证事务的一致性. undo 回滚行记录到某个特定版本,undo
是逻辑日志,根据每行记录进行记录.
undo 存放在数据库内部的undo段,undo段位于共享表空间内.
undo 只把数据库逻辑的恢复到原来的样子.

undo日志除了回滚作用之外, undo
实现MVCC(多版本并发控制),读取一行记录时,发现事务锁定,通过undo恢复到之前的版本,实现非锁定读取.

    myisam引擎不支持事务, innodb和BDB引擎支持

>>
唯一索引可以是允许NULL值的,并且分区列只要是唯一索引的一个组成部分,不需要整个唯一索引列都是分区列

触发器

 触发器的触发事件 , 可以是 INSERT 、UPDATE 或者 DELETE 。

 触发时间 , 可以是 BEFORE 或者 AFTER。

 同一个表相同触发时间的相同触发事件 , 只能定义一个触发器,只支持基于行触发。

 触发器的原子性,InnoDB支持事务,MyISAM不支持。

MySQL binlog的几种日志录入格式以及区别

(1)、binlog的日志格式的种类和分别
(2)、适用场景;
(3)、结合第一个问题,每一种日志格式在复制中的优劣。

  • Statement:
    每一条会修改数据的sql都会记录在binlog中,过程导向(没有关注结果)。
    优点:记录sql语句上下文相关信息
    缺点:存储过程,或function,以及trigger的调用和触发无法被正确复制
  • Row:
    不记录sql语句上下文相关信息,仅保存哪条记录被修改成什么样子,结果导向(不关注过程)。
    优点:仅需要记录那一条记录被修改成什么了。所以会非常清楚的记录下每一行数据修改的细节。
    缺点:产生大量的日志内容。
  • Mixedlevel:
    是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则
    采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

6.myisam和innodb 2者selectcount(*)哪个更快,为什么

myisam更快,因为myisam内部维护了一个计数器,可以直接调取。

>>
其次,记录的内容不同,无论用户将二进制日志文件记录的格式设为STATEMENT还是ROW,又或者是MIXED,其记录的都是关于一个事务的具体操作内容,即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是关于每个页(Page)的更改的物理情况。

备份

数据备份(深入浅出Mysql 27章 备份与恢复)

 全备份与增量备份的比较。

 确保 MySQL 打开 log-bin 选项,有了 BINLOG,MySQL
才可以在必要的时候做完 整恢复,或基于时间点的恢复,或基于位置的恢复。

逻辑备份(将数据库中的数据备份为一个文本文件,备份的文件可以被查 看和编辑。)

物理备份

 冷备份:cp移动数据文件的方法。

 恢复:移动数据文件,使用 mysqlbinlog 工具恢复自备份以来的所有
BINLOG。

 热备份:(将要备份的表加读锁,然后再 cp 数据文件到备份目录。)

 MyISAM:mysqlhotcopy工具。

 ibbackup 是 Innobase 公司(www.innodb.com)的一个热备份工具。

2者selectcount(*)哪个更快,为什么

myisam更快,因为myisam内部维护了一个计数器,可以直接调取。

1. drop,delete与truncate的区别

drop直接删掉表 truncate删除表中数据,再插入时自增长id又从1开始
delete删除表中数据,可以加where字句。
(1)
DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE
TABLE
则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
(2) 表和索引所占空间。当表被TRUNCATE
后,这个表和索引所占用的空间会恢复到初始大小,而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
(3) 一般而言,drop > truncate > delete
(4) 应用范围。TRUNCATE 只能对TABLE;DELETE可以是table和view
(5) TRUNCATE 和DELETE只删除数据,而DROP则删除整个表(结构和数据)。
(6) truncate与不带where的delete
:只删除数据,而不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。
(7) delete语句为DML(data maintain Language),这个操作会被放到 rollback
segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。
(8) truncate、drop是DLL(data define
language),操作立即生效,原数据不放到 rollback segment中,不能回滚
(9) 在没有备份情况下,谨慎使用 drop 与
truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除,如果于事务无关,用truncate即可实现。如果和事务有关,或老师想触发trigger,还是用delete。
(10) Truncate table 表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE
语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE
速度快,且使用的系统和事务日志资源少。DELETE
语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE
通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
(11) TRUNCATE TABLE
删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用
DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。
(12) 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE
TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE
不记录在日志中,所以它不能激活触发器。

>> 对于InnoDB存储引擎而言,其是通过LSN(Log Sequence
Number)来标记版本的。而LSN是8字节的数字,其单位是字节。每个页有LSN,重做日志中也有LSN,Checkpoint也有LSN。可以通过命令SHOW
ENGINE INNODB STATUS来观察:

数据库ACID

数据库的ACID

数据库事务介绍

原子性(Atomicity)一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。

一致性(Consistency)数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性(Isolation)一个事务所做的修改在最终提交以前,对其他事务是不可见的。

持久性(Durability)一旦事务提交,则其所做的修改不会永久保存到数据库。

4 种隔离级别

MVVC的简单介绍

READ
UNCOMMITTED(未提交读)脏读
:事务中的修改,即使没有提交,对其他事务也都是可见的。

READ
COMMITTED(提交读)不可重复读
:事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

REPEATABLE
READ(可重复读):幻读:
一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。

SERIALIZABLE(可串行化) 强制事务串行执行

MVVC是个行级锁的变种,它在普通读情况下避免了加锁操作,自特定情况下加锁

表中有大字段X(例如:text类型),且字段X不会经常更新,以读为为主,请问

(1)、您是选择拆成子表,还是继续放一起;
(2)、写出您这样选择的理由。
拆带来的问题:连接消耗 + 存储拆分空间;不拆可能带来的问题:查询性能;
如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区)
顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的?
InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

3.MySQL的复制原理以及流程

基本原理流程,3个线程以及之间的关联;
1.
主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;

  1. 从:io线程——在使用start slave 之后,负责从master上拉取 binlog
    内容,放进 自己的relay log中;
  2. 从:sql执行线程——执行relay log中的语句;

>> 从MySQL
5.6版本开始,还可以通过information_schema架构下的表INNODB_BUFFER_POOL_STATS来观察缓冲的状态

索引

索引(存储引擎 快速找到记录的一种数据结构,索引的基本功能)

什么是B-Tree

MySQL索引背后的数据结构及算法原理

MySQL性能优化-慢查询分析、优化索引和配置

innodb的读写参数优化

(1)、读取参数
global buffer pool以及 local buffer;

(2)、写入参数;
innodb_flush_log_at_trx_commit
innodb_buffer_pool_size

(3)、与IO相关的参数;
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0

(4)、缓存参数以及缓存的适用场景。
query cache/query_cache_type

索引优化策略

  • ### 1 索引类型

    1.1 B-tree索引
    注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上,
    各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree,Myisam,innodb中,默认用B-tree索引,但抽象一下—B-tree系统,可理解为”排好序的快速查找结构”.

    1.2 hash索引
    在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)

    疑问: 既然hash的查找如此高效,为什么不都用hash索引?
    答: 
    1)hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,比主键为id为例,
    那么随着id的增长, id对应的行,在磁盘上随机放置.
    2)不法对范围查询进行优化.
    3)无法利用前缀索引. 比如 在btree中,
    field列的值“hellopworld”,并加索引 查询
    xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)
    因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
    4)排序也无法优化.
    5)必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据

  • ### 2 btree索引的常见误区

    2.1 在where条件常用的列上都加上索引
    例: where cat_id=3 and price>100 ;
    //查询第3个栏目,100元以上的商品
    误: cat_id上,和, price上都加上索引.
    错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.

    2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用
    误: 多列索引上,索引发挥作用,需要满足左前缀要求.

  • ### 在多列上建立索引后,查询语句发挥作用的索引:

    为便于理解, 假设ABC各10米长的木板, 河面宽30米.
    全值索引是则木板长10米,
    Like,左前缀及范围查询, 则木板长6米,
    自己拼接一下,能否过河对岸,就知道索引能否利用上.
    如上例中, where a=3 and b>10, and c=7,
    A板长10米,A列索引发挥作用
    A板正常接B板, B板索引发挥作用
    B板短了,接不到C板, C列的索引不发挥作用.

索引应用举例:

图片 2

  • innodb的主索引文件上
    直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
    myisam中, 主索引和次索引,都指向物理行(磁盘位置).

    注意: 对innodb来说, 
    1: 主键索引 既存储索引值,又在叶子中存储行的数据
    2: 如果没有主键, 则会Unique key做主键 
    3: 如果没有unique,则系统生成一个内部的rowid做主键.
    4:
    像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

  • ### 聚簇索引

    优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
    劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.
    聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,(不要用随机字符串或UUID)否则会造成大量的页分裂与页移动.

  • ### 高性能索引策略

    对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.
    对于innodb的主键,尽量用整型,而且是递增的整型.
    如果是无规律的数据,将会产生的页的分裂,影响速度.

  • ### 索引覆盖:

    索引覆盖是指
    如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为”索引覆盖”

  • ### 理想的索引

    1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.

    注:
    索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
    针对列中的值,从左往右截取部分,来建索引
    1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
    2: 截的越长, 重复度越低,区分度越高,
    索引效果越好,但带来的影响也越大–增删改变慢,并间接影响查询速度.

    所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.
    惯用手法: 截取不同长度,并测试其区分度,
    select count(distinct left(word,6))/count(*) from dict;

    对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.
    对于左前缀不易区分的列 ,建立索引的技巧:如 url列

    列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决
    1: 把列内容倒过来存储,并建立索引
    Moc.udiab.www//:ptth
    Ti.euxiz.www//://ptth
    这样左前缀区分度大,
    2: 伪hash索引效果
    同时存 url_hash列

    多列索引 多列索引的考虑因素—列的查询频率、列的区分度。

  • ### 索引与排序

    排序可能发生2种情况:
    1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, using index
    2:
    先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)

    我们的争取目标—–取出来的数据本身就是有序的! 利用索引来排序.

  • ### 重复索引与冗余索引

    重复索引: 是指 在同1个列(如age), 或者 顺序相同的几个列(age,school),
    建立了多个索引, 称为重复索引,
    重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.

    冗余索引:是指2个索引所覆盖的列有重叠,称为冗余索引
    比如x,m,列,加索引index x(x),index xm(x,m)
    x,xm索引, 两者的x列重叠了, 这种情况,称为冗余索引.
    甚至可以把 index mx(m,x) 索引也建立, mx, xm
    也不是重复的,因为列的顺序不一样.

  • ### 索引碎片与维护

    在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.
    我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.
    比如: 表的引擎为innodb , 可以 alter table xxx engine innodb
    optimize table 表名,也可以修复.

    注意:
    修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.
    这个过程,如果表的行数比较大,也是非常耗费资源的操作.所以,不能频繁的修复.

    如果表的Update操作很频率,可以按周/月,来修复.
    如果不频繁,可以更长的周期来做修复.

>> S和X锁都是行锁,兼容是指对同一记录(row)锁的兼容性情况。

事件

   
 
类似于Linux的定时任务,某个时间或者每隔一段时间执行一段SQL代码。

你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?

主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

7.MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别
char是一种固定长度的类型,varchar则是一种可变长度的类型

(2)、varchar(50)中50的涵义
最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order
by col采用fixed_length计算col长度(memory引擎也一样)

(3)、int(20)中20的涵义 是指显示字符的长度
但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001
~~~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
20表示最大显示宽度为20,但仍占4字节存储,存储范围不变;

(4)、mysql为什么这么设计
对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;

>>
Binlog_cache_use记录了使用缓冲写二进制日志的次数,binlog_cache_disk_use记录了使用临时文件写二进制日志的次数

MySQL索引详解 (一般使用磁盘I/O次数评价索引结构的优劣。)

 磁盘存取原理

 局部性原理与磁盘预读

M 阶 B-Tree

 图片 3

 根节点至少有2个子树。

 每个非叶子节点由n-1个key和n个指针组成。

 分支节点至少拥有m/2颗子树,最多拥有m个子树。(除根节点和叶子结点外)

 所有叶节点具有相同的深度,等于树高 h。

 每个叶子节点最少包含一个key和两个指针,最多包含2d-1个key和2d个指针。

B+ Tree

 内节点不存储data,只存储key。

 叶子节点不存储指针。

MySQL 索引实现

 MyISAM 索引文件和数据文件是分离,非聚集索引。

 InnoDB 叶节点包含了完整的数据记录,聚集索引。根据主键聚集。

500台db,在最快时间之内重启

采用docker swarm
或者自动化配置和部署工具,如Puppet、Chef、Ansible和SaltStack

2.数据库范式

1 第一范式(1NF)

在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。
所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。在第一范式(1NF)中表的每一行只包含一个实例的信息。简而言之,第一范式就是无重复的列。

2 第二范式(2NF)

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。
第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是非主属性非部分依赖于主关键字。

3 第三范式(3NF)

满足第三范式(3NF)必须先满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。(我的理解是消除冗余)

>>
在Linux和UNIX环境下,还可以使用UNIX域套接字。UNIX域套接字其实不是一个网络协议,所以只能在MySQL客户端和数据库实例在一台服务器上的情况下使用。用户可以在配置文件中指定套接字文件的路径,如–socket=/tmp/mysql.sock。当数据库实例启动后,用户可以通过下列命令来进行UNIX域套接字文件的查找:

EXPLAIN 字段介绍

 possible_keys:显示可能应用在这张表中的索引。

 key:实际使用的索引。

 key_len:使用的索引的长度,越短越好。

 ref:显示索引的哪一列被使用了。

 rows:MySQL认为必须检索的用来返回请求数据的行数。

 type:使用了何种类型。从最好到最差的连接类型为system、const(常量)、eq_ref、ref、range、index(索引全表扫描)和ALL(全表扫描)。

innodb的事务与日志的实现方式

(1)、有多少种日志;

  • 错误日志:记录出错信息,也记录一些警告信息或者正确的信息。
  • 查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。
  • 慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
  • 二进制日志binlog:记录对数据库执行更改的所有操作。
  • 中继日志relay log:
  • 事务日志 redo log / undo log:

(2)、事物的4种隔离级别

  • 读未提交(RU)
  • 读已提交(RC)
  • 可重复读(RR)
  • 串行

(3)、事务是如何通过日志来实现的,说得越深入越好。
事务日志是通过redo和innodb的存储引擎日志缓冲(Innodb log
buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log sequence
number)号; 当事务执行时,会往InnoDB存储引擎的日志
的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”

数据库相关

>>
非锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下,这是默认的读取方式,即读取不会占用和等待表上的锁。但是在不同事务隔离级别下,读取的方式不同,并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外,即使都是使用非锁定的一致性读,但是对于快照数据的定义也各不相同。

存储引擎

各种存储引擎的区别与联系   
 
(存储数据技术和策略,存储机制、索引技巧、锁定水平等)

数据库存储引擎 
   show table status 显示表的相关信息

InnoDB与MyISAM的比较(从5.7开始innodb存储引擎成为默认的存储引擎。)

 锁机制:行级锁,表级锁

 事务操作:事务安全,不支持

InnoDB
(1)可靠性要求比较高,要求事务;(2)表更新和查询都相当的频繁,并且行锁定的机会比较大的情况。

 MySQL4.1之后每个表的数据和索引存储在一个文件里。

 InnoDB
采用了MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE
READ(可重复读) ,行级锁。

 自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。

 外键约束。MySQL支持外键的存储引擎只有InnoDB。

 支持自动增加列AUTO_INCREMENT属性。

MyIsam  (1)做很多count
的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。

 表存储在两个文件中,数据文件(MYD)和索引文件(MYI)

 表级锁,读=共享锁,写=排它锁。

 适合选择密集型的表,插入密集型的表。

MySQL数据库cpu飙升到500%的话他怎么处理?

(1)、没有经验的,可以不问;
(2)、有经验的,问他们的处理思路。

  • 找出占用的线程杀掉,分析日志,找问题,解决
  • mysql> show processlist; 找出占用cpu的线程
  • 常见问题 :
    1. 睡眠连接过多,严重消耗mysql服务器资源(主要是cpu,
      内存),并可能导致mysql崩溃。
      解决办法 :
      mysql的配置my.ini文件中wait_timeout,
      即可设置睡眠连接超时秒数,如果某个连接超时,会被mysql自然终止。
      mysql> set global wait_timeout=20;
    2. 增加 tmp_table_size 值
    3. SQL语句没有建立索引
    4. 函数计算的,放到应用层进行

1.InnoDB的日志

InnoDB有很多日志,日志中有2个概念需要分清楚,逻辑日志和物理日志.

  • 1.1 逻辑日志
    有关操作的信息日志成为逻辑日志.
    比如,插入一条数据,undo逻辑日志的格式大致如下:
    <Ti,Qj,delete,U>
    Ti表示事务id,U表示Undo信息,Qj表示某次操作的唯一标示符

    undo日志总是这样:
    1). insert操作,则记录一条delete逻辑日志. 
    2). delete操作,则记录一条insert逻辑日志.
    3). update操作,记录相反的update,将修改前的行改回去.

  • 1.2 物理日志
    新值和旧值的信息日志称为物理日志. <Ti,Qj,V> 物理日志

    binlog(二进制日志)就是典型的逻辑日志,而事务日志(redo
    log)则记录的物理日志,他们的区别是什么呢?

    1. redo log
      是在存储引擎层产生的,binlog是在数据库上层的一种逻辑日志,任何存储引擎均会产生binlog.
    2. binlog记录的是sql语句, 重做日志则记录的是对每个页的修改.
    3. 写入的时间点不一样. binlog是在事务提交后进行一次写入,redo
      log在事务的进行中不断的被写入.
    4. redo log是等幂操作(执行多次等于执行一次,redo
      log记录<T0,A,950>记录新值,执行多少次都一样),binlog不一样;
  • 1.3 日志种类
    错误日志:记录出错信息,也记录一些警告信息或者正确的信息。
    查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。
    慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
    二进制日志:记录对数据库执行更改的所有操作。
    中继日志、事务日志等。

  • 1.4 总结
    1, redo log(事务日志)保证事务的原子性和持久性(物理日志)
    2, undo log保证事务的一致性,InnoDB的MVCC(多版本并发控制)也是用undo
    log来实现的(逻辑日志).
    3, redo log中带有有checkPoint,用来高效的恢复数据.
    4, 物理日志记录的是修改页的的详情,逻辑日志记录的是操作语句.
    物理日志恢复的速度快于逻辑日志.

>>
假设有张用户消费表,每次用户购买一样物品后其金额都是减的,若这时有“不怀好意”的用户做了类似减去一个负值的操作,这样用户的钱没减少反而会不断增加

Mysql死锁问题

Mysql悲观锁总结和实践

Mysql乐观锁总结和实践

SELECT … LOCK IN SHARE MODE SELECT … FOR UPDATE:(LOCK IN SHARE
MODE 在有一方事务要Update 同一个表单时很容易造成死锁)

乐观锁:取锁失败,产生回溯时影响效率。

 取数据时认为其他线程不会对数据进行修改。

 更新时判断是否对数据进行修改,版本号机制或CAS操作。

悲观锁:每次取数据都会加锁。

innodb_lock_wait_timeout 等待锁超时回滚事务:  【超时法】

直观方法是在两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。

wait-for graph算法来主动进行死锁检测:  【等待图法】

innodb还提供了wait-for
graph算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for
graph算法都会被触发。

5点不同

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

  • InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败;

  • InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。而MyISAM是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  • InnoDB支持MVCC, 而MyISAM不支持

  • InnoDB不保存表的具体行数,执行select count(*) from
    table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
    但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含
    where条件时,两种表的操作是一样的。

  • Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高;

  • 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。

  • DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除

4.数据库优化相关

  • 临时表在如下几种情况被创建(临时表会消耗性能):
    1、如果group by 的列没有索引,必产生内部临时表。
    2、如果order by 与group by为不同列时,或多表联查时order by ,group by
    包含的列不是第一张表的列,将会产生临时表 
    3、distinct 与order by 一起使用可能会产生临时表
    4、如果使用SQL_SMALL_RESULT,MySQL会使用内存临时表,除非查询中有一些必须要把临时表建立在磁盘上.
    5、union合并查询时会用到临时表
    6、某些视图会用到临时表,如使用temptable方式建立,或使用union或聚合查询的视图
    想确定查询是否需要临时表,可以用EXPLAIN查询计划,并查看Extra列,看是否有Using
    temporary.

  • 建表: 表结构的拆分,如核心字段都用int,char,enum等定长结构
    非核心字段,或用到text,超长的varchar,拆出来单放一张表.
    建索引: 合理的索引可以减少内部临时表 
    写语句: 不合理的语句将导致大量数据传输以及内部临时表的使用

  • 表的优化与列类型选择
    表的优化:
    1: 定长与变长分离
    如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time 
    即每一单元值占的字节是固定的.
    核心且常用字段,宜建成定长,放在一张表.
    而varchar, text,blob,这种变长字段,适合单放一张表,
    用主键与核心表关联起来.
    2:常用字段和不常用字段要分离.
    需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.
    3:合理添加冗余字段.

  • 列选择原则:
    1:字段类型优先级 整型 > date,time > enum,char >
    varchar > blob

    列的特点分析:
    整型: 定长,没有国家/地区之分,没有字符集的差异
    time定长,运算快,节省空间. 考虑时区,写sql时不方便 where >
    ‘2005-10-12’;
    enum: 能起来约束值的目的,
    内部用整型来存储,但与char联查时,内部要经历串与值的转化 Char 定长,
    考虑字符集和(排序)校对集 varchar, 不定长
    要考虑字符集的转换与排序时的校对集,速度慢.相比于char增加了一个长度标识,处理时需要多运算一次。
    text/Blob 无法使用内存临时表

    附: 关于date/time的选择,明确意见

    2: 够用就行,不要慷慨 (如smallint,varchar(N))
    原因: 大的字段浪费内存,影响速度
    以年龄为例 tinyint unsigned not null ,可以存储255岁,足够.
    用int浪费了3个字节 以varchar(10) ,varchar(300)存储的内容相同,
    但在表联查时,varchar(300)要花更多内存

    3: 尽量避免用NULL()
    原因: NULL不利于索引,要用特殊的字节来标注.
    每一行多了一个字节在磁盘上占据的空间其实更大.

    Enum列的说明
    1: enum列在内部是用整型来储存的
    2: enum列与enum列相关联速度最快
    3: enum列比(var)char 的弱势—在碰到与char关联时,要转化. 要花时间.
    4:
    优势在于,当char非常长时,enum依然是整型固定长度.当查询的数据量越大时,enum的优势越明显.
    5: enum与char/varchar关联
    ,因为要转化,速度要比enum->enum,char->char要慢,但有时也这样用—–就是在数据量特别大时,可以节省IO.

  • SQL语句优化
    1)应尽量避免在 where
    子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
    2)应尽量避免在 where 子句中对字段进行 null
    值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
    select id from t where num is null
    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
    select id from t where num=0
    3)很多时候用 exists 代替 in 是一个好的选择
    4)用Where子句替换HAVING 子句 因为HAVING
    只会在检索出所有记录之后才对结果集进行过滤

  • explain出来的各种item的意义;
    select_type 
    表示查询中每个select子句的类型
    type
    表示MySQL在表中找到所需行的方式,又称“访问类型”
    possible_keys 
    指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
    key
    显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
    key_len
    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
    ref
    表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值 
    Extra
    包含不适合在其他列中显示但十分重要的额外信息

  • profile的意义以及使用场景;
    查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中
    Systemlock, Table lock 花多少时间等等


>> InnoDB存储引擎从InnoDB 1.0.x版本开始支持一种称为Fast Index
Creation(快速索引创建)的索引创建方式——简称FIC。

 具体类型介绍:

单列索引:不允许为空

 普通索引 不允许有空值

 唯一索引

 主键索引 在 InnoDB 引擎中很重要

组合引擎:多个字段上创建的索引,复合索引时遵循最左前缀原则。

 查询中某个列有范围查询,则其右边的所有列都无法使用查询

全文索引:

空间索引:

参考:细说mysql索引、我的MYSQL学习心得(九)
索引

innodb引擎的4大特性

  • 插入缓冲(insert buffer)
    插入主键聚集索引,是顺序的,不需要磁盘的随机读取;但是这也导致同一个表中的非聚集索引不是顺序的,因为B+树的特性决定了非聚集索引插入的离散型。
    插入缓存就是为提高非聚集索引的插入和更新操作的性能而做的优化设计,其原理将插入数据先放到内存就直接返回上层,上层看来已经插入成功,其实插入数据还在内存中,内部会触发内存的索引数据与物理的索引数据进行合并操作,合并时将多个插入合并到一个操作(正好一个索引页),这样大大提高了对非聚集索引插入的性能。

  • 二次写(double write)
    为了提升数据页的可靠性。
    写数据页的时候宕机怎么办?
    重做日志,但是如果物理页已经损坏了怎么版?
    用doubleWrite:发现物理页损坏了,则找到其前面的一个副本,用副本来还原当前页,再重做日志。

  • 自适应哈希索引(ahi)
    原先的索引是B+树结构,当查询频繁,建立哈希可以提高效率,则自动构建哈希索引,提高速度。

  • 异步IO(Async IO)
    同时发起多个IO请求(索引页的扫描),可以将多个IO请求合并为一个IO操作,同时将每个IO请求的结果进行Merge。

  • 刷新邻接页
    刷新一个脏页的同事检查所在区的其他页是否需要一起刷新。

5.innodb引擎的4大特性

插入缓冲(insert buffer),二次写(double
write),自适应哈希索引(ahi),预读(read ahead)

>> InnoDB从1.0.x版本开始引入了Change Buffer,可将其视为Insert
Buffer的升级。从这个版本开始,InnoDB存储引擎可以对DML操作——INSERT、DELETE、UPDATE都进行缓冲,他们分别是:Insert
Buffer、Delete Buffer、Purge buffer。

sql优化

  • 使用explain,分析优化, 各item的意义;
    select_type
    表示查询中每个select子句的类型
    type
    表示MySQL在表中找到所需行的方式,又称“访问类型”
    possible_keys
    指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
    key
    显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
    key_len
    表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
    ref
    表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
    Extra
    包含不适合在其他列中显示但十分重要的额外信息

  • profile的意义以及使用场景
    查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中
    Systemlock, Table lock 花多少时间等等

数据库相关面试题

>>
InnoDB存储引擎表是索引组织的,即B+Tree的结构,这样每个页中至少应该有两条行记录(否则失去了B+Tree的意义,变成链表了)。因此,如果页中只能存放下一条记录,那么InnoDB存储引擎会自动将行数据存放到溢出页中

4.MySQL中myisam与innodb的区别,至少5点

1>.InnoDB支持事物,而MyISAM不支持事物
2>.InnoDB支持行级锁,而MyISAM支持表级锁
3>.InnoDB支持MVCC, 而MyISAM不支持
4>.InnoDB支持外键,而MyISAM不支持
5>.InnoDB不支持全文索引,而MyISAM支持。

>>
。因此对于不同的分区类型,MySQL数据库对于NULL值的处理也是各不相同。

第9章 性能调优

>>
此外需要注意的是,MySQL官方手册中定义的65535长度是指所有VARCHAR列的长度总和,如果列的长度总和超出这个长度,依然无法创建

>>
具体来看,缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓冲(insert
buffer)、自适应哈希索引(adaptive hash
index)、InnoDB存储的锁信息(lock info)、数据字典信息(data
dictionary)等

>>
InnoSQL对information_schema进行了扩展,添加了两张数据字典表,这样用户可以非常方便和快捷地查看undo的信息。首先增加的数据字典表为INNODB_TRX_ROLLBACK_SEGMENT。

>>
从InnoDB存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block),

>>
分区功能并不是在存储引擎层完成的,因此不是只有InnoDB存储引擎支持分区,常见的存储引擎MyISAM、NDB等都支持。但也并不是所有的存储引擎都支持,如CSV、FEDORATED、MERGE等就不支持。在使用分区功能前,应该对选择的存储引擎对分区的支持有所了解。

>> B+树索引的本质就是B+树在数据库中的实现。

>>
从重做日志缓冲往磁盘写入时,是按512个字节,也就是一个扇区的大小进行写入。因为扇区是写入的最小单位,因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有doublewrite。

>>
许多数据库的文档会这样告诉读者:聚集索引按照顺序物理地存储数据。如果看图5-14,可能也会有这样的感觉。但是试想一下,如果聚集索引必须按照特定顺序存放物理记录,则维护成本显得非常之高。所以,聚集索引的存储并不是物理上连续的,而是逻辑上连续的。

>> InnoDB存储引擎支持覆盖索引(covering
index,或称索引覆盖),即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小要远小于聚集索引,因此可以减少大量的IO操作。

>>
常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页,甚至是全部的页,而这些页通常来说又仅在这次查询操作中需要,并不是活跃的热点数据。如果页被放入LRU列表的首部,那么非常可能将所需要的热点数据页从LRU列表中移除,而在下一次需要读取该页时,InnoDB存储引擎需要再次访问磁盘。

>>
Facebook采用PHP脚本来现实OSC,而并不是通过修改InnoDB存储引擎源码的方式。OSC最初由Facebook的员工Vamsi
Ponnekanti开发。此外,OSC借鉴了开源社区之前的工具The openarkkit toolkit
oak-online-alter-table。实现OSC步骤如下:

>> SELECT a,HEX(a)        -> FROM j\G;

隔离性主要是处理和避免并发情况下出现的一些异常的问题。

Spring应该是会帮我们处理回滚的。自己用JDBC写我们捕获异常后,一般也会手动回滚。

姜承尧

>>
InnoDB存储引擎不会在执行rollback时去收缩这个表空间。虽然InnoDB不会回收这些空间,但是会自动判断这些undo信息是否还需要,如果不需要,则会将这些空间标记为可用空间,供下次undo使用。

>>
从MySQL数据库的官方手册可得知,著名的Internet新闻站点Slashdot.org运行在InnoDB上。Mytrix、Inc.在InnoDB上存储超过1
TB的数据,还有一些其他站点在InnoDB上处理插入/更新操作的速度平均为800次/秒。这些都证明了InnoDB是一个高性能、高可用、高可扩展的存储引擎。

>>
在关闭时,参数innodb_fast_shutdown影响着表的存储引擎为InnoDB的行为。该参数可取值为0、1、2,默认值为1。

>>
还需要特别注意的是InnoDB存储引擎中自增长的实现和MyISAM不同,MyISAM存储引擎是表锁设计,自增长不用考虑并发插入的问题。因此在master上用InnoDB存储引擎,在slave上用MyISAM存储引擎的replication架构下,用户必须考虑这种情况。

>>
设置innodb_data_file_path参数后,所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。若设置了参数innodb_file_per_table,则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间

>>
计算TPS的方法是(com_commit+com_rollback)/time。但是利用这种方法进行计算的前提是:所有的事务必须都是显式提交的,如果存在隐式地提交和回滚(默认autocommit=1),不会计算到com_commit和com_rollback变量中。

>>
Oracle数据库中产生死锁的常见原因是没有对外键添加索引,而InnoDB存储引擎会自动对其进行添加,因而能够很好地避免了这种情况的发生

>>
对于OLAP的应用,分区的确是可以很好地提高查询的性能,因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表,其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区,则只需要扫描相应的分区即可。这就是前面介绍的Partition
Pruning技术。

>> 可以通过命令SHOW ENGINE INNODB STATUS来观察InnoDB中的IO
Thread:

>>
扁平事务的主要限制是不能提交或者回滚事务的某一部分,或分几个步骤提交。

>>
InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive
Hash Index,AHI)。

>>
若为每一个事务分配一个单独的undo页会非常浪费存储空间,特别是对于OLTP的应用类型

>>
参数innodb_force_recovery影响了整个InnoDB存储引擎恢复的状况。该参数值默认为0,代表当发生需要恢复时,进行所有的恢复操作,当不能进行有效恢复时,如数据页发生了corruption,MySQL数据库可能发生宕机(crash),并把错误写入错误日志中去。

>>
latch一般称为闩锁(轻量级的锁),因为其要求锁定的时间必须非常短。若持续的时间长,则应用的性能会非常差。在InnoDB存储引擎中,latch又可以分为mutex(互斥量)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性,并且通常没有死锁检测的机制。

>> 对于InnoDB存储引擎中的latch,可以通过命令SHOW ENGINE INNODB
MUTEX来进行查看

>还记得6.6节中所说的内容吗?InnoDB存储引擎并不会回滚大部分的错误异常,但是死锁除外。发现死锁后,InnoDB存储引擎会马上回滚一个事务,这点是需要注意的。因此如果在应用程序中捕获了1213这个错误,其实并不需要对其进行回滚。

>>
如果启用了innodb_file_per_table的参数,需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲Bitmap页,其他类的数据,如回滚(undo)信息,插入缓冲索引页、系统事务信息,二次写缓冲(Double
write
buffer)等还是存放在原来的共享表空间内。这同时也说明了另一个问题:即使在启用了参数innodb_file_per_table之后,共享表空间还是会不断地增加其大小。

>>
如果在一棵高度为3的辅助索引树中查找数据,那需要对这棵辅助索引树遍历3次找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终找到一个完整的行数据所在的页,因此一共需要6次逻辑IO访问以得到最终的一个数据页。

>> History list length就代表了undo
log的数量,这里为12。purge操作会减少该值。然而由于undo
log所在的页可以被重用,因此即使操作发生,History list
length的值也可以不为0。

>>
由于将多个硬盘组合成为一个逻辑扇区,RAID看起来就像一个单独的硬盘或逻辑存储单元,因此操作系统只会把它当作一个硬盘。

>>
但是在某些情况下,用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句,即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读(locking
read)操作:□ SELECT…FOR UPDATE□ SELECT…LOCK IN SHARE MODE

>> 正如前面所说的,目前Insert
Buffer存在一个问题是:在写密集的情况下,插入缓冲会占用过多的缓冲池内存(innodb_buffer_pool),默认最大可以占用到1/2的缓冲池内存。

>> InnoDB存储引擎的逻辑存储结构大致如图4-1所示。

>>
重做日志缓冲一般不需要设置得很大,因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件,因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可

>>
要查看二进制日志文件的内容,必须通过MySQL提供的工具mysqlbinlog。对于STATEMENT格式的二进制日志文件,在使用mysqlbinlog后,看到的就是执行的逻辑SQL语句

>>
在对缓冲池的脏页进行刷新时,并不直接写磁盘,而是会通过memcpy函数将脏页先复制到内存中的doublewrite
buffer,之后通过doublewrite
buffer再分两次,每次1MB顺序地写入共享表空间的物理磁盘上,然后马上调用fsync函数,同步磁盘,避免缓冲写带来的问题。在这个过程中,因为doublewrite页是连续的,因此这个过程是顺序写的,开销并不是很大。在完成doublewrite页的写入后,再将doublewrite
buffer中的页写入各个表空间文件中,此时的写入则是离散的。

>> COMMIT和COMMIT
WORK语句基本是一致的,都是用来提交事务。不同之处在于COMMIT
WORK用来控制事务结束后的行为是CHAIN还是RELEASE的。如果是CHAIN方式,那么事务就变成了链事务。

第2章 InnoDB存储引擎

>> 另一张数据字典表为INNODB_TRX_UNDO,用来记录事务对应的undo
log,方便DBA和开发人员详细了解每个事务产生的undo量

>>
对于MySQL数据库来说,官方没有提供真正的增量备份的方法,大部分是通过二进制日志完成增量备份的工作。这种备份较之真正的增量备份来说,效率还是很低的

>>
当发生数据库宕机时,可能InnoDB存储引擎正在写入某个页到表中,而这个页只写了一部分,比如16KB的页,只写了前4KB,之后就发生了宕机,这种情况被称为部分写失效(partial
page
write)。在InnoDB存储引擎未使用doublewrite技术前,曾经出现过因为部分写失效而导致数据丢失的情况。有经验的DBA也许会想,如果发生写失效,可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到,重做日志中记录的是对页的物理操作,如偏移量800,写’aaaa’记录。如果这个页本身已经发生了损坏,再对其进行重做是没有意义的。这就是说,在应用(apply)重做日志前,用户需要一个页的副本,当写入失效发生时,先通过页的副本来还原该页,再进行重做,这就是doublewrite

>>
可以看到,不管怎么变化,B+树总是会保持平衡。但是为了保持平衡对于新插入的键值可能需要做大量的拆分页(split)操作。因为B+树结构主要用于磁盘,页的拆分意味着磁盘的操作,所以应该在可能的情况下尽量减少页的拆分操作。

>>
通过上面的例子可以看到,delete操作并不直接删除记录,而只是将记录标记为已删除,也就是将记录的delete
flag设置为1。而记录最终的删除是在purge操作中完成的。

>>
因为存在上述写入方面的问题,闪存提供的读写速度是非对称的。读取速度要远快于写入的速度,因此对于固态硬盘在数据库中的应用,应该好好利用其读取的性能,避免过多的写入操作。

>>
即使将sync_binlog设为1,还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时,在一个事务发出COMMIT动作之前,由于sync_binlog为1,因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志,但是提交还没有发生,并且此时发生了宕机,那么在MySQL数据库下次启动时,由于COMMIT操作并没有发生,这个事务会被回滚掉。但是二进制日志已经记录了该事务信息,不能被回滚。

第3章 文件

>>
因此对于不能进行索引覆盖的情况,优化器选择辅助索引的情况是,通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的,即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘,随机读操作非常快,同时有足够的自信来确认使用辅助索引可以带来更好的性能,那么可以使用关键字FORCE
INDEX来强制使用某个索引

>>
建议在从服务上启用read-only选项,这样能保证从服务器上的数据仅与主服务器进行同步,避免其他线程修改数据

>> RAID 5具有和RAID
0相近似的数据读取速度,只是多了一个奇偶校验信息,写入数据的速度相当慢,若使用Write
Back可以让性能改善不少。

>>
当启动实例时,MySQL数据库会去读取配置文件,根据配置文件的参数来启动数据库实例。这与Oracle的参数文件(spfile)相似,不同的是,Oracle中如果没有参数文件,在启动实例时会提示找不到该参数文件,数据库启动失败。而在MySQL数据库中,可以没有配置文件,在这种情况下,MySQL会按照编译时的默认参数设置启动实例

事务提交以后,要么成功,要么失败,失败了就一定要回滚。

>>
当出现MySQL数据库不能正常启动时,第一个必须查找的文件应该就是错误日志文件,该文件记录了错误信息,能很好地指导用户发现问题。

>>
。稍有不同的是InnoDB存储引擎对传统的LRU算法做了一些优化。在InnoDB的存储引擎中,LRU列表中还加入了midpoint位置。新读取到的页,虽然是最新访问的页,但并不是直接放入到LRU列表的首部,而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下称为midpoint
insertion strategy。

>>
从概念上来说,数据库是文件的集合,是依照某种数据模型组织起来并存放于二级存储器中的数据集合;数据库实例是程序,是位于用户与操作系统之间的一层数据管理软件,用户对数据库数据的任何操作,包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的,应用程序只有通过数据库实例才能和数据库打交道。

>> 需要注意的是,Native
AIO需要操作系统提供支持。Windows系统和Linux系统都提供Native
AIO支持,而Mac OSX系统则未提供

>>
默认情况下,MySQL实例会按照一定的顺序在指定的位置进行读取,用户只需通过命令mysql–help
| grep my.cnf来寻找即可。

>> 每个页存放的行记录也是有硬性定义的,最多允许存放16KB /
2-200行的记录,即7992行记录

>>
联合索引的第二个好处是已经对第二个键值进行了排序处理。例如,在很多情况下应用程序都需要查询某个用户的购物情况,并按照时间进行排序,最后取出最近三次的购买记录,这时使用联合索引可以避免多一次的排序操作,因为索引本身在叶子节点已经排序了。

>> InnoDB
1.0.x版本开始引入压缩页,即每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K,因此每个区对应页的数量就应该为512、256、128。

>>
我用python写了一个py_innodb_page_info小工具,用来查看表空间中各页的类型和信息,用户可以在code.google.com上搜索david-mysql-tools进行查找

>>
事务(Transaction)是数据库区别于文件系统的重要特性之一。在文件系统中,如果正在写文件,但是操作系统突然崩溃了,这个文件就很有可能被破坏。当然,有一些机制可以把文件恢复到某个时间点。不过,如果需要保证两个文件同步,这些文件系统可能就显得无能为力了。例如,在需要更新两个文件时,更新完一个文件后,在更新完第二个文件之前系统重启了,就会有两个不同步的文件。

>> 数据库中的B+树索引可以分为聚集索引(clustered
inex)和辅助索引(secondary
index[插图],但是不管是聚集还是辅助的索引,其内部都是B+树的,即高度平衡的,叶子节点存放着所有的数据。聚集索引与辅助索引不同的是,叶子节点存放的是否是一整行的信息。

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注