sql优化

一、插入优化

 

insert优化

1、批量插入（最多插入1000条以内）。

2、手动提交事务。

3、主键顺序插入。

 

如果一次性需要插入大批量数据，使用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 '表' fields terminated by ' , ' lines terminated by '/n' ;

 

 

二、主键优化

数据组织方式

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

 

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

页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），innoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

   MERGE_THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

 

主键设计原则

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

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

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

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

 

三、order by优化

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

2、尽量使用覆盖索引。

3、多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则。

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

 

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

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

     using index性能高于using filesort ，所以优化order by 尽量用using index。 

 

 

四、group by 优化

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

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

 

 

五、limit优化

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

 

 

六、count优化

myisam引擎把一个表的总行数存在了磁盘上，因此执行count（*）的时候会直接返回这个数，效率很高。

innoDB 引擎就很麻烦，他执行count（*）的时候，需要把一行一行地从引擎里面读出来，然后累积计数。

优化思路：自己计数。

按照效率排序的话：count（字段）< count（主键）< count（1）≈ count（*），尽量使用count(*)