MySQL面试系列-06
MySQL面试系列-06
1. MySQL 假设一天几万以上的增量，预计运维三年，怎么优化？
1）设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
2）选择合适的表字段数据类型和存储引擎，适当的添加索引。
3）mysql库主从读写分离。
4）找规律分表，减少单表中的数据量提高查询速度。
5）添加缓存机制，比如memcached，apc等。
6）不经常改动的页面，生成静态页面。
7）书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。
2. 存储过程和触发器及自定义函数有什么区别？
1、存储过程
存储过程是为了完成特定功能经过编译的sql语句集；
2、触发器
触发器是一个特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL
也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动调用。
创建触发器
语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
例如：
CREATE TRIGGER tableref BEFORE INSERT ON table1
FOR EACH ROW BEGIN
INSERT INTO table2 SET a2 = NEW.a1;
DELETE FROM table3 WHERE a3 = NEW.a1;
UPDATE table4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
注：在INSERT触发程序中，仅能使用NEW.col_name，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，没有新行。在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来引用更新后的行中的列。
触发器用于完成一些出发条件引发的操作，执行是自动化的。
3、自定义函数
只能通过return语句返回单个值或者表的对象，存储过程不能调用return语句，但可以通过out参数返回多个值
3. MySQL 中如何根据日期查询当天、本周、本月等数据记录？
查询当天数据，SQL语句如下：
select * from 表名 where to_days(时间字段名) = to_days(now());
查询昨天数据，SQL语句如下：
SELECT * FROM 表名 where TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= 1
查询最近7天、30天的数据，SQL语句如下：
SELECT * FROM 表名 where DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date(时间字段名)
SELECT * FROM 表名 where DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date(时间字段名)
查询上月的数据，SQL语句如下：
SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now() , '%Y%m') , date_format(时间字段名,'%Y%m')) =1
查询本月的数据，SQL语句如下：
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, '%Y%m') = DATE_FORMAT( CURDATE(),'%Y%m')
4. MySQL 索引使用有哪些注意事项？
从三个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景，索引规则。
索引哪些情况会失效
查询条件包含or，可能导致索引失效
如何字段类型是字符串，where时一定用引号括起来，否则索引失效
like通配符可能导致索引失效。
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
在索引列上使用mysql的内置函数，索引失效。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
索引字段上使用is null， is not null，可能导致索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
mysql估计使用全表扫描要比使用索引快,则不使用索引。
索引不适合哪些场景
数据量少的不适合加索引
更新比较频繁的也不适合加索引
区分度低的字段不适合加索引（如性别）
索引的一些潜规则
覆盖索引
回表
索引数据结构（B+树）
最左前缀原则
索引下推
5. MySQL 遇到过死锁问题吗，如何解决？
排查死锁的一般步骤：
查看死锁日志show engine innodb status;
找出死锁Sql
分析sql加锁情况
模拟死锁案发
分析死锁日志
分析死锁结果
6. 【字节跳动】MySQL 中为什么使用 B+ 树索引？
MySQL常用的索引为：哈希索引，B+树索引。而树的话，无非就是前中后序遍历、二叉树、二叉搜索树、平衡二叉树，更高级的红黑树、B树、B+树。
树形结构查找的时间与树的高度是成正比的，高度越高查找的时间越长，而B树是一种多路搜索树，每个结点至多可以有两个子节点，M路的B树最多能拥有M个子节点。 每个结点子节点数增加的同时，就是树的结构变小，查找起来也更快。但M也不能太大，如果特别大的话一个结点的子节点太多就退化成了数组，查询较慢。
MySQL索引文件是存在磁盘的，不是在内存上，红黑树在内存上效果要好过B树，但是B树更适合存在磁盘上的文件索引。因为每次不可能将一整个索引都加载进内存，这时可以从B树的一个结点开始找，找不到就加载子节点，一步一步向后找。
7. 为什么要使用自增 ID 作为主键？
1、若定义主键(PRIMARY KEY)，InnoDB会选择主键作为聚集索引，反之未定义主键，则InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引。
没有唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。
2、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放。
每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，若页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。
3、若表使用自增主键，则每次插入新的记录就会顺序添加到当前索引节点的后续位置，当写满一页就会自动开辟一个新的页。
4、若使用非自增主键，因为每次插入主键的值都近似于随机，所以每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL将为新记录插到合适位置而移动数据，甚至可能被回写到磁盘上而从缓存中清除掉，此时又要从磁盘上读回来，这将增大了开销。同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续需通过OPTIMIZE TABLE来重建表并优化填充页面。
