sql优化

insert优化

 批量插入

```sql

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3, erry');

```

### 手动提交事务

```sql

start transaction;

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,Jerry');

insert into tb_test values(4,'Tom'),(5,'Cat'),(6,Jerry');

insert into tb_test values(7,Tom'),(8,'Cat'),(9,!erry');

commit;

```

主键顺序插入

```sql

# 主键顺序插入： 1，2，3，4，5，6，7....

# 主键乱序插入：1，4，2，6，9，5，8....

```

### 大批量插入数据

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

## 主键优化

### 数据组织方式

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

### 页分裂

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

>**性能损耗**：页分裂需要重新分配内存并移动数据，这是一项耗时的操作。

**空间浪费**：页分裂后，新页通常不是完全填满的，这可能导致数据碎片和空间浪费。

**树的不平衡**：频繁的页分裂可能导致B+树的结构不平衡，进而影响查询性能。

### 页合并

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

### 主键设计原则

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

2. 插入数据时，尽量选择顺序插入，选择使用AUTO INCREMENT自增主键

3. 尽量不要使用UUID做主键或者是其他自然主键，如身份证号

4. 业务操作时，避免对主键的修改。

## order by 优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引接返回排序结果的排序都叫 FileSort排序。

2. Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

可以给排序字段建索引

```sql

# 举个例子

create index idx_age_pho on tb_user(age,phone);

# 还可以为字段值定升序还是降序

create index idx_age_pho on tb_user(age asc,phone desc);

```

### 原则

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

2. 尽量使用覆盖索引。

3. 多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则 (ASC/DESC)。

4. 如果不可避免的出现filesot，大数据量排序时，可以适当增大排序缓冲区大小 sort buffer size(默认256k)。

## group by优化

1. 在分组操作时，可以通过索引来提高效率。

2. 分组操作时，索引的使用也是满足最左前缀法则。

## limit优化

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

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

```sql

explain select

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

```

## count 优化

**MyISAM**：引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高;

**innoDB引擎**：就麻烦了，它执行 count()的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路: 可以使用redis进行计数，当插入一条数据就+1，删除就-1。

### count的几种用法

count)是一个聚合函数，对于返回的结果集，一行行地判断，如果count 函数的参数不是 NULL，累计值就加1，否则不加，最后返回累计值。

**用法**: count (*) 、count (主键)、count (字段)、count (1)

## update优化

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

```sql

update tb_user set name='zhangsan' where name='lisi'

# 如果update的where条件的字段name没有加索引，此时为表锁，事务没有提交的话，其他update不能完成。

```

**后记

美好的一天，到此结束，下次继续努力！欲知后续，请看下回分解，写作不易，感谢大家的支持！！ **