必知必会：MySQL

• 复制表结构和其数据：

create table table_name_new as select * from table_name_old

• 表结构相同，复制表数据：

insert into table_name_new select * from table_name_old

• 表结构不同，复制表数据：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

• 1.三大范式

（1）每个字段的值都是不可再拆分的最小数据单元。
（2）数据库中的每条记录都是可唯一标识的，非主键都完全依赖于主键，不能只依赖主键的一部分。
（3）表中的每个非主键字段都要与主键直接相关。

• 2.union和union all区别

union：会对结果集进行去重。
union all：不会去重。
那肯定union all执行效率更高。

• 3.drop和truncate、delete区别

drop：删除表数据行、删除表结构、释放磁盘空间，不可回滚。

truncate：删除表数据行，但不删除表结构，释放磁盘空间，不可回滚。
delete：删除数据行，不释放磁盘空间；delete可带where条件删除，其他两个不行。

• 4.count(1)、count(*)、count(列名) 区别

count(1)是用1代表代码行，统计的时候，不会忽略列值为NULL的。

count(*)包括了所有的列，相当于行数，会自动优化指定到哪一个字段，且不会忽略列值为null的。
count(列名) 只包括列名那一列，在统计时会忽略列值为null的。 执行效率上，如果表有主键，count(主键）最优；其他情况下count(1)、count(*)比count(列名)快。

• 5.SQL语句的执行顺序：

from -> on -> join -> where ->group by -> having -> select -> distinct -> order by -> limit

• 6.基础架构

分三层：客户端、Server层、存储引擎层。
Server层，包括查询解析、分析、优化、缓存、内置函数、存储过程、触发器、视图等。
存储引擎层：负责数据的存储和提取，MySQL 5.5之后默认InnoDB。

• 7.存储引擎的选择

（1）InnoDB：如果要提供提交、回滚和恢复的事务安全能力。并要求实现并发控制，选用InnoDB。
（2）MyISAM：如果主要是用来插入和查询记录，则选用MyISAM。
（3）MEMORY：如果只是临时存放数据，数据量不大且不需要较高的数据安全性。 MYSQL使用该内存的MEMORY引擎作为临时表，存放查询的中间结果。

InnoDB存储引擎特有的日志有redo log 和undo log。
redo log：重做日志，记录的是InnoDB存储引擎的事务日志。
undo log：回滚日志，如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚至之前修改的样子。

InnoDB和MyISAM的区别：
MyISAM只支持表锁，不提供事务支持，索引是非聚簇索引，数据结构是B树；
InnoDB 则支持行锁，且提供事务支持，索引是聚簇索引，数据结构是B+树。

• 8.一条更新语句怎么执行了解吗？

更新语句的执行，除了要写入表中，还要记录相应的redolog和binlog日志。
（1）先找引擎获取要更新的数据行。如果更新的这一行记录所在的数据页本来就在内存中了，则直接返回给执行器；否则从磁盘读取到内存再返回。
（2）执行器拿到引擎给的行数据，进行修改得到新的数据行，在调用引擎接口写入这行新数据。
（3）引擎层将结果更新到内存中，同时写入redo log，并将这行记录状态修改为prepare，并告知执行器。
（4）执行器将这个操作写入binlog，然后调用引擎的提交事务接口，引擎把刚写入的redo log更新为commit状态，更新完成。

• 9..事务的四大特性：

原子性、一致性、隔离性、持久性

隔离性：通过事务的锁机制来实现。
原子性和持久性：由redo log来保证。
一致性：由undo log来保证。记录的事务的cud操作，回滚时做相反的cud来恢复数据。

• 10.怎么看执行计划

explain查看执行计划。
主要关注ID、select_type（查询类型）、table（当前explain的行正在访问的表）、 type（说明的是如何查找表中的行）、keys（实际采用的索引，可用来判断索引是否失效）、 possible_key（可能使用的索引，分析索引优化可参考）、rows（估算出的读取的数据行数，原则上越少越好）。

• 11.为什么使用索引会加快查询速度？

如果不用索引，按照标的顺序遍历，不管查哪些数据，都需要从头到尾遍历一遍。
如果加了索引，就是按B+TREE生成一个索引文件，在查找时，根据索引文件查找，与根节点比较，然后一层层比较， 定位到叶子节点的索引，拿到主键ID，根据主键ID映射到数据行信息，能大幅提升查找的效率。

B+树获取全表数据时，只需要遍历所有叶子节点就可以拿到全部数据了，B+树深度一般1-3层就可以满足千万级数据存储。

• 12.索引什么情况下会失效？

（1）隐式类型转换；
（2）like通配符不是左匹配。
（3）索引列使用内置函数。
（4）索引列使用not in ，!=，<>。
（5）连接的两个表的关联字段的编码格式不一致。
（6）优化器预估使用全表扫描比使用索引更快。

• 13.主从复制原理

数据写入master，master更新binlog，并创建一个dump线程向slave推送binlog；
slave创建一个IO线程接收binlog，并记录到relaylog；
然后slave开启一个SQL线程读取relaylog事件，并在slave执行来完成同步，生成slave自己的binlog。

• 14.数据库的cpu飙升，怎么处理？

使用top命令观察，确认是mysqlid导致还是其他原因；
如果是mysqlid导致，show processlist，查看session情况，确任是否有消耗资源的sql在运行。
找到这些SQL后，分析执行计划，看数据量，索引等。

处理：
（1）先kill掉这些线程，看cpu是否下降；如果对业务有影响，需要提供补偿方式。

（2）分析看是要修改内存参数，还是优化SQL，加索引等。 另外，如果是同一时段请求过多，需考虑是否需要限制连接数。