MySQL常见问题

soulballad约 2684 字大约 9 分钟

三种SQL语言

DDL(定义语言): 用于定义数据表的结构(如新增、删除、修改等) create/alert/drop table, create/drop index等；
DML(操纵语言): 用于查询与修改数据记录 select, update, delete, insert；
DCL(控制语言): 用来控制数据库的访问 grant, revoke, lock, rollback, commit, savepoint；

myisam和innodb区别:

锁事存主聚行外查： ((行锁+表锁)&表锁、支持事务、ibd&(myi+myd)、主键、聚集索引、存储表的行数、支持外键、查询流程(回表&(先到myi查地址，再到myd查数据)))

MySQL事务失效的场景

存储引擎、非事务方法A调用事务B引起事务失效、like 查询用％开头引起的事务失效、MySQL在使用不等于导致索引失效

MySQL索引失效的场景

联合索引不是最左匹配、使用了select *、索引列参与运算、索引列使用函数、错误like使用、隐式类型转换、使用or操作、两列做比较、不等于比较、is not null、not in和not exists、order by导致索引失效、参数不同导致索引失效、优化器发现走索引效率低

MySQL中in和or会走索引吗

in 不一定；or 不一定; 在使用or关键字时，切记两个条件都要添加索引，否则会导致索引失效。

CHAR和VARCHAR的区别？

CHAR的长度是不可变的，而VARCHAR的长度是可变的，
CHAR的存取速度要比VARCHAR快得多，因为其长度固定，方便程序的存储与查找，但是CHAR类型会浪费空间
CHAR的存储英文字符占用1个字节，汉字占用两个字节; VARCHAR的存储英文字符和汉字都占用2个字节。
两者的存储数据都是非unicode的字符数据。

datetime和timestamp区别？

datetime占8个字节，可表示1000-01-01~9999-12-31时间范围，不自动转换时区；
timestamp占4个字节，可表示1970-01-01~2038-01-19时间范围，会自动切换为当前时区存储；
开发时可考虑使用long类型存储时间戳；

exist和in的区别？

  -- in
  select * from a where id in (select id from b);
  -- exists
  select * from A where exists(select 1 from B where B.id = A.id);

使用in时,sql语句是先执行子查询，也就是先查询子表b，再查主表a。
而使用exists是先查主表a, 再查询子表b。
根据小表驱动大表(即小的数据集驱动大的数据集)的原则, 如果主查询中的表较大且又有索引时应该用in。反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

union和union all区别

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

如何理解数据库三范式

第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解；
第二范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
第三范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。。

MySQL执行顺序

以 select * from A left join B on xxx where Axx group by xx order by xx; 为例

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

SQL优化的经验

查询语句无论是使用哪种判断条件 等于、小于、大于， WHERE 左侧的条件查询字段不要使用函数或者表达式
使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 sql 语句，我们通常是使用 explain sql 来分析这条 sql 语句，这样方便我们分析，进行优化。
当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1
不要直接使用 SELECT * ，而应该使用具体需要查询的表字段，因为使用 EXPLAIN 进行分析时，SELECT * 使用的是全表扫描，也就是 type = all。
为每一张表设置一个 ID 属性
避免在 WHERE 字句中对字段进行 NULL 判断
避免在 WHERE 中使用 != 或 <> 操作符
使用 BETWEEN AND 替代 IN
为搜索字段创建索引
选择正确的存储引擎，InnoDB 、MyISAM 、MEMORY 等
使用 LIKE %abc% 不会走索引，而使用 LIKE abc% 会走索引
对于枚举类型的字段(即有固定罗列值的字段)，建议使用 ENUM 而不是 VARCHAR ，如性别、星期、类型、类别等
拆分大的 DELETE 或 INSERT 语句
选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数。
字段设计尽可能使用 NOT NULL
用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN
进行水平切割或者垂直分割
注意索引使用条件，防止索引失效

批量向MySQL导1000W数据，如何优化

直接导入
使用存储过程循环拼接
使用load data infile
修改ENGINE=InnoDB为MyISAM（v5.1之前是MyISAM，之后是InnoDB）
减少IO次数
SQL写法优化（一条SQL语句插入多条数据）
合理设置批量大小（在事务中进行插入处理，不要一条数据提交一次，肯定要分批处理）
尽量顺序插入（减少索引的维护压力）
合并事务+事务+有序数据的优化插入方式
注意SQL批量插入的大小必须合理
事务执行时间不要太长
合理设置MySQL相应配置参数，增加缓存或减少不必要日志磁盘读写

分库分表数据分片规则

比较常见的有：Hash取模分表、数值Range分表、一致性Hash算法分表

MySQL的事务ACID是如何实现的？

InnoDB 是通过日志和锁来保证的事务的 ACID特性，具体如下：

1）通过数据库锁的机制，保障事务的隔离性；
2）通过 Redo Log（重做日志）来，保障事务的持久性；在事务提交前，将 Redo Log 持久化，当系统崩溃时，即时数据没有持久化，也可以通过 Redo Log 将数据恢复到崩溃之前的状态。
3）通过 Undo Log （撤销日志）来，保障事务的原子性；在数据操作之前先进行备份，如果出现了错误或者用户执行了 Rollback 语句，可以通过 Undo Log 将数据恢复到事务开始之前的状态。
4）通过 Undo Log （撤销日志）来，保障事务的一致性；

如何保证 REPEATABLE READ 级别不产生幻读？

MySQL Innodb存储引擎中，通过 间隙锁防止幻读的产生
在RR的隔离级别下，Innodb使用MVCC和 next-key locks(行锁和间隙锁的组合)解决幻读，

什么是MVCC

什么是MVCCopen in new window

MVCC工作的事务隔离级别是啥？

Read Committed
Repeatable Read;

超大分页怎么处理

用id优化 select * from user where id>1000000 limit 100
用覆盖索引优化 select * from table where id in (select id from table where age > 20 limit 1000000,10)
在业务允许的情况下限制页数

什么是MySQL的MRR优化

MRR，全称「Multi-Range Read Optimization」，使用 MRR 时，SQL 语句的执行过程是这样的：

先把通过二级索引取出的值缓存在缓冲区中，这个缓冲区叫做 read_rnd_buffer ，简称 rowid buffer。
再把这部分缓冲区中的数据按照ID进行排序。
如果二级索引扫描到索引文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序；
然后再依次根据ID去聚集索引中获取整个数据行。
线程调用 MRR 接口取 rowId，然后根据rowId 取行数据；
当根据缓冲区中的 rowId 取完数据，则继续调用过程 2) 3)，直至扫描结束；

MRR 的本质：是在回表的过程中，把分散的无序回表，变成排序后有序的回表，从而实现随机磁盘读尽可能变成顺序读。

explain关注字段

id: 选择标识符
select_type: 查询类型
table: 输出结果集的表
partitions: 匹配的分区
type: 表的连接类型
possible_keys: 查询时可能使用的索引
key: 实际使用的索引
key_len: 索引字段的长度
ref: 列与索引的比较
rows: 扫描出的行数
filtered: 按表条件过滤的行百分比
extra: 执行情况描述和说明

分库分表数据迁移方案

停机迁移方案
双写迁移方案

MySQL主从复制

主从复制原理:
- binlog->binlog dump thread->io thread->ready log->sql thread
主从复制同步方式:
- 异步复制、同步复制、半同步复制、多库并行复制、异步复制之GTID复制
主从同步延迟
- 「Mysql默认采用的异步复制」，因为它的效率明显是最高的。因为只要写入bin log后事物就结束返回成功了。但由于从库从主库异步拷贝日志以及串行执行 SQL 的特点，所以从库的数据一定会比主库慢一些，是有延时的。
- 影响延迟因素：
  - 主节点如果执行一个很大的事务，那么就会对主从延迟产生较大的影响
  - 网络延迟，日志较大，slave数量过多
  - 主上多线程写入，从节点只有单线程同步
  - 机器性能问题，从节点是否使用了“烂机器”
  - 锁冲突问题也可能导致从机的SQL线程执行慢
- 优化主从复制延迟：
  - 1）大事务：将大事务分为小事务，分批更新数据
  - 2）减少Slave的数量，不要超过5个，减少单次事务的大小
  - 3）Mysql 5.7之后，可以使用多线程复制，使用MGR复制架构
  - 4）在磁盘、raid卡、调度策略有问题的情况下可能会出现单个IO延迟很高的情况，可用iostat命令查看DB数据盘的IO情况，再进一步判断
  - 5）针对锁问题可以通过抓去processlist以及查看information_schema下面和锁以及事务相关的表来查看

MySQL高可用方案

主从或主主 + Keepalived
MHA（Master-Mater replication manager for MySQL），MMM（MySQL Master High Available）
pxc
Galera
MGR/InnoDB Cluster
Xenon
Orchestrator

MySQL常见问题

# 三种SQL语言

# myisam和innodb区别:

# MySQL事务失效的场景

# MySQL索引失效的场景

# MySQL中in和or会走索引吗

# CHAR和VARCHAR的区别？

# datetime和timestamp区别？

# exist和in的区别？

# union和union all区别

# 如何理解数据库三范式

# MySQL执行顺序

# SQL优化的经验

# 批量向MySQL导1000W数据，如何优化

# 分库分表数据分片规则

# MySQL的事务ACID是如何实现的？

# 如何保证 REPEATABLE READ 级别不产生幻读？

# 什么是MVCC

# MVCC工作的事务隔离级别是啥？

# 超大分页怎么处理

# 什么是MySQL的MRR优化

# explain关注字段

# 分库分表数据迁移方案

# MySQL主从复制

# MySQL高可用方案