【MySQL】SQL优化

上一篇索引是针对查询语句进行优化,但在MySQL中可不仅有查询语句,针对其他的SQL语句同样也能进行优化

1.插入数据

插入数据所使用的关键字为insert,SQL语句为

insert into 表名(字段1,字段2......) values(值1,值2......); 

这样插入数据一次只能插入一条数据,相对来说效率是比较低的,虽然values后面可以跟多个括号,但如果是几万,几十万条数据呢
这时候insert的性能就不能满足我们的需要了,我们需要另一条指令:load
load指令可以将本地磁盘文件的数据直接加载到数据库中

设置local_infile参数,开启从本地加载文件导入数据的开关:
  set global local_infile=1;
使用load导入数据:
  load data loacl file 文件路径 into table 表名 fields terminated by 分隔符 lines terminated by 分隔符(这里是每一行数据之间的分隔符);

2.主键优化

设计原则:

1. 尽量降低主键长度(因为二级索引叶子结点存储的是主键,所以主键过长的话也会影响查询效率)
2. 插入数据的时候尽量选择顺序插入,选择auto_increment自增主键(乱序插入会产生页分裂的情况,下面会介绍)
3. 避免对主键进行修改

在InnoDB引擎里面,表数据都是根据主键顺序组织存储的,这种存储方式的表称为索引组织表(IOT)
介绍页分裂之前先回顾下InnoDB引擎的逻辑存储结构,由大到小为:表空间->段->区->页->行
页是InnoDB磁盘管理的最小单元,一个页的大小默认为16k,64个页为一个区
假设现在插入数据是主键乱序插入,这里就只显示叶子节点了

因为B+Tree的叶子节点是有序的,所以15应该要放在页1,但现在页1满了,此时就会开辟一个新的页,然后来到页1中间的位置,把一半的数据移动到新的页中,然后15再插入到后面,页之间的指针也会改变

相对应的也有一个页合并的情况:
当我们删除一条数据的时候,这条数据实际上并没有被删除,而是会被标记为删除状态,此时这条数据占据的空间是可以被使用的,当页中的删除标记达到阈值(默认为页的50%),InnoDB引擎就会查看相邻的页是否能够进行合并,如果可以的话就会进行合并

3.order by 优化

MySQL中的排序有两种操作

• Using filesort : 通过索引或扫描读取满足条件的数据,然后在排序缓冲区完成排序操作
• Using index : 通过有序索引直接返回有序数据
  很显然Using index的效率要高
  所以order by排序尽量选择有索引的字段,但需要注意的是多字段排序的情况

1. 多字段排序也遵循最左前缀法则
2. 如果多字段排序是一个升序,一个降序,那么在创建索引的时候需要指明,否则就是Using filesort操作

4.group by优化

group by操作是使用临时表,这个操作效率比较低
group by通常是和select进行搭配使用,那么使用索引同样可以提高group by的效率,这里索引同样也遵循最左前缀法则

5.limit优化

limit需要优化的场景主要是我们需要查询的数据在数据表中靠后的位置并且整个表的数据量比较大的时候,比如limit 10000000,10这种
优化方案首先是select后面尽量不要写"*",然后是使用子查询

6.update优化

update需要注意的问题是行锁升级为表锁
行锁和表锁在这里简单介绍下,简单来说行锁就是锁住一行的数据,表锁就是锁住整个数据表的数据
在使用update进行修改数据的时候一般都会加上条件,InnoDB引擎的行锁是针对索引的,所以字段如果有索引就是行锁,否则就是表锁,并且索引不能失效,否则也会升级为表锁

SQL优化到这结束,下一篇是视图,完