mysql优化之sql语句优化、以及mysql一些高频面试题

文章目录

• 一、索引
• • 1、什么是索引
  • 2、添加索引的原则
  • 3、索引的优缺点
  • 4、索引分类
  • 5、mysql存储过程（方法）
• 二、MySQL的逻辑架构
• • 1、逻辑架构
  • 2、MyISAM 和 InnoDB的区别
• 三、mysql的索引数据结构
• • 1、B Tree
  • 2、B+ Tree
• 四、缓冲池 Buffer Pool
• • 1、预读机制
  • 2、预读失效
  • 3、淘汰策略
• 五、Explain 用法
• • 1、id
  • 2、type
  • 3、key_len
  • 4、extra
  • 5、总结
• 六、SQL的优化方案
• • 1、sql语句优化方案
  • 2、口诀
  • 3、其他优化方案

一、索引

1、什么是索引

索引是一种用于加速数据查询和排序操作的数据结构。它类似于书籍的目录，可以帮助数据库快速定位和获取所需的数据，从而提高查询的效率

2、添加索引的原则

经常查询的字段，添加索引，不经常查询的字段，就不要添加索引
字段中的数据，如果有大量重复的，该字段不能添加
一个表不要添加很多索引，最多2~3个索引

3、索引的优缺点

1、添加索引，查询速度非常的快
2、新增，修改和删除数据比较慢
3、添加索引，无形中占用了我们的空间

4、索引分类

1） 主键索引 所有的主键都是⼀种索引，天然的。
2） 唯⼀索引 将⼀个类设置为unique ，这种设置也是⼀种索引。
3） 普通索引 表中的普通类，可以设置为普通索引
4） 联合索引 由多个列组成的索引

-- 如果设计表的时候忘记添加索引，可以后来追加
alter table sc01 add primary key(sid); -- 追加主键
alter table sc01 add unique(sid); -- 追加唯一索引
alter table sc01 add index sname; -- 追加普通索引

5、mysql存储过程（方法）

存储过程其实就是一个类似于方法的SQL片段，用于提供一个功能。可以反复调用

drop procedure if exists 'init_data';  -- 如果名字存在就删除
delimiter ;;  -- 声明存储过程结束符号为;;

-- 类似于java中方法的参数（in用来接收参数，out用来接收返回值）  declare 定义局部变量
create procedure 'init_data' (in data_count int)  
begin  -- begin end 类似于java中的{}
    declare i int default 0;  -- i的默认值设置为0
    while i < data_count do  -- 循坏data_count次
        insert into student values (i,'zhangsan',round((rand() * 100)));  -- 插入数据
        end while ;
end ;;
delimiter ;  --  将结束符号重新声明为;

call init_data(5000);  -- 执行存储过程 传入参数

二、MySQL的逻辑架构

1、逻辑架构

连接层
连接层是一些客户端和连接服务
服务层
服务层主要完成大多数的核心服务功能，如SQL接口等
引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同。
存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与引擎层的交互

2、MyISAM 和 InnoDB的区别

这两个引擎，MyISAM 注重性能，读取数据的速度非常的快，InnoDB注重事务，注重安全，读取速度比较慢。
我们企业中，一般会做一个MySQL主从复制 可以将主的MySQL服务器，配置为InnoDB, 从服务器可以配置为MyISAM，因为它负责读，可以做到读写分离。

三、mysql的索引数据结构

1、B Tree

B Tree 的缺点：

B Tree 上的每一个节点都存放的有数据，会导致存放的节点比较少，没有B+Tree多
B Tree 在进行范围查找的时候，查询次数比较多，效率低

2、B+ Tree

B+ Tree 对比B Tree 的优点：

有序的，想查找某个范围速度非常的快
数据节点和指针分开了，一次获取到的数据比B Tree多很多，这样的设计，同样是三层架构，可以存储的数据比较多

四、缓冲池 Buffer Pool

缓冲池，简单来说就是一块内存区域。它存在的原因之一是为了避免每次都去访问磁盘，把最常访问的数据放在缓存里，提高数据的访问速度。

1、预读机制

在查询时，mysql的innodb会提前预读多条数据，存入缓冲池中，从而优化I/O，减少IO操作

2、预读失效

在进行预读时，缓冲池会加载大量的数据页，从而导致缓冲池中已存在的常用的数据页替换出去，造成缓冲池污染
此时会通过淘汰策略来将一些数据合理的淘汰清除掉

3、淘汰策略

LFU：最近最不常用算法，根据数据的历史访问频率来淘汰数据，Frequently 为频率的意思
LRU：最近最少使用算法，根据数据的历史访问记录来进行淘汰数据，Recently 为最近的意思
ARC：自适应替换缓存算法，结合了LRU和LFU的优点
FIFO：先进先出算法，淘汰最早进入缓存的数据项

五、Explain 用法

Explain ：用于分析SQL语句的关键字

1、id

id如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id值越大，优先级越高，越先执行

2、type

这个指标是说明我们这个SQL是好还是坏的一个非常重要的指标；
system > const > eq_ref > ref > range > index > ALL SQL从好变坏

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
Index：称之为覆盖索引 即联合索引的全字段匹配
ALL: 遍历全表以找到匹配的行,没有使用到索引

3、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好（char类型就比varchar短两位，null比not null长1位）

4、extra

包含不适合在其他列中显示但十分重要的额外信息

1）Using filesort （出现这个说明非常不好） ：进行order by的时候没有使用到索引建立的梯子，中间有断层
2）Using temporary （出现这个说明非常不好） ：使了用临时表保存中间结果
3）using index 说明还可以，还不错 ：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行
4）Using where 表明使用了where过滤
5）using join buffer ：使用了连接缓存
6 impossible where ：where子句的值总是false，不能用来获取任何元素。 比如：select * from user where name=“张三” and name=”李四

5、总结

通过 explain 进行sql分析的时候，其实就是查看这个大的SQL拆分以后，每一个小的SQL语句是如何执行的。
并且查看每一个小的SQL语句它的type类型 和 Extra 这两个字段的值
查看type类型是否为ALL
查看Extra 这个字段是否出现 filesort temporary

六、SQL的优化方案

1、sql语句优化方案

– 1、全字段匹配
– 2、最佳左前缀法则
– 3、索引列上不计算
– 4、不能使用索引中范围条件右边的列
– 5、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
– 6、使用不等于(!= 或者<>)的时候无法使用索引
– 7、注意字段的null值和 not null 对sql的影响
– 8、like以通配符开头(‘%abc…’)mysql索引失效
– 9、字符串不加单引号索引失效 （底层使用数据类型转换）
– 10、少用or,用它来连接时会索引失效

2、口诀

– 全值匹配才最棒，最佳左前缀法则；
– 刘备大哥不能死，关羽兄弟不能断；
– 索引列上少计算，范围之后全完蛋；
– like百分写最右，覆盖索引有妙用；
– 不等空值还有OR，索引影响要注意；

3、其他优化方案

– ● 索引优化： 合理设计索引，优化查询性能，避免全表扫描。
– ● 查询优化： 优化SQL查询语句，避免慢查询，提高查询效率。
– ● 缓存优化： 合理利用MySQL内置的缓存机制，如查询缓存、结果缓存等，减少对数据库的访问次数。
– ● 硬件优化： 选择合适的硬件配置，包括CPU、内存、磁盘等，提高数据库的吞吐量和响应速度。