SQL优化

插入数据

如果我们需要一次性往数据库表中插入多条记录，可以从以下三个方面进行优化

insert into tb_test values(1,'tom');

insert into tb_test values(2,'cat');

insert into tb_test values(3,'jerry');

1. 优化方案一

批量插入数据

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

2. 优化方案二

手动控制事务(一条DML(增删改)语句会自动提交一次事务。)

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

取决于mysql的组织结构,主键顺序插入性能高于乱序插入

大批量插入数据

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

可以执行如下指令，将数据加载到表结构中：

-- 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
select @@local_infile
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引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

2). 页分裂

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

A. 主键顺序插入效果

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

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

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

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

B. 主键乱序插入效果

①. 加入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会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

这个里面所发生的合并页的这个现象，就称之为 "页合并"。

MERGE_THRESHOLD：合并页的阈值.

4). 索引设计原则

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

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

3.尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

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

order by优化

最左前缀法则:如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

MySQL的排序，有两种方式：

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

order by语句使用索引最左前缀或使用where子句与order by子句条件组合满足索引最左前缀。

where子句中如果出现索引范围查询会导致order by索引失效。

KEY a_b_c(a,b,c)
order by 能使用最左前缀
order by a,b
order by a,b,c
order by a desc,b desc,c desc

如果where使用索引的最左前缀定义为常量,则order by 能使用索引
where a = const order by b,c
where a = const and b=const order by c
where a = const and b > const order by b,c
where a = const order by b,c

不能使用索引进行排序
order by a asc ,b desc,c desc 排序不一致
where g = const order by b,c  丢失a索引
where a in(...) order by b,c a在范围查找

示例:

-- 单表查询优化 L334
CREATE TABLE IF NOT EXISTS article (
id INT(10) PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) NOT NULL,
category_id INT(10) NOT NULL,
views INT(10) NOT NULL,
comments INT(10) NOT NULL,
title VARBINARY(255) NOT NULL,
content TEXT NOT NULL
);
 
INSERT INTO article(author_id, category_id, views, comments, title, content) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

#1.查询category_id为1的，且comments大于1的情况下，views最多的id和author_id的信息
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;
#2.建立索引
alter table article add index idx_ccv(category_id,comments,views);
show index from article
#3.再次测试
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;
#4.重新创建索引
drop index idx_cv on article;
alter table article add index idx_cv(category_id,views);
#5.再次测试
explain select id,author_id 
from article
where category_id=1 and comments>1 order by views desc limit 1;

关联查询优化

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。

案例演示

#1.联表查询   L314
explain select * from class left join book on class.card = book.card;
#2.建立索引
alter table book add index idx_card(card); 
drop index idx_card on book
#3.测试
explain select * from class left join book on class.card = book.card;

BNLJ(Block Nested-Loop Join)算法:

BNLJ的原理是内外两层循环，对外循环中的每条记录，都要再内循环中做一次检索。

group by优化

思路和排序优化是一样的.

L376
EXPLAIN select count(*),sname from students GROUP BY sname
EXPLAIN select count(*),age from students GROUP BY age
EXPLAIN select count(*),sname from students where sname="小明" GROUP BY age
EXPLAIN select count(*),sname from students where sname="小明" and age =22 GROUP BY score
EXPLAIN select count(*),sname from students where sname="小明" and age > 22 GROUP BY score
alter table students add index idx_sas(sname,age,score);

慢查询日志

慢查询日志简介

MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。

慢查询日志使用

L370

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数。

查看是否开启：show variables like '%slow_query_log%';

开启日志：set global slow_query_log = 1;

设置时间: set long_query_time = 1;

查看时间: SHOW VARIABLES LIKE 'long_query_time%';

查看超时的sql记录日志：Mysql的数据文件夹下

5.5\Data\设备名称-slow.log

注意：非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来一定的性能影响。

count优化

select count(*) from tb_user

如果数据量很大，在执行count操作时，是非常耗时的

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

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

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

count用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法：count（*）、count（主键）、count（字段）、count（数字）

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

对SQL的优化