MySql学习-sql优化

学习参考https://www.bilibili.com/video/BV1Kr4y1i7ru?p=89&spm_id_from=pageDriver&vd_source=c1e24194b786e20ae3c82b47a6df3723

目录

一.插入数据

        1.insert插入 

                一般优化

                大批量插入数据(使用load) 

二.主键优化

        1.数据组织方式

        2.页分裂

                (1)主键顺序插入

                (2)主键乱序插入

        3.页合并

        4.主键设计原则

order by 优化 

        1.

        2. 

        3.

        4.

总结

ground by 优化 

limit优化

count优化

        count(主键)

        count(字段)

        count(1)

        count(*)

        结论

 update优化

---

一.插入数据

        1.insert插入 

                一般优化

主键顺序插入要优于乱序插入。

                大批量插入数据(使用load) 

mysql--local-infile -u root -p;

select @@loacl_infile;

set global local_infile = 1;

load data loacl infile '文件地址' into table '表名' 
fields terminated by '每个字段分隔所有的标点符号' 
lines terminated by '\n';

                开启loacl_infile

                载入数据

二.主键优化

        1.数据组织方式

        2.页分裂

                (1)主键顺序插入

                (2)主键乱序插入

 主键递增原则，50理应在47后面，

而第一页已经满了，

对半分裂第一页

(即为取出23，47)

与50一起放入第三页，在改变双向链表的连接顺序

        3.页合并

                删除并不是真删除

 所谓被删除数据目前只是被标记为删除而已。

一个页中被标记为删除的数据超过一半就会去寻求合并

 

页合并的阈值也可以自己设置，

在创建表或者索引的时候去指定就行。

        4.主键设计原则

order by 优化 

 

age和phone没有索引，所以使用的是较为低效的filesort。

可以通过对phone和age建立一个联合索引来避免。

建立索引https://blog.youkuaiyun.com/xiangdahaodaima/article/details/126173101 

前提是覆盖索引。

        1.

 如下

        2. 

若是倒序，如下

 反向索引扫描

        3.

即出现using index,又出现using filesort;违反了最左前缀法则。

        4.

 创建索引时默认是按从小到大顺序排，所以出现倒序是要额外的排序。

可以通过创建age和phone的联合索引，但不同顺序来优化

a是小到大排

d是大到小排 

先安age排序，在安phone排序。

按照B+树的结构，

若是同为升序或同为降序，直接扫描叶子结点的双向链表返回结果就行。

而若不同，就需要额外的排序除非在创建索引的时候指定了顺序。

       查看排序缓冲区

show variables like 'sort_buffer_size';

总结

ground by 优化 

 

 using temporary,使用临时表，性能低。

排序没有走索引、使用 union 、子查询连接查询、使用某些视图等原因

（详见 internal-temporary-tables ），因此创建了一个内部临时表。

创建联合索引。

 using index.

不满足最左前缀法则。

但是走联合索引，取age值（using index），

放入中间表排序(using temparory)，得出结果，

 

有profession满足前缀法则。

limit优化

 查询的页数越大就越慢。

 

这里应该是借用了覆盖索引的思想，找到对应id在回到聚集索引查询。 

count优化

        count(主键)

 

        count(字段)

 

        count(1)

 

        count(*)

        结论

 

 update优化

 

有索引是行级锁

没索引是表锁

在开启事务中

若一个事务修改了某一值，根据该值所属字段，

若有索引，则是行级锁，其他事务可以修改该表中的其他行，

若无索引，则是表锁，在事务完成前阻塞其他事务对该表的更新行为。效率降低。