每一个非主属性完全依赖于金沙js娱乐场官方网站:,char是一种固定长度的类型

2NF,每一个非主属性完全依赖于,varchar则是一种可变长度的类型,char是一种固定长度的类型,根据配置文件的参数来启动数据库实例,而在MySQL数据库中,第二范式要求实体的属性完全依赖于主关键字,如果不存在部门信息表

金沙js娱乐场官方网站 2

恢复

     完全恢复

 将备份作为输入执行。

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

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

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

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

 

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

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

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

>>
MySQL数据库支持的分区类型为水平分[插图],并不支持垂直分[插图]。此外,MySQL数据库的分区是局部分区索引,一个分区中既存放了数据又存放了索引。而全局分区是指,数据存放在各个分区中,但是所有数据的索引放在一个对象中。目前,MySQL数据库还不支持全局分区。

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

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

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

 磁盘存取原理

 局部性原理与磁盘预读

M 阶 B-Tree

 金沙js娱乐场官方网站 1

 根节点至少有2个子树。

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

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

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

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

B+ Tree

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

 叶子节点不存储指针。

MySQL 索引实现

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

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

表中有大字段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将完成表锁,,并发将无从谈起

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

9.什么是视图?以及视图的使用场景有哪些?

视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。
只暴露部分字段给访问者,所以就建一个虚表,就是视图。
查询的数据来源于不同的表,而查询者希望以统一的方式查询,这样也可以建立一个视图,把多个表查询结果联合起来,查询者只需要直接从视图中获取数据,不必考虑数据来源于不同表所带来的差异。

视图 

MySQL数据库视图

MySQL –
视图算法

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

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

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

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

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

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

innodb引擎的4大特性

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

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

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

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

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

>>
在单个节点上运行分布式事务没有太大的实际意义,但是要在MySQL数据库的命令下演示多个节点参与的分布式事务也是行不通的。通常来说,都是通过编程语言来完成分布式事务的操作的。当前Java的JTA(Java
Transaction
API)可以很好地支持MySQL的分布式事务,需要使用分布式事务应该认真参考其API

1.何为范式?

第一范式(1NF):第一范式就是无重复属性的列。

第二范式(2NF):第二范式是在第一范式的基础上建立起来的,即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

第二范式要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是非主属性非部分依赖于主关键字。

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

索引类型:

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

 哈希索引

 无法用于排序。

 只支持全部匹配。

 只支持等值比较。

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

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

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

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

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

>>
对于辅助索引的创建,InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中,不需要重建表,因此速度较之前提高很多,并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了,InnoDB存储引擎只需更新内部视图,并将辅助索引的空间标记为可用,同时删除MySQL数据库内部视图上对该表的索引定义即可。这里需要特别注意的是,临时表的创建路径是通过参数tmpdir进行设置的。用户必须保证tmpdir有足够的空间可以存放临时表,否则会导致创建索引失败。

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

(1)、有多少种日志;错误日志:记录出错信息,也记录一些警告信息或者正确的信息。查询日志:记录所有对数据库请求的信息,不论这些请求是否得到了正确的执行。慢查询日志:设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。二进制日志:记录对数据库执行更改的所有操作。中继日志:事务日志:
(2)、事物的4种隔离级别隔离级别读未提交(RU)读已提交(RC)可重复读(RR)串行
(3)、事务是如何通过日志来实现的,说得越深入越好。事务日志是通过redo和innodb的存储引擎日志缓冲(Innodb
log buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log
sequence number)号;
当事务执行时,会往InnoDB存储引擎的日志的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”。

数据库ACID

数据库的ACID

数据库事务介绍

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

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

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

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

4 种隔离级别

MVVC的简单介绍

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

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

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

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

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

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 花多少时间等等

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

2.sql 语句优化点有哪些?

  • 应尽量避免在 where 子句中使用 != 或 <>
    操作符,否则将引擎放弃使用索引而进行全表扫描。

  • 应尽量避免在 where 子句中对字段进行 null
    值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询:

select id from t where num=0

  • 很多时候用 exists 代替 in 是一个好的选择

  • 用 Where 子句替换 HAVING 子句 因为 HAVING
    只会在检索出所有记录之后才对结果集进行过滤

触发器

 触发器的触发事件 , 可以是 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语句来区分对待记录的日志形式

>>
Cardinality为NULL,在某些情况下可能会发生索引建立了却没有用到的情况。或者对两条基本一样的语句执行EXPLAIN,但是最终出来的结果不一样:一个使用索引,另外一个使用全表扫描。这时最好的解决办法就是做一次ANALYZE
TABLE的操作。

12.innodb引擎的4大特性

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

备份

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

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

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

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

物理备份

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

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

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

 MyISAM:mysqlhotcopy工具。

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

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. 函数计算的,放到应用层进行

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

4.索引的实现?

平衡多路搜索树B树(B-tree)。B树(Balance Tree)又叫做B-
树(其实B-是由B-tree翻译过来,所以B-树和B树是一个概念)
,它就是一种平衡多路查找树。下图就是一个典型的B树:

金沙js娱乐场官方网站 2

image.png

日志

错误日志:记录了当 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;

>>
当InnoDB存储引擎回滚时,它实际上做的是与先前相反的工作。对于每个INSERT,InnoDB存储引擎会完成一个DELETE;对于每个DELETE,InnoDB存储引擎会执行一个INSERT;对于每个UPDATE,InnoDB存储引擎会执行一个相反的UPDATE,将修改前的行放回去。

8.drop、delete与truncate分别在什么场景之下使用?

1.不再需要一张表的时候,用drop。
2.想删除部分数据行时候,用delete,并且带上where子句
3.保留表而删除所有数据的时候用truncate

事件

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

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

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

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

3.索引是什么?有什么作用以及优缺点?

索引是对数据库表中一或多个列的值进行排序的结构,是帮助MySQL高效获取数据的数据结构。

优点:

第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。

第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

第四,在使用分组和排序
子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

缺点:

第一,创建索引和维护索引要耗费时间,这种时间随着数据 量的增加而增加。

第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

存储引擎

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

数据库存储引擎 
   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中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)存储和计算均一样;

>>
在InnoDB存储引擎中,Cardinality统计信息的更新发生在两个操作中:INSERT和UPDATE。

5.什么是事务?

事务(Transaction)是并发控制的基本单位。所谓的事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。

索引

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

什么是B-Tree

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

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

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

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

>>
另外需要注意的是,_rowid只能用于查看单个列为主键的情况,对于多列组成的主键就显得无能为力了

14.MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的?

InnoDB是基于索引来完成行锁例: select * from tab_with_index where id =
1 for update;for update 可以根据条件来完成行锁锁定,并且 id
是有索引键的列,如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起。

三范式

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

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

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

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

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

4NF :没有多值依赖。

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

>> 在InnoDB存储引擎中大量使用了AIO(Async
IO)来处理写IO请求,这样可以极大提高数据库的性能。而IO
Thread的工作主要是负责这些IO请求的回调(call back)处理

7.简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除,但是三者有一些差别。
delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl,
操作立即生效,原数据不放到rollback segment中,不能回滚.
操作不触发trigger.
如果你对这三者的用法还不太熟悉,建议阅读: drop、truncate和delete的区别

 具体类型介绍:

单列索引:不允许为空

 普通索引 不允许有空值

 唯一索引

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

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

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

全文索引:

空间索引:

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

MySQL的复制原理以及流程

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

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

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

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

InnoDB支持事物,而MyISAM不支持事物
InnoDB支持行级锁,而MyISAM支持表级锁
InnoDB支持MVCC, 而MyISAM不支持
InnoDB支持外键,而MyISAM不支持
InnoDB不支持全文索引,而MyISAM支持。

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不会重新建立表,而是一行一行的删除

>>
由于文档的DML操作实际并不删除索引中的数据,相反还会在对应的DELETED表中插入记录,因此随着应用程序的允许,索引会变得非常大,即使索引中的有些数据已经被删除,查询也不会选择这类记录。

6.数据库的乐观锁和悲观锁是什么?

数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。
悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作
乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。

数据类型

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 二进制方式存储

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存储引擎的默认设置下,这是默认的读取方式,即读取不会占用和等待表上的锁。但是在不同事务隔离级别下,读取的方式不同,并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外,即使都是使用非锁定的一致性读,但是对于快照数据的定义也各不相同。

15.问了下MySQL数据库cpu飙升到500%的话他怎么处理?

列出所有进程 show processlist 观察所有进程
多秒没有状态变化的(干掉)查看超时日志或者错误日志
(做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,,,,当然不排除网络状态突然断了,,导致一个请求服务器只接受到一半,比如where子句或分页子句没有发送,当然的一次被坑经历)。

EXPLAIN 字段介绍

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

 key:实际使用的索引。

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

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

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

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

mysql中myisam与innodb的区别

>> 相对于latch的查看,lock信息就显得直观多了。用户可以通过命令SHOW
ENGINE INNODB
STATUS及information_schema架构下的表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS来观察锁的信息

16.你们数据库是否支持emoji表情,如果不支持,如何操作?

如果是utf8字符集的话,需要升级至utf8_mb4方可支持。

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

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

>>
而这棵B+树存放在共享表空间中,默认也就是ibdata1中。因此,试图通过独立表空间ibd文件恢复表中数据时,往往会导致CHECK
TABLE失败

>>
用户通常对undo有这样的误解:undo用于将数据库物理地恢复到执行语句或事务之前的样子——但事实并非如此。undo是逻辑日志,因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了,但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中,可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如,一个事务在修改当前一个页中某几条记录,同时还有别的事务在对同一个页中另几条记录进行修改。因此,不能将一个页回滚到事务开始的样子,因为这样会影响其他事务正在进行的工作。

>> 当前3TB的MySQL数据库已并不少见,但是3
TB的内存却非常少见。目前Oracle Exadata旗舰数据库一体机也就只有2
TB的内存。

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

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

>>
对于MySQL数据库的OLTP测试,和fileio一样需要经历prepare、run和cleanup阶段。prepare阶段会根据选项产生一张指定行数的表,默认表在sbtest架构下,表名为sbtest(sysbench默认生成表的存储引擎为InnoDB)。例如创建一张8000W的表:

>>
用户可以通过额外的参数long_query_io将超过指定逻辑IO次数的SQL语句记录到slow
log中。该值默认为100,即表示对于逻辑读取次数大于100的SQL语句,记录到slow
log中。而为了兼容原MySQL数据库的运行方式,还添加了参数slow_query_type,用来表示启用slow
log的方式

>>
丢失更新是另一个锁导致的问题,简单来说其就是一个事务的更新操作会被另一个事务的更新操作所覆盖,从而导致数据的不一致。

>> 因此,除了超时机制,当前数据库还都普遍采用wait-for
graph(等待图)的方式来进行死锁检测。较之超时的解决方案,这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式

>>
默认情况下,二进制日志并不是在每次写的时候同步到磁盘(用户可以理解为缓冲写)。因此,当数据库所在操作系统发生宕机时,可能会有最后一部分数据没有写入二进制日志文件中,这会给恢复和复制带来问题

>>
因为已经用完了32个碎片页,新的页会采用区的方式进行空间的申请,如果此时用户再通过py_innodb_page_info工具来看表空间文件t1.ibd,应该可以看到很多类型为Freshly
Allocated Page的页:

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

>>
如果程序是串行的,那么不可能发生死锁。死锁只存在于并发的情况,而数据库本身就是一个并发运行的程序,因此可能会发生死锁。

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

>>
使用事务的表存储引擎(如InnoDB存储引擎)时,所有未提交(uncommitted)的二进制日志会被记录到一个缓存中去,等该事务提交(committed)时直接将缓冲中的二进制日志写入二进制日志文件,而该缓冲的大小由binlog_cache_size决定,默认大小为32K。

>>
二进制日志会记录所有与MySQL数据库有关的日志记录,包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关该存储引擎本身的事务日志。

>>
在前面介绍的RANGE、LIST、HASH和KEY这四种分区中,分区的条件是:数据必须是整型(interger),如果不是整型,那应该需要通过函数将其转化为整型,如YEAR(),TO_DAYS(),MONTH()等函数。MySQL5.5版本开始支持COLUMNS分区,可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区,分区根据类型直接比较而得,不需要转化为整型。此外,RANGE
COLUMNS分区可以对多个列的值进行分区。

>> 在MySQL官方文档中将不可重复读的问题定义为Phantom
Problem,即幻像问题。

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

>>
正如前面所介绍的那样,联合索引(a,b)其实是根据列a、b进行排序,因此下列语句可以直接使用联合索引得到结果:
   SELECT … FROM TABLE WHERE a=xxx ORDER BY b

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

>>
平衡二叉树的查找性能是比较高的,但不是最高的,只是接近最高性能。最好的性能需要建立一棵最优二叉树,但是最优二叉树的建立和维护需要大量的操作,因此,用户一般只需建立一棵平衡二叉树即可。

>> 一致性的非锁定读(consistent nonlocking
read)是指InnoDB存储引擎通过行多版本控制(multi
versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作,这时读取操作不会因此去等待行上锁的释放。相反地,InnoDB存储引擎会去读取行的一个快照数据。

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

>> 带有保存点的扁平事务(Flat Transactions with
Savepoint),除了支持扁平事务支持的操作外,允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效,放弃整个事务不合乎要求,开销也太大

>>
这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息,其余信息还是存放在默认的表空间中

>> 从InnoDB
1.2.x版本开始,可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成,则所有表中页的大小都为innodb_page_size,不可以对其再次进行修改。除非通过mysqldump导入和导出操作来产生新的库

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

MySQL技术内幕:InnoDB存储引擎(第2版)

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

>> MySQL
5.1开始可以将慢查询的日志记录放入一张表中,这使得用户的查询更加方便和直观。慢查询表在mysql架构下,名为slow_log

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

>>
因此,在InnoDB存储引擎的设计中对undo页可以进行重用。具体来说,当事务提交时,首先将undo
log放入链表中,然后判断undo页的使用空间是否小于3/4,若是则表示该undo页可以被重用,之后新的undo
log记录在当前undo log的后面

>> 聚集索引(clustered
index)就是按照每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据,也将聚集索引的叶子节点称为数据页。

>> 事务提交后并不能马上删除undo log及undo
log所在的页。这是因为可能还有其他事务需要通过undo
log来得到行记录之前的版本。故事务提交时将undo
log放入一个链表中,是否可以最终删除undo log及undo
log所在页由purge线程来判断。

>>
 B+树中的B不是代表二叉(binary),而是代表平衡(balance),因为B+树是从最早的平衡二叉树演化而来,但是B+树不是一个二叉树。

>>
因此为了保证事务的ACID中的持久性,必须将innodb_flush_log_at_trx_commit设置为1,也就是每当有事务提交时,就必须确保事务都已经写入重做日志文件。那么当数据库因为意外发生宕机时,可以通过重做日志文件恢复,并保证可以恢复已经提交的事务。

第4章 表

>>
不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分

>>
解决死锁问题最简单的方式是不要有等待,将任何的等待都转化为回滚,并且事务重新开始。

>>
在默认情况下,在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。在MySQL官方手册中将其称为InnoDB存储引擎的日志文件,不过更准确的定义应该是重做日志文件(redo
log
file)。为什么强调是重做日志文件呢?因为重做日志文件对于InnoDB存储引擎至关重要,它们记录了对于InnoDB存储引擎的事务日志。

>>
树中的任意一个事务的回滚会引起它的所有子事务一同回滚,故子事务仅保留A、C、I特性,不具有D的特性。

>>
此外还存在另一种死锁,即当前事务持有了待插入记录的下一个记录的X锁,但是在等待队列中存在一个S锁的请求,则可能会发生死锁

>>
相信在任何一本关于数据库原理的书中,可能都会提到数据库与传统文件系统的最大区别在于数据库是支持事务的

>>
另一个和慢查询日志有关的参数是log_queries_not_using_indexes,如果运行的SQL语句没有使用索引,则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件。

>>
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接。

>>
在用户启用了参数innodb_file_per_talbe后,创建的表默认大小是96KB。区中是64个连续的页,创建的表的大小至少是1MB才对啊?其实这是因为在每个段开始时,先用32个页大小的碎片页(fragment
page)来存放数据,在使用完这些页之后才是64个连续页的申请。这样做的目的是,对于一些小表,或者是undo这类的段,可以在开始时申请较少的空间,节省磁盘容量的开销

>>
据了解,大部分的用户质疑SERIALIZABLE隔离级别带来的性能问题,但是根据Jim
Gray在《Transaction
Processing》一书中指出,两者的开销几乎是一样的,甚至SERIALIZABLE可能更优!!!因此在InnoDB存储引擎中选择REPEATABLE
READ的事务隔离级别并不会有任何性能的损失

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

>>
。这里需要非常注意的是,主键的选择根据的是定义索引的顺序,而不是建表时列的顺序。

>> 参数completion_type为2时,COMMIT WORK等同于COMMIT AND
RELEASE。在事务提交后会自动断开与服务器的连接

>>
在通过表INNODB_LOCKS查看了每张表上锁的情况后,用户就可以来判断由此引发的等待情况了。当事务较小时,用户就可以人为地、直观地进行判断了。但是当事务量非常大,其中锁和等待也时常发生,这个时候就不这么容易判断。但是通过表INNODB_LOCK_WAITS,可以很直观地反映当前事务的等待

>>
在第2章中已经提到,写入重做日志文件的操作不是直接写,而是先写入一个重做日志缓冲(redo
log buffer)中,然后按照一定的条件顺序地写入日志文件

>>
之前已经说过MySQL的复制是异步实时的,并非完全的主从同步。若用户要想得知当前的延迟,可以通过命令SHOW
SLAVE STATUS和SHOW MASTER STATUS得知,如:

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

>>
对RAID卡进行配置可以在服务器启动时进入一个类似于BIOS的配置界面,然后再对其进行各种设置。此外,很多厂商都开发了各种操作系统下的软件对RAID进行配置,如果用户使用的是LSI公司生产提供的RAID卡,则可以使用MegaCLI工具来进行配置。

>>
用户可以设置对整个列的数据进行索引,也可以只索引一个列的开头部分数据,如前面创建的表t,列b为varchar(8000),但是用户可以只索引前100个字段

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

>> MySQL
5.6.5版本开始新增了一个参数log_throttle_queries_not_using_indexes,用来表示每分钟允许记录到slow
log的且未使用索引的SQL语句次数。该值默认为0,表示没有限制。在生产环境下,若没有使用索引,此类SQL语句会频繁地被记录到slow
log,从而导致slow log文件的大小不断增加,故DBA可通过此参数进行配置。

>>
。由于InnoDB存储引擎表是索引组织表,因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

>>
上述代码将一个需要处理1亿用户的大事务分解为每次处理10万用户的小事务,通过批量处理小事务来完成大事务的逻辑。每完成一个小事务,将完成的结果存放在batchcontext表中,表示已完成批量事务的最大账号ID。

>> MySQL数据库DBA的一个常用的命令是SHOW
TABLES,该命令会显示出当前数据库下所有的表。但因为视图是虚表,同样被作为表显示出来

>> InnoDB存储引擎从1.2.x版本开始支持全文检索的技术,其采用full
inverted index的方式

>>
后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件,同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

>> 因此,USE
INDEX只是告诉优化器可以选择该索引,实际上优化器还是会再根据自己的判断进行选择。而如果使用FORCE
INDEX的索引提示,如:

>>
innodb_change_buffer_max_size值默认为25,表示最多使用1/4的缓冲池内存空间。而需要注意的是,该参数的最大有效值为50。

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

第5章 索引与算法

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

>> update undo log记录的是对delete和update操作产生的undo
log。该undo log可能需要提供MVCC机制,因此不能在事务提交时就进行删除