MySQL DBA面试整理

文档：MySQL DBA面试整理.note
链接：http://note.youdao.com/noteshare?id=8ac1a828da5dfd0d05d3c2edf2744311&sub=C0463009298A48A4951D3321D406DD59

MySQL DBA面试整理

主从复制

为什么要进行主从？

背景：有一个SQL语句需要进行表锁

影响：表暂停服务，不对外提供服务

目的：读写分离，一个库用来读，一个用来写写

工作：据的热备

用途：架构拓展，IO过高，降低IO的频率，提高单个机器的IO性能

优点：减轻主库的负载，当主库宕机，可以快速切换到从库

主从与集群的区别

主从：配置好主从之后同一张表只能对一个服务器进行写操作。如果在从上进行了写操作，之后主也进行了写的操作，会造成主从不同步。宕机之后需要手工切换

集群：有多台数据库服务器组成，数据库的写入和查询随机到一台数据库服务器，其他的机器会自动同步，单一出现问题不会造成影响

缺点：目前mysql集群值针对NDB，如果是innodb或其他存储引擎不可以

原理：

1、数据库有一个binlog文件：最重要的日志文件，所有的DDL/DML语句以事件的形式记录

二进制索引文件（xxx.index）用于记录所有的二进制文件

二进制文件（xxx.000*）记录DDL/DML

2：基本原理：将binlog文件复制

3：过程：在从库relay-log中重做日志文件中执行binlog中的SQL语句

4线程：

1、主库进行DDL/DML

2、从库发起连接请求

3、主库开启Binlog dump thread 将binlog传输到从库relay-log

4、从库启动I/Othread线程，将binlog写入到relay-log

5、从库创建一个SQL线程，从relay-log中读取，将内容写入到从库的数据库

方式：

异步

主库把提交事件写进binlog

给用户返回提交成功

异步传输给从库relay-log

从库应用relay-log

半同步

主库将提交事件写进binlog

将binlog传给从库relay-log

从库相应主库binlog传输完成

异步传输给从库relay-log

从库应用relay-log

延迟问题：

传输延迟

产生原因：

dump是单线程读取binlog速度慢---------增加物理读能力

网络延迟-----------------------------------------增加网络带宽

从库IO线程写能力小------------------------------raid+flash（缓存）

应用延迟

从库只有一个SQL线程----------------------------避免主库有大量的DML

慢查询-------------------------------------------创建主键/索引

从库性能低---------------------采用mixed：SQL语句（update会丢失）/行（binlog太大）/混合复制

MHA

MHA原理

该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA
Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA
Node运行在每台MySQL服务器上，MHA
Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL
5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

manager做了几件事情

1、监控主和从

2、主宕机之后，manager会做几件事情

  1. 找到新的主
    1. 如果指定候选，直接使用候选作为新的主
    2. 如果没有指定候选，或者指定了多个候选，选择延迟最小的作为主
  2. 从旧的主保存binlog
  3. 应用binlog到新的主
  4. 在新的主上启动新的地址，也就是地址的漂移

通过执行一个脚本来实现

3、一定要注意查看manager上面的日志

如何把坏掉的主加入到 mha 环境中：

mha 默认情况下，宕掉的旧主通过以下步骤加入到 mha 环境中去，以后不能再充当
master，因为加上了 no_master 参数，认为重新成为主可能会造成数据的不一致。

1、让旧主成为从

CHANGE MASTER TO MASTER_HOST='192.168.10.52',MASTER_PORT=3306,
MASTER_LOG_FILE='mha-server.000003',MASTER_LOG_POS=154, MASTER_USER='repl',
MASTER_PASSWORD='repl';

确保新主上有旧主可以使用的复制用户

2、加入到 manager 的监控中

masterha_conf_host --command=add --conf=/etc/masterha/app1.cnf
--hostname=192.168.10.51 --block=server2 --params="no_master=1;ignore_fail=1"

也可以手工直接编辑 app1.cnf

如何将失败的 master 强行加进来

1、删除 fialover complete 文件

2、dead master 重新安装 mysql 软件、使用备份恢复搭建新的主从

3、编辑 app1.cnf，将新的从加入到配置文件中，重启 manager 进程

1、手工编辑

2、使用命令加入

MHA 日常监控命令：

# masterha_check_status --conf=/etc/masterha/app1.cnf //日常 mha
监控，主要监控manager 节点

# masterha_check_repl --conf=/etc/masterha/app1.cnf

# masterha_check_ssh --conf=/etc/masterha/app1.cnf //经常用来排错

1、发现旧主已经over，就地保存binlog，指定start-position

2、再次确定旧主已经over

3、确定其他的从的状态

4、关闭主应用和漂移ip

  1. MHA的优缺点

MHA工具的优点:

由Perl语言开发的开源工具master自动监控和故障转移

master crash 不会导致主从数据不一致性

可以支持基于GTID的复制模式(MySQL 5.7版本)MHA在进行故障转移时更不易产生数据丢失

同一个监控节点可以监控多个集群

MHA加强了数据的安全性

MHA工具的缺点:

需要编写脚本或利用第三方工具来实现VIP的配置

MHA启动后只会对主数据库进行监控

需要基于SSH免认证配置，存在一定的安全隐患

没有提供从服务器的读负载均衡功能

mha 因为延迟不能启动新的主时，解决办法：

方法 1：

  1. 删除 fialover complete 文件
  2. 在希望成为主的那个下面，添加 check_repl_delay=0
  3. 再次启动 manager
  4. 原来的延迟还会继续跑，新的操作不能进行，因为漂移地址没有过来，read_only

没有关闭。mha 也会等到所有的应用日志都跑完以后，才会提升新的主。 你可以强行关闭
readonly，连接本地地址 50，先进行操作，很可能会发生主键冲突。

方法 2：

或者干等，slave 赶上 master 以后，删除 error，重启启动 manager。
需要等到新主应用所有的 binlog，生产上接受不了。所以对于 mha
来说，如果存在严重的延迟的话，基本上不能用。

方法 3：

不依靠 mha，关闭 manager，将其中一个 slave 的 ip 地址进行修改，readolny
参数去掉，提升为主

  1. MHA能切换几次

只能一次

3.触发器的作用域是什么以及触发器的最小单位

作用域是表，最小单位是行

读写分离

1.应用层解决;

2.中间件实现

应用层实现的的优缺点:

优点:

1、多数据源切换方便，由程序自动完成;

2、不需要引入中间件;

3、理论上支持任何数据库;

缺点:

1、由程序员完成，运维参与不到;

2、不能做到动态增加数据源;

SQL的执行顺序

1、先执行from、join、where、on
部分，这是最主要的资源消耗点，要合理使用索引，注意表的关联顺序

2、group by、having（分组聚合），要注意优化分组聚合

3、order by，要注意优化order by，使用索引去除磁盘排序

4、执行limit，要注意优化limit，特别是在分页查询中，通过limit
实现优化，特别是order by limit，两种手法：1、前面的页使用order by limit，走order
by 索引2、后面的页使用join 的方式来优化

5、最后执行select 列，不要将子查询放在select 和from
之间，因为对于返回的每一行数据，子查询都要执行一次

锁

答：

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。

加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

基本锁类型：锁包括行级锁和表级锁

Mysql中有哪几种锁？

1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

一. 如何避免锁

可以在修改资源的时候一下子获得所有需要修改的资源的锁，以后不再能获得其它的锁，直到本次修改完成。

  1. 可以按某种顺序依次获得资源的锁。

二. 如果已经产生了锁的解决办法

  1. 通过检查机制找出锁，然后牺牲掉那个进程。
  2. 通过设置超时机制，自动杀掉持锁时间超时的进程，

Latch，mutex、pin

  1. 在innodb 中查询到需要修改的数据页
    1. 此时会发生latch(r)争用,因为需要锁住数据链对数据链进行遍历
  2. 没有找到的话释放latch(r),去磁盘查找,发生物理读
  3. 在磁盘中查找时分为使用索引和不使用索引
    1. 使用索引会提高查询速度,提高sql的工作效率
    2. 不使用索引会发生全表扫描,会导致大量数据涌入innodb_buffer_pool
  4. 查找到的数据会放到innodb_buffer_pool里面
    1. 此时需要在innodb_buffer_pool中查找free或者clean页
    2. 在查找free和clean页时如果free已经全部用完而且在clean不够用的情况下会发生磁盘写,导致db_write工作,然后也会更新redo和ondo,redo的作用是用来记录未写入到磁盘的脏数据的日志,用于后期数据库将在innodb_buffer_pool中的脏数据更新到磁盘中,ondo的作用是记录脏数据之前的数据,用于用户执行rollback操作
    3. 此时会发生latch(x)争用,因为要将磁盘中的数据写入到innodb_buffer_pool中
  5. 在数据从磁盘上将数据写入到free或者clean中时,会发生waits特别是在数据的查找时没有使用索引时会导致大量数据涌入innodb_buffer_pool,导致发生waits
  6. 释放所有的latch
    1. 写完数据后释放latch(x)和latch(s)
  7. 将innodb_buffer_pool中的数据进行修改,增加pin锁
    1. Pin锁的目的是为了在修改的一瞬间保证这个数据页不会被进行任何ddl操作,以避免修改失败
    2. 在修改开始前会对改数据行所在的数据页增加表级别的ix锁,用于避免此时其他事务对该表执行ddl操作
    3. 修改过程中会对要修改的行添加x锁,使该行进入到排他状态,以保证修改数据中,不会再其他线程不会发生脏读
  8. 此时如果有其他用户线程查找该行数据,会读取到该行数据最新一次commit后的数据,而不会读取到当前事务修改的数据,这也是为了避免脏读
  9. 修改完数据后释放pin锁
  10. 当修改完数据后会发生undo和redo
    1. Undo记录修改前的数据用于rollback
    2. redo用于记录修改后的日志写入磁盘中的log file以用来慢慢更新脏数据
  11. 此时ix锁和x锁都没有释放,因为没有发生commit
    1. 也就意味着此时该表仍然处于可以进行的是select和dml不能进行ddl的状态
    2. 当然如果dml中要更新的数据是被加了x锁的这一行和读取这一行数据也是不可以的,因为该行处于x锁状态
  12. commit提交
    1. 在数据修改成功后,也就是该事务完成后提交,ix表锁和x行锁释放,但数据不会及时的写入到磁盘上,因为用通过redo进程中的log
      file来后台更新磁盘上的数据

事务

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

事务特性：

（1）原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

（2）一致性或可串性。事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

（3）隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，

（4）
持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

或者这样理解：

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

MVCC

实现了一致性读:

1、避免脏读(已提交读)，写不阻塞读

2、实现可重复读

MVCC 实现原理:

修改删除一个数据页里的数据行，事务开始后，分配一个事务槽，数据页，先把数据行里的旧的事务
id 和 roll pointer 和原来的数据写到数据页里去，新的roll pointer
指向新的数据页的位置，事务提交了，又开启了一个事务，还是修改这行数据，相当于新的
roll pointer 从 undo 事务块指向了旧的 undo 事务块，这就是多版本控制 MVCC，在
undo 里面可以保存数据的不同时刻的多个版本。

描述 MVCC

1、MVCC 使得在一个事务中，所有的 select 访问到的是同一个时刻的数据，
反复执行一个 select，得到的数据是一致的

2、通过 undo 来实现

3、实现了 select 的可重复读隔离级别

4、可重复读隔离级别通过 MVCC 来实现 select，通过 gap lock 来实现 dml

5、mvcc 会因为长事务、大事务导致 undo 暴涨。undo 在低版本中，没有办
法自动回收，在新的版本中，undo 的自动回收机制风险很大，因此也要谨慎使
用。要避免长事务，通过监控 innodb_trx 视图，里面有两个列，一个是事务的开
始时间，一个是事务的修改行数，判断是长事务还是大事务，对于长事务，要及
时进行回滚或者提交。

备份

冷备

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将要害性文件拷贝到另外的位置的一种说法。对于备份MySQL信息而言，冷备份时最快和最安全的方法。

冷备份的优点是：

　　１、 是非常快速的备份方法（只需拷文件）

　　２、 轻易归档（简单拷贝即可）

　　３、 轻易恢复到某个时间点上（只需将文件再拷贝回去）

　　４、 能与归档方法相结合，做数据库“最佳状态”的恢复。

５、 低度维护，高度安全。

但冷备份也有如下不足：

１、 单独使用时，只能提供到“某一时间点上”的恢复。

２、
再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

３、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。

４、 不能按表或按用户恢复。

冷备份中必须拷贝的文件包括：

１、 所有数据文件

２、 所有控制文件

３、所有联机REDO LOG文件

４、 Init.ora文件（可选）

值得注重的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的

热备：

热备份是在数据库运行的情况下，采用archivelog
mode方式备份数据库的方法。即热备份是系统处于正常运转状态下的备份。所以，如果你有一个冷备份而且又有热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog()方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。

热备份的优点是：

　　1． 可在表空间或数据库文件级备份，备份的时间短。

　　2． 备份时数据库仍可使用。

　　3． 可达到秒级恢复（恢复到某一时间点上）。

　　4． 可对几乎所有数据库实体做恢复

　　5． 恢复是快速的，在大多数情况下爱数据库仍工作时恢复。

　　热备份的不足是：

　　1． 不能出错，否则后果严重

2． 若热备份不成功，所得结果不可用于时间点的恢复

（回答要点，概念，区别，包括所使用的原件的优点和缺点，把自己知道的备份都说出来）

mysql逻辑备份：

mysql逻辑备份是指备份sql语句，再恢复的时候执行备份的sql语句实现数据库数据的重现

mysqldump：是采用SQL级别的备份机制，他将数据表导成SQL脚本文件，是最常用的逻辑备份方法

缺点

对于myisam表：锁定

对于innodb表，通过undo的mvcc实现一致性，期间不能进行DDL，会导致数据不一致

备份时间长，所有的块都需要进行读取，抽取数据行，写入备份文件

恢复时间长（磁盘IO，索引创建），比如insert
，过程会产生redo、undo、SQL解析、double write、锁、binlog 等

不适用于大表、大数据库

优点

能够实现备份+binlog——恢复到任意时间点

可以生成CSV、delimited text、XML format

有助于避免数据损坏，如果磁盘驱动器故障复制文件，文件是损坏的

如何进行逻辑备份

举例：经典用法

mysqldump -uroot -p123 -l -F --single-transaction ceshi>ceshi.sql

-l：锁住myisam，不锁innodb

-F：刷新binlog，恢复的时候就可以直接使用新binlog开始恢复

--single-transaction：innodb不锁表和行

物理备份：

物理备份就是备份数据文件了，比较形象点就是复制cp数据文件，

物理备份工具

xtrabackup：

缺点：

1、文件大

2、不总是可以跨平台、操作系统和MySQL版本

优点：

1、基于文件的物理备份

2、恢复快，不需要执行任何SQL语句，不需要构建索引，缓存数据等

3、自动完成备份鉴定

1、在线备份，不阻塞任何的SQL语句;

2、备份性能好，备份的实质是复制数据库文件和重做日志文件;

3、支持压缩备份，通过选项，可以支持不同级别的压缩。

4、跨平台支持，ibbackup 可以运行在linux、windows以及主流的unix系统平台上。

原理

备份所有数据页和备份期间所有的redo日志--将数据页能恢复到备份结束时刻

+binlog，将数据库恢复到对应的时间点

回滚备份结束时刻未提交的事务

innodb使用redolog保持数据的一致性

myisam使用锁保持备份的一致性

Xtrabackup的优点以及机制

工作机制:

对于innodb存储引擎表其备份工作原理是:

1、记录备份开始时，innodb存储引擎重做日志文件检查点的LSN;

2、 复制共享表空间文件以及独立表空间文件:

3、记录复制完表空间文件后，innodb存储引擎重做日志文件检查点的LSN;

4、复制在备份时产生的重做日志。

5、在备份期间不会对数据库本身有任何影响，所做的操作只是复制数据库文件，因此任何对数据库的操作都是允许的，不会阻塞任何操作。

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆跑车的话，那么没有设计和使用索引的MySQL就是一个自行车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE
子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

1.索引的目的是什么？
快速访问数据表中的特定信息，提高检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性。
加速表和表之间的连接

使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

2.索引对数据库系统的负面影响是什么？
负面影响：
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

3.为数据表建立索引的原则有哪些？
在最频繁使用的、用以缩小查询范围的字段上建立索引。

在频繁使用的、需要排序的字段上建立索引

4.什么情况下不宜建立索引？
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

Xtrabackup 的优点以及机制

工作机制:

对于innodb存储引擎表其备份工作原理是:

1、记录备份开始时，innodb存储引擎重做8志文件检查点的LSN;

2、复制共享表空间文件以及独立表空间文件;

3、记录复制完表空间文件后，innodb存储引擎重做日志文件检查点的LSN;

4、复制在备份时产生的重做日志。

5、在备份期间不会对数据车本身有任何影响，所做的操作只是复制数据库文件，因此任何对数据库的操作都是允许的，不会阻塞任何操作。

优点:

1、在线备份，不阻塞任何的SQL语句;

2、备份性能好，备份的实质是复制数据库文件和重做日志文件;

3、支持压缩备份，通过选项，可以支持不同级别的压缩。

4、跨平台支持，ibbackup 可以运行在linux、windows 以及主流的unix系统平台上。

Innodb

1、mysql 为了支持oltp 系统而设计的一个引擎，目前是mysql 的默认引擎

2、支持事务，这是oltp 最基本的要求，事务支持ACID、原子性、一致性、持久性、隔离性

3、支持行锁，大大提升MySQL 的并发性能

4、通过undo 实现写不阻塞读，进一步提升并发性能

5、支持事务的四种隔离级别

6、支持redo、有了redo 以后可以实现快速提交、脏缓冲区、崩溃恢复

7、change buffer，提升dml 的性能，能够解决二级索引导致的io 问题

8、double write，解决了写入时数据损坏的问题，但是也放大了写入的压力

9、自适应hash 索引,解决了树高部分的资源消耗

10、异步IO,提升cpu 的使用率，降低wait io 等待，提升系统性能

11、刷新邻接页，加速脏页的回写，但是也放大了写压力

12、支持MVCC

特点

1、支持行锁，并发性能好

2、支持 M CC，支持事务

3、支持外键

4、提供一致性非锁定读，并发能力更强

5、能够使用大内存和充分利用 c 资源

函数和存储过程的区别

存储过程定义：存储过程就是具有名字的一段代码，完成一个特定的功能。存储过程保存在数据字典中

使用存储过程的原因：（1）将重复性很高的一些操作封装到一个存储过程中，简化了对这些SQL的调用，可以重复使用（2）批量处理【SQL语句+循环语句】
（3）统一接口

函数的特点：函数特点：（1）函数有返回值，两个return，第一个标志着返回什么类型，第二个是实际的返回值。（2）调用函数，函数需要出现在等号的右边。（3）其他地方与定义存储过程一样

两者区别：

存储过程实现的过程要复杂一些，而函数的针对性比较强。

存储过程可以通过 out参数有多个返回值，而函数只有一个返回值；

存储过程一般的独立的来执行，而函数往往是作为其他SQL语句的一部分来使用；

函数使用select调用，存储过程使用call调用

存储过程建立是 create procedure 函数建立是create function

我们比较喜欢使用存储过程原因如下：

自定义函数的使用比较麻烦

（2）存储过程的out参数也可以返回值，而且可以返回多个值

性能定位的问题

1、OS 层面定位性能问题：

1、top、H：是否有过载线程（大于75%有异常，大于90%必须查看），根据线程ID
找到对应的会话，找到对应的sql，看sql 的执行计划，然后进行优化。

2、vmstat：r 队列是否有过多的r 线程（是否有异常SQL 或者tps
增加）以及是否在使用swap。

1、如果有过多的r，说明有大量的用户连接正在执行，这很有可能说明出现了异常SQL
并且频繁执行，执行时间过长，占用会话不释放，会导致会话异常高，r
队列暴增。通过show processlist 找到对应的SQL，看执行计划，进行优化。

2、如果swap
在使用，可能是用户线程暴增（导致使用物理内存短缺，使用到swap），或者在系统上执行大量的文件操作（导致active
内存过多，free内存减少，swap 被使用）

3、iostat：io 是否过载（io 量很大，高出正常值很多，可能是异常的SQL 导致IO
过载）、io 是否有问题（iops 达不到平时高度，但是繁忙度已经100%，即io
低但繁忙度高，找硬件工程师）、写性能是否差（查看service time
列，是否配置写缓存、写闪存）

4、free：空闲内存是否够用，free
内存短缺不一定有事，如果过多使用到swap，才是内存真正短缺，造成内存短缺的三种情况

1、线程（用户连接）突然增加

2、对文件进行操作（比如对大文件进行备份，并且通过网络拖走）

3、应用服务器在数据库服务器上）

5、cat /proc/meminfo 里面active 内存是否过多

2、数据库层面定位异常SQL：

1、连接线程是否大幅增加：

1、慢sql（异常SQL，个别线程负载过高一定是异常SQL 导致的）

2、锁（在数据字典级别可以查到事务锁）

3、tps、qps 大幅增加（业务高）

连接线程的暴增，可能会导致系统内存的耗尽

2、找出异常SQL：

1、慢查询日志——已经慢

2、show processlist——正在慢

3、监控(zabbix 等)数据库当前负载是否突然增加:

Innodb_rows_deleted

Innodb_rows_inserted

Innodb_rows_read

Innodb_rows_updated

Binlog太多怎么删除

1、如果数据库没有搭建主从同步这样的服务 , binlog 日志也不用的情况下 , 你可以将
/etc/my.cnf 文件下面行删除或注释掉log-bin=mysql-bin

2、如果你需要这样的文件,那么在主配置文件 /etc/my.cnf
中添加行,来限制binlog日志文件存在时间,过期自动删除expire_logs_days = 7 # 只保留
7 天的日志文件

3、如果直接得到一台服务器,上面的 binlog
日志已经影响到了磁盘空间,那么你将使用下面命令清除mysql> reset master; #
清空所有 binlog 文件

4、如果服务器mysql 还做了主从同步 ,因为 binlog 文件删除过多的话 ,
会导致数据不同步

mysql> show slave status\G # 你首先要来 slave 上查看从库读 binlog 到了哪里

Master_Log_File: mysql-bin.000009 # 我们可以看到是 mysql-bin.000009

mysql> purge master logs to 'mysql-bin.000009'; # 我们可以在 master
中执行这条指令 , 将 binlog 文件删除至 mysql-bin.000009 前

mysql> purge master logs before '2014-11-18 00:00:00'; # 将 binlog
文件删除至此日期前

linux怎么开放一个端口

开放端口的方法：

方法一：命令行方式

  1. 开放端口命令： /sbin/iptables -I INPUT -p tcp --dport 8080 -j ACCEPT

2.保存：/etc/rc.d/init.d/iptables save

3.重启服务：/etc/init.d/iptables restart

4.查看端口是否开放：/sbin/iptables -L -n

方法二：直接编辑/etc/sysconfig/iptables文件

1.编辑/etc/sysconfig/iptables文件：vi /etc/sysconfig/iptables加入内容并保存：-A
RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 8080 -j ACCEPT

2.重启服务：/etc/init.d/iptables restart

3.查看端口是否开放：/sbin/iptables -L -n

主键和唯一键的区别

区别项 Primary Key（主键） Unique（唯一建）
唯一性 可以 可以
是否可以为空 不可以 可以
允许个数 只能有1个 允许多个
是否允许多列组合 允许 允许

mysql优点

MySQL是一个小型关系型数据库管理系统,MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

1． 它使用的核心线程是完全多线程，支持多处理器。

2．
有多种列类型：1、2、3、4、和8字节长度自有符号／无符号整数、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、BLOB、DATE、TIME、DATETIME、
TIMESTAMP、YEAR、和ENUM类型。

3．
它通过一个高度优化的类库实现SQL函数库并像他们能达到的一样快速，通常在查询初始化后不该有任何内存分配。没有内存漏洞。

4． 全面支持SQL的GROUP BY和ORDER
BY子句，支持聚合函数(COUNT()、COUNT(DISTINCT)、AVG()、STD()、SUM()、MAX()和MIN())。你可以在同一查询中混来自不同数据库的表。

5． 支持ANSI SQL的LEFT 0UTER JOIN和ODBC。

6．
所有列都有缺省值。你可以用INSERT插入一个表列的子集，那些没用明确给定值的列设置为他们的决省值。

7． MySQL可以工作在不同的平台上。支持C、C＋＋、Java、Perl、PHP、Python和TCL
API。

避免全盘扫描

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by
涉及的列上建立索引:

尝试下面的技巧以避免优化器错选了表扫描：

使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。

对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

WHERE t1.col_name=t2.col_name；

用--max-seeks-for-key=1000选项启动mysqld或使用SET
max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。

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

如：

select id from t where num is null

NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT
NULL，或者使用一个特殊的值，如0，-1作为默认值。

不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列
就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

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

select id from t where num=0

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

MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT
id FROM t WHERE col LIKE 'Mich%';”这个查询将使用索引，但“SELECT id FROM t WHERE
col LIKE '%ike';”这个查询不会使用索引。

  1. 应尽量避免在 where 子句中使用 or
    来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，

如：

select id from t where num=10 or num=20

可以这样查询： select id from t where num=10 union all select id from t where
num=20

4 .in 和 not in 也要慎用，否则会导致全表扫描，

如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

5.下面的查询也将导致全表扫描：

select id from t where name like '%abc%' 或者

select id from t where name like '%abc' 或者

若要提高效率，可以考虑全文检索。

而select id from t where name like 'abc%' 才用到索引

  1. 如果在 where
    子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推
    迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where
子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

如：

select id from t where substring(name,1,3)='abc'--name

select id from t where
datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id 应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where
子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，

如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table
#t(...)

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

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，

如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了
insert 及 update 的效率，因为 insert 或 update
时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered
索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新
clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar
，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t
，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替
create table，避免造成大量 log
，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create
table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate
table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD
游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET
NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC
消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

游标

游标

在存储过程中，如果某条select语句返回的结果集中只有1行，可以使用select
into语句来得到该行进行处理。如果结果集中有多行，又该如何得到其中的每一行进行处理呢？这必须使用游标

游标cursor：

可以看做是指向查询结果集的指针。通过它，就可以一次一行的从结果集中把行拿出来处理

游标的处理过程：4步

声明游标

打开游标

检索游标

关闭游标

（1）声明游标

DECLARE cursor_name CURSOR FOR select_statement;

语义：声明一个游标cursor_name，让它指向查询select_statement的结果集

游标声明必须出现在变量和条件声明的后面，但是在异常处理声明前面

一个过程中可以有多个游标声明

（2）打开游标

OPEN cursor_name;

打开游标时才执行相应的select_statement

（3）检索游标

FETCH cursor_name INTO var_name [, var_name] ...

从游标cursor_name中拿出一行，把该行的各个列值保存到各个变量中

一次只拿一行。拿完后，自动移动指针到下一行

如果没有拿到行，会抛出异常，其SQLSTATE代码值为
‘02000‘。要检测到该情况，需要声明异常处理程序 (针对条件 NOT FOUND 也可以)

通常需要在一个循环中来执行fetch语句，通过检测以上异常来结束循环

（4）关闭游标

CLOSE cursor_name;

收回游标占用的内存

示例：

DELIMITER $$

CREATE PROCEDURE number_of_players(

OUT pnumber INTEGER

)

BEGIN

DECLARE a_playerno INTEGER; ##定义一个局部变量，数据类型是int

DECLARE FOUND BOOLEAN DEFAULT TRUE;
##定义局部变量，数据类型是boolean，默认值是ture

DECLARE c_players CURSOR FOR ##定义游标，关键字是cursor for

SELECT playerno FROM PLAYERS; ##将游标的值和select语句返回的结果集关联起来

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET FOUND = FALSE;
##异常处理，所有以2开头的错误都可以捕获到，将found值设置为false

SET pnumber = 0;

OPEN c_players; ##打开游标

FETCH c_players INTO a_playerno; ##将第一行的数据给变量，然后指向第二行

WHILE FOUND DO ##循环，found为真时执行

SET pnumber = pnumber + 1;

FETCH c_players INTO a_playerno;
##将第二行的数据给变量，然后指向第三行，执行循环，执行完最后一行的值，没有值就会报错，然后会被异常处理捕获

END WHILE;

CLOSE c_players; ##关闭游标

END$$

DELIMITER ;

mysql> call number_of_players(@pnumber);

Query OK, 0 rows affected (0.00 sec)

mysql> select @pnumber;

+----------+

| @pnumber |

+----------+

| 14 |

+----------+

1 row in set (0.00 sec)

通过示例我们了解到的游标的四个步骤

定义游标

将一个游标的和一个select进行关联

打开游标

将一个游标和一个结果集关联，执行了select

获取游标

需要使用循环进行数据的获取

当获取到最后一个结果以后，再次执行循环的时候，就会报错，这个错误以2开头。

这个时候，我们需要定义一个对2开头的错误的捕获

关闭游标，结果集消失

释放资源

SSH怎么配置免密登录

Root/. ssh配置文件

1、服务器 master 上生成密钥，执行命令 ssh-keygen -t rsa

2、进入Root/. ssh配置文件

authorized_keys: 存放远程免密登录的公钥,主要通过这个文件记录多台机器的公钥。

id_rsa: 生成的私钥文件

id_rsa.pub: 生成的公钥文件

known_hosts: 已知的主机公钥清单

3、远程密钥登录

方式一，通过 ssh-copy-id 命令设置。最后一个参数是要免密钥登录的服务器 ip 地址。

ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.1.100

方式二，通过 scp
命令直接将该文件远程复制过去，使用这种方式需要注意，如果你之前已经配置了其它服务器上的密钥，这是使用这种方法，就会覆盖掉你原来的密钥，这时候是不建议使用这种方式的，如果你是先将该文件复制到服务器上的一个目录下，然后在使用追加的方式，将密钥追加到
authorized_keys 也是完全 OK 的。如果你只有两台服务器也是可以直接复制到文件。

scp -p ~/.ssh/id_rsa.pub root@<ip>:/root/.ssh/authorized_keys

Truncate和delete的区别

1、表和索引所占空间。当表被truncate
后，这个表和索引所占用的空间会恢复到初始大小，delete操作不会减少表或索引所占用的空间。

2、应用范围。truncate 只能对table；delete可以是table和view。

3、truncate 和delete只删除数据， drop则删除整个表（结构和数据）。

4、delete语句为dml（data maintain language),这个操作会被放到 rollback
segment中,事务提交后才生效。如果有相应的
tigger,执行的时候将被触发。truncate是dll（data define
language),操作立即生效，原数据不放到 rollback segment中，不能回滚。

5、在没有备份情况下，谨慎使用
truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老是想触发trigger,还是用delete。

6、truncate table 表名 速度快,而且效率高,因为:

truncate table 在功能上与不带 where 子句的 delete
语句相同：二者均删除表中的全部行。但 truncate table 比 delete
速度快，且使用的系统和事务日志资源少。

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

7、truncate table
删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用
delete

MHA的搭建

搭建MHA前期准备工作：

三台服务器

主：50

从：51

从：52，兼职作为manager

除了manager以外所有的节点都是node

node+manager两种类型

1、三台服务器安装os，配置网络，关闭防火墙和selinux

2、在三台服务器上安装mysql软件，要求版本一样

3、在三台服务器上安装xrtabackup

4、搭建主从

一主两从

搭建两次从服务器

不建议使用半同步复制

注意几个点：

1、从库都该成read-only模式

2、从库的relay log自动删除功能要关闭

3、三个数据库的server_id必须不一致

log-bin = mysql-bin //要求所有可能成为主库的节点开启二进制日志

relay_log_purge = 0 //要求所有可能成为主库的节点都要配置此项

read_only = 1 //MHA 要求所有 slave 节点配置为 read_only = 1

server-id = 2 //注意主从节点的 server-id 不同

5、安装node和manager软件，这个属于mha软件

安装的时候，需要依赖很多的per软件和Perl库，因此可能会比较麻烦

去下载已经安装好的虚拟机

6、配置互信（配置SSH免密登录）

节点之间可以互相执行命令、拷贝数据，这些都不需要密码。

配置完成互信以后，一定要进行测试。

配置MHA:

1、先配置manager，manager上配置的主要是整个集群的基本信息：

让manager认识所有的节点，包括谁是主，谁优先成为主

[server default]

manager_workdir=/var/log/masterha/app1

manager_log=/var/log/masterha/app1/manager.log

master_binlog_dir=/var/lib/mysql/data

master_ip_failover_script=/usr/bin/master_ip_failover //失败切换

master_ip_online_change_script=/usr/bin/master_ip_online_change //主动切换

user=mha_monitor //manager 使用这个账号连接各个数据库

password=mha_monitor //manager 使用这个账号密码连接各个数据库

ping_interval=1 //manager 每隔 1 秒钟，探测一下各个节点

remote_workdir=/tmp //指定远端目录

repl_user=repl //各个节点之间的复制账号和密码

repl_password=repl

ssh_user=root

[server1]

hostname=192.168.10.50

#candidate_master=1

port=3306

[server2]

hostname=192.168.10.51

candidate_master=1 //指定 2 节点优先成为主，建设没有这个选项，默认使用最新的
slave 成为主

check_repl_delay=0 //关闭延迟监测

port=3306

[server3]

hostname=192.168.10.52

port=3306

上面配置好以后，就可以在 manager 上面执行相关的脚本进行测试

上面的配置就是告诉 manager，我这个集群的基本信息

2、mha检查ssh免密登录：

masterha_check_ssh --conf=/etc/masterha/app1.cnf

3、mha检查复制

masterha_check_repl --conf=/etc/masterha/app1.cnf

4、在主库上配置虚拟 ip

# ifconfig eth0:1 192.168.10.223

5、在manager上配置漂移ip的脚本

manager需要使用这两个脚本，在新的主上启动漂移ip

master_ip_failover

master_ip_online_change

将这两个中的所有地址改成漂移ip地址就行了

这两个脚本在manager节点上

6、启动 manager：

nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master
--ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1
&

建议：在启动manager以前，启动好主从

15.insert进去的是一条数据，select查找的是相同的数据。原理是怎么实现的。

  1. xtrabackup是否有行锁(没有行锁，只有全局的锁)

17.事务在底层是怎么实现的

  1. ignix的错误日志在哪
  2. where like “%a’实际的业务中会使用到，怎么解决这个问题

20、Zabbix建立的参数有哪些

事务运行原理

  1. 在innodb 中查询到需要修改的数据页

(1) 此时会发生latch(r)争用,因为需要锁住数据链对数据链进行遍历

  1. 没有找到的话释放latch(r),去磁盘查找,发生物理读
  2. 在磁盘中查找时分为使用索引和不使用索引

(1) 使用索引会提高查询速度,提高sql的工作效率

(2) 不使用索引会发生全表扫描,会导致大量数据涌入innodb_buffer_pool

  1. 查找到的数据会放到innodb_buffer_pool里面

(1) 此时需要在innodb_buffer_pool中查找free或者clean页

(2)
在查找free和clean页时如果free已经全部用完而且在clean不够用的情况下会发生磁盘写,导致db_write工作,然后也会更新redo和ondo,redo的作用是用来记录未写入到磁盘的脏数据的日志,用于后期数据库将在innodb_buffer_pool中的脏数据更新到磁盘中,ondo的作用是记录脏数据之前的数据,用于用户执行rollback操作

(3) 此时会发生latch(x)争用,因为要将磁盘中的数据写入到innodb_buffer_pool中

在数据从磁盘上将数据写入到free或者clean中时,会发生waits特别是在数据的查找时没有使用索引时会导致大量数据涌入innodb_buffer_pool,导致发生waits

  1. 释放所有的latch

(1) 写完数据后释放latch(x)和latch(s)

  1. 将innodb_buffer_pool中的数据进行修改,增加pin锁

(1)
Pin锁的目的是为了在修改的一瞬间保证这个数据页不会被进行任何ddl操作,以避免修改失败

(2) 在修改开始前会对改数据行所在的数据页增加表级别的ix锁

① 用于避免此时其他事务对该表执行ddl操作

(3)
修改过程中会对要修改的行添加x锁,使该行进入到排他状态,以保证修改数据中,不会再其他线程不会发生脏读

此时如果有其他用户线程查找该行数据,会读取到该行数据最新一次commit后的数据,而不会读取到当前事务修改的数据,这也是为了避免脏

  1. 修改完数据后释放pin锁
  2. 当修改完数据后会发生undo和redo

(1) Undo记录修改前的数据用于rollback

(2) redo用于记录修改后的日志写入磁盘中的log file以用来慢慢更新脏数据

  1. 此时ix锁和x锁都没有释放,因为没有发生commit

(1) 也就意味着此时该表仍然处于可以进行的是select和dml不能进行ddl的状态

(2)
当然如果dml中要更新的数据是被加了x锁的这一行和读取这一行数据也是不可以的,因为该行处于x锁状态

  1. commit提交

(1)
在数据修改成功后,也就是该事务完成后提交,ix表锁和x行锁释放,但数据不会及时的写入到磁盘上,因为用通过redo进程中的log
file来后台更新磁盘上的数据

ext4和ext3的区别

区别如下：

1、Ext3文件系统最多只能支持32TB的文件系统和2TB的文件，根据使用的具体架构和系统设置，实际容量上限可能比这个数字还要低，即只能容纳2TB的文件系统和16GB的文件。而Ext4的文件系统容量达到1EB，而文件容量则达到16TB，这是一个非常大的数字了。对一般的台式机和服务器而言，这可能并不重要，但对于大型磁盘阵列的用户而言，这就非常重要了。

2、Ext3目前只支持32000个子目录，而Ext4取消了这一限制，理论上支持无限数量的子目录。

3、Ext3文件系统使用32位空间记录块数量和i-节点数量，而Ext4文件系统将它们扩充到64位。

4、当数据写入到Ext3文件系统中时，Ext3的数据块分配器每次只能分配一个4KB的块，如果写一个100MB的文件就要调用25600次数据块分配器，而Ext4的多块分配器“Multiblock
Allocator（MBAlloc）”支持一次调用分配多个数据块。

5、Ext3的数据块分配策略是尽快分配，而Ext4的策略是尽可能地延迟分配，直到文件在缓冲中写完才开始分配数据块并写入磁盘，这样就能优化整个文件的数据块分配，显著提升性能。

6、Ext3文件系统采用间接映射地址，当操作大文件时，效率极其低下。例如，一个100MB大小的文件，在Ext3中要建立25600个数据块（以每个数据块大小为4KB为例）的映射表；而Ext4引入了盘区概念，每个盘区为一组连续的数据块，上述文件可以通过盘区的方式表示为“该文件数据保存在接下来的25600个数据块中”，提高了访问效率。

7、Ext4支持更大的i-节点。之前的Ext3默认的i-节点大小128字节，Ext4为了在i-节点中容纳更多的扩展属性，默认i-节点大小为256字节。另外，Ext4还支持快速扩展属性和i-节点保留。

InnoDB与Myisam区别

MyISAM InnoDB
构成上的区别： 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
.frm文件存储表定义。
数据文件的扩展名为.MYD (MYData)。
索引文件的扩展名是.MYI (MYIndex)。
事务处理上方面: MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持 InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能
SELECT   UPDATE,INSERT，Delete操作 如果执行大量的SELECT，MyISAM是更好的选择 1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表
2.DELETE   FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
3.LOAD   TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用
对AUTO_INCREMENT的操作 每表一个AUTO_INCREMEN列的内部处理。 如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中，而不是存在磁盘上  关于该计算器的算法实现，请参考
MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。 AUTO_INCREMENT列在InnoDB里如何工作
AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引 更好和更快的auto_increment处理
表的具体行数 select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含   where条件时，两种表的操作是一样的 InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
锁 表锁 提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in
SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like "%aaa%"

AWK命令

AWK命令用于对数据的提取，常用的还有grep和 sed 俗称三剑客

AWK命令倾向于将一行分成数个字段来处理，在shell脚本中是个强有力的工具用来对数据提取

AWK的常用语法

-F来指定分隔符

AWK的数据字段变量

$0表示整行文本

$1表示文本中第一个数据字段

$2表示文本中第二个数据字段

$n表示文本中第n个数据字段

AWK命令的完整语法

AWK命令的基本语法

awk的指令一定要用单引号括起

awk的动作一定要用花括号括起

模式可以是正则表达式、条件表达式或两种组合，条件表达式不能加/ /

如果模式是正则表达式要用/定界符

多个动作之间用;号分开

示例：awk -F: '/^[^h]/{print $1,$7}' /etc/passwd

不显示以h开头的行的第一列和第七列 [^h]表示不以h开头的行

awk -F '[😕]' '{print $1,$10}' /etc/passwd

以：或者/作为分隔符显示第1列和第10列

AWK命令的语法

awk命令的操作符

正则表达式和bash一致

数学运算：+，-，*，/， %，++（自加一），- -（自减一）

逻辑关系符：&&， ||， !

比较操作符：>，<，>=，!=，<=，== ~ !~

文本数据表达式：== （精确匹配）

~波浪号表示匹配后面的模式，想要某个字段进行模式匹配，就要使用~（波浪符号）
使用格式： 字段 ~ /模式/

who | awk '$2 ~ /pts/{print $1}‘

第二列要包含有pts，然后输出第一列

awk -F: '$3 ~ /\<...\>/ {print $1,$3}' /etc/passwd

冒号作为分隔符，第三列是三个字符的输出第一列和第三列

seq 100 | awk '$1 % 5 == 0 || $1 ~ /^1/{print $1}'

输出整除5的数和以1开头的数

awk -F: '$1 == "root"{print $1,$3}' /etc/passwd

第一个字段等于root就输出第一个字段和第三个字段

阿里7题

问题1：你之前处理过MySQL的哪些案例？

解答思路：说到案例，逃离不了MySQL的五大知识模块：体系结构、数据的备份恢复、复制、高可用集群架构和优化。我们可以从这五个方向着手考虑，比如：

MySQL版本的升级；

处理集群架构中的各种“坑”和问题（你遇到过的就可以）；

根据公司业务类型，合理设计MySQL库、表和后期架构；

定期进行灾备恢复演练；

恢复误删除的数据信息。

02

问题2：什么是死锁？锁等待？如何优化这类问题？通过数据库哪些表可以监控？

解答思路：死锁是指两个或多个事务在同一资源上互相占用，并请求加锁时，而导致的恶性循环现象。当多个事务以不同顺序试图加锁同一资源时，就会产生死锁。

锁等待：MySQL数据库中，不同session在更新同行数据时，会出现锁等待的现象。重要的三张锁的监控表：innodb_trx、innodb_locks和innodb_lock_waits。

03

问题3：MySQL主从复制的具体原理是什么？

解答思路：直接阐述原理即可，表达一定要清楚。

主服务器把数据更新记录到二进制日志中，从服务器通过I/O
thread向主库发起binlog请求，主服务器通过I/O dump
thread把二进制日志传递给从库，从库通过I/O
thread记录到自己的中继日志中。然后再通过SQL thread应用中继日志中SQL的内容。

04

问题4：MySQL有哪些索引类型？

解答思路：可以从三个角度去谈。

首先从数据结构角度上可以分为B+tree索引、hash索引、fulltext索引（InnoDB、MyISAM都支持）。其次从存储角度上可以分为聚集索引和非聚集索引。最后逻辑角度上可以分为primary
key、normal key、单列、复合和覆盖索引。

05

问题5：服务器负载过高或者网页打开缓慢，简单说说你的优化思路？

解答思路：我们可以通过前面讲过的优化思路中的四维度模型去阐述。

首先要发现问题的过程，通过操作系统、数据库、程序设计、硬件角度四个维度找到问题所在。先找到瓶颈点的位置，制定好优化方案，形成处理问题的体系模型。体系制定好之后，在测试环境进行优化方案的测试。测试环境下如果优化效果很好，再实施到生产环境上。最后做好处理问题的记录。好记性不如烂笔头，多做总结，方可大步前进。

06

问题6：如何优化一条慢SQL语句？

解答思路：针对SQL语句的优化，我们不要一上来就回答添加索引，这样显得太不专业。我们可以从如下几个角度去分析：

①回归到表的设计层面，数据类型选择是否合理。

②大表碎片的整理是否完善。

③表的统计信息是不是准确的。

④审查表的执行计划，判断字段上面有没有合适的索引。

⑤针对索引的选择性，建立合适的索引（就又涉及大表DDL的操作问题。所以说，我们要有能力把各个知识点联系起来）

07

问题7：为什么要为InnoDB表设置自增列做主键？

解答思路：使用自增列做主键，写入顺序是自增的，和B+数叶子节点分裂顺序一致。InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致时，存取效率是最高的。

知乎60题

Mysql中有哪几种锁？

1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

Mysql中有哪些不同的表格？

共有5种类型的表格：

MyISAM

Heap

Merge

INNODB

ISAM

简述在MySQL数据库中MyISAM和InnoDB的区别

MyISAM：

不支持事务，但是每次查询都是原子的；

支持表级锁，即每次操作是对整个表加锁；

存储表的总行数；

一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；

采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDb：

支持ACID的事务，支持事务的四种隔离级别；

支持行级锁及外键约束：因此可以支持写并发；

不存储总行数；

一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；

主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？

read uncommited ：读到未提交数据

read committed：脏读，不可重复读

repeatable read：可重读

serializable ：串行事物

CHAR和VARCHAR的区别？

1.CHAR和VARCHAR类型在存储和检索方面有所不同
2.CHAR列长度固定为创建表时声明的长度，长度值范围是1到255
当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。

主键和候选键有什么区别？

表格的每一行都由主键唯一标识,一个表只有一个主键。

主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

myisamchk是用来做什么的？

它用来压缩MyISAM表，这减少了磁盘或内存使用。

MyISAM Static和MyISAM Dynamic有什么区别？

在MyISAM
Static上的所有字段有固定宽度。动态MyISAM表将具有像TEXT，BLOB等字段，以适应不同长度的数据类型。

MyISAM Static在受损情况下更容易恢复。

如果一个表有一列定义为TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段将获取当前时间戳。

列设置为AUTO INCREMENT时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

怎样才能找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID将返回由Auto_increment分配的最后一个值，并且不需要指定表名称。

你怎么看到为表格定义的所有索引？

索引是通过以下方式为表格定义的：

SHOW INDEX FROM
<tablename>;

LIKE声明中的％和_是什么意思？

％对应于0个或更多字符，_只是LIKE语句中的一个字符。

如何在Unix和Mysql时间戳之间进行转换？

UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的命令
FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令

列对比运算符是什么？

在SELECT语句的列比较中使用=，<>，<=，<，>
=，>，<<，>>，<=>，AND，OR或LIKE运算符。

BLOB和TEXT有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据。TEXT是一个不区分大小写的BLOB。

BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。

mysql_fetch_array和mysql_fetch_object的区别是什么？

以下是mysql_fetch_array和mysql_fetch_object的区别：

mysql_fetch_array（） – 将结果行作为关联数组或来自数据库的常规数组返回。

mysql_fetch_object – 从数据库返回结果行作为对象。

MyISAM表格将在哪里存储，并且还提供其存储格式？

每个MyISAM表格以三种格式存储在磁盘上：

·“.frm”文件存储表定义

·数据文件具有“.MYD”（MYData）扩展名

索引文件具有“.MYI”（MYIndex）扩展名

Mysql如何优化DISTINCT？

DISTINCT在所有列上转换为GROUP BY，并与ORDER BY子句结合使用。

1

SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;


如何显示前50行？

在Mysql中，使用以下代码查询显示前50行：

SELECT*FROM

LIMIT 0,50;

可以使用多少列创建索引？

任何标准表最多可以创建16个索引列。

NOW（）和CURRENT_DATE（）有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。

CURRENT_DATE（）仅显示当前年份，月份和日期。

什么是非标准字符串类型？

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

什么是通用SQL函数？

CONCAT(A, B) –
连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。

FORMAT(X, D)- 格式化数字X到D有效数字。

CURRDATE(), CURRTIME()- 返回当前日期或时间。

NOW（） – 将当前日期和时间作为一个值返回。

MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） – 从日期值中提取给定数据。

HOUR（），MINUTE（），SECOND（） – 从时间值中提取给定数据。

DATEDIFF（A，B） – 确定两个日期之间的差异，通常用于计算年龄

SUBTIMES（A，B） – 确定两次之间的差异。

FROMDAYS（INT） – 将整数天数转换为日期值。

MYSQL支持事务吗？

在缺省模式下，MYSQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。

但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB
tables的话，你的MYSQL就可以使用事务处理,使用SET
AUTOCOMMIT=0就可以使MYSQL允许在非autocommit模式，在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。

mysql里记录货币用什么字段类型好

NUMERIC和DECIMAL类型被Mysql实现为同样的类型，这在SQL92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

例如：

salary DECIMAL(9,2)

在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。

因此，在这种情况下，能被存储在salary列中的值的范围是从-9999999.99到9999999.99。

mysql有关权限的表都有哪几个？

Mysql服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。

列的字符串类型可以是什么？

字符串类型是：

SET

BLOB

ENUM

CHAR

TEXT

MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

a. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
b. 选择合适的表字段数据类型和存储引擎，适当的添加索引。
c. mysql库主从读写分离。
d. 找规律分表，减少单表中的数据量提高查询速度。
e。添加缓存机制，比如memcached，apc等。
f. 不经常改动的页面，生成静态页面。
g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2,
field_3 FROM TABLE.

锁的优化策略

  1. 读写分离
  2. 分段加锁
  3. 减少锁持有的时间
  4. 多个线程尽量以相同的顺序去获取资源

不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

索引的底层实现原理和优化

B+树，经过优化的B+树

主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引。

什么情况下设置了索引但无法使用

1.以“%”开头的LIKE语句，模糊匹配

  1. OR语句前后没有同时使用索引
  2. 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

实践中如何优化MySQL

最好是按照以下顺序优化：

1.SQL语句及索引的优化

  1. 数据库表结构的优化

3.系统配置的优化

4.硬件的优化

优化数据库的方法

选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM

使用连接(JOIN)来代替子查询

适用联合(UNION)来代替手动创建的临时表

事务处理

锁定表、优化事务处理

适用外键，优化锁定表

建立索引

优化查询语句

简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。

主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字
PRIMARY KEY 来创建。

索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。

索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

数据库中的事务是什么?

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

事务特性：

（1）原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

（2）一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态

（3）隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，

（4）
持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

或者这样理解：

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

SQL注入漏洞产生的原因？如何防止？

SQL注入产生的原因：程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。

防止SQL注入的方式：
开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置

执行sql语句时使用addslashes进行sql语句转换

Sql语句书写尽量不要省略双引号和单引号。

过滤掉sql语句中的一些关键词：update、insert、delete、select、 * 。

提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。

为表中得字段选择合适得数据类型

字段类型优先级: 整形>date,time>enum,char>varchar>blob,text
优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得数据类型，应该优先选择占用空间小的数据类型

存储时期

Datatime:以 YYYY-MM-DD HH:MM:SS
格式存储时期时间，精确到秒，占用8个字节得存储空间，datatime类型与时区无关
Timestamp:以时间戳格式存储，占用4个字节，范围小1970-1-1到2038-1-19，显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改timestamp列得值
Date:（生日）占用得字节数比使用字符串.http://datatime.int储存要少，使用date只需要3个字节，存储日期月份，还可以利用日期时间函数进行日期间得计算
Time:存储时间部分得数据
注意:不要使用字符串类型来存储日期时间数据（通常比字符串占用得储存空间小，在进行查找过滤可以利用日期得函数）
使用int存储日期时间不如使用timestamp类型

对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

1.索引的目的是什么？
快速访问数据表中的特定信息，提高检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性。
加速表和表之间的连接

使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

2.索引对数据库系统的负面影响是什么？
负面影响：
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

3.为数据表建立索引的原则有哪些？
在最频繁使用的、用以缩小查询范围的字段上建立索引。

在频繁使用的、需要排序的字段上建立索引

4.什么情况下不宜建立索引？
对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

解释MySQL外连接、内连接与自连接的区别

先说什么是交叉连接:
交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

内连接
则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。
外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中
的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。

左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接。

Myql中的事务回滚机制概述

事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。

要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚

SQL语言包括哪几部分？每部分都有哪些操作关键字？

SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询（DQL）四个部分。

数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等

数据操纵：Select ,insert,update,delete,

数据控制：grant,revoke

数据查询：select

完整性约束包括哪些？
数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

分为以下四类：

  1. 实体完整性：规定表的每一行在表中是惟一的实体。

域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。

参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign
key、check、UNIQUE) 。

三个范式？

答：第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。。

范式化设计优缺点:

优点:

可以尽量得减少数据冗余，使得更新快，体积小

缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

反范式化:

优点:可以减少表得关联，可以更好得进行索引优化

缺点:数据冗余以及数据异常，数据得修改需要更多的成本

主键、外键和索引的区别？

主键、外键和索引的区别

定义：

主键–唯一标识一条记录，不能有重复的，不允许为空

外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值

索引–该字段没有重复值，但可以有一个空值

作用：

主键–用来保证数据完整性

外键–用来和其他表建立联系用的

索引–是提高查询排序的速度

个数：

主键–主键只能有一个

外键–一个表可以有多个外键

索引–一个表可以有多个唯一索引

你可以用什么来确保表格里的字段只接受特定范围里的值?

答：Check限制，它在数据库表格里被定义，用来限制输入该列的值。

触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。

说说对SQL语句优化有哪些方法？（选择几条）

（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。

（3） 避免在索引列上使用计算

（4）避免在索引列上使用IS NULL和IS NOT NULL

（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by
涉及的列上建立索引。

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

（7）应尽量避免在 where
子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

网络部分

posted @ 2019-06-23 11:45  DBA_zzher  阅读(4088)  评论(0)  编辑  收藏  举报