Mysql学习7--SQL优化

insert优化：

关键点：

1.多使用批量插入语句

例如： insert into table values(...,...),(...,...),(...,...);

2.多使用手动提交事务来进行插入数据

start transaction; insert into table values(),(); insert into table values(),(); insert into table values(),(); commit;

大批量插入数据：

使用MYSQL提供的load指令进行插入：

mysql --local-infile -u root -p #连接数据库 set global local_infile = 1 #设置全局参数，开启从本地加载文件导入数据的开关 load data local infile '/root/sql1.log' into table '' fields terminated by ',' lines terminated by '\n';

主键优化：

主键设计原则：

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

数据组织方式：

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

逻辑存储结构：

页分裂：

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

主键顺序插入：

主键乱序插入：

1

2

3

页合并：

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

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

order by 优化：

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

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

优化法则：

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

尽量使用覆盖索引。

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

如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256K）

Group by 优化：

优化思路：

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

limit优化：

常见问题是limit 20000,10 ,此时需要MYSQL排序前20010记录，仅仅返回20000 -- 20010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：

一般分页查询时，通过建立覆盖索引提高性能，然后通过覆盖索引加子查询形式优化

例如：select * from table a, (select id from table order id limit 20000,10) s where a.id =s.id

count优化：

用法：

• • count(主键）：

InndDB引擎会遍历整张表，把每一行的主键id都取出来，返回至服务层。服务层拿到主键后，直接按进行累加（主键不可能为Null)

• • count(字段）：

没有not null约束：Innodb引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加

有not null约束：Innodb引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加.

• • count（1）：

Innodb引擎会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加

• • count（*）：

Innodb引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

根据效率进行排序的话，count(*)=count(1)>count(id)>count(字段），尽量使用count(*).

update优化：

核心：

Innodb的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。（表锁则会影响并发时候的执行性能）