MySql

2024-08-21 18:14:22   8  举报





AI智能生成

MySql是一个开源的、流行的关系型数据库管理系统，它使用SQL语言来管理数据。它支持大量的数据库操作，包括创建、修改、查询和删除数据。此外，MySql还提供了诸如事务处理、索引、存储过程、触发器、视图和备份等功能，以满足各种应用场景的需求。MySql文件通常具有.sql或.ibd扩展名，其中.sql文件用于存储SQL脚本和数据库的结构，.ibd文件则用于存储数据库表的数据和索引。MySql支持多种操作系统，如Linux、Windows和macOS，并能与多种编程语言进行交互，如Python、Java、C++等。由于其稳定性、高性能和低成本，MySql在Web应用、企业应用和移动应用中得到了广泛应用。

项目管理

作者其他创作

大纲/内容

Main Topic

索引

概念

索引下推（Index Condition Pushdown，ICP）

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可
以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过
滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全
行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果

为什么范围查找MySql没有用索引下推优化？

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like
KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

索引设计原则

代码先行，索引后上

联合索引尽量覆盖条件

不要在小基数字段上建立索引

长字符串我们可以采用前缀索引

where与order by冲突时优先where

索引优化

分页场景优化技巧

根据自增且连续的主键排序的分页查询

这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验
所示（

根据非主键字段排序的分页查询

Join关联查询优化

mysql的表关联常见有两种算法

嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动
表）里取出满足条件的行，然后取出两张表的结果合集。

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有
索引的情况下 NLJ 算法比 BNL算法性能更高

对于关联SQL的优化

关联字段加索引，让mysql做join操作时尽量选择NLJ算法

小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去
mysql优化器自己判断的时间

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集

count(*)查询优化

Main Topic