同乐学堂1、为什么InnoDB表最好要有自增列做主键？
这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好！
2、顺序的主键什么时候会造成更坏的结果？
对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间歇性竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑下重新设计表或者应用，或者更改innodb_autoinc_lock-mode配置。如果你的服务器版本还不支持innodb_autoinc-lock_mode参数，可以升级到新版本的InnoDB，可能对这种场景工作的更好。
3、为什么需要设置双1才能保证主从数据的一致性？
双1：innodb_flush_log_at_trx_commit=1 and sync_binlog=1
sync_binlog=n，当每次提交N次事务提交之后，MySQL将进行一次fsny之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。 在MySQL中sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候性能是最好的，但是风险也是最大的。因为一旦系统crash，在binlog_cache中的所有binlog信息都会丢失。
innodb_flush_log_at_trx_commit=1 是每一次事务提交或事务的指令都需要把日志写入（flush）硬盘，这是很费时的，在使用电池供电缓存（Battery backed up cache）时。
innodb_flush_log_at_trx_commit=2 是不写入硬盘而是写入系统缓存，日志仍然会每秒flush到硬盘，所以一般不会丢失超过1-2秒的更新，系统挂了时才可能丢数据
innodb_flush_log_at_trx_commit=0 会更快一些，安全性比较差，即使mysql挂了可能会丢失事务的数据
4、有几种binlog格式，区别是什么 ？
Row，Statement，Mixed=Row+Statement
1. Row
日志中会记录成每一行数据被修改的形式，然后在 slave 端再对相同的数据进行修改。
优点：在 row 模式下，bin-log 中可以不记录执行的 SQL 语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以 row 的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或 function ，以及 trigger 的调用和触发无法被正确复制的问题。
缺点：在 row 模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
2. Statement
每一条会修改数据的 SQL 都会记录到 master 的 bin-log 中。slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。
优点：在 statement 模式下，首先就是解决了 row 模式的缺点，不需要记录每一行数据的变化，减少了 bin-log 日志量，节省 I/O 以及存储资源，提高性能。因为他只需要记录在 master 上所执行的语句的细节，以及执行语句时候的上下文的信息。
缺点：在 statement 模式下，由于他是记录的执行语句，所以，为了让这些语句在 slave 端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在 slave 端杯执行的时候能够得到和在 master 端执行时候相同的结果。另外就是，由于 MySQL 现在发展比较快，很多的新功能不断的加入，使 MySQL 的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug 也就越容易出现。在 statement 中，目前已经发现的就有不少情况会造成 MySQL 的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep() 函数在有些版本中就不能被正确复制，在存储过程中使用了 last_insert_id() 函数，可能会使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行来记录的变化，所以不会出现类似的问题。
3. Mixed
从官方文档中看到，之前的 MySQL 一直都只有基于 statement 的复制模式，直到 5.1.5 版本的 MySQL 才开始支持 row 复制。从 5.0 开始，MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现，给 MySQL Replication 又带来了更大的新挑战。另外，看到官方文档说，从 5.1.8 版本开始，MySQL 提供了除 Statement 和 Row 之外的第三种复制模式：Mixed，实际上就是前两种模式的结合。在 Mixed 模式下，MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。新版本中的 statment 还是和以前一样，仅仅记录执行的语句。而新版本的 MySQL 中对 row 模式也被做了优化，并不是所有的修改都会以 row 模式来记录，比如遇到表结构变更的时候就会以 statement 模式来记录，如果 SQL 语句确实就是 update 或者 delete 等修改数据的语句，那么还是会记录所有行的变更。
注意：
条件1：当binlog format设置为mixed时，普通复制不会有问题，但是级联复制在特殊情况下会binlog丢失.
条件2：当出现大量数据（400W左右）扫描的更新，删除，插入的时候，且有不确定dml语句（如：delete from table where data<’N’ limit ）的时候.
当条件1 和 条件2 同时满足时，会导致主从复制数据丢失问题的发生.只能设置binlog_format=Row
5、MySQL的复制原理以及流程？
原理就是从库copy主库的二进制日志到自己的中继日志里，然后进行解析主库的更改事件进行快速重放来跟主库进行实时同步。
流程：
1、主库上把数据更改记录到二进制日志中。
2、备库将主库上的日志复制到自己的中继日志中。
3、备库读取中继日志中的事件，将其重放到备库数据 之上。
6、MySQL中myisam与innodb的区别，至少5点
1、表锁差异（ InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表） 2、myisam 不支持事务，innodb 支持 (不支持事务的最显著缺点，崩溃后无法安全恢复) 3、InnoDB 中不保存表的具体行数 。 因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表 4、 InnoDB支持外键，MyISAM不支持 5、MyISAM支持GIS数据，InnoDB不支持 6、MyISAM类型的二进制数据文件可以在不同操作系统中迁移、而InnoDB的却不行。 7、MyISAM（堆组织表）使用的是非聚簇索引 InnoDB（索引组织表）使用的聚簇索引、 8、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
7、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义以及 int（20）代表的含义。
varchar与char的区别 ：
一个可变长度，一个定长，检索的值并不总是相同，因为检索时从CHAR列删除了尾部的空格。
CHAR有固定长度，所以在处理速度上要比VARCHAR快很多，但是浪费空间。
varchar 总会在总长度加上一个字节，来存储字符长度，跟MISAM cout原理相似，所以MISAM,建议用char。
Varchar存储可变长字符串，小于255字节时需要1个额外字节(大于255需要2个额外字节)存储长度，最大长度为65532字节(所有列总和)；
实验：
|
1 2 3 4 5 |
|
结果：
|
1 2 |
|
varchar(50)中的50 代表， 最大存储50个字符 。超过则数据库不会存储
int(M) M表示的不是数据的最大长度，只是数据宽度，并不影响存储多少位长度的数据。
主要用来标识业务中数据长度。实际就是一个显示作用！
8、 innodb的事务与日志的实现方式，以及它有多少种日志？
事务实现：在Innodb存储引擎中，事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的。
日志种类： 错误,查询,超时查询,二进制,中继,事务
http://blog.csdn.net/mchdba/article/details/8664943
9、 MySQL数据库cpu飙升到500%的话该怎么处理？
分析原因：那一定是开发写的烂SQL导致的，或者某些意外情况，黑客攻击等！
紧急处理方法：
|
|
利用pt工具，直接结束所有大5秒 的select查询，把这些慢查询杀死并记录到文件里，后期跟慢慢跟开发算账，哈哈~。这里的秒数可按情况来定！
如果不紧急：
1、top命令确认是否mysqld进程占据了所有资源。（就是windows的任务管理器）
2、查看error日志，无任何异常。
3、SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 查看是否有死锁
4、show full processlist; 查看没有耗时非常大的慢sql再跑
5、安装 sysstat包，通过监控脚本和命令，定位cup狂飙的具体程序，进程，以及IO.
等在杀掉其他占用过高进程。只要不影响mysql相关进程的程序稳定在执行即可。
6、如果没有开启慢查询日志，可以set命令开启慢查询日志，等待一段时间观察比较恶心的sql将其灭杀！
10、 sql语句怎么进行优化?
定制sql开发规范、避免全表扫描、避免大事务操作！以及用更高效的关系词，来替换现有的关系词
或者大sql拆分成小sql来执行！
以上是个人回答，存在不准确，大家可以根据百度自行参考！
11、 备份计划，mysqldump以及xtranbackup的实现原理?
mysqldump 原理：
1、 连接服务器（ connect）
2、加锁
FLUSH 、FLUSH TABLES WITH READ LOCK （是为了避免较长的事务操作造成FLUSH TABLES WITH READ LOCK操作迟迟得不到锁，但同时又阻塞了其它客户端操作）
3、 设置当前会话的事务隔离等级为RR，RR可避免不可重复读和幻读、。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
4、开启事务。
4、 获取当前数据库的快照
5、 记录了开始备份时，binlog的状态信息
6、 释放锁。
7、 得到库以及表的创建语句、
8、commit。
xtranbackup 实现原理
官方解答：
首先，在logfile中找到并记录最后一个checkpoint（“last checkpoint LSN”），然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile；接着，开始拷贝全部的数据文件.ibd；在拷贝全部数据文件结束之后，才停止拷贝logfile。
因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致
备份流程：
1 首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql redo的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中
2 复制innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方
3 复制结束后，执行flush table with read lock操作
4 复制.frm .myd .myi文件
5 并且在这一时刻获得binary log 的位置
6 将表进行解锁unlock tables
7 停止xtrabackup_log进程
备份计划，一周全备，剩下是增量，差异备份的实施。
12、 500台db，在最快时间之内重启?
编写shell脚本,或者利用ansible工具等，实现快速批量重启。
13、 innodb的读写参数怎么进行优化?
1、 提高 Innodb_buffer_pool_size的大小为mysql服务器的内存大小的百分之75.
2、根据CPU核数等线程能力，提高读写线程数，5.5默认是4个。
innodb_read_io_threads | 4 |
| innodb_write_io_threads | 4 |
+-------------------------+-------
3、 max_connections == 服务器根据自己的实际情况进行增加，这个越大会消耗越多内存，适当调整。
4、 通过SHOW GLOBAL STATUS命令，如果你发现每秒钟有许多的Sort_merge_passes输出（Sort_merge_passes表示不得不做合并排序算法的数量），我们就需要考虑增加sort_buffer_size的值
5、 写入参数
Insert_buffer_size；
Innodb_double_write；
Innodb_write_io_thread
innodb_flush_method
6、 与IO相关的参数
Innodb_log_buffer_size
innodb_flush_log_at_trx_commit
innodb_file_io_threads
innodb_max_dirty_pages_pct
7、缓存参数以及缓存的适用场景。
https://www.lvtao.net/database/mysql_innodb.html
14、 如何监控数据库？慢日志是怎么查询和定位的？
1、首先要开启慢日志。（会带来一部分性能消耗，慎重开启）
2、可以使用MySQL自带的mysqldumpslow工具进行分析。
3、也可以将慢查询日志保存到表中，通过select来统计，查询。
4、或者利用pt工具的query功能。
http://blog.csdn.net/wulantian/article/details/40296541#comments
http://www.cnblogs.com/kerrycode/p/5593204.html
15、 如何做主从一致性校验？
可以利用： 利用pt-table-checksum做主从一致性校验 。
16、你们数据库是否支持emoji表情，如果不支持，如何操作？
-
mysql的版本必须为v5.5.3或更高
-
把数据库的编码改成
utf8mb4 -- UTF-8 Unicode -
然后需要存储emoji表情的字段选择
utf8mb4_general_ci -
数据库连接也需要改为
utf8mb4
为了应对无线互联网的机遇和挑战、避免 emoji 表情符号带来的问题、涉及无线相关的 MySQL 数据库建议都提前采用utf8mb4 字符集，这必须要作为移动互联网行业的一个技术选型的要点。
17、 如何维护数据库的数据字典的?
这个还真没想过，是一个mysql的新特性，来记录mysql中所有原信息。肯定要保护好它了。设置好它的权限，和安全级别。并核对内置的信息是否准确。
这个大家维护的方法都不同，我一般是直接在生产库进行注释，利用工具导出成excel方便流通
http://www.cnblogs.com/suifu/p/5865357.html
18、你是如何设计数据库开发规范的？
根据前人的总结，以及自己总结的可能会影响数据库性能，安全，以及管理层次，以及团队良好的操作协同性，从命名，到空间合理规划，以及sql用法规避慢查询，规避安全等不利因素去设计数据库开发规范。
19、表中的以读为主的大字段（例如：text类型）并不经常更新，您是选择拆成子表还是继续放在一起？说明理由！
拆成子表，提高查询效率。现在有好多性能不错的中间件，分表是直接提升查询性能的手段。
一些弊端可以忽略不计。
20、 一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
这道题其实体现到最快：
数据响应最快：
方案1：
- where tid=50000
- where tid=50001
- where tid=50002
- where tid=50003
.......
方案2：
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;
小结：尽量不使用join，等，操作太大的表，尽量利用索引，避免扫描表操作出现。
怎么才能不出想大范围扫描表，就要提高自己的基础知识，以及风骚的sql写法和定制比较好的开发规范。
21、如何从mysqldump产生的全库备份中只恢复某一个库、某一张表？
恢复一个report库： mysql -uroot -p123456 report --one-database <fulldump.sql
恢复一张t_order表：
1）从备份查询恢复表的表结构
[root@mha backup]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t_order`/!d;q' fulldump.sql
2）从备份查询出表的表数据
[root@mha2 backup]# grep 'INSERT INTO `t_order`' fulldump.sql >data.sql
微信扫一扫，打赏作者吧～
