网站导航
> 文章列表 > 数据库MySQL —— SQL优化

数据库MySQL —— SQL优化

网友: 文章列表 2024-03-21 16:45:39

数据库MySQL —— SQL优化

 目录

一、插入数据优化

1.  插入多个数据

2.  大批量插入数据

二、主键优化

1.  页分裂

2.  叶合并

3.   主键设置原则

三、order by优化

四、group by优化

 五、limit优化

 六、count优化

七、update优化

一、插入数据优化

1.  插入多个数据

提到插入数据，数据库MySQL中我们自然联想到使用 insert语句 进行插入数据：

insert into table values(1,'');
insert into table values(2,'');
insert into table values(3,'');
... ... ...

这样每次我们只能插入一个数据，但是如果我们想要一次添加多个数据呢？

  • 批量插入：insert into table values(1,''),(2,''),(3,''),...;
  • 手动提交事务： 
    start transaction;
insert into table values(1,''),(2,''),(3,'');
insert into table values(4,''),(5,''),(6,'')
insert into table values(7,''),(8,''),(9,'');
commit;

2.  大批量插入数据

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

 load指令插入同样需要顺序插入，主键顺序插入性能高于乱序插入。

二、主键优化

首先我们需要了解在InnoDB中数据的组织方式：

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table lOT)。

此B+Tree索引结构中，叶子节点均为行数据，非叶子节点仅仅起一个索引数据的作用。

1.  页分裂

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

  • 主键顺序插入：第一页满则新建一页继续插入，页与页之间存在双向指针。

  •  主键乱序插入：已知两页已满，新插入数据会找到第一页50%位置，将超出数据信存放在新开辟的页中，然后将链表指针进行重新设置，从而导致了页分裂。

2.  叶合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到 MERGE_THRESHOLD（默认为页的50%) ,InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

知识小贴纸：MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

3.   主键设置原则

  • 满足业务需求的情况下，尽量降低主键的长度。
  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。UUID：随机字符串（不重复）
  • 业务操作时，避免对主键的修改。

三、order by优化

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  • 尽量使用覆盖索引。
  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
  • Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort 排序。
  •  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;

 由于 age 和 phone 均没有索引，索引查找效率很低。

create index idx_user_age_phone_aa on tb_user(age,phone);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 age asc,phone desc;

create index idx_user_age _phone_ad on tb_user(age asc ,phone desc);explain select id , age , phone from tb_user order by age asc,phone desc;

四、group by优化

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

explain select profession , count(*) from tb_user group by profession ;


create index idx_user_pro_age_sta on tb_user(profession , age , status);explain select profession , count(*) from tb_user group by profession ;


explain select profession , count(*) from tb_user group by age;

explain select profession , count(*) from tb_user 
where profession = '软件工程' group by age;

 五、limit优化

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * 
from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a 
where t.id = a.id;

 六、count优化

explain select count(*) from tb_user ;

如果表结构中数据量比较大，执行时间也会很长，效率比较低，这取决于InnoDB的处理方式。

  • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高。（前提条件：没有where条件语句，否则效率同样很低）
  • lnnoDB引擎同样很麻烦，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
  • 优化思路：自己计数。

count的几种用法：

  • count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count 函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。
  • 用法：count (*) 、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(主键id) < count(1)  = count(*)，所以尽量使用count(*)。

七、update优化

update student set no ='123456789' where id = 1;update student set no ='123456789' where name='张三';

InnoDB的行锁是针对于索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

  • 行锁：
  •  表锁：

执行update语句时，name字段没有索引，此时加入的表锁而不是行锁。

前事务提交，后事务才能执行。

如果我们给name字段加上索引，此时加入的就是行锁而不是表锁了，两个事务就都可以运行。

网站商业源码

网络标签：

相关问题