数据库优化---sql优化（含代码）

1.插入数据（insert优化）

1.1批量插入

insert into user values(1,"tom",23);

1.2手动提交事务

start into user values(1,"tom",23);
commit;

1.3主键顺序插入

按照由小到大自然顺序来

大批量插入数据

#客户端链接时加参数  local infile
mysql --local infile -u root -p
#开启本地加载文件的开关
set global local_infile=1;
#执行load指令，加载到表结构中
load data infile '/root/sql.log' into table 'user' files terminated by ',' lines terminated by '\n'; 

2.主键优化

数据组织方式

索引组织表：根据主键顺序存储
逻辑存储空间

页分裂

主键乱序插入，就会造成数据页中主键值大小不能满足索引使用条件（违背二分法）。页分裂的作用就是让乱序主键变成顺序。
这也是，uuid生成时无序的，所以主键最好是主键自增，或者有顺序的原因。

页合并

删除一行记录，实则记录只是被标记为删除，让它的空间允许其它记录声明使用，并非实际删除。当一个页的删除记录达到50%时，innoDB就会寻找靠近的页合并来优化空间。
MERGE_THRESHOLD:合并页阙值，自己可以在创建表或者创建索引时设定。

主键设计原则

1.尽管小的主键长度
2.使用主键自增来插入数据
3.不要使用自然主键（身份证号）

由上图就知道，查找的时候，主键太长，数字匹配浪费时间、存储空间。插入的时候，不按顺序来，那又会造成树结构的调整，降低效率。

3.order by优化

• Using filesort:同过表索引或全表扫描，读取满足的数据行，然后在排序缓冲区sort buffer 中完成排序操作------说人话就是，不通过索引返回的，就叫这个
• Using index:有序索引扫描直接返回有序数据，不需要额外排序，操作效率高

#看下是什么索引
explain select id,age,phone from user order by age,phone;
#没有就创建索引
create index idx_user_age_phone on user(age,phone);

成功后：

1.根据排序字段建立合适的索引，多字段–》遵循最左前缀法则
2.使用覆盖索引
3.多字段内排序不同，需注意联合索引在创建时的规则
4.不可避免产生filesort，大数据量排序时，适当增加缓冲区大小

4.group by 优化

例子

#创建联合索引
create index idx_user_pro_age_sta on user(profession,age,status);
#使用
explain select profession,count(*) from user group by profession; 
explain select profession,count(*) from user group by profession,age; 

1.分组操作，通过索引提高效率
2.最左前缀法则

5.limit优化

问题是：取数据中一段数据，其他不要，查询排序代价很大
优化思路：通过覆盖索引+子查询方式进行

explain select * from user u .(select id from user2 by limit 200000,10)u2 where u.id = u2.id;

6.count优化

count四种用法

• count（主键）：innoDB遍历整张表，把每行主键id取出来，返回给服务器，然后进行累加
• count(字段)：
  • 无not null— innoDB遍历整张表，把每行主键id取出来，返回给服务器，服务器判断不为null，然后进行累加
  • 有not null— 就同主键
• count（1）：innoDB遍历整张表，不取值返回给服务器，服务器对于返回的每一行放数字1进去，然后直接进行累加
• count（*）：innoDB引擎不全部取出来字段，专门做了优化，不取值，直接累加
  效率排序
  count( * ) = count(1)>count(id)>count(字段)

count（*）不同引擎效果不同：

• MylSAM引擎把一个表的总行数存在磁盘上，所以count(*)效率很高
• innoBD引擎是一行一行从引擎读出来，然后count（*）累计计数

7.update优化

由于innoDB的行锁对索引加锁，不针对记录，并且索引不能失效，否则行锁变表锁。所以尽量根据主键、索引字段进行数据更新。