SQL优化

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

2). 优化方案二

手动控制事务

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;

3). 优化方案三

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

大批量插入数据

如果一次性需要插入大批量数据 ( 比如 : 几百万的记录 ) ，使用 insert 语句插入性能较低，此时可以使 用MySQL 数据库提供的 load 指令进行插入。操作如下：

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数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' ;

主键优化

1). 数据组织方式

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

(index organized table IOT) 。

2). 页分裂

页可以为空，也可以填充一半，也可以填充 100% 。每个页包含了 2-N 行数据 ( 如果一行数据过大，会行溢出) ，根据主键排列。

主键顺序插入效果

从磁盘中申请页， 主键顺序插入。

第一个页没有满，继续往第一页插入

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

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

主键乱序插入效果

加入 1#,2# 页都已经写满了，存放了如图所示的数据

此时再插入 id 为 50 的记录，我们来看看会发生什么现象

索引结构的叶子节点是有顺序的。按照顺序，应该存储在 47 之后。

但是 47 所在的 1# 页，已经写满了，存储不了 50 对应的数据了。 那么此时会开辟一个新的页 3# 。

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

移动数据，并插入 id 为 50 的数据之后，那么此时，这三个页之间的数据顺序是有问题的。 1# 的下一个页，应该是3# ， 3# 的下一个页是 2# 。 所以，此时，需要重新设置链表指针。

3).页合并

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

当我们继续删除 2#的数据记录。

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

删除数据，并将页合并之后，再次插入新的数据 21 ，则直接插入 3# 页

主键优化具体内容

满足业务需求的情况下，尽量降低主键的长度。

插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键。

尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。 因为身份证号是无序的。

业务操作时，避免对主键的修改。

order by优化

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort

buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index ，不需要

额外排序，操作效率高。 

group by优化

limit优化

select * from tb_user limit 0,10;
表示从0开始选择，后面的10条记录

参考链接：sql语句中limit的用法，limit分页查询语句以及总结(一)_sql limit分页-CSDN博客

count优化

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

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

如果说要大幅度提升 InnoDB 表的 count 效率，主要的优化思路：自己计数 ( 可以借助于 redis 这样的数 据库进行, 但是如果是带条件的 count 又比较麻烦了。

update

总结：