mysql常考知识点_mysql进阶学习一之知识点总结

环境：centos7+mysql5.7

1 mysql5.7的安装

这里使用yum安装，参考这个老哥的博客  ，反正我安装的时候一言难尽，重装了好几次，哎，平常自己玩的都是用的docker...

装好了记得根据上面的博客去初始化密码哦！

2 mysql的逻辑架构

mysql底层就是经过下面这几部分，我们要知道，执行引擎才是真正干活的；

可以发现这里运行了缓存，首先在缓存中取数据，缓存中能命中就直接从缓存中去，缓存中存数据是sql语句为键，实际的数据为值

例如：select name from stu where id = 1;  和select name from stu where id <2 and id>0  ，虽然两条sql在我们看来是一样的，但是不会命中缓存，因为sql不一样

在上图中第5步中的优化器Optimizer，会对我们执行的sql进行优化，例如我们写的sql格式如下：

然后在优化器中可能就变成了下面这种(每次的优化都有可能不一样)

可以看看mysql中有几种执行引擎：show engines;

默认的就是Innodb，从下面可以看到这个引擎支持事务，行锁和外键，一般还会说MyISAM的特点，就是不支持事务，不支持行锁(只支持表锁)，不支持外键；

两个引擎还有一个不同就是，MyISAM只缓存索引，而InnoDb缓存索引和实际的数据；

那什么时候会使用MyISAM引擎呢？其实就是mysql自带的一些表会用这个引擎，因为不需要高并发嘛，一般也是管理员去修改一下，所以锁住整张表也无所谓；

3.性能

项目中mysql使用时间长了之后， 性能会下降，sql的执行时间变长，一般有几个原因

(1)sql中关联的表太多：比如有太多的join，我们需要对sql进行优化

(2)没有充分的利用索引： 看情况去建立索引

(3)数据量太多：sql的优化已经到了极致，数据量太多就需要进行分库分表

(4)mysql服务器参数调优：这个一般也不会去修改，主要是调整my.cnf文件

对sql进行优化，一般就是添加索引，我们一些情况需要添加索引：

(1)频繁的作为查询条件的字段

(2)与其他表作为关联的字段

(3)建立组合索引优于单键索引

(4)  排序语句中的字段加索引，因为索引是已经排序好了的，可以极大的提高排序速度

(5)分组的字段，因为group by是先做的order by，然后再进行的分组

不应该创建索引的字段：

(1)表的记录太少了

(2)经常进行增删改的字段，因为也需要对索引进行相应的操作

(3)where条件中用不到的字段

(4)区分度不高的字段，比如性别

4 explain查看执行计划

在mysql的逻辑架构图中的第5步会调用优化器Optimizer会生成执行计划，然后执行引擎就是根据这个执行计划去执行sql的；

explain命令就是查看这个执行计划的，用法就是sxplain+sql语句，示例： explain select * from stu where id = 0

我们可以根据执行计划看到：

(1)表的读取顺序

(2) 操作表的操作类型

(3)索引的使用情况

(4)表之间的引用

(5)每张表有多少行数据被查询

...

下面中框框中是比较重要的字段，要明白其中的含义才能更好的分析，想知道每个字段详细意思的，看看这个老哥的博客

5 　索引失效

有的时候一些不规范的sql写法会导致明明新建了sql，但是没有走索引，常见的有这么几种情况：

(1)没有遵循最左匹配原则，比如索引是(a，b，c),但是你只使用了b,或者(b,c)

(2)对索引列使用了!=进行判断，xxx where age != 10

(3)使用了is not null   ,xxx where age is not null

(4)like模糊查询时百分号在前面,  xxx where age like '%8'

(5)字符串不加引号，比如xxx where name = 1  ,name建立了索引，而且那么是varchar类型的，此时索引会失效

(6)对索引列使用函数计算了 ,例如xxx where max(age) > 10

6 关联查询优化

(1)a left join b  : a是驱动表，a会全部扫描，b是被驱动表，b可以走索引

所以做连表查询的时候，数据少的做驱动表，数据多的建立索引，作为被驱动表，简称小表驱动大表

(2)a inner join b: mysql自己会将结果集小的作为驱动表，大的作为被驱动表

(3)子查询不要作为驱动表，可能用不到索引

(4)能关联的就直接关联查询，不要用子查询

7. 子查询优化

不要使用not in 或者not exists，使用改写sql写法，改成xxx a left join  b on xxx is null，这样会走索引

8 排序分组优化

(1)(a,b)是索引，xxx where a = 1 order by b会走索引

(2)尽量使用索引字段进行排序，必须要有limit关键字才会走索引

(3)order by a desc,b asc  :   一个降序，一个升序不会走索引

(4)order by a desc,desc : 走索引

9. 覆盖索引

select a,b from xxx

刚好(a,b)是索引，所以这个sql就会走索引查询数据直接返回，不会进行回表

10 mysql主从复制

原理如下：

分三步：

1.主节点master将增删改的sql记录到二进制日志文件(binary log)中

2.从节点slave的io线程将主节点的二进制日志文件拷贝到自己的中继日志文件(Replay log)中

3. 从节点又启动一个sql线程去读取中继日志，执行sql

一般主从节点是在不同的服务器中，可以知道主从节点经过了多次io操作，所以主从复制会有延迟的效果，比如你往数据库中插入一条数据，然后你去查询却查不到