阅读量:0
1、插入数据
· insert优化
批量插入：
insert into tb_user values(1,'tom'),(2,'cat'),(3,'jerry');
如果插入数据过大，可以将业务分割为多条insert语句进行插入。
手动提交事务：
start transaction;
insert into tb_user values(1,'tom'),(2,'cat'),(3,'jerry');
insert into tb_user values(4,'tom'),(5,'cat'),(6 ,'jerry');
commit;
主键顺序插入：
主键乱序插入：8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入：1 2 3 4 5 7 8 9 15 21 88 89
取决于MySQL的数据组织结构，主键顺序插入的性能要优于主键乱序插入。
· 大批量插入数据
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下： 
#客户端连接服务端时，加上参数 - local-infile
mysql --local-infile -u -p
#设置全局参数local_file为1，开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fileds terminated by ',' lines terminated by '/n'
select @@local_infile; +----------------+ | @@local_infile | +----------------+ | 0 | +----------------+ 1 row in set (0.00 sec) set global local_infile=1; Query OK, 0 rows affected (0.00 sec)2、主键优化
· 数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）
· 页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排序。
页分裂
· 页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录达到MEGRE_THRESHOLD(合并页的阈值，可以自己设置，默认为页的50%)，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。 
页合并
· 主键的设计原则
1、满足业务需求的情况下，尽量降低主键长度（如果主键较长，二级索引比较多，会占用大量磁盘空间，在搜索时耗费大量磁盘IO）。
2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
3、尽量不要使用UUID做主键或其他自然主键，如身份证号。（每一次生成的UUID是无序的，插入时可能出现乱序，从而导致页分裂）
4、业务操作时，避免对主键的修改。
3、order by优化
1、Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
2、Using Index：通过有序索引顺序扫描直接返回有序数据，种情况即为Using Index，不需要额外排序，操作效率高。
explain select id,age,phone from tb_user order by age; explain select id,age,phone from tb_user order by age,phone;
可以看到额外信息显示的是Using filesort，效率相对较低。需要建立索引提高效率。
create index idx_age_phone on tb_user(age,phone); explain select id,age,phone from tb_user order by age; explain select id,age,phone from tb_user order by age,phone;
explain select id,age,phone from tb_user order by age desc,phone desc; #也走索引，但是走反向全表索引 explain select id,age,phone from tb_user order by phone,age; #违背了最左前缀原则，phone走索引，age不走 explain select id,age,phone from tb_user order by age asc,phone desc;， #也会出现一个走索引，另一个不走，这是因为默认是按照升序查询的，降序需要额外查询此时我们可以建立一个联合索引 create index idx_age_phone_ad on tb_user(age asc,phone desc);
总结：
1、根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
2、尽量使用覆盖索引。
3、多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
4、如果不可避免的的出现filesort，大量数据排序时，可以适当增大排序缓冲区sort_buffer_size（默认256k）
4、group by优化
drop index idx_age_phone on tb_user; drop index tb_user_age_gender_pro on tb_user; explain select profession,count(*) from tb_user group by profession; #Extra:Using temporary用到了临时表，性能较低 #创建索引 create index tb_user_age_gender_pro on tb_user(age,gender,profession); explain select profession,count(*) from tb_user group by profession; #Extra:Using index及Using temporary原因在于违背了最左前缀原则 #根据age进行分组 explain select age,count(*) from tb_user group by age;在分组操作时，可以通过索引来提高效率，并且索引的使用也需要满足最左前缀法则。
5、limit优化
select * from staff_table limit 3; select * from staff_table limit 3, 3; select * from staff_table limit 6, 3; #如果数据库数据量过大，查询页数越大所耗费的时间就越长而且MySQL所需要排序的数据量也比较庞大，而我们仅仅需要三行数据，其他记录丢弃，查询排序的代价很大。优化方案：通过覆盖索引加子查询的形式来优化
select * from staff_table limit 3; select * from staff_table limit 3, 3; select * from staff_table limit 6, 3; #如果数据库数据量过大，查询页数越大所耗费的时间就越长 show index from staff_table; alter table staff_table add constraint staff_table_id primary key(id); select s.* from staff_table s ,(select * from staff_table limit 6, 3) a where s.id=a.id; #我们可以把select * from staff_table limit 6, 3的查询结果看成一张表6、count优化
· MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，而InnoDB引擎相对比较麻烦，他执行count(*)的时候，需要把数据一行一行的从引擎里面读出来，然后累积计数。
优化思路：自己计数
· count的几种用法
1、count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。
2、用法：count(8)、count(逐渐)、count(字段)、count(1)。
count(主键):InnoDB引擎会遍历整张表，帮每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。
count(字段):
没有not null约束：InnoDB会遍历整张表把每一行的字段值都提取出来，返回服务层，服务层判断是否为null，不为null，计数累加。
有not null约束：InnoDB会遍历整张表把每一行的字段值都提取出来，返回服务层，直接按行进行累加。
count(1):InnoDB引擎遍历整张表，但不取值。服务器对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(*):InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
按照效率排序，count(*)效率更高，count(字段)效率最低，所以尽量使用count(*)。
7、update优化
我们在使用UPDATE更新语句更改表中数据时，可能会导致表中产生行级锁或者是表级锁。
UPDATE语句的优化就是为了避免表中出现表级锁，从而影响并发的性能。
当UPDATE语句更新表数据时，WHERE条件使用的是索引字段，那么此时会出现行级锁，只是锁住这一行数据，对表中其他的数据没有任何影响，性能最高，但是当WHERE条件使用的不是索引字段时，此时就会出现表级锁，只有当UPDATE语句的事务提交完毕，表级锁才会释放，大大影响并发的性能。
