mysql面试知识点

本博客内容来自视频教程：链接: https://pan.baidu.com/s/1ZwVJy7dmYfzW5qPkyP1-ww 提取码: nrd5 ，里面有视频有文档，加油哦

博客的主要内容

主要内容：

如何分析一个sql的性能：

性能析要从（慢查询日志，然后看explain执行计划，show profile即sql执行时的硬件消耗）

知识点扩充

mysql在执行查询时，一般情况下只会用到一个索引，这个索引由 优化器去进行选择

聚集索引（IOT索引组织表）和非聚集索引（堆组织表）的存储开

开启慢查询日志的操作参考博客：https://www.cnblogs.com/salv/p/10706473.html

看懂慢查询日志参考博客：https://www.cnblogs.com/kerrycode/archive/2018/11/15/9963764.html

mysql的架构：

处理sql区域（控制区） + 存储引擎（和文件系统进行交互）

优化器选择出最优的索引，然后交给存储引擎innodb

innodb存储引擎优点：支持事务，默认的引擎

数据在底层是如何存储的:innodb中数据文件和索引文件在同一个文件中

死锁产生的条件：只有行锁可以产生死锁

mysql存储中日志文件：是顺序io，数据文件是随机io，先写日志，后写数据文件，随机io消耗的时间长：包含寻道时间，旋转时间和传输时间，顺序io只消耗传输时间。

性能分析的三步走思路：

第一步：首先通过慢查询日志功能，去找到查询较慢的sql（慢查询日志，不要在生产机中开，日志是增量的，而且影响效率，上线前的测试可以看）

第二部：然后查看第一步sql，的执行计划

第三部：利用show profile查看对应sql的使用情况

索引是什么：

索引是帮助mysql高效获取数据结构的数据结构，在mysql中索引都用的是B+树数据结构组织的索引，索引往往是存在磁盘中的，

索引的优势：

1，提高检索效率，提高io成本（类似于书的目录）

2，通过索引可以提高数据排序的效率，降低cpu的消耗，被索引的列会自动排序，索引列包括：单列索引和组合索引，只不过组合索引更复杂，而且，当排序时用的索引列，我们用order by速度回更快

索引的劣势:

1，索引会占用磁盘空间，索引越大，而且磁盘空间占用的越大，读取索引占用的io也就多了

2，索引虽然可以提高查询效率，但是更新效率会变低，除了更新表结构还要去更新索引

常用的索引分类：

单列索引：

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

唯一索引，允许为空，但是索引列值不能相同

主键索引，不能为空不能重复

组合索引：在多个字段上建的一个索引就是组合索引

组合索引：用的是最左匹配原则（建议使用组合索引，去代替单例索引）

创建单列索引：

单列索引之普通索引

CREATE INDEX index_name on table(column(length))

ALTER TABLE table_name ADD INDEX index_name(column(length))

创建单列索引之唯一索引
 

CREATE UNIQUE INDEX index_name ON table(column(length))

组合索引

ALTER TABLE table_name ADD index index_name(column1,column2)

删除索引

drop index index_name on table_name

查看索引：

show index from table_name

b树和b+树的区别：

b树是专门为磁盘或其他存储设备设计的一种多叉平衡查找树（与二叉树相比，有多个叉），b树的高度一般，一般决定了树的读取次数，及时间复杂度log(n)

如果三层树结构可以存储20G数据，那么四层树结构就可以存储几十T

B树和B+树的最大区别在于：

B树是非叶子和叶子节点都会存储数据，但是B+树只会在叶子节点上存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，是顺序链表

 

聚集索引：也叫索引组织表

Innodb存储引擎数据组织方式是聚集索引表：完整的记录存储在主键索引中，同过主键索引可以找到记录所有的列，也就是说索引和表数据是在一起的，就是聚集索引

聚集索引之主键索引：主键索引的叶子节点（聚集索引）存储数据行，辅助索引存储键值，innodb中要求表必须有主键，如果没有显示指定，mysql会自动找一个唯一标识的列，作为主键，如果这样的列不存在，mysql会隐式的生成一个列，该列不显示，数据类型是长整型，主键索引存储的是索引和数据行

聚集索引之辅助索引：辅助索引存储的主键值和索引字段

非聚集索引表：也用的b+树，用的是堆组织表，存的是地址，数据行对应的物理地址，聚集索引存的表中的数据

如何创建组合索引：好处：将一个索引，相当于建了多个，节省了空间，

mysql 中的where 查询条件从左往右，但是oracle 中的查询条件是从右往左

前缀索引一般用来：

再任何一个sql 前面加上explain ，我们都可以得到对应的执行信息

其中type 和extra这两个字段很重要，标志是否走索引，以及走索引的级别

加上\G就变成如下这种形式

各个字段的意思：

每一个查询语句都有一个id来标识，partitions 表示用到的分区，如果没有用到，不会显示，possible_keys表示的优化器可能会用到的索引，key 索引，key_length 表示的是用到的索引长度的和，即创建索引的长度之和

type字段及其值进行分析

type这个是重点：type的值如下，性能从上往下一次下降

system出现的情况：只有一行数据或者有空表

回表取数据：即我们的sql中用到的索引树上面没有对应的数据时，我们需要去主键索引中查询，上述过程sql中，查询的是name中的所有值，而前面我们创建索引用的是前缀索引，即索引树用的是name的前10位。

当用到了索引覆盖就要用index

all表示主键索引中所有的表数据,j即进行全表扫描 ，然后在server层进行数据过滤

extra中字段的值及其分析：

该列表示的不包含其他列中的信息，显示额外的信息，该列中包含信息的中类有十几种

当值为using index 时，表示的是用到了索引覆盖，就表示没有用到回表查询。可以直接通过索引进行查询

查询时尽量不要用select  *,因为一般来说我们不会将所有字段来 建一个组合索引，所以用不到组合索引，就用不到索引覆盖，这样就造成要回表查询

如果同时出现using where 表示，不仅进行查找时使用，我更是在展示时使用

如果没有同时出现 using where 表示只进行查找数据，而非展示展示时使用

知识点补充：数据的分页：物理分页和逻辑分页，物理分页，指的是我们在数据库的层就就行了分页，逻辑分页：表示的，我们查询所有数据，然后在内存中进行逻辑分页

我们在innodb存储层数据尽量做到了过滤，那么server层得到的数据就变得更少，在mysql5.6版本以后，有了icp ，即索引下垂，这样就可以在innodb存储层，做到尽量的过滤

用索引时：要注意的事项：

1，全值匹配我最爱，即最好用的是全职

2，用最左前缀法则，即用索引时最好要最左前缀，而且查询条件还不能跳过 中间的索引，如果中断，只会用到前面的su

3，不在索引列上做任何操作（计算，函数，（自动 或手动）类型转换），会导致索引失效，转而向全表扫描

4，存储引擎不能使用索引中范围条件中右边的列，例如> <等范围，作为条件，范围条件

5，尽量是用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 

6，mysql 在使用的 != 或者<>的时候，无法使用索引，进而造成全变扫描

7.is null 或者 is not null 无法使用索引

8，字符串查询不加单引号，索引失效

9，尽量是少使用or，用or连接，索引失效

 

性能优化从服务器层面进行优化：

如何查看自己的内存是否足够大

内存预热：

将mysql server 启动的时候，将数据读到内存中

sql 设计层面的优化

sql 优化：

 

not exits 用到索引，not in 是全表扫描

 

当开启事务时，如果某列不用索引，对某行记录进行修改，事务未提交时，会造成整个表 加锁，造成阻塞，给其他线程的操作造成阻塞，当加上索引，更新操作时，mysql对其加锁在 行级别上不会影响其他记录

 

查询范围对锁的影响：事务中 如果条件是范围，那么会造成范围的锁，范围之外的不会阻塞（当然前天是条件都加索引了）

乐观锁和悲观锁：

悲观锁：因此每次对某资源进行操作时，都会持有一个独占的锁，然后再操作资源。

对改行进行了加锁，别人查询时别人就不能channel了

乐观锁：不总存在竞争，加一个版本号，我们可以根据版本号进行修改

面试问题：https://mp.weixin.qq.com/s/k0-IheG_tvG3V_tRDrlg_w

 

 

myslq的常用操作：

数据库编码：

 

mysql的备份和恢复：

约束：

 

 

jdbc：加载驱动类又叫注册驱动类，

那么加载驱动类和得到jdbc的连接有联系：

加载驱动了会执行对应的静态方法，这个静态方法，调用

结果集：有可能新，是否敏感，是否可更新三个特性

数据库滚动（滚动结果集，当为敏感时，得到数据集后当数据库发生变化时，会更新结果集，但是 没有数据库驱动支持这个功能），可更新

预编译的优点：

 

用statement也可以实现预编译的效果：

自己写一个工具类：

配置文件和加载驱动我们可以放到静态代码块中

代用jdbc接口中的方法，用到日期类型是 ：sql包下的日期，而我们的实体类（要在各个层中出现）中的属性不能用sql中的数据类型

需要将日期类型转化：

在sql 下的Date，Time，TImestap数据类型，继承的是java.util.Date,所以可以将sql下的直接赋值给 util下的Date类型

所以我们只需：将java.util.Date 赋值给sql报下的日期类型

由于sql包下的Date，Time，TImestap三个类都有long类型，即毫秒的构造器，所以我们只需将java.util.Date下的时间转化为毫秒，然后分别调用对应的构造方法：

jdbc设置隔离级别：但是我们没有必要设置

连接池：

 

mysql 的表空间：有独立表空间和共享表空间，共享表空间就是多个表用一个文件，独立表空间就是每个表一个文件

建议使用独立表空间 中 查看表空间设置用 show variables like "%innodb_file_per_table"

独立表空间的好处：

1.使用truncate table 时可以回收表空间，共享表空间不能回收压缩表空间，可以将表建在不同的磁盘和表设备上，减少io的征用，可以用系统备份的方式独立备份单个表

独立表空间缺点：每一表至少一个文件，系统打文件的句柄多，需要提高系统打开文件的系统限制，表的结构需要组织，消耗更多的磁盘空间，drop table 相当于drop tablespace，

什么是二级索引：

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

建议缓冲池大小设置为系统可用内存的60%-80%，我们推荐使用75%

设置缓冲池的大小：innodb_buffer_pool_size(设置缓冲池大小),  innodb_buffer_pool_instances（设置缓冲池实例，内存在32G以上的用建议设置8个实例）

 

mysql 权限用户：建议设置三个用户：管理员用户，业务用户，低权用户

管理员用户有超级权限，业务员有增删改查及其他所有权限，低权用户只有查询权限，用户监控或信息查询