SQL优化

插入数据

批量插入

insert into tb values(x,x),(x,x),(x,x)

手动提交事务

start transaction;

insert into tb values(x,x),(x,x),(x,x);

insert into tb values(x,x),(x,x),(x,x);

commit;

主键顺序插入

大批量插入数据

insert语句插入性能较低，可使用MYSQL数据库提供的load指令进行插入

客服端连接服务器时加上参数--local-infile

mysql --local-infile -u root -p

设置全局参数local-infile=1，开启从本地加载文件导入数据开关

set globel local_infile=1;

执行load指令将准备好的数据，加载到表结构中。

load data local infile '/root/sql1.log' into table 'tb' fields terminated by ',' lines terminated by '\n';

select @@local_infile;

主键优化

数据组织方式

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

页分裂

当插入一行记录，每页包含2——N数据，根据主键排序

页合并

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

主键设计原则

尽量降低主键长度

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

尽量不要使用UUID做主键或其它自然主键。

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

order by优化

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

2) Using index：有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高。

1）根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则

2)尽量使用覆盖索引

3）多字段排序，一个升一个降，注意联合索引创建规则

4）不可避免出现filesort，大数量排序，可适当增大排序缓冲区大小sort_buffer_size(默认256K)

group by优化

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

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

limit优化

limit 2000000,10，此时MYSQL排序前2000010记录，仅返回2000000~1000010记录，其它记录丢弃，查询代价太大。

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

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

count优化

MYISAM引擎吧一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；

InnoDB引擎执行count（*）的时候，需要吧数据一行一行地从引擎里面读出来，然后累积计数。

效率：count(字段)<count(主键id)<count(1)≈count(*),尽量使用count(*）

update优化

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

尽量根据主键/索引字段进行数据更新。