MySQL优化

1、插入数据

（1）批量插入，避免频繁的与数据库建立网络连接，sql语句如下：

insert into tab values(1,'TOM')，(2,'JERRY');

注：不建议一次插入超过1000条数据，如果插入几万条数据，需要分多次插入，详见：（2）手动提交事务

 

（2）手动提交事务，默认情况下，每执行一条sql语句都会进行一次事务提交，当需要执行多个sql语句时，可以放到一个事务中，手动提交事务，避免频繁的开启提交事务。        

 

start transaction；

        Insert into tab values(1,'TOM')，(2,'JERRY');

        Insert into tab values(1,'TOM')，(2,'JERRY');

        Insert into tab values(1,'TOM')，(2,'JERRY');

commit；

（3）主键顺序插入

主键顺序插入的性能大于主键乱序插入，详见：主键优化

 

（4）大批量数据插入（一次插入100万条记录）

使用insert插入大批量数据性能较低，通过使用load插入数据（100w条记录，insert需要10多分钟，load需要10几秒）

load可以把本地结构化文件加载到MySQL数据表中，准备工作如下：

-- 1）客户端连接服务器时，加上参数 --local-infile

mysql --local-infile -u root -p

-- 2）设置全局参数local_infile为1，开启从本地加载文件到数据库

set global local_infile=1;

-- 3）执行load命令将准备好的结构化文件加载到数据表中（也按照主键顺序的方式插入，性能更好）

load data local infile '/root/sql.txt' into table `tbl_users` fields terminated by ',' lines terminated by '\n';

 

2、主键优化

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

如果主键长度较长，二级索引占用大量的磁盘空间，在查询数据时会消耗大量的磁盘io

（2）插入数据时，尽量选择顺序插入，选择使用auto_increament自增主键

如果时顺序插入，会按照数据页的顺序写入数据，而乱序插入数据，会导致页分裂

（3）尽量不要使用uuid做主键或者其他自然主键，如身份证号（该情况类似乱序插入，且长度较长）

（4）操作时，避免对主键进行修改

 

备注：页分裂and页合并原理

3、order by优化

mysql中的排序方式：

（1）using filesort：通过表的索引或全表扫描，读取满足条件的数据后，在排序缓冲区sort buffer中完成排序

（2）using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高

create index index_age_sal on tab_user(age,sal);
explain select id,sal,age fromtab_user order byage,sal ;

查询的字段必须是索引中的字段，否则不会通过索引（index）查询，而是通过全表扫描

 

#创建联合索引，指定索引的默认排序规则（默认排序规则:asc）,后续order by时按照索引的规则排序，就可以通过索引排序

 

如果想实现“age，sal desc”排序，需要再创建一个索引：

create index index_age_sal_ad on tab_user(age asc,sal desc);

总结：

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

（2）尽量使用覆盖索引， 即：查询的字段包含于索引中

（3）多字段排序，一个升序一个降序，检查索引中的排序规则是否与之一致，如果不一致，需要重新创建索引

（4）如果不可以避免出现filesort，大数据量排序时，如果排序缓冲区满了后，会在磁盘排序，效率低。

可以适当增加排序缓冲区大小（默认256k）：

show variables like'sort_buffer_size'；

在/etc/my.cnf修改 sort_buffer_size = 2M

 

4、group by优化

主要优化索引对group by的影响

（1）在分组时，可以通过索引提高效率

（2）在分组时，索引的使用遵循最左边前缀法则

（3）尽量使用覆盖索引， 即：分组的字段包含于索引中

 

5、limit优化

在大数据量情况下：分页越靠后，查询数据耗时越多。

如：limit 2000000,10 ，此时需要排序前2000010条记录，仅返回2000000-2000010条记录，其他记录丢弃，查询排序的代价非常大

优化方案：覆盖索引+子查询，（不支持覆盖索引+in）

select t1.* from tab_usa t1 , (select id from tab_usa order by id limit 2000000,10) t2 where t1.id=t2.id;

 

6、count优化

如果存储引擎是MyISAM，表的总行数写在磁盘上，因此执行count(*)直接返回这个数，效率高（不能有where条件，否则也慢）

InnoDB引擎，执行count(*)，需要把数据一行一行从引擎中读出来，然后累积计数

优化思路：自己计数

count的几种用法：

count(主键)

InnoDB会遍历整张表，把每一行的id值取出来，返回给服务层。服务层拿到主键后，之间按行进行累加（主键不可能时null）

count(字段）

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

有not null约束，innoDB会遍历整张表，把每一行的字段都取出来，返回给服务层，服务层直接进行计数累加

count(1)

innoDB会遍历整张表，但是不取值。服务层对返回的每一行数据，放一个数字1，直接进行累加

count(*)

innoDB不会把全部字段取出来，而是专门做了优化，不取值，服务层之间按行进行累加

性能对比：

count(*) ≈ count(1) > count(主键) > count(字段）

 

7、update优化

 

案例1：在事务中提交

update emp set sal=30000 where empno=7369; 其中empno是主键

因empno=7369是主键，在其他客户端操作empno=7369所在行数据时，出现堵塞（产生了InnoDB行级锁）

 

案例2：在事务中提交

update emp set sal=30000 where ename='JONES';  其中ename没有索引

因ename没有索引，会产生表级锁，其他操作emp时，出现堵塞

 

因此，更新数据时，一定按照索引字段进行更新（即：把待更新的字段添加索引），否则会出现行级锁变成表级所，降低并发性能。

InnoDB的行级锁是针对索引加的锁，不是针对记录加的锁