SQL 优化 注：工作学习必备知识点

目录

1.1插入数据

1.2主键优化

主键设计原则：

1.3  order by优化

1.4 group by优化

1.5 limit 优化

1.6 count 优化 

1.7update优化

---

1.1插入数据

insert优化：insert into tb_ai values(1,'yom');

                   ..........;

1.1.1

批量插入：insert into_ai values(1,'yom'),(2,'qou'),(3,'awd');

手动提交事务：start transaction;

                         insert  into tb_ai values(1,'you'),(2,'qou'),(3,'awd');

                         .........;

                         commit;

主键顺序插入： 1  2   3    4     5

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

要用load操作：//客户端连接服务端，加上参数 --local infile

                        mysql --local-infile -u root -p

                        //设置全局参数local_infile为1，开启本地加载文件导入数据的开关

                       set global local_infile=1;

                        //执行load指令将准备好的数据，加载到表结构中

                        load data local infile '/root/sql1.log'into 'tb_ai' fields terminated by',' lins terminated by '\n;

         

---

1.2主键优化

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

页分裂：
页可以为空，可以填充一半，可以填充100%。每个页包含了2-N行数据，根据主键排列。

页合并：

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

当删除的记录达到默认页的一半，IoonDB会开始寻找靠近的页（前后） 看看是否可以将俩个页合并以优化空间使用。

主键设计原则：

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

插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

在进行业务操作时，避免对主键的修改。

---

1.3  order by优化

using filesort:通过表的索引或全表扫描，读取满足条件的数据行，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

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

这边大家可以试试对于age 和phone 分别进行升序和降序排序 发现会成为filesort，当然我们可以建立索引，使它成为效率高的index。

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

           尽量使用覆盖索引。

           多字段排序，一个升序，一个降序，此时需要注意联合索引在创建时的规则。

           大数据排序时，可以适当的增大缓冲区大小 sort_buffer_size(默认256K);

        

---

1.4 group by优化

show index from tb_ai;//查看索引

select profession,count(*)from tb_ai group by profession;//专业进行分组

explain select profession,count(*)from tb_ai group by profession;//查看执行计划，观看extra效率。

create index  idx_ai_pro_age_sta on tb_ai(profession,age,status);//建立联合索引

select profession,count(*)from tb_ai group by profession;//再次进行专业进行分组

explain select profession,count(*)from tb_ai group by profession;//再次查看执行计划，观看extra效率。发现成为using index。

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

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

---

1.5 limit 优化

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

---

1.6 count 优化 

myisam引擎把一个表的总行存入磁盘上，因此执行count（*）时候会直接返回这个数，效率很高。

innodb引擎十分麻烦，因为它执行count（*）时，需要把数据一行一行从引擎里面读取出来，然后累计计数。

优化思路：自己计数（笨办法）。

count的几种用法：
count（）是一个聚合函数，对于返回的结果集，一行一行的判断，如果count函数的参数不是null，累计值就加1，反之不加

用法：count（*），count（主键） count（字段） count（1）

eg：select count（*）from tb_ai;

        select count(students) from tb_ai;

注：计数不包括null；

count（主键）：会遍历整张表，把每一行的id 都取出来，返回给服务层。

count（1）：会遍历正整张表，但不取值。放一个数字‘1’，直接进行累计。

count（*）：innodb直接做了优化，不取值，直接进行按行累计。

所以在工作中尽量使用count（*）；

---

1.7update优化

避免行锁升级为表锁。

尽量根据主键/索引字段进行数据更新。

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