Mysql总结

一、关系型数据库与非关系型数据库(Nosql)

关系型数据库有Oracle、mysql、SQLite等；非关系型数据库有MongoDb、redis、HBase等

区别：

1. 关系型数据库，是指采用了关系模型来组织数据的数据库，关系模型指的就是二维表格模型，最大特点就是事务的一致性。非关系型数据库使用键值对存储数据，一般不支持ACID特性
2. 关系型数据库支持SQL。非关系型数据库不提供sql支持
3. 关系型数据库为了维护一致性，导致其读写性能比较差，不适合处理海量数据。
4. 非关系型数据库基于键值对，数据没有耦合性，容易扩展
5. 关系型数据库则只支持基础类型。非关系型数据库的存储格式是键值对形式、文档形式、图片形式等等

二、事务四大特性

1. 原子性(Atomicity)：整个事务中的所有操作，要么全部完成，要么全部不完成。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态
2. 一致性(Correspondence)：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
3. 隔离性(Isolation)：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。
4. 持久性(Durability)：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

三、数据库隔离级别

1. Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
2. Repeatable read (可重复读)：默认级别。可避免脏读、不可重复读的发生。
3. Read committed (读已提交)：可避免脏读的发生。
4. Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

   1）在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。 

    2）以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。    

1、mysql 查询事务的隔离级别 select @@tx_isolation; 
2、在MySQL数据库中设置事务的隔离 级别： 
set [glogal | session] transaction isolation level 隔离级别名称; 
set tx_isolation=’隔离级别名称;’

• 脏读：指在一个事务处理过程里读取了另一个未提交的事务中的数据，造成两个事务得到的数据不一致。

• 不可重复读：读取了前一事务提交的数据

• 幻读：读取了另一条已经提交的事务。所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如将一个表的某个数据项全设置为1，这时另一个事务添加了一条等于2的数据，造成幻觉）

四、Mysql的存储引擎

1)  MySql主要有两种存储引擎：InnoDB和MyISAM。其它还有Memory、Archive

2）InnoDB和MyISAM区别：

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

3) 应用场景：

1. MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2. InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

4) MVCC机制：一种多版本并发控制机制。MVCC是通过保存数据在某个时间点的快照来实现的，不同存储引擎的MVCC不同，典型的有乐观并发控制和悲观并发控制。它是为了解决代替锁机制控制并发操作带来系统开销较大,能降低其系统开销

五、索引

5.1 索引分类：

1. 普通索引：仅加速查询
2. 唯一索引：加速查询 + 列值唯一（可以有null）
3. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
4. 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
5. 全文索引：对文本的内容进行分词，进行搜索

5.2 索引类型：

1. FULLTEXT：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
2. HASH：HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高
3. BTREE：MySQL里默认和最常用的索引类型。BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。
4. RTREE：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找

5.3 最左前缀原则：

     最左前缀原则：顾名思义是最左优先。以最左边的为起点任何连续的索引都能匹配上

     例：当创建(a,b,c)复合索引时，想要索引生效的话,只能使用 a和(a,b)和(a,b,c)三种组合

     注：如果第一个字段是范围查询需要单独建一个索引

     注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。     

5.4 聚集索引和非聚集索引的区别

1. 聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同。非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同
2. 聚集索引一个表中只能拥有一个聚集索引。非聚集索引一个表中可以拥有多个非聚集索引
3. 聚集索引查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

5.5 导致索引失效的一些情况

      1．隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

      由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

       错误的例子：select * from test where tu_mdn=13333333333;

       正确的例子：select * from test where tu_mdn='13333333333';

     2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

      错误的例子：select * from test where id-1=9;

      正确的例子：select * from test where id=10;

    3. 使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

      错误的例子：select * from test where round(id)=10; 说明，此时id的索引已经不起作用了

      正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id));然后 select * from test where round(id)=10; 这时函数索引起作用了

   4. 以下使用会使索引失效，应避免使用；

     a. 使用 <> 、not in 、not exist、!=

     b. like "%_" 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）

     c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。

     d. 字符型字段为数字时在where条件里不添加引号.

     e. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

   5. 不要将空的变量值直接与比较运算符（符号）比较。

     如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。

   6. 不要在 SQL 代码中使用双引号。

     因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。

   7. 将索引所在表空间和数据所在表空间分别设于不同的磁盘chunk上，有助于提高索引查询的效率。

   8. Oracle默认使用的基于代价的SQL优化器（CBO）非常依赖于统计信息，一旦统计信息不正常，会导致数据库查询时不使用索引或使用错误的索引。

 一般来说，Oracle的自动任务里面会包含更新统计信息的语句，但如果表数据发生了比较大的变化（超过20%）,可以考虑立即手动更新统计信息，例如：analyze table abc compute statistics，但注意，更新   统计信息比较耗费系统资源，建议在系统空闲时执行。

   9. Oracle在进行一次查询时，一般对一个表只会使用一个索引.

   因此，有时候过多的索引可能导致Oracle使用错误的索引，降低查询效率。例如某表有索引1（Policyno）和索引2（classcode），如果查询条件为policyno = ‘xx’ and classcode = ‘xx’，则系统有可能会使用索   引2，相较于使用索引1，查询效率明显降低。

 10. 优先且尽可能使用分区索引。

六、锁

   MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁，行级锁和页级锁。

  6.1 表级锁

1. MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）
2. 表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎
3. 加表锁：MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

  6.2 行级锁

1. 行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的InnoDB存储引擎
2. 锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。但行级锁定也最容易发生死锁。
3. 通过for update可以给select语句加排他锁。for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁

  6.3 页级锁

1. 页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间

七、数据库三范式

1. 第一范式（1NF）:在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。第一范式就是无重复的列。

2. 第二范式（2NF）：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求实体的属性完全依赖于主关键字

3. 第三范式（3NF）：满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式就是属性不依赖于其它非主属性。消除冗余

八、数据库优化

1.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 只会在检索出所有记录之后才对结果集进行过滤
5. 一些情况下，可以使用连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表
6. 适当添加冗余字段，减少表关联

2.索引优化

   建议创建索引列

1. 频繁作为查询条件的字段
2. 查询中与其他表关联的字段
3. 查询中排序的字段
4. 查询中统计或分组字段

  不建议创建索引列

1. 频繁更新的字段
2. where 条件中用不到的字段
3. 表记录太少
4. 经常增删改的表
5. 字段的值的差异性不大或重复性高
6. where 条件中用不到的字段    

3.数据库结构优化

1. 使用可以存下数据最小的数据类型
2. 使用简单的数据类型。int 要比 varchar 类型在mysql处理简单
3. 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
4. 尽可能使用 not null 定义字段，因为 null 占用4字节空间
5. 尽量少用 text 类型,非用不可时最好考虑分表
6. 尽量使用 timestamp 而非 datetime
7. 单表不要有太多字段，建议在 20 以内
8. 读写分离、库表分离

4.服务器参数调优

  内存、IO、安全方面的参数设置

5.硬件选购

详情访问https://blog.youkuaiyun.com/qq_33314107/article/details/80457327

九、主从复制原理

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

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

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

    1）Statement：每一条会修改数据的sql都会记录在binlog中

    优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

    缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同 的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以user-defined-functions(udf)会出现问题).

    使用以下函数的语句也无法被复制：
   * LOAD_FILE()
   * UUID()
   * USER()
   * FOUND_ROWS()
   * SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

   同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

   2）Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改

   优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题

   缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

    3）Mixedlevel: 是以上两种level的混合使用

    一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

十二、问题总结

1、连接分为三种：内连接、外连接、交叉连接

   外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN) 右外连接(RIGHT OUTER JOIN或RIGHT JOIN) 全外连接(FULL OUTER JOIN或FULL JOIN)。

   交叉连接：概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小，用cross join (不带条件where...)连接

2、Innodb引擎的4大特性

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

3、myisam和Innodb两个存储引擎，select count(*)哪个更快，为什么

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

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

   varchar与char的区别：char是一种固定长度的类型，varchar则是一种可变长度的类型；

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

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

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

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

6、一个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、存储过程与触发器的区别

     触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

8、MySQL中InnoDB引擎的行锁是通过加在什么上完成

   InnoDB是基于索引来完成行锁    例: select * from tab_with_index where id = 1 for update;

   for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

9、mysql并发情况下怎么解决？

   通过事务、隔离级别、锁

10、死锁怎么解决？

   详情访问https://www.cnblogs.com/sivkun/p/7518540.html

10、xtrabackup实现原理 

    在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件的事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。