所以sql语句
select name from student where name not in
(select name from student where fenshu <=80);
9.删除除了 id 号不同, 其他都相同的学生冗余信息
create table student2(
id int auto_increment primary key,
code varchar(20),
name varchar(20));
insert into student2 values(null,'2005001','张三'),(null,'2005002','李四'),(null,'2005001','张三');
如下语句，mysql报告错误，可能删除依赖后面统计语句，而删除又导致统计语句结果不一致。
delete from student2 where id not in(select min(id) from student2 group by name);
解决办法：将统计语句的表（group by的表）作为一个临时表， 临时表与主表比较，作为删除的条件数据
delete from student2 where id not in
(select mid from (select min(id) as mid from student2 group by code,name) t);
10.什么是存储过程
存储过程是一些编译好的SQL语句。
因为系统在调用SQL的时候比较浪费时间，所以之前先将一些基本的SQL语句代码进行编译（对单表或多表的增删改查），然后再给代码取一个名字，在需要这个功能时去调用它就可以了。
优缺点
效率高：因为
存储过程是编译后的代码
降低网络通信：
因为存储过程代替了SQL语句，传输的命令比传递sql语句的数据量要小
安全性：
在一定的程度确保数据安全
12.
索引是什么
索引是对数据库中一或多个列值的排序，帮助数据库高效获取数据库的数据结构。
假如我们用类比的方法，数据库中的索引就相当于书籍中的目录一样，当我们想找到书中的摸个知识点，我们可以直接去目录中找而不是在书中每页的找，但是这也抛出了索引的一个缺点，在对数据库修改的时候要修改索引到导致时间变多。
几个基本的索引类型： 普通索引、唯一索引、主键索引、全文索引
索引优点
加快检索速度
唯一索引确保每行数据的唯一性
在使用索引的过程可以优化隐藏器，提高系统性能
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。
12.2什么是优化隐藏？
优化隐藏就是指在执行查询语句、使用多表连接检索或者指定查询语句操作的对象表时，明确地指出应该使用的查询方法、连接算法或者对表的操作方式。
当使用优化隐藏时，一定要认真考虑优化隐藏对性能的影响。在SQL Server 7.0中，提供了三种类型的优化隐藏，即查询优化隐藏、连接优化隐藏和表优化隐藏。
查询优化隐藏控制整个语句的执行规划。可以通过在SQL语句的最后增加一个OPTION子句，来实现查询优化隐藏。另外，还可以同时指定多个查询优化隐藏，这些选项之间由逗号分割开来。使用Hash Group或者Order Group选项，可以为分组子句或者分组计算子句产生合计数值，但是这两个选项不能同时使用。在联合Union运算符中，可以使用合并、散列和连接选项，这时既可以使用一个选项，也可以同时使用多个选项。当同时使用多个选项时，系统将在这几个选项中选用成本最低的方法进行操作。
连接优化隐藏指定某一个连接运算应该如何最优地实现。连接就是把两个或者多个表中的记录连接起来，通常是连接两个表中的记录。在连接中，使用两个表中的某些列组成连接的条件表达式。为了使用连接优化隐藏，可以在连接运算符之前使用四个关键字中的其中一个关键字，这四个关键字分别是Loop、Hash、Merge和Remote。
表优化隐藏指定在查询语句中使用的单个基表的访问特征。通过在表名后面增加一个With子句，可以指定表的优化隐藏。在检索、插入、修改和删除等查询语句中都可以使用表优化隐藏。对于多个优化隐藏，可以使用逗号或者空格分割开。
13.数据库中的乐观锁和悲观锁
乐观锁、悲观锁，主要是作用在并发访问控制的时侯
悲观锁
在并发时，假定会发生并发冲突。屏蔽任何违反数据完整性的操作。
乐观锁
在并发时，假定不会发生并发冲突。只有在提交操作时，检查是否违返数据的完整性。
悲观锁:
一段执行逻辑加上悲观锁后，不同线程同时执行时，只能有一个线程执行，其他的线程在入口处等待，直到锁被释放。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
乐观锁:
一段执行逻辑加上乐观锁后，不同线程同时执行时，所有线程可以同时进入执行。在最后更新数据的时候，才检查这些数据是否被其他线程修改了(版本和执行初是否相同)；没有修改则进行更新，否则放弃本次操作。
从解释上可以看出，悲观锁具有
很强的独占性，
也是最安全的。
而乐观锁
很开放、效率高，
安全性比悲观锁低。因为在乐观锁检查数据版本一致性时也可能被其他线程修改数据。
进一步解释，todo
14.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 不记录在日志中，所以它不能激活触发器。
15.MySQL的复制原理以及流程
基本原理流程，3个线程以及之间的关联；
1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
3. 从：sql执行线程——执行relay log中的语句；
详解：mysql主从复制
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。
下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：
在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。
Mysql主从复制的实现原理图大致如下(来源网络)：
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
(1)、复制基本原理流程
1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
3. 从：sql执行线程——执行relay log中的语句；
(2)、MySQL复制的线程有几个及之间的关联
MySQL 的复制是基于如下 3 个线程的交互（ 多线程复制里面应该是 4 类线程）：
1. Master 上面的 binlog dump 线程，该线程负责将 master 的 binlog event 传到slave；
2. Slave 上面的 IO 线程，该线程负责接收 Master 传过来的 binlog，并写入 relay log；
3. Slave 上面的 SQL 线程，该线程负责读取 relay log 并执行；
4. 如果是多线程复制，无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制， SQL 线程只做 coordinator，只负责把 relay log 中的 binlog读出来然后交给 worker 线程， woker 线程负责具体 binlog event 的执行；
(3)、MySQL如何保证复制过程中数据一致性及减少数据同步延时
一致性主要有以下几个方面：
1.在 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 线程执行到的位置和用户事务恢复到一致性的状态。
2. MySQL 5.6 引入 GTID 复制，每个 GTID 对应的事务在每个实例上面最多执行一次， 这极大地提高了复制的数据一致性；
3. MySQL 5.5 引入半同步复制， 用户安装半同步复制插件并且开启参数后，设置超时时间，可保证在超时时间内如果 binlog 不传到 slave 上面，那么用户提交事务时不会返回，直到超时后切成异步复制，但是如果切成异步之前用户线程提交时在 master 上面等待的时候，事务已经提交，该事务对 master
上面的其他 session 是可见的，如果这时 master 宕机，那么到 slave 上面该事务又不可见了，该问题直到 5.7 才解决；
4. MySQL 5.7 引入无损半同步复制，引入参 rpl_semi_sync_master_wait_point，该参数默认为 after_sync，指的是在切成半同步之前，事务不提交，而是接收到 slave 的 ACK 确认之后才提交该事务，从此，复制真正可以做到无损的了。
5.可以再说一下 5.7 的无损复制情况下， master 意外宕机，重启后发现有 binlog没传到 slave 上面，这部分 binlog 怎么办？？？分 2 种情况讨论， 1 宕机时已经切成异步了， 2 是宕机时还没切成异步？？？ 这个怎么判断宕机时有没有切成异步呢？？？ 分别怎么处理？？？
5.5 是单线程复制， 5.6 是多库复制（对于单库或者单表的并发操作是没用的）， 5.7 是真正意义的多线程复制，它的原理是基于 group commit， 只要
master 上面的事务是 group commit 的，那 slave 上面也可以通过多个 worker线程去并发执行。 和 MairaDB10.0.0.5 引入多线程复制的原理基本一样。
(4)、工作遇到的复制 bug 的解决方法
16.MySQL中myisam与innodb的区别，至少5点
(1)、问5点不同；
1>.InnoDB支持事物，而MyISAM不支持事物
2>.InnoDB支持行级锁，而MyISAM支持表级锁
3>.InnoDB支持MVCC, 而MyISAM不支持
4>.InnoDB支持外键，而MyISAM不支持
5>.InnoDB不支持全文索引，而MyISAM支持。
6>.InnoDB的存储容量上限是64T，MyISAM没有上限
7>.InnoDB支持哈希索引，MyISAM不支持。
(2)、innodb引擎的4大特性
插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
(3)、2者select count(*)哪个更快，为什么
myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
17.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)存储和计算均一样；
18.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 前滚到该事务完成时并提交掉。
19.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等修改数据的语句，还是会记录所有行的 变更。
20.下MySQL数据库cpu飙升到500%的话他怎么处理？
当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。
如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。
找出消耗高的 sql， 看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，
有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等
观察所有进程 多秒没有状态变化的(干掉)
查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,,,,当然不排除网络状态突然断了,,导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送,,当然的一次被坑经历)
21.1 sql优化
(1)、explain出来的各种item的意义
id:每个被独立执行的操作的标志，表示对象被操作的顺序。一般来说， id 值大，先被执行；如果 id 值相同，则顺序从上到下。
select_type :表示查询中每个select子句的类型
table:名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。
type：表示MySQL在表中找到所需行的方式，又称“访问类型”。join 类型。
possible_keys ：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
key：实际用到的索引，若没有使用索引，显示为NULL
key_len：用到的索引键的平均长度，单位为字节。
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 。表示本行被操作的对象的参照对象，可能是一个常量用 const 表示，也可能是其他表的
Extra：重要的补充信息。
partitions:匹配的分区信息。
rows:估计每次需要扫描的行数。
filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。
explain 中的索引问题 ：
Explain 结果中，一般来说，要看到尽量用 index(type 为 const、 ref 等， key 列有值)，避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列，需要建立索引。
被驱动表的连接列，也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时，也可以考虑建立索引来达到直接排序和汇总的需求
(2)、profile的意义以及使用场景；
Profile 用来分析 sql 性能的消耗分布情况。
当用 explain 无法解决慢 SQL 的时候，需要用profile 来对 sql 进行更细致的分析，找出 sql 所花的时间大部分消耗在哪个部分，确认 sql的性能瓶颈。
查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等
查看profile是否开启：
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF | --开启SQL语句剖析功能
| profiling_history_size | 15 | --设置保留profiling的数目，缺省为15，范围为0至100，为0时将禁用profiling
+------------------------+-------+
基于会话级别开启profile：
mysql> set profiling = 1; --关闭则用set profiling = off
然后，先执行sql语句，再show：
mysql> select distinct d.account,a.server_id from tab_appserver_user a
-> inner join tab_department_parent b on a.key_id = b.parent_id
-> inner join tab_department_member c on b.department_id = c.department_id and c.state=1
-> and c.isdefault=1 inner join tab_user_info d on c.user_id = d.user_id and d.state=1
-> where a.type=1
-> union
-> select distinct b.account,a.server_id from tab_appserver_user a
-> inner join tab_user_info b on a.key_id = b.user_id and b.state=1
-> where a.type=0;
--显示缓存的profile
mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration | Query
1 | 0.86754250 | select distinct d.account,a.server_id from tab_appserver_user a
+----------+------------+-------------------------------------------------------+
4 rows in set (0.00 sec)
从上面可以看到时间的消耗为0.8秒
以下是具体的消耗，进行详细的列出
mysql> show profile for query 1; --1是query_id
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000018 |
| checking query cache for query | 0.000099 |
| Opening tables | 0.000963 |
| System lock | 0.000015 |
| Table lock | 0.000169 |
| optimizing | 0.000020 |
| statistics | 0.000027 |
| preparing | 0.000018 |
| Creating tmp table | 0.000055 |
| executing | 0.000003 |
| Copying to tmp table | 0.704845 | --这里耗时最多，可见它是最主要的消耗点
...........
查看cpu的消耗情况
mysql> show profile cpu for query 1;
--查看内存消耗
mysql> show profile memory for query 1;
--查看io及cpu的消耗
mysql> show profile block io,cpu for query 1;
使用查询语句对消耗进行排序：
select * from emp;
SELECT
state,
sum( duration ) AS total_r,
round( 100 * sum( duration ) / ( SELECT sum( duration ) FROM information_schema.profiling WHERE query_id = 1 ), 2 ) AS pct_r,
count( * ) AS calls,
sum( duration ) / count( * ) AS "r/call"
information_schema.profiling
WHERE
query_id = 1
GROUP BY state
ORDER BY total_r DESC;
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL
，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
2、使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT customerid FROM salesinfo)
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。
但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。
如果使用连接（JOIN）..来完成上面的查询工作，速度将会快很多。
尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.customerid =salesinfo.customerid
WHERE salesinfo.customerid IS NULL;
连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
3、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并在的一个查询中。
在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。
使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的
字段数目
要想同。下面的例子就演示了一个使用UNION的查询。
SELECT name,phone FROM client UNION
SELECT name,birthdate FROM author UNION
SELECT name,supplier FROM product
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。
更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。
设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。
要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN;
INSERT INTO salesinfo SET customerid=14;
UPDATE inventory SET quantity =11 WHERE item='book';
COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。
由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。
如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
lock table emp;
select * from emp where empno=1;
.............
update emp set ename='vita' where empno=1;
unlock tables;
这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCK TABLE语句可以保证在UNLOCK TABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有合法customerid的记录都不会被更新或插入到salesinfo中。
CREATE TABLE customerinfo( customerid int primary key) engine = innodb;
CREATE TABLE salesinfo( salesid int not null,customerid int not null, primary key(customerid,salesid),foreign key(customerid) references customerinfo(customerid) on delete cascade)engine = innodb;
注意例子中的参数“on delete cascade”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=INNODB。如例中所示。
7、使用索引
索引
是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
那该对哪些字段建立索引呢？
一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDER BY排序的字段上。
尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况
例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。
下面是应该注意的几个方面。
a、
首先，最好是在相同类型的字段间进行比较的操作
在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。
b、
其次，在建有索引的字段上尽量不要使用函数进行操作
例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。
ｃ、
第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用
链接：https://www.jianshu.com/p/dac715a88b44
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 只会在检索出所有记录之后才对结果集进行过滤
22.备份计划，mysqldump以及xtranbackup的实现原理
(1)、备份计划；
视库的大小来定，一般来说 100G 内的库，可以考虑使用 mysqldump 来做，因为 mysqldump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(mysqldump 备份
出来的文件比较小，压缩之后更小)。100G 以上的库，可以考虑用 xtranbackup 来做，备份速度明显要比 mysqldump 要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期
(2)、备份恢复时间；
物理备份恢复快，逻辑备份恢复慢
这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考
20G的2分钟（mysqldump）
80G的30分钟(mysqldump)
111G的30分钟（mysqldump)
288G的3小时（xtra)
3T的4小时（xtra)
逻辑导入时间一般是备份时间的5倍以上
(3)、备份恢复失败如何处理
首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。
(3)、xtranbackup实现原理
在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。
(4)、mysqldump和xtrabackup实现原理
mysqldump
mysqldump 属于逻辑备份。加入–single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，
之后显式开启一个事务(START TRANSACTION /
!40100 WITH CONSISTENTSNAPSHOT
/)，这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。 如果加上–master-data=1 的话，在刚开始的时候还会加一个数据库的读锁
(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster 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 等其他引擎的表给备份出来，备份完后解锁。 这样就做到了完美的热备。
23.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');
24.500台db，在最快时间之内重启
puppet，dsh，ansible
25.innodb的读写参数优化
(1)、读取参数
global buffer pool以及 local buffer
innodb_buffer_pool_size：此
参数用来设置Innodb 最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间，对Innodb 整体性能影响也最大。
对于一台单独给MySQL 使用的主机，并假设只使用innodb引擎，一般建议该参数为物理内存的75%左右。
当系统上线之后，我们可以通过Innodb 存储引擎提供给我们的关于Buffer Pool 的实时状态信息作出进一步分析，来确定系统中Innodb 的Buffer Pool 使用情况是否正常高效：
mysql> show status like 'Innodb_buffer_pool_%';
+-----------------------------------------+---------------+
| Variable_name | Value |
+-----------------------------------------+---------------+
| Innodb_buffer_pool_pages_data | 999020 |
| Innodb_buffer_pool_pages_dirty | 47643 |
| Innodb_buffer_pool_pages_flushed | 474668167 |
| Innodb_buffer_pool_pages_LRU_flushed | 365125 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_made_not_young | 0 |
| Innodb_buffer_pool_pages_made_young | 203410903 |
| Innodb_buffer_pool_pages_misc | 49552 |
| Innodb_buffer_pool_pages_old | 368697 |
| Innodb_buffer_pool_pages_total | 1048572 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 66348855 |
| Innodb_buffer_pool_read_ahead_evicted | 3716819 |
| Innodb_buffer_pool_read_requests | 3215992991498 |
| Innodb_buffer_pool_reads | 65634998 |
| Innodb_buffer_pool_wait_free | 651 |
| Innodb_buffer_pool_write_requests | 21900970785 |
+-----------------------------------------+---------------+
从上面的值我们可以看出总共1048572个 pages，其中放数据的有999020个 pages，且已没有free状态的page。
read 请求3215992991498次，其中有65634998次所请求的数据在buffer pool 中没有，也就是说有65634998 次是通过读取物理磁盘来读取数据的，所以很容易也就得出了Innodb Buffer Pool 的Read 命中率大概在为：(3215992991498- 65634998)/ 3215992991498* 100% = 99.998%。
innodb_buffer_pool_instances
：该参数将innodb_buffer_pool划分为不同的instance，每个instance独立的LRU、FLUSH、FREE、独立的mutex控制。
对于比较大的innodb_buffer_pool_size，建议设置多个instances，避免内存锁的争用。
Innodb_read_io_thread
：根据处理器内核数决定
Read_buffer_size：
Sort_buffer_size：
(2)、写入参数；
Insert_buffer_size：
Innodb_double_write：
Innodb_write_io_thread：
innodb_flush_method：
(3)、与IO相关的参数；
innodb_log_file_size
：设置innodb redo log file的大小，从性能角度来看，日志文件越大越好，可以减少buffer pool checkpoint的频率，但是在MySQL的官方版本中，iinnodb_log_file_size*innodb_log_files_in_group不能超过4G。
日志文件越大，也意味着MySQL实例crash之后恢复的时间越长，不过一般生成系统都会配置主从库，因此这个因素可以忽略不考虑。
一般来说，在我个人维护的环境中，比较偏向于将事务日志设置为3 组，每个日志设置为256MB 大小，整体效果还算不错。
innodb_log_buffer_size：
这个参数就是用来设置Innodb 的Log Buffer 大小的，系统默认值为1MB。Log Buffer的主要作用就是缓冲Log 数据，提高写Log 的IO 性能。一般来说，如果你的系统不是“写负载非常高且以大事务居多”的话，8MB 以内的大小就完全足够了。
我们也可以通过系统状态参数提供的性能统计数据来分析Log 的使用情况：
mysql> show status like 'innodb_log%';
+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 3486920147 |
| Innodb_log_writes | 352577360 |
+---------------------------+------------+
如果Innodb_log_waits不等于0的话，表示出现过Log Buffer的写等待，表示innodb_log_buffer_size有可能过小。
innodb_flush_log_at_trx_commit：
innodb_flush_log_at_trx_commit = 0，Innodb 中的Log Thread 每隔1 秒钟会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush 操作，保证数据确实已经写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发Log Thread 将log buffer 中的数据写入文件。所以，当设置为0 的时候，当MySQL Crash 和OS Crash 或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更。
innodb_flush_log_at_trx_commit = 1，这也是Innodb 的默认设置。我们每次事务的结束都会触发Log Thread 将log buffer 中的数据写入文件并通知文件系统同步文件。这个设置是最安全的设置，能够保证不论是MySQL Crash 还是OS Crash 或者是主机断电都不会丢失任何已经提交的数据。
innodb_flush_log_at_trx_commit = 2，当我们设置为2 的时候，Log Thread 会在我们每次事务结束的时候将数据写入事务日志，但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的，所以Log Thread 的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread 就完全不知道了。所以，当设置为2 的时候，MySQL Crash 并不会造成数据的丢失，但是OS Crash 或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。
从上面的分析我们可以看出，当innodb_flush_log_at_trx_commit 设置为1 的时候是最安全的，但是由于所做的IO 同步操作也最多，所以性能也是三种设置中最差的一种。如果设置为0，则每秒有一次同步，性能相对高一些。如果设置为2，可能性能是三这种最好的。但是也可能是出现Crash后丢失数据最多的。到底该如何设置设置，就要根据具体的场景来分析了。一般来说，如果完全不能接受数据的丢失，那么我们肯定会通过牺牲一定的性能来换取数据的安全性，选择设置为1。而如果我们可以丢失很少量的数据(比如说1 秒之内)，那么我们可以设置为0。当然，如果大家觉得我们的OS 足够稳定，主机硬件设备，而且主机的供电系统也足够安全，我们也可以将innodb_flush_log_at_trx_commit 设置为2 让系统的整体性能尽可能的高。
innodb_file_io_threads：
innodb_max_dirty_pages_pct：
表示innodb从buffer中刷新脏页的比例不超过这个值，每次checkpoint的脏页刷新为：innodb_max_dirty_pages_pct*innodb_io_capacity
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
26.你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？
监控的工具有很多，例如zabbix，lepus，我这里用的是lepus
27.你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？
主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等
28.你们数据库是否支持emoji表情，如果不支持，如何操作？
如果是utf8字符集的话，需要升级至utf8_mb4方可支持
29.你是如何维护数据库的数据字典的？
这个大家维护的方法都不同，我一般是直接在生产库进行注释，利用工具导出成excel方便流通。
30.你们是否有开发规范，如果有，如何执行的
以上 参考：
https://blog.csdn.net/qq_33223299/article/details/77096587?utm_source=copy
https://blog.csdn.net/u013252072/article/details/52912385
http://blog.itpub.net/29371470/viewspace-1355948/
https://blog.csdn.net/dbanote/article/details/38051255
