Mysql-sql优化

insert优化

批量插入数据

insert into user values(wecunge001), (wecunge002), (wecunge003);

手动控制事务

start transaction;

...
...


conmmit;

主键按顺序插入（主键优化）

        防止页分裂。

        尽量降低主键的长度，插入时尽量使用自增主键，尽量避免对主键的修改。

大量数据批量插入（load）

开启批量插入

mysql –-local-infile -u root -p

set global local_infile = 1;

执行插入操作

load data local infile '/root/user.log' into user fieldsterminated by ',' lines terminated by '\n' ;

fieldsterminated：设置分隔符

order by优化

Using filesort （性能低）: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort

buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要

额外排序，操作效率高。

根据排序时使用的字段建立联合索引（遵循最左前缀法则）

尽量使用覆盖索引

字段一个升序一个降序时根据升序和降序建立索引

create index idx on user(a asc ,b desc);

asc:升序，desc:降序

在大数据量时，如果不可避免的出现Using filesor可以适当扩充缓冲区

group by优化

根据分组时使用的字段建立联合索引（遵循最左前缀法则）

limit优化

尽可能的建立覆盖索引

count优化

尽可能的使用count(*)

count(主键):

InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。

服务层拿到主键后，直接按行进行累加(主键不可能为null)

 

count(字段):

没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出

来，返回给服务层，服务层判断是否为null，不为null，计数累加。

有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返

回给服务层，直接按行进行累加。

count(数字):

InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”

进去，直接按行进行累加。

count(*):

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

按行进行累加。

效率：count(字段) < count(主键 id) < count(1) ≈ count(*)

update优化

尽量通过索引更新（防止由行锁升级为表锁）