MySQ基础知识以及面试题

MySQL基础知识

1、MySQL的体系结构

2、存储引擎

• MyISAM

由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。

• InnoDB

mysql 5.5版本以后默认的存储引擎
由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。

3、优化SQL步骤

3.1查看SQL执行频率(借鉴指标—查看以插入还是查询为主)

当前session中所有统计参数的值

show status like 'Com_______';

show status like 'Innodb_rows_%';

3.2定位低效率执行SQL

慢查询日志：通过日志查询执行效率低的SQL语句

show processlist:查看实时的SQL语句的执行情况

3.3explain分析执行计划

查询SQL语句的执行计划

explain select * from xxx

id：操作表的顺序 （表的执行顺序）

​ id值相同 优先级从上到下

​ id值越大 优先级越高越先执行

select_type：查询的select的类型

table：数据属于哪一张表

type：显示访问类型

3.4show profile分析SQL

通过该工具分析出执行SQL语句的时间耗费

3.5trace分析优化器执行计划

通过trace文件进一步了解为什么优化器选择A，而不是B

4、索引的使用

4.1 索引的分类

1.从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

2.从应用层次来分：普通索引，唯一索引，复合索引。

3.根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

4.2 为什么使用索引

索引的目的在于提高查询效率

索引能极大的减少存储引擎需要扫描的数据量

索引可以把随机IO变成顺序IO

索引可以帮助我们在进行分组、排序等操作时，避免使用临时表

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B+树的形式保存。

如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。

4.3 如何避免索引失效：

1. 全值匹配，针对索引中的列都指定其具体值
2. 最左前缀法则：复合索引中 最左
3. 范围查询之后的字段 索引失效
4. 运算操作 索引失效
5. 字符串不加单引号 索引失效
6. 尽量使用覆盖索引 避免select（只访问索引的查询（索引列完全包含查询列））
7. 用or分割开的条件 or 后没有索引 条件失效
8. 模糊匹配 %加前面 索引失效 通过覆盖索引解决
9. 如果MYSQL评估使用索引比全表更慢，则不用索引
10. null值 有时会失效 根据表中数据量 底层进行评估
11. in 走索引 not in不走
12. 尽量使用复合索引，少使用单列索引

5、SQL优化

5.1大批量插入数据

1. 主键顺序插入
2. 关闭唯一性校验 执行SET UNIQUE_CHECKS=0 导入结束后 开启唯一性校验
3. 手动提交事务 SET AUTOCOMMIT=0 导入结束后再开启事务提交

5.2优化insert语句

5.3优化order by语句

6、应用优化

6.1使用连接池

6.2减少对MYSQL的访问

1. 避免对数据进行重复检索
2. 增加cache层 添加缓存

6.3负载均衡

1. 通过MYSQL的主从复制，实现读写分离，增删改 走主节点，查询走从节点，降低单台服务器的读写压力
2. 采用分布式数据架构

7、MySQL中查询缓存优化

8、内存管理及优化

9、并发参数调整

10、锁问题

10.1 锁概述

锁是计算机协调多个进程或线程并发访问某一些资源的机制

10.2 锁分类

1. 按粒度分：表锁、行锁
2. 按类型分：读锁（共享锁）、写锁（排他锁）

10.3MySQL锁

读锁会阻塞写，不会阻塞读

写锁，既会阻塞读，又会阻塞写

10.4 InnoDB行锁

隔离级别越高 数据库效率越低

建议：

• 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少索引条件，以及索引范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能使用低级别事务隔离

MySQL面试题

数据库的三范式是什么？

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式：任何非主属性不依赖于其它非主属性。

一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

数据库引擎如果是 MyISAM ，那 id 就是 8。

数据库引擎如果是 InnoDB，那 id 就是 6。

InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。

ACID 是什么？

Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。

Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

MySQL 的内连接、左连接、右连接有什么区别？

内连接关键字：inner join；左连接：left join；右连接：right join。

内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

乐观锁和悲观锁？

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

MySQL 索引是怎么实现的？

目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

B 树和B+树

B树是一颗多路平衡查找树，B+树是B树的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

1. 只在叶子节点存储data数据，这样非叶子节点就能存储更多的key
2. 为所有叶子结点增加了一个链指针

原理图：B树和B+树详解

B树的每个节点存储了key和data，key是一条数据记录的键值，是唯一的，data存储的是数据记录除key以外的数据。而B+树只在叶子节点存储data数据，这样非叶子节点就能存储更多的key。所以B+树相较于B树来说更加的矮胖，因为索引树很大不能一次IO读取进内存，树的深度越浅，查找数据时IO的次数就越少，效率就更快。

B+树的每个叶子节点的指针指向相邻的叶子节点，构成一个有序链表，可以按照关键码排序的次序遍历全部记录。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树叶子节点指针为null，则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

数据库选用B+树的原因：

B+树还有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，b+tree 数据节点存在指针，所以范围查找不需要多次查找。，而B树不支持。这是数据库选用B+树的最主要原因。

的次序遍历全部记录。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树叶子节点指针为null，则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

数据库选用B+树的原因：

B+树还有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，b+tree 数据节点存在指针，所以范围查找不需要多次查找。，而B树不支持。这是数据库选用B+树的最主要原因。