Mysql基础 吊打面试官

 

1. 数据类型

Tinyint(1字节-128 127)  （2的8次方除2 ===2的8次方除2 减1）（无符号为2的8次方减1）

Smallint(2字节)

Mediumint(3字节)

Int(4个字节)

Bigint(8个字节)

Float(4个字节)

Double(8个字节)

Date(3个字节)

Time(3个字节)

Year(1个字节)

Datetime(8个字节)

Timestamp(4个字节)

Varchar char text

 

Char（10）和varchar（10）的区别   int(4)代表的意思

Char是定长数据，varchar是可变长数据类型，char（10）varchar（10）都是最多可以存储10个字节的字符，char（10）如果存储不够10个字节的会用空格补齐，但是varchar(10)会根据字符长度占用空间，由于char类型的长度定长，所以对存储和查询友好（空间换取时间的转换）

Int(4) 没有实际意义，如果勾选了zerofill 如果位数不够4个会自动在前边用0补齐

1. 索引

主键索引，唯一索引，普通索引，组合索引

主键索引：一个表里只能有一个主键索引，不允许有空值

唯一索引：索引列中的值必须是唯一的，但是允许为空值

普通索引：没有什么限制，允许在定义索引的列中插入重复值和空值

组合索引：遵循最左原则

 

添加索引命令：ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

删除索引命令ALTER TABLE book DROP INDEX BkBookNameIdx;

查看索引：SHOW INDEX FROM book\G;

 

索引规则：

全值匹配我最爱，最左前缀要遵守；

   带头大哥不能死，中间兄弟不能断；

   索引列上少计算，范围之后全失效；

   LIKE百分写最右，覆盖索引不写星；

   不等空值还有or，索引失效要少用。

1. 存储引擎myisam和innodb的区别

1. 结构

Myisam会生成三个文件：表结构：.frm  数据：.MYD 索引文件：.MYI

Innodb数据和索引都在一个文件.ibd  表结构：.frm

1. 事务

Myisam不支持事务，Innodb支持事务

1. 锁

Myisam支持表级锁(lock table)，Innodb支持行级锁（for update）

 

Mysql 已默认引擎为innodb

1. 存储过程和触发器

1. 存储过程：是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，
   存储在数据库中，经过第一次编译后再次调用不需要再次编译
2. 触发器：触发器是一种特殊类型的存储过程，它又不同于存储过程，
   触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用

1. 数据库三范式

1. 要求数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值（数据达到原子性，使数据达到不可再分）
2. 使每一行数据具有唯一性，并消除数据之间的“部分依赖”（数据都依赖于主键字段）
3. 独立性，消除传递依赖（去除冗余）

 

注：在开发中，我们还是要依据业务场景，适当的反三范式来设计表达到开发和业务的均衡。

1. 事务四大特性（ACID）

1. 原子性：对数据库的一组操作，要么全部成功，要么一个失败全部回滚，并且失败不会对数据库造成任何影响。
2. 一致性：指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性
3. 隔离性：用户并发对数据库操作，数据库会为每一个用户开启一个事务，多个并发事务之间相互隔离
4. 持久性：指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

1. 事务隔离级别

1. 读未提交：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读
2. 读已提交（oracle默认级别）：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生
3. 可重复读（mysql默认级别）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4. 可串行化：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

1. 脏读，不可重复读和幻读

1. 脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
2. 不可重复读：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
3. 幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

 

1. Mysql锁问题

1. 分类：  操作类型分（读锁（共享锁）  写锁（排它锁））  

       操作粒度分（表锁， 行锁）

1. Myisam引擎（表锁）

1. 增加表锁：lock table tablename1 read(write),tablename2 read(write)
2. 查看表锁：show open tables; 如果In_use显示不为0，则表示表被加锁
3. 释放锁： unlock tables;

1. Innodb 引擎（行锁）

1. 增加锁：update  自动加行锁   select后加for update  为某一行读取加锁

具体案例请参考：https://www.cnblogs.com/developer_chan/p/9234925.html

https://www.cnblogs.com/developer_chan/p/9237966.html这两篇文章

1. Delete，drop，truncate的区别

1）delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2）truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

3) drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

 

在速度上，一般来说，drop> truncate > delete

1. Mysql主从复制流程及原理

     最经典的图例:

 
 原理：三个线程

主开一个IO线程把修改的sql写入binary log（二进制）

从开一个IO线程读取主的binary log然后写入relay log（中继日志）

从开一个sql线程，处理中继日志的sql写入从库

 

1. 产生同步延迟的情况

1. 主库TPS并发高，超过了slave一个sql线程执行的能力
2. 从库在执行一个大型的query 发生了锁等待，从库同步延迟

1. 降低延迟的方法

1. 提高从库的服务器性能
2. 降低从库的安全级别：关闭bin log

1. Mysql慢查询处理

1. 定位慢查询：a. 根据慢查询日志b. 使用show processlist定位，查看正在执行的慢查询
2. 分析原因：

Explain 分析sql语句

重点关注：select_type type extra

Select_type: 查询类型：简单，子查询，连表查询等等

Type（重点）: 自称使用索引级别
              system: 查询对象只有一条数据，支持引擎：myisam和memory

const: 使用了主键索引或者唯一索引，最好的情况

eq_ref: 表连接时基于主键或非null的唯一索引

ref: 基于普通索引的等值查询

fulltext: 全文检索

range: 利用索引进行范围查询

index：全索引扫描

1. Sql优化

1. 索引失效的情况：

1. like是以%开头的查询语句
2. 使用多列索引的查询语句(违反了最左原则)
3. 使用OR关键字查询语句（or条件前后字段都有索引才会用到，不然会放弃使用索引）
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引（隐式转换）
5. 对索引列进行运算

1. 优化方法：

1. 首先应考虑在where 及order by 涉及的列上建立索引
2. 应避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
3. 应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
4. 应尽量避免在where 子句中对字段进行表达式和函数操作，这将导致引擎放弃使用索引而进行全表扫描

1. 为什么MyISAM会比Innodb 的查询速度快

InnoDB 在做SELECT的时候，要维护的东西比MYISAM引擎多很多；

1）InnoDB 要缓存数据和索引，MyISAM只缓存索引块，这中间还有换进换出的减少

2）innodb寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比INNODB要快

3）InnoDB 还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

MVCC ( Multi-Version Concurrency Control )多版本并发控制

InnoDB ：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的

SELECT InnoDB必须每行数据来保证它符合两个条件

1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。

2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。