MySQL面试必过

MySQL事务的四大特性

事务的四大特性ACID

• 原子性Atomicity：
  一个事务必须被事务不可分割的最小工作单元，整个操作要么全部成功，要么全部失败，一般就是通过commit和rollback来控制

• 一致性Consistency：
  数据库总能从一个一致性的状态转换到另一个一致性的状态，比如付费视频网站下单支付成功后，开通视频播放权限，只要有任何一方发生异常就不会成功提交事务

• 隔离性Isolation：
  一个事务相对于另一个事务是隔离的，一个事务所做的修改是在最终提交以前，对其他事务是不可见的

• 持久性Durability：
  一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失

脏读、不可重复读、幻读

• 脏读： 事务中的修改即使没有提交，其他事务也能看见，事务可以读到未提交的数据称为脏读

• 不可重复读： 同个事务前后多次读取，不能读到相同的数据内容，中间另一个事务也操作了该同一数据

• 幻读： 当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，发现两次不一样，产生幻读

幻读和不可重复读的区别是： 前者是一个范围，后者是本身，从总的结果来看, 两者都表现为两次读取的结果不一致

常见的隔离级别由低到高有哪几种，MySQL默认是哪种

事务的隔离级别越高，事务越安全，但是并发能力越差

1. Read Uncommitted(未提交读，读取未提交内容)
   事务中的修改即使没有提交，其他事务也能看见，事务可以读到为提交的数据称为脏读
   也存在不可重复读、幻读问题 ​

   例子：
   公司运营人员姐配置了一个商品，500元的商品，配置成50元，但是事务没提交。
   你刚好看到这个商品这么便宜准备购买，但是运营人员马上回滚了事务，重新配置并提交了事务，你准备下单的时候发现价格变回了500元。

2. Read Committed(提交读，读取提交内容)
   一个事务开始后只能看见已经提交的事务所做的修改，在事务中执行两次同样的查询可能得到不一样的结果，也叫做不可重复读(前后多次读取，不能读到相同的数据内容)，也存幻读问题。
   例子：
   炮哥在某电商网站有1000积分，准备去兑换核潜艇，查询数据库确实有1000积分，但是炮哥的女友同时也在别的地方登录，把1000积分兑换了航空母舰，且在炮哥之前提交事务；当系统帮炮哥兑换核潜艇时，发现积分预计没了，兑换失败。
   炮哥事务A事先读取了数据，他女友事务B紧接了更新了数据且提交了事务，事务A再次读取该数据时，数据已经发生了改变。

3. Repeatable Read(可重复读,MySQL默认的事务隔离级别)
   解决脏读、不可重复读的问题，存在幻读的问题，使用MVCC机制 实现可重复读
   例子：
   炮哥在某电商网站有1000积分，准备去兑换核潜艇，查询数据库确实有1000积分 炮哥的女友同时也在别的地方登录先兑换了这个核潜艇，炮哥的事务提交的时候发现存在了，之前读取的没用了，像是幻觉
   幻读问题：MySQL的InnoDB引擎通过MVCC自动帮我们解决，即多版本并发控制

4. Serializable(可串行化)
   解决脏读、不可重复读、幻读，可保证事务安全，但强制所有事务串行执行，所以并发效率低

存储引擎InnoDB、MyISAM异同点和选择

区别项	InnoDB	MyISAM
事务	支持	不支持
锁粒度	行锁，适合高并发	表锁，不适合高并发
是否默认	默认	非默认
是否支持外键	支持	不支持
适合场景	读写均衡，写大于读场景，需要事务	读多写少场景，不需要事务
全文索引	不支持，可以通过插件实现, 更多使用ElasticSearch	支持全文索引

MySQL索引

索引名称	索引特点	创建语句
普通索引	最基本的索引，仅加速查询	CREATE INDEX idx_name ON table_name(filed_name)
唯一索引	加速查询，列值唯一，允许为空；组合索引则列值的组合必须唯一	CREATE UNIQUE INDEX idx_name ON table_name(filed_name_1,filed_name_2)
主键索引	加速查询，列值唯一，一个表只有1个，不允许有空值	ALTER TABLE table_name ADD PRIMARY KEY ( filed_name )
组合索引	加速查询，多条件组合查询	CREATE INDEX idx_name ON table_name(filed_name_1,filed_name_2)
覆盖索引	索引包含所需要的值，不需要“回表”查询，比如查询 两个字段，刚好是 组合索引 的两个字段
全文索引	对内容进行分词搜索，仅可用于MyISAM， 更多用ElasticSearch做搜索	ALTER TABLE table_name ADD FULLTEXT ( filed_name )

索引的优缺点有哪些，使用时应该注意什么

考虑点：结合实际的业务场景，在哪些字段上创建索引，创建什么类型的索引

索引好处：
	快速定位到表的位置，减少服务器扫描的数据
	有些索引存储了实际的值，特定情况下只要使用索引就能完成查询

索引缺点：
	索引会浪费磁盘空间，不要创建非必要的索引
	插入、更新、删除需要维护索引，带来额外的开销
	索引过多，修改表的时候重构索引性能差

索引优化实践：
	前缀索引，特别是TEXT和BLOG类型的字段，只检索前面几个字符，提高检索速度
	尽量使用数据量少的索引，索引值过长查询速度会受到影响
	选择合适的索引列顺序
	内容变动少，且查询频繁，可以建立多几个索引
	内容变动频繁，谨慎创建索引
	根据业务创建适合的索引类型，比如某个字段常用来做查询条件，则为这个字段建立索引提高查询速度
	组合索引选择业务查询最相关的字段

索引失效

1、最左匹配原则
2、如果使用了!=会导致后面的索引全部失效
3、查询条件中有or
4、like查询是以’%'开头
5、对查询的列上有运算或者函数的
6、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
7、左连接查询或者右连接查询查询关联的字段编码格式不一样
8、如果MySQL估计使用全表扫描要比使用索引快,则不使用索引
9、连接查询中，按照优化器顺序的第一张表不会走索引
10、如果查询中没有用到联合索引的第一个字段，则不会走索引

项目中数据库的优化

业务核心数据存储在MySQL里面，针对业务创建合适的索引
打点数据、日志等存储在ElasticSearch或者MongoDB里面

数据库查询指令的执行顺序

select 查看哪些结果字段
from 从哪个表查询
where 初步过滤条件
group by 过滤后进⾏分组[重点]
having 对分组后的数据进⾏⼆次过滤[重点]
order by 按照怎样的顺序进⾏排序返回[重点]

select video_id,count(id) as num from table_name group by video_id having num > 6
order by video_id desc

MySQL中的varchar和char有什么区别

对比项	char(16)	varchar(16)
长度特点	长度固定，存储字符	长度可变，存储字符
长度不足情况	插入的长度小于定义长度时，则用空格填充	小于定义长度时，按实际插入长度存储
性能	存取速度比varchar快得多	存取速度比char慢得多
使用场景	适合存储很短的,固定长度的字符串，如⼿机号、MD5值等	适合用在长度不固定场景，如收货地址，邮箱地址等