您的位置:1010cc时时彩经典版 > 1010cc安卓版 > 1010cc时时彩经典版:mysql数据库相关整理,常见问

1010cc时时彩经典版:mysql数据库相关整理,常见问

发布时间:2019-11-17 15:54编辑:1010cc安卓版浏览(195)

    三范式

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

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

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

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

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

    4NF :没有多值依赖。

    MySQL的复制原理以及流程

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

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

    数据库相关

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

    数据类型

    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中myisam与innodb的区别

    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, 物理日志记录的是修改页的的详情,逻辑日志记录的是操作语句. 物理日志恢复的速度快于逻辑日志.

    姜承尧

    存储引擎

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

    数据库存储引擎     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)

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

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

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

    2.事务的实现原理

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

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

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

    第1章 MySQL体系结构和存储引擎

    数据库ACID

    数据库的ACID

    数据库事务介绍

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

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

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

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

    4 种隔离级别

    MVVC的简单介绍

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

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

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

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

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

    innodb引擎的4大特性

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

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

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

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

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

    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 (可串行化)
    强制事务串行执行,避免了上面说到的 脏读,不可重复读,幻读 三个的问题.

    >> 在上述例子中使用了mysqld_safe命令来启动数据库,当然启动MySQL实例的方法还有很多,在各种平台下的方式可能又会有所不同。

    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算法都会被触发。

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

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

    2.2 原子性和持久性的实现

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

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

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

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

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

    索引

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

    什么是B-Tree

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

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

    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.3 一致性的实现

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

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

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

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

    索引类型:

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

     哈希索引

     无法用于排序。

     只支持全部匹配。

     只支持等值比较。

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

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

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

    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来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”

    3. 索引有什么用

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

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

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

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

    >> 需要特别注意的是,存储引擎是基于表的,而不是数据库。

     具体类型介绍:

    单列索引:不允许为空

     普通索引 不允许有空值

     唯一索引

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

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

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

    全文索引:

    空间索引:

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

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

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

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

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


    >> 关于NDB存储引擎,有一个问题值得注意,那就是NDB存储引擎的连接操作(JOIN)是在MySQL数据库层完成的,而不是在存储引擎层完成的

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

     磁盘存取原理

     局部性原理与磁盘预读

    M 阶 B-Tree

     1010cc时时彩经典版 1

     根节点至少有2个子树。

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

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

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

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

    B Tree

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

     叶子节点不存储指针。

    MySQL 索引实现

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

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

    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 索引类型

      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列的索引不发挥作用.

    索引应用举例:

    1010cc时时彩经典版 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操作很频率,可以按周/月,来修复.
      如果不频繁,可以更长的周期来做修复.

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

    EXPLAIN 字段介绍

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

     key:实际使用的索引。

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

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

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

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

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

    数据库相关面试题

    >> MySQL提供了一个非常好的用来演示MySQL各项功能的示例数据库,如SQL Server提供的AdventureWorks示例数据库和Oracle提供的示例数据库。据我所知,知道MySQL示例数据库的人很少,可能是因为这个示例数据库没有在安装的时候提示用户是否安装(如Oracle和SQL Server)以及这个示例数据库的下载竟然和文档放在一起

    视图 

    MySQL数据库视图

    MySQL - 视图算法

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

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

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

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

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

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

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

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

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

    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 不记录在日志中,所以它不能激活触发器。

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

    触发器

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

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

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

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

    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)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。(我的理解是消除冗余)

    第2章 InnoDB存储引擎

    事件

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

    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

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

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

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

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

    备份

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

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

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

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

    物理备份

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

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

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

     MyISAM:mysqlhotcopy工具。

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

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

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

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

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

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

    恢复

         完全恢复

     将备份作为输入执行。

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

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

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

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

     

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

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

    5.innodb引擎的4大特性

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

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

    日志

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

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

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

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

    日志文件小结:

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

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

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

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

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

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

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

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

    开放性问题:

    一个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;

    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)存储和计算均一样;

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

    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的区别

    1010cc时时彩经典版 3

     

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

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

     

    >> 从InnoDB 1.0.x版本开始,允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竞争,增加数据库的并发处理能力。可以通过参数innodb_buffer_pool_instances来进行配置,该值默认为1。

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

    >> 在InnoDB存储引擎中,缓冲池中页的大小默认为16KB,同样使用LRU算法对缓冲池进行管理

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

    >> 那为什么不采用朴素的LRU算法,直接将读取的页放入到LRU列表的首部呢?这是因为若直接将读取到的页放入到LRU的首部,那么某些SQL操作可能会使缓冲池中的页被刷新出,从而影响缓冲池的效率。

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

    >> Buffer pool hit rate,表示缓冲池的命中率,这个例子中为100%,说明缓冲池运行状态非常良好。通常该值不应该小于95%。若发生Buffer pool hit rate的值小于95%这种情况,用户需要观察是否是由于全表扫描引起的LRU列表被污染的问题。

    >> 执行命令SHOW ENGINE INNODB STATUS显示的不是当前的状态,而是过去某个时间范围内InnoDB存储引擎的状态。从上面的例子可以发现,Per second averages calculated from the last 24 seconds代表的信息为过去24秒内的数据库状态。

    >> 在LRU列表中的页被修改后,称该页为脏页(dirty page),即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘,而Flush列表中的页即为脏页列表。需要注意的是,脏页既存在于LRU列表中,也存在于Flush列表中

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

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

    >> 因此Checkpoint(检查点)技术的目的是解决以下几个问题:□ 缩短数据库的恢复时间;□ 缓冲池不够用时,将脏页刷新到磁盘;□ 重做日志不可用时,刷新脏页。

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

    >> InnoDB存储引擎的关键特性包括:□ 插入缓冲(Insert Buffer)□ 两次写(Double Write)□ 自适应哈希索引(Adaptive Hash Index)□ 异步IO(Async IO)□ 刷新邻接页(Flush Neighbor Page)

    >> InnoDB存储引擎开创性地设计了Insert Buffer,对于非聚集索引的插入或更新操作,不是每一次直接插入到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,若在,则直接插入;若不在,则先放入到一个Insert Buffer对象中,好似欺骗。数据库这个非聚集的索引已经插到叶子节点,而实际并没有,只是存放在另一个位置。

    >> 然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge(合并)操作,这时通常能将多个插入合并到一个操作中(因为在一个索引页中),这就大大提高了对于非聚集索引插入的性能。

    >> 辅助索引不能是唯一的,因为在插入缓冲时,数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生,从而导致Insert Buffer失去了意

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

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

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

    >> 可能令绝大部分用户感到吃惊的是,Insert Buffer的数据结构是一棵B 树。在MySQL 4.1之前的版本中每张表有一棵Insert Buffer B 树。而在现在的版本中,全局只有一棵Insert Buffer B 树,负责对所有的表的辅助索引进行Insert Buffer。

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

    >> 。这是因为表的辅助索引中的数据可能还在Insert Buffer中,也就是共享表空间中,所以通过ibd文件进行恢复后,还需要进行REPAIR TABLE操作来重建表上所有的辅助索引。

    >> 如果说Insert Buffer带给InnoDB存储引擎的是性能上的提升,那么doublewrite(两次写)带给InnoDB存储引擎的是数据页的可靠性。

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

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

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

    >> 值得注意的是,哈希索引只能用来搜索等值的查询,如SELECT*FROM table WHERE index_col='xxx'。而对于其他查找类型,如范围查找,是不能使用哈希索引的,因此这里出现了non-hash searches/s的情况

    >> 用户可以在发出一个IO请求后立即再发出另一个IO请求,当全部IO请求发送完毕后,等待所有IO操作的完成,这就是AIO。AIO的另一个优势是可以进行IO Merge操作,也就是将多个IO合并为1个IO,这样可以提高IOPS的性能

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

    >> 参数innodb_use_native_aio用来控制是否启用Native AIO,在Linux操作系统下,默认值为ON

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

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

    >> 参数innodb_force_recovery还可以设置为6个非零值:1~6。大的数字表示包含了前面所有小数字表示的影响

    第3章 文件

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

    >> Oracle数据库存在所谓的隐藏参数(undocumented parameter),以供Oracle“内部人士”使用,SQL Server也有类似的参数。有些DBA曾问我,MySQL中是否也有这类参数。我的回答是:没有,也不需要。即使Oracle和SQL Server中都有些所谓的隐藏参数,在绝大多数的情况下,这些数据库厂商也不建议用户在生产环境中对其进行很大的调整。

    >> MySQL数据库中的参数可以分为两类:□ 动态(dynamic)参数□ 静态(static)参数动态参数意味着可以在MySQL实例运行中进行更改,静态参数说明在整个实例生命周期内都不得进行更改,就好像是只读(read only)的

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

    >> 设置long_query_time这个阈值后,MySQL数据库会记录运行时间超过该值的所有SQL语句,但运行时间正好等于long_query_time的情况并不会被记录下。也就是说,在源代码中判断的是大于long_query_time,而非大于等于

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

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

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

    >> 查看slow_log表的定义会发现该表使用的是CSV引擎,对大数据量下的查询效率可能不高。用户可以把slow_log表的引擎转换到MyISAM,并在start_time列上添加索引以进一步提高查询的效率。

    >> 不能忽视的是,将slow_log表的存储引擎更改为MyISAM后,还是会对数据库造成额外的开销。

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

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

    >> 。同样地,从MySQL 5.1开始,可以将查询日志的记录放入mysql架构下的general_log表中,该表的使用方法和前面小节提到的slow_log基本一样

    >> 二进制日志(binary log)记录了对MySQL数据库执行更改的所有操作,但是不包括SELECT和SHOW这类操作,因为这类操作对数据本身并没有修改。

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

    >> 此外,binlog_cache_size是基于会话(session)的,也就是说,当一个线程开始一个事务时,MySQL会自动分配一个大小为binlog_cache_size的缓存,因此该值的设置需要相当小心,不能设置过大。当一个事务的记录大于设定的binlog_cache_size时,MySQL会把缓冲中的日志写入一个临时文件中,因此该值又不能设得太小

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

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

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

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

    >> MySQL 5.1开始引入了binlog_format参数,该参数可设的值有STATEMENT、ROW和MIXED

    >> 上面的这个例子告诉我们,将参数binlog_format设置为ROW,会对磁盘空间要求有一定的增加。而由于复制是采用传输二进制日志方式实现的,因此复制的网络开销也有所增加。

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

    >> 但不论表采用何种存储引擎,MySQL都有一个以frm为后缀名的文件,这个文件记录了该表的表结构定义。

    >> frm还用来存放视图的定义,如用户创建了一个v_a视图,那么对应地会产生一个v_a.frm文件,用来记录视图的定义,该文件是文本文件,可以直接使用cat命令进行查看

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

    >> 。独立表空间的命名规则为:表名.ibd。通过这样的方式,用户不用将所有数据都存放于默认的表空间

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

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

    >> 每个InnoDB存储引擎至少有1个重做日志文件组(group),每个文件组下至少有2个重做日志文件,如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性,用户可以设置多个的镜像日志组(mirrored log groups),将不同的文件组放在不同的磁盘上,以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致,并以循环写入的方式运行。

    >> InnoDB存储引擎先写重做日志文件1,当达到文件的最后时,会切换至重做日志文件2,再当重做日志文件2也被写满时,会再切换到重做日志文件1中。

    >> 若磁盘本身已经做了高可用的方案,如磁盘阵列,那么可以不开启重做日志镜像的功能

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

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

    本文由1010cc时时彩经典版发布于1010cc安卓版,转载请注明出处:1010cc时时彩经典版:mysql数据库相关整理,常见问

    关键词: