MYSQL-SQL优化

SQL优化

插入数据

批量插入

insert into tb_ test values(1,‘tom’),(2,‘cat’),(3,‘jerry’);

手动提交事务而非自动

主键顺序插入

主键顺序插入高于乱序插入

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by \n';

主键优化

页分裂

页可以为空、也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。

顺序插入的话：

但是要是乱序插入！！！

以上过程，找到50%的地方，然后23 47移动到第三个页 然后重新定义指针顺序——页分裂

页合并

那么与之相对应的就是页合并

要是删除的时候，

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

知识小贴士：MERGE_THRESHOLD合并页的阈值（默认50%），可以自己设置，在创建表或者创建索引时指定。

主键设计原则

1. 满足业务需求的情况下，尽量降低主键的长度。
2. 插入数据时，尽量选择顺序插入，选择使用AUTO_ INCREMENT自增主键。
3. 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
4. 业务操作时，避免对主键的修改。

orderby优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index,不需要额外排序，操作效率高。

默认是升序排序（asc），若其中一列用到了降序排序，此列将不会用到索引。

要符合最左前缀法则、符合覆盖索引

#创建索引`
`create index idx_user_age_phone_aa on tb_user(age,phone);

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 ;（全都是降序）由于索引默认是升序的，此时会用的是Backward index scan倒序索引扫描。

group by优化

在分组操作时，可以通过索引来提高效率。
分组操作时，索引的使用也是满足最左前缀法则的，

limit优化

• 一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃,查询排序的代价非常大。
• select * 需要返回的数据很多，可能会出现回表查询，要达到覆盖索引，可以先返回id，然后根据id进行子查询，但limt不能出现在子查询里，所以可以这样写：
  select s.* from tb_sku s, (select id from tb_sku order by id limit 9000000,10) a where s.id = a.id;

count优化

count的几种用法

• count (主键)
  • InnoDB引擎会遍历整张表，把每一行的 主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。
• count (字段)
  • 没有not null约束: InnoDB引擎会遍历整张表把每一行的字 段值都取出来，返回给服务层,服务层判断是否为null，不为null，计数累加。
  • 有not null约束: InnoDB 引擎会遍历整张表把每一行的字段值都取出来, 返回给服务层，直接按行进行累加。
• count (1)
  • InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。
• count (*)
  • InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序，count(字段) < count(主键id) < count(1) ≈ count(*)，所以尽量使用count(*)。

update优化

show tables;`
`select * from course;`
`update course set name = 'javaEE' where id =1;

更新数据时一定要根据索引字段，要不然不上行锁，而是表锁

update student set no =' 2000100100' where id = 1 ;`
`update student set no = '2000100105' where name='韦一笑';

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。（如果update的内容没有索引，那么会加上表锁，而update一个加了索引的列，会加上行锁）