---

---

MySQL DBA 面试全揭秘

---

---

1.请描述下MySQL中InnoDB支持的四种事务隔离级别名称，以及逐级之间区别

?read uncommitted:未提交读。可以读取到其他线程修改(未提交)的数据

?read committed:提交读。只能读取到其他线程已经提交的数据。解决脏读，修改的数据可能最后未提交。只锁定索引，并且不锁定索引前的间隙

?repeatable read:可重复读。不能读取到其他线程提交的数据，间隙锁解决幻读。使用唯一索引进行等值查询，则只锁定索引，不锁定索取前间隙。其他查询，则不仅锁定索引，并且锁定索取范围包含的间隙

?serializable:串行化读。所有的访问都串行化。将select转为select ... lock in share mode 即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。

2.Mysql数据库有多少种日志

?错误日志：记录出错信息，也记录一些警告信息或者正确的信息

?慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

?二进制日志：记录对数据库执行更改的所有操作

?查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

3.事务是如何通过日志来实现的

?隔离性： 通过锁实现

?原子性、一致性和持久性是通过 redo和undo来完成的。

?在Innodb存储引擎中，事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”，innodb通过此方式来保证事务的完整性。也就意味着磁盘上存储的数据页和内存缓冲池上面的页是不同步的，是先写入redo log，然后写入data file，因此是一种异步的方式。通过 show engine innodb status/G 来观察之间的差距

4.mysqldump以及xtranbackup的实现原理，

?mysqldump是最简单的逻辑备份方式。在备份myisam表的时候，如果要得到一致的数据，就需要锁表，简单而粗暴。而在备份innodb表 的时候，加上–master-data=1 –single-transaction 选项，在事务开始时刻，记录下binlog pos点，然后利用mvcc来获取一致的数据，由于是一个长事务，在写入和更新量很大的数据库上，将产生非常多的undo，显著影响性能，所以要慎用。

?优点：简单，可针对单表备份，在全量导出表结构的时候尤其有用。

?缺点：简单粗暴，单线程，备份慢而且恢复慢，跨IDC有可能遇到时区问题

?xtrabackup它实际上是物理备份+逻辑备份的组合。在备份 innodb表的时候，它拷贝ibd文件，并一刻不停的监视redo log的变化，append到自己的事务日志文件。在拷贝ibd文件过程中，ibd文件本身可能被写”花”，这都不是问题，因为在拷贝完成后的第一个 prepare阶段，Xtrabackup采用类似于innodb崩溃恢复的方法，把数据文件恢复到与日志文件一致的状态，并把未提交的事务回滚。如果同 时需要备份myisam表以及innodb表结构等文件，那么就需要用flush tables with lock来获得全局锁，开始拷贝这些不再变化的文件，同时获得binlog位置，拷贝结束后释放锁，也停止对redo log的监视。

作为DBA，特别是Mysql的DBA，需要了解很多方面的内容，架构，性能，分析，代码等等。有时候一个人不可能面面俱到，特别是在面试的时候，面试官的喜好就能决定面试的结果，所以在面试之前多方面了解一下，以备不时之需。

MYSQL数据库服务器性能分析的方法命令有哪些?

• ?Show status

• ?一些值得监控的变量值：

• ?Bytes_received和Bytes_sent

• ?和服务器之间来往的流量。

• ?Com_*服务器正在执行的命令。

• ?Created_*在查询执行期限间创建的临时表和文件。

• ?Handler_*存储引擎操作。

• ?Select_*不同类型的联接执行计划。

• ?Sort_*几种排序信息。

• ?Show session status like ‘Select’;

• ?Show profiles

• ?SET profiling=1;

• ?Show profiles\G

• ?Show profile;

MYSQL数据库索引类型都有哪些?

• ?B-TREE索引加速了数据访问，因为存储引擎不会扫描整个表得到需要的数据。相反，它从根节点开始。根节点保存了指向子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点中值的上界和下界。最后，存储引擎可能无法找到需要的数据，也可能成功地找到包含数据的叶子页面。

  例：B-TREE索引 对于以下类型查询有用。匹配全名、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配一部分并且匹配某个范围中的另一部分；

• ?B-TREE索引的局限：如果查找没有从索引列的最左边开始，它就没什么用处。不能跳过索引中的列，存储引擎不能优先访问任何在第一个范围条件右边的列。例：如果查询是where last_name=’Smith’ AND first_name LIKE ‘J%’ AND dob=’1976-12-23’;访问就只能使用索引的头两列，因为LIKE是范围条件。

• ?哈希索引建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中每一行的指针。

• ?因为索引只包含了哈希码和行指针，而不是值自身，MYSQL不能使用索引中的值来避免读取行。

• ?MYSQL不能使用哈希索引进行排序，因为它们不会按序保存行。

• ?哈希索引不支持部分键匹配，因为它们是由被索引的全部值计算出来的。也就是说，如果在（A，B）两列上有索引，并且WHERE子句中只使用了A，那么索引就不会起作用。

• ?哈希索引只支持使用了= IN（）和的相等比较。它们不能加快范围查询。例如WHERE price > 100;

• ?访问哈希索引中的数据非常快，除非碰撞率很高。当发生碰撞的时候，存储引擎必须访问链表中的每一个行指针，然后逐行进行数据比较，以确定正确的数据。如果有很多碰撞，一些索引维护操作就有可能会变慢。

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

?MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

• ?user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

• ?db权限表：记录各个帐号在各个数据库上的操作权限。

• ?table_priv权限表：记录数据表级的操作权限。

• ?columns_priv权限表：记录数据列级的操作权限。

• ?host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

mysql 中 myisam 与 innodb 的区别

• ?Innodb 支持事务， myisam 不支持；

• ?Innodb 支持行级别锁， myisam 不支持；

• ?Innodb 不能通过直接拷贝表文件的方法拷贝表到另外一台机器， myisam支持；

• ?Innodb 是索引组织表， myisam 是堆表；

• ?Innodb 表不容易损坏， myisam 容易；

• ?Innodb 表支持多种行格式， myisam 不支持；

世界上没有最快的刀，也没有最结实的盾。所谓无坚不破，估计说的也是临时磨刀，不快也亮。就像面试一样，先能过去面试，才能谈及薪水。加油吧兄弟姐妹们！

Mysql面试题集

1.mysql 的复制原理以及流程

• (1)基本原理流程， 3 个线程以及之间的关联。

MySQL 的复制原理： Master 上面事务提交时会将该事务的 binlog event 写入

binlog file,然后 master 将 binlog event 传到 slave 上面， slave 应用该 binlog event 实现逻辑复 制。

MySQL 的复制是基于如下 3 个线程的交互（ 多线程复制里面应该是 4 类线程）：

a. Master 上面的 binlog dump 线程，该线程负责将 master 的 binlog event 传到

slave；

b. Slave 上面的 IO 线程，该线程负责接收 Master 传过来的 binlog，并写入 relay

log；

c. Slave 上面的 SQL 线程，该线程负责读取 relay log 并执行；

d. 如果是多线程复制，无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真

正的多线程复制， SQL 线程只做 coordinator，只负责把 relay log 中的 binlog

读出来然后交给 worker 线程， woker 线程负责具体 binlog event 的执行；

• (2)再问一致性延时性，数据恢复。

一致性可以从以下几个方面来讲：

a.在 MySQL5.5 以及之前， slave 的 SQL 线程执行的 relay log 的位置只能保存

在文件（ relay-log.info）里面，并且该文件默认每执行 10000 次事务做一次

同步到磁盘， 这意味着 slave 意外 crash 重启时， SQL 线程执行到的位置和

数据库的数据是不一致的，将导致复制报错，如果不重搭复制，则有可能会

导致数据不一致。 MySQL 5.6 引入参数 relay_log_info_repository，将该参

数设置为 TABLE 时， MySQL 将 SQL 线程执行到的位置存到

mysql.slave_relay_log_info 表，这样更新该表的位置和 SQL 线程执行的用

户事务绑定成一个事务，这样 slave 意外宕机后， slave 通过 innodb 的崩溃

恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。

b. MySQL 5.6 引入 GTID 复制，每个 GTID 对应的事务在每个实例上面最多执行

一次， 这极大地提高了复制的数据一致性；

c. MySQL 5.5 引入半同步复制， 用户安装半同步复制插件并且开启参数后，设

置超时时间，可保证在超时时间内如果 binlog 不传到 slave 上面，那么用户

提交事务时不会返回，直到超时后切成异步复制，但是如果切成异步之前用

户线程提交时在 master 上面等待的时候，事务已经提交，该事务对 master

上面的其他 session 是可见的，如果这时 master 宕机，那么到 slave 上面该

事务又不可见了，该问题直到 5.7 才解决；

d. MySQL 5.7 引入无损半同步复制，引入参 rpl_semi_sync_master_wait_point，

该参数默认为 after_sync，指的是在切成半同步之前，事务不提交，而是接

收到 slave 的 ACK 确认之后才提交该事务，从此，复制真正可以做到无损

的了。

延时性：

可以讲下 5.5 是单线程复制， 5.6 是多库复制（对于单库或者单表的并发操作是

没用的）， 5.7 是真正意义的多线程复制，它的原理是基于 group commit， 只要

master 上面的事务是 group commit 的，那 slave 上面也可以通过多个 worker

线程去并发执行。 和 MairaDB10.0.0.5 引入多线程复制的原理基本一样。

• (3)再问各种工作遇到的复制 bug 的解决方法

没碰到就说没有吧

2.mysql 中 varchar 与 char 的区别以及 varchar(20)中的20代表的涵义

• (1)varchar 与 char 的区别

在单字节字符集下， char（ N） 在内部存储的时候总是定长， 而且没有变长

字段长度列表中。 在多字节字符集下面， char(N)如果存储的字节数超过 N，

那么 char（ N）将和 varchar（ N）没有区别。在多字节字符集下面，如果存

储的字节数少于 N，那么存储 N 个字节，后面补空格，补到 N 字节长度。 都

存储变长的数据和变长字段长度列表。 varchar(N)无论是什么字节字符集，都

是变长的，即都存储变长数据和变长字段长度列表。

• (2)varchar(50)中 50 的涵义

• (3)int（ 20）中 20 的涵义

不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报

表展示

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

(1)有多少种日志

redo/undo

(2)日志的存放形式

redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系

统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。

Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata*文件里面， 5.6 之后，可以通

过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata*之外。

(3)事务是如何通过日志来实现的。

基本流程如下：

因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改

数据页，再记数据页修改的 redo。 Redo（里面包括 undo 的修改） 一定要比数据页

先持久化到磁盘。 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的

状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo

把该事务的修改回滚到事务开始之前。 如果有 commit 记录，就用 redo 前滚到该事

务完成时并提交掉

4.mysql 数据库 cpu 飙升到 500%的话他怎么处理

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不

是，找出占用高的进程，并进行相关处理。如果是 mysqld 造成的， show processlist，看

看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行

计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉

这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改

内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，

有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

1. 讲述下如何做sql优化

?explain 出来的各种 item 的意义

?profile 的意义以及使用场景。

?explain 中的索引问题。

(1) explain 出来的各种 item 的意义

id:每个被独立执行的操作的标志，表示对象被操作的顺序。一般来说， id 值大，先被执行；

如果 id 值相同，则顺序从上到下。

select_type：查询中每个 select 子句的类型。 具体待明天补充。

table:名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。

partitions:匹配的分区信息。

type:join 类型。 具体指待明天补充。

possible_keys：列出可能会用到的索引。

key:实际用到的索引。

key_len:用到的索引键的平均长度，单位为字节。

ref:表示本行被操作的对象的参照对象，可能是一个常量用 const 表示，也可能是其他表的

key 指向的对象，比如说驱动表的连接列。

rows:估计每次需要扫描的行数。

filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。

extra:重要的补充信息。 具体待明天补充。

(2) profile 的意义以及使用场景。

Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候，需要用

profile 来对 sql 进行更细致的分析，找出 sql 所花的时间大部分消耗在哪个部分，确认 sql

的性能瓶颈。 (我用的也不多，期待更好的答案)

(3) explain 中的索引问题。

Explain 结果中，一般来说，要看到尽量用 index(type 为 const、 ref 等， key 列有值)，避

免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列，需要建立索引。

被驱动表的连接列，也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立

联合索引。当有排序或者 group by 的需求时，也可以考虑建立索引来达到直接排序和汇总

的需求

2. 讲述下做过何种备份及相关计划

?备份计划

?备份恢复时间

?备份恢复失败如何处理

原理：

mysqldump 属于逻辑备份。加入--single-transaction 选项可以进行一致性备份。后台进程

会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVEL

REPEATABLE READ)，之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENT

SNAPSHOT */)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的

数据读取出来。 如果加上--master-data=1 的话，在刚开始的时候还会加一个数据库的读锁

(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(show

master status)，马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务。

Xtrabackup:

xtrabackup 属于物理备份，直接拷贝表空间文件，同时不断扫描产生的 redo 日志并保存下

来。最后完成 innodb 的备份后，会做一个 flush engine logs 的操作(老版本在有 bug，在

5.6 上不做此操作会丢数据)，确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交

概念，因为 xtrabackup 并不拷贝 binlog，所以必须保证所有的 redo log 都落盘，否则可

能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点，数据文件虽

然不是一致性的，但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事

情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，

备份完后解锁。 这样就做到了完美的热备。

备份计划：

视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump

更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份

出来的文件比较小，压缩之后更小)。

100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般

是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。

备份恢复时间：

物理备份恢复快，逻辑备份恢复慢

备份恢复失败如何处理：

首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、

权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。、

3.500 台 db，在最快时间之内重启

可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。 也可以使用 salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多线程工具同时操作多台服务器

4.innodb 的读写参数优化

?读取参数， global buffer pool 以及 local buffer

?写入参数

?与 IO 相关的参数

?缓存参数以及缓存的适用场景

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

Global buffer：

Innodb_buffer_pool_size

innodb_log_buffer_size

innodb_additional_mem_pool_size

local buffer(下面的都是 server 层的 session 变量，不是 innodb 的)：

Read_buffer_size

Join_buffer_size

Sort_buffer_size

Key_buffer_size

Binlog_cache_size

(2)写入参数

insert_buffer_size资源由 www.eimhe.com 美河学习在线收集提供

innodb_double_write

innodb_write_io_thread

innodb_flush_method

(3)与 IO 相关的参数

Sync_binlog

Innodb_flush_log_at_trx_commit

Innodb_lru_scan_depth

Innodb_io_capacity

Innodb_io_capacity_max

innodb_log_buffer_size

innodb_max_dirty_pages_pct

(4)缓存参数以及缓存的适用场景

指的是查询缓存吗？？？ 使用于读多写少，如分析报表等等

query_cache_size

query_cache_type

query_cache_limit

maximumquery_cache_size

作为DBA，不懂开发可以，不懂优化可以么？估计大部分是不可以的。因为开发的问题真的太多啦，多到你自己都觉得绝望！优化是个大大的标题，涉及到方方面面，主机，网络，SQL语句，不管你做了多久的DBA，有些东西还是得知道一点。

1. mysql如何实现高效分页

例题：SELECT * FROM `bigdata` ORDER BY id DESC LIMIT 100000,2000;

耗时： 0.813ms

如何对上述语句在高并发的情况下进行优化？

答案：

利用clue方法，给翻页提供一些线索，比如还是SELECT * FROM `bigdata` order by id desc，按id降序分页，每页2000条，当前是第50页，当前页条目id最大的是102000，最小的是100000。如果我们只提供上一页、下一页这样的跳转（不提供到第N页的跳转）。

那么在处理上一页的时候SQL语句可以是：

SELECT * FROM `bigdata` WHERE id<=102000 ORDER BY id DESC LIMIT 2000; #上一页

耗时：0.015ms

处理下一页的时候SQL语句可以是：

SELECT * FROM `bigdata` WHERE id>102000 ORDER BY id ASC LIMIT 2000; #下一页

耗时：0.015ms

2.Mysql 语句explain的含义：

mysql> explain SELECT * FROM `bigdata` WHERE to_id = 6696 AND del = 0 AND bigdata=0 ORDER BY `send_time` DESC LIMIT 4;

+—-+————-+———+——+—————+——-+———+——-+——+—————————–+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+———+——+—————+——-+———+——-+——+—————————–+

| 1 | SIMPLE | bigdata | ref | to_id | to_id | 4 | const | 1 | Using where; Using filesort |

+—-+————-+———+——+—————+——-+———+——-+——+—————————–+

1 row in set (0.00 sec)

答案：

?table 显示这一行的数据是关于哪张表的

?type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

?possible_keys 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

?key 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

?key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好

?ref 显示索引的哪一列被使用了，如果可能的话，是一个常数

?rows MYSQL认为必须检查的用来返回请求数据的行数

?Extra 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra 列返回的描述的意义

?Distinct 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

?Not exists MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

?Range checked for each

?Record（index map:#）没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

?Using filesort 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

?Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

?Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

?Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题

不同连接类型的解释（按照效率高低的顺序排序）

?system 表只有一行：system表。这是const连接类型的特殊情况

?const 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

?eq_ref 在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

?ref 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

?range 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

?index 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

?ALL 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

3.举例说明mysql常用的hint

答案：

?强制索引 FORCE INDEX

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

?忽略索引 IGNORE INDEX

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。

?关闭查询缓冲 SQL_NO_CACHE

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;

有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。

?强制查询缓冲 SQL_CACHE

SELECT SQL_CALHE * FROM TABLE1;

如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

?优先操作 HIGH_PRIORITY

HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。

SELECT HIGH_PRIORITY * FROM TABLE1;

?滞后操作 LOW_PRIORITY

LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。

update LOW_PRIORITY table1 set field1= where field1= …

延时插入 INSERT DELAYED

INSERT DELAYED INTO table1 set field1= …

INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有 空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增 的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

?强制连接顺序 STRAIGHT_JOIN

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

?强制使用临时表 SQL_BUFFER_RESULT

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;

一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

4.举例说明如何对mysql索引优化

答案：

?创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

?复合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

?索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

?使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

?排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

?like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

?不要在列上进行运算

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01’;

?不使用NOT IN和操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id<3来代替。

1. drop,delete与truncate的区别

?drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

?DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

?表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

?一般而言，drop > truncate > delete

?应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

?TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

?truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

?delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

?truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

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

?Truncate table 表名 速度快,而且效率高,因为:

?truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

?TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

?对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

2.存储过程与触发器的区别

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

3.一张表,里面有ID自增主键,当insert了10条记录之后,删除了第10,9,8条记录,再把Mysql重启,再insert一条记录,这条记录的ID是11还是8?

==>此问题答案在留言10条后发布

4.MySQL的联结含义及用法

?内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

?外联结：分为外左联结和外右联结。

?左联结A、B表的意思就是将表A中的全部记录和表B中联结的字段与表A的联结字段符合联结条件的那些记录形成的记录集的联结，这里注意的是最后出来的记录集会包括表A的全部记录。

n右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：

lSelect A.name B.name From A Left Join B On A.id=B.id 和Select A.name B.name From B Right Join A on B.id=A.id执行后的结果是一样的。

?全联结：将两个表中存在联结关系的字段的所有记录取出形成记录集的联结（这个不需要记忆，只要是查询中提到了的表的字段都会取出，无论是否符合联结条件，因此意义不大）。

?无联结：不用解释了吧，就是没有使用联结功能呗，也有自联结的说法。

---

---

因为有大家的支持，我们才能做到现在，感谢你们这一路上对我们的支持.在这篇文章中，我们将主要针对MySQL的实用技巧，讲讲面试中相关的问题.

1. 如何使用SELECT语句找到你正在运行的服务器的版本并打印出当前数据库的名称?

答：下面的语句的结果会显示服务器的版本和当前的数据库名称

 mysql> SELECT VERSION(), DATABASE();

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

| VERSION()               | DATABASE() |
 +-------------------------+------------+
 | 5.5.34-0ubuntu0.13.10.1 | NULL       |

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

 1 row in set (0.06 sec)

在Database一列中显示NULL是因为我们当前没有选择任何数据库。因此，使用下面的语句先选择一个数据库,就能看到相应的结果。

mysql> use Tecmint;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select VERSION(), DATABASE();

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

| VERSION()               | DATABASE() |
+-------------------------+------------+
| 5.5.34-0ubuntu0.13.10.1 | tecmint    |

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

 1 row in set (0.00 sec)

2. 使用非运算符(!)从表”Tecmint”中列出除了”SAM”以外的所有user记录

答：使用下面的语句

mysql> SELECT * FROM Tecmint WHERE user !=SAM;

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

 | date                | user | host   | root | local   | size | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | venus | barb     | venus | 98151 |

 | 2001-05-15 08:50:57 | TIM | venus  | phil | venus   | 978 | 
+---------------------+---------+---------+---------+---------+-------+

3. ‘AND’运算符有可能带着非运算符(!)一起用吗？

答：当我们使用‘=’号时用‘AND’连接，用‘!=’时用‘OR’连接，下面是‘=’和AND运算符一起用的例子

mysql> SELECT * FROM mail WHERE user = SAM AND root = phil

‘!=’和OR运算符的例子

mysql> SELECT * FROM mail WHERE user != SAM OR root != phil

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

| date                | user | host    | root | local   | size | 
+---------------------+---------+---------+---------+---------+-------+ 
| 2001-05-14 14:42:21 | Anthony | venus | barb    | venus | 98151 |

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

• = : 等于
• != : 不等于
• ! : 代表“非”的运算符

AND和OR在MySQL中被看作连接运算符

4. IFNULL()当打在MySQL中有什么作用?

答：使用IFNULL()方法能使MySQL中的查询更加精确。IFNULL()方法将会测试它的第一个参数，若不为NULL则返回该参数的值，否则返回第二个参数的值

mysql> SELECT name, IFNULL(id,'Unknown') AS 'id' FROM taxpayer;

+---------+---------+ | name    | id | 
+---------+---------+ 
| bernina | 198-48  | | bertha  | Unknown | 
| ben | Unknown | | bill    | 475-83 | 
+---------+---------+

5. 如果你只想知道从一个结果集的开头或者结尾开始的特定几条记录，该如何实现？

答：我们需要把LIMIT语句接在ORDER BY语句后使用，以达到上述效果。

显示一行记录

mysql> SELECT * FROM name LIMIT 1;

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

| id | name | birth      | color | foods                | cats | 
+----+------+------------+-------+----------------------+------+ 
| 1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0    |

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

显示5行记录

mysql> SELECT * FROM profile LIMIT 5;

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

| id | name | birth      | color | foods                 | cats | 
+----+------+------------+-------+-----------------------+------+ 
| 1 | Fred | 1970-04-13 | black | lutefisk,fadge,pizza | 0    | 

| 2  | Mort | 1969-09-30 | white | burrito,curry,eggroll | 3 | 
| 3 | Brit | 1957-12-01 | red   | burrito,curry,pizza | 1    |

 | 4  | Carl | 1973-11-02 | red | eggroll,pizza         | 4 | 
| 5 | Sean | 1963-07-04 | blue  | burrito,curry | 5    |

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

显示按照ORDER BY排序后的第一条记录

mysql> SELECT * FROM profile ORDER BY birth LIMIT 1;

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

 | id | name | birth      | color | foods          | cats | 
+----+------+------------+-------+----------------+------+ 
| 9 | Dick | 1952-08-20 | green | lutefisk,fadge | 0    |

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

6. Oracle 和 MySQL该如何选择，为什么?

答：它们都有各自的优点和缺点。考虑到时间因素，我倾向于MySQL。

选择MySQL而不选orcale的原因

• MySQL开源
• MySQL轻便快捷
• MySQL对命令行和图形界面的支持都很好
• MySQL支持通过Query Browser进行管理

7. MySQL中如何得到当前日期?

答：在MySQL中获取当前日期就是如下的SELECT语句这么简单。

mysql> SELECT CURRENT_DATE();

 +----------------+ |

 CURRENT_DATE() | 

+----------------+ |

 2014-06-17     | 

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

8. MySQL中如何将表导出为XML文件?

答：我们可以使用’-e’(export)选项来把MySQL表或整个数据库导出到XML文件。当处理大型表的时候我们可能需要手动导出，不过对于小表的话可以直接使用想phpMyAdmin等这样的工具。

mysql -u USER_NAME -xml -e 'SELECT * FROM table_name' > table_name.xml

上面的例子中USER_NAME是数据库的用户名，table_name是待导出为xml文件的表名，table_name.xml是存放数据的xml文件

9. MySQL_pconnect是什么? 它和MySQL_connect有什么区别?

答：MySQL_pconnect()打开一个持久的数据库连接，这意味着数据库不是在每次页面加载的时候被打开一个新连接，因此我们不能使用MySQL_close()来关闭一个持久的连接。

MySQL_pconnect和MySQL_connect最简要的区别是：

与MySQL_pconnect不同，MySQL_connect在每次页面被加载的时候打开连接，这个连接可以使用MySQL_close()语句来关闭。

10. 当你需要查看一个名为’mysql’的数据库中’user’表中的所有索引时，你会如何做?

答：下面的命令将会显示出‘user’表中所有的索引

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
| user  |          0 | PRIMARY  |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               | 
| user  |          0 | PRIMARY  |            2 | User        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               | 
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
2 rows in set (0.00 sec)

11. 什么是CSV表?

答：CSV是逗号分隔值（Comma-Separated Values）或也被称为字符分隔值（Character-Separated Values）的缩写。CSV表以纯文本和表格形式来存储数据。

每一条记录都使用特定的分隔符隔开(如逗号,分号,…)，并且每条记录都有着顺序相同的列。CSV表最广泛地被用来存储用于导入和导出的电话联系人，并能够用来存储任何类型的纯文本数据。

以上就是这次要讲的全部内容。我还会带来其他你们应该会喜欢的有趣的文章。到那时敬请关注并访问Tecmint，不要忘了在下方的评论栏中留下你们的宝贵意见。

---

---

1、MySQL的复制原理以及流程

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

1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；

3. 从：sql执行线程——执行relay log中的语句；

2、MySQL中myisam与innodb的区别，至少5点

(1)、问5点不同；

1>.InnoDB支持事物，而MyISAM不支持事物

2>.InnoDB支持行级锁，而MyISAM支持表级锁

3>.InnoDB支持MVCC, 而MyISAM不支持

4>.InnoDB支持外键，而MyISAM不支持

5>.InnoDB不支持全文索引，而MyISAM支持。

(2)、innodb引擎的4大特性

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

(3)、2者selectcount(*)哪个更快，为什么

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

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

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

5、问了MySQL binlog的几种日志录入格式以及区别
(1)、binlog的日志格式的种类和分别
(2)、适用场景；
(3)、结合第一个问题，每一种日志格式在复制中的优劣。
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等修改数据的语句，还是会记录所有行的 变更。

6、问了下MySQL数据库cpu飙升到500%的话他怎么处理？
(1)、没有经验的，可以不问；
(2)、有经验的，问他们的处理思路。
列出所有进程  show processlist  观察所有进程  多秒没有状态变化的(干掉)
查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,,,,当然不排除网络状态突然断了,,导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送,,当然的一次被坑经历)

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

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

8、备份计划，mysqldump以及xtranbackup的实现原理
(1)、备份计划；
这里每个公司都不一样，您别说那种1小时1全备什么的就行
(2)、备份恢复时间；
这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考
20G的2分钟（mysqldump）
80G的30分钟(mysqldump)
111G的30分钟（mysqldump)
288G的3小时（xtra)
3T的4小时（xtra)
逻辑导入时间一般是备份时间的5倍以上

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

9、mysqldump中备份出来的sql，如果我想sql文件中，一行只有一个insert....value()的话，怎么办？如果备份需要带上master的复制点信息怎么办？
--skip-extended-insert
[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insert
Enter password:
  KEY `idx_c1` (`c1`),
  KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `helei`
--

LOCK TABLES `helei` WRITE;
/*!40000 ALTER TABLE `helei` DISABLE KEYS */;
INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');
INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');
INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');

10、500台db，在最快时间之内重启
puppet，dsh

11、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
并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更

• 第一个：读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。
• 第二个：我们“行骗”的时候，比如说我们竞标的时候压测，把query cache打开，还是能收到qps激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，访问量并不多，那么就不要打开，例如社交网站的，10%的人产生内容，其余的90%都在消费，打开还是效果很好的，但是你如果是qq消息，或者聊天，那就很要命。
• 第三个：小网站或者没有高并发的无所谓，高并发下，会看到 很多 qcache 锁 等待，所以一般高并发下，不建议打开query cache

12、你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？
监控的工具有很多，例如zabbix，lepus，我这里用的是lepus

13、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？
主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

14、你们数据库是否支持emoji表情，如果不支持，如何操作？
如果是utf8字符集的话，需要升级至utf8_mb4方可支持

15、你是如何维护数据库的数据字典的？
这个大家维护的方法都不同，我一般是直接在生产库进行注释，利用工具导出成excel方便流通。

16、你们是否有开发规范，如果有，如何执行的
有，开发规范网上有很多了，可以自己看看总结下

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

.

19、如何从mysqldump产生的全库备份中只恢复某一个库、某一张表？

答案见：http://suifu.blog.51cto.com/9167728/1830651

开放性问题：据说是腾讯的
一个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;

---

---

一、sql语句应该考虑哪些安全性？

（1）防止sql注入，对特殊字符进行转义，过滤或者使用预编译的sql语句绑定变量。 
（2）最小权限原则，特别是不要用root账户，为不同的类型的动作或者组建使用不同的账户。 
（3）当sql运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息。

---

二、简单描述MySQL中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响。

（1）索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。 
（2）普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。 
（3）普通索引允许被索引的数据列包含重复的值，如果能确定某个数据列只包含彼此各不相同的值，在为这个数据索引创建索引的时候就应该用关键字UNIQE把它定义为一个唯一所以，唯一索引可以保证数据记录的唯一性。 
（4）主键，一种特殊的唯一索引，在一张表中只能定义一个主键索引，逐渐用于唯一标识一条记录，是用关键字PRIMARY KEY来创建。 
（5）索引可以覆盖多个数据列，如像INDEX索引，这就是联合索引。 
（6）索引可以极大的提高数据的查询速度，但是会降低插入删除更新表的速度，因为在执行这些写操作时，还要操作索引文件。

---

三、一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ？

（1）如果表的类型是MyISAM，那么是18。 
因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。 
（2）如果表的类型是InnoDB，那么是15。 
InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。

---

四、请简述项目中优化sql语句执行效率的方法，从哪些方面。sql语句性能如何分析？

（1）尽量选择较小的列 
（2）将where中用的比较频繁的字段建立索引 
（3）select子句中避免使用‘*’ 
（4）避免在索引列上使用计算，not，in和<>等操作 
（5）当只需要一行数据的时候使用limit 1 
（6）保证表单数据不超过200w，适时分割表 
（7）针对查询较慢的语句，可以使用explain来分析该语句具体的执行情况

---

五、mysql_fetch_row()和mysql_fetch_array()的区别

这两个函数，返回的都是一个数组，区别就是第一个函数返回的数组是只包含值，我们只能row[0],row[1],这样以数组下标来读取数据，而mysql_fetch_array()返回的数组既包含第一种，也包含键值对的形式，我们可以这样读取数据，（假如数据库的字段是 username,passwd）:row[‘username‘],row[‘passwd‘。

---

---

乐视mysql面试题

mysql面试题：mysql如何实现插入时如果不存在则插入如果存在则更新的操作？

1.  
    

 
 
 

1.  
    
    
    
    
   

 

    

   

 

   

    

 

 

 

 

     

 

 

 

       

           

 

    

>

>

>

>

 

        

 

   

  

    

   

   

            

           

  

                      

 

 

 

 

  

 

   

><

 

>

>

<

---

---

  

 

---

 

---

---

---

 

---

---

 

---

---

---

---

---

 
 

---

---

---

---

---