MySQL:SQL优化

插入数据

insert

如果一次性往mysql中插入多条数据，可以使用以下几种优化方案：

1.使用批量插入数据 （适合500-1000这样子）

insert into test values(1,'1'),(2,'2'),(3,'3')..;

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' ;

测试17完成，要是使用insert大概十多分钟

 主键优化

主键顺序插入的性能是要高于乱序插入的，原因：

行数据，都是存储在聚集索引的叶子节点上的。InnoDB的逻辑结构图：

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

页分裂

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

A.主键顺序插入效果

B.主键乱序插入效果

C.页合并

索引的设置原则

• 满足业务需求的情况下，尽量降低主键的长度。
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
• 业务操作时，避免对主键的修改。

order by 优化

mysql 的排序有两种方式：

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

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

某表有phone age两个字段，且没有索引的情况下：
explain select id,age,phone from tb_user order by age ;
explain select id,age,phone from tb_user order by age, phone ;
extra都是Using filesort，因为phone和age都没有索引

创建索引后：
explain select id,age,phone from tb_user order by age;
在执行上面两个查询语句，extra变为Using index，性能提高！

对俩者进行降序排序：
explain select id,age,phone from tb_user order by age desc , phone desc ;
Extra中出现了 Backward index scan，这个代表反向扫描索引

根据phone，age进行升序排序，phone在前，age在后。
explain select id,age,phone from tb_user order by phone , age;
排序时,也需要满足最左前缀法则,否则也会出现 filesort

根据age, phone进行降序一个升序，一个降序
explain select id,age,phone from tb_user order by age asc , phone desc ;
会出现Using filesort    可以再创建一个联合索引
create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

order by优化原则:

• A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
• B. 尽量使用覆盖索引。
• C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
• D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

 group by优化

A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到）

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

select id from tb_user limt 900000,10;
select * from tb_user limt 900000,10;  前者更快

explain select * from tb_user where id in (select id from tb_user limt 900000,10);   --不可行
--可以这样
explain select a.* from tb_user a,(select id from tb_user order by limt 900000,10)b where a.id=b.id;

 count优化

select count(*) from tb_user;  在数据量大的时候也是非常耗时的

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
• InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。
用法：count（*）、count（主键）、count（字段）、count（数字）

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

update优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。

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

要避免行锁升级为表锁.