MySQL进阶 | SQL优化 | 按关键字分类及原理 | 无知的我费曼笔记（图文排版无水印）

无知的我正在复习MySQL进阶知识。。。。

笔记特点是 我重新整理了涉及资料的一些语言描述、排版，而使用了自己比较容易理解的描述、同样是回答了一些常见关键问题

如果有遇到有任何无法进展问题或者疑惑的地方，应该在讨论区留言 或者 其他途径以寻求及时的帮助，以加快学习效率 或者 培养独立解决问题的能力、扫清盲点、补充细节

insert 优化

批量插入数据（500~1000条数据）

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

这是因为单条插入，每次都需要重新连接数据库

手动控制事务

start transaction; 
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); 
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); 
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); 
commit;

这是因为当执行完一条插入语句后，每次会自动提交事务

主键顺序插入

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

这是因为由MySQL的数据组织结构决定的

使用 load指令 插入（大批量插入数据）

-- 客户端连接服务端时，加上参数 -–
local-infile mysql –-local-infile -u root -p 

-- 查看local_infile是否开启
select @@local_infile;


-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 
set global local_infile = 1; 

-- 执行load指令将准备好的数据，加载到表结构中 
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

比如下图

主键 优化

这里主要回答为什么

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。

//行数据，都是存储在聚集索引的叶子节点上的。

InnoDB的逻辑结构图

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。
那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂

页可以为空，也可以填充一半，也可以填充100%。

每个页包含了2-N行数据(依旧往满的页插入数据，会行溢出)，根据主键排列。

是比较耗费性能的操作

主键顺序插入效果

当第一个页写满之后，再写入时会创建第二个页，页与页之间通过指针连接

当第二页写满了，再往第三页写入

主键乱序插入效果

此时再插入id为50的记录

=>会再次开启一个页，写入新的页中吗？

不会。因为，索引结构的叶子节点是有顺序的。

=>按照顺序，应该存储在47之后。

但是47所在的1#页，已经写满了，存储不了50对应的数据了。

=>那么此时会开辟一个新的页 3#。

=>但是并不会直接将50存入3#页，而是会将1#页后一半的数据，移动到3#页，然后在3#页，插入50。

移动数据，并插入id为50的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1#的下一个页，应该是3#， 3#的下一个页是2#。

=>所以，此时，需要重新设置链表指针。

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

索引设计原则

满足业务需求的情况下，尽量降低主键的长度。这是因为在二级索引中，每个二级索引都会存储主键，主键过长耗费的磁盘IO就越多

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
尽量不要使用UUID做主键或者是其他自然主键，如身份证号。这是因为其是
无序的且字符串很长导致检索会耗费过多的磁盘IO

业务操作时，避免对主键的修改。这是因为会导致对应的索引结构也要对应地改变

order by 优化

order by优化原则

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。

多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。（如果占满了，会在磁盘空间中排序）

语法 在创建索引时指定排序方式

//如果创建索引时不指定其排序，默认升序

升序/降序联合索引结构图

group by 优化

group by 优化原则

在分组操作时，可以通过索引来提高效率。

分组操作时，索引的使用也是满足最左前缀法则的。

imit 优化

——问题
在数据量比较大时，如果进行limit分页查询，在查询时，查询的起始位置越往后，分页查询效率越低。

这是因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

——优化思路
一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

——通过id查询返回所有数据

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

注意 in 关键字不支持 limt。上面实现的原理是 先根据分页查询出所有的id，将查询出来的id看作一个独立的表，然后进行多表查询

count 优化

——问题
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。

InnoDB 引擎更麻烦，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

——主要优化思路
自己计数。可以借助于redis这样的数据库进行，但是如果是带条件的count还是麻烦。

update 优化

update course set name = 'javaEE' where id = 1 ;
#当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。\

update course set name = 'SpringBoot' where name = 'PHP' ;
#当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。

这是因为InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。