mysql之SQL优化

1、插入数据

insert：

一般我们使用insert都是一条语句一条语句插入，但是如果数据量比较大的情况下，需要使用一定的优化手段进行插入。

（1）优化方案一

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

（2）优化方案二：手动事务插入

 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,'Jerry');

commit;

 （3）优化方案三：主键顺序插入大于乱序插入

在连接客户端时 使用loal-infile

mysql --local-infile -u root -p 

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

set global local_infile=1

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

load data local infile ‘D:\\cc.sql’ into table tb_user fields terminated by ',' lines terminated by '\n';

2、主键优化

主键顺序插入的性能比乱序插入的性能高

（1）索引的数据结构是B+数，主键的叶子节点中保存的是行数据，而行数据保存在以下逻辑结构图中的页中，如下所示。每一页固定是16K的数据，如果行的数据超过了此页的存储大小，就会放到下一页中，页与页之间用指针链接。

 

索引叶子节点，当插入数据时，如果第一页插入空间不够的情况下，会下放到第二页中，第二页中的数据如下：

 那当下一个数字时5怎么办呢？因为叶子节点是顺序插入的，所以肯定要插到3的后面。mysql是怎么规划的呢？这里涉及到一个概念：页分裂。

（2）页分裂

此时第一页由于空间原因5插入不进去了，所以需要重新开辟新的页3，将第一页一半的数据移动到页3，然后5插入到页3中。

此时页之间值不是顺序的，所以需要重新对指针进行设置。

 

 （3）页合并

数据被删除之后，mysql又怎么规划其存储结构呢？这里就涉及到页合并了。下图中将10删除，此存储空间并不会被删除，只是将此空间设置为删除标记并且它的空间允许其他记录声明使用。

 当删除达到此空间的MERGE_THRESHOLD（默认是50%），则会触发空间删除，并且将空间进行合并，将第3页中的数据移动到第2页中。

 （4）主键的设计原则：

1、满足业务需求，降低主键长度

2、保证主键是顺序插入，减少页分裂的操作

3、尽量不要使用uuid做主键，或者身份证号  此主键无法保证顺序性

4、避免对主键进行修改

 3、order by优化

MySQL的排序，有两种方式：

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

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

图中username被设置索引，name无索引，通过排序查询相同的值，可以知道在100w条数据中，order by username查询花费0s，order by name查询花费0.29s。说明order by尽量覆盖索引排序。

 以下图中当oder by多个字段排序时，字段肯定遵循最左前缀法则，如果可以，可以创建联合索引，提高查询效率。

 由上述的测试,我们得出order by优化原则:

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

B. 尽量使用覆盖索引。

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

 4、group by优化

分组操作，我们主要来看看索引对于分组操作的影响。图中name,birthday是联合索引，当使用birthday分组时，出现using temporary，原因是因为对于分组操作， 在联合索引中，也是符合最左前缀法则的。

 所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

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

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

5、limit优化

在100w的数据中，limit越往后，查询效率越低。

 那怎么优化上述查询呢，通过索引+子查询的方式：

6、count优化

下面图中四个用法都可以计算表的总个数，但是其效率上是有差别的，我们可以发现count(name)是最差的需要遍历全表，count(*), count(1),count(id)都会走索引，count(*)和count(1)实际上效率查不到，查询的时候不会将值返回服务层，而是直接计算按行进行计算。count(id)的区别是会将值返回给服务层，然后由服务层按行进行计算。

 

 效率上：count(name)< count(id)<count(1)~count(*)，所以尽量使用count(*)。

7、update优化

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