MYSQL进阶篇-SQL优化快速查询表

MYSQL 优化

3.1插入数据

3.1.1Insert

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

优化方案一 ：批量插入数据 Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

优化方案二 ：手动控制事务

start transaction;

Insert XX

commit；

优化方案三 ：主键顺序插入，性能要高于乱序插入。

3.1.2大批量插入数据

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用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'

3.2主键优化

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

页分裂

页合并

索引设计原则：

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

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

3：尽量不要使用UUID做主键或者是其他自然主键，如身份证号。 业务操作时，避免对主键的修改。

3.3order by优化

Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。

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

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

order by优化原则

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

B. 尽量使用覆盖索引。

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

D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

3.4group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能：

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

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

3.5limit优化

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

3.6count优化

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

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

3.7update优化

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