SQL_optimize

SQL优化

插入数据(insert)优化

普通插入：

采用批量插入（一次插入的数据不建议超过1000条）
手动提交事务
主键顺序插入

大批量插入：

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

# 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）
mysql --local-infile -u root 
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
select @@local_infile;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '文件名' into table 'table_name' fields terminated by ',' lines terminated by '\n';

Caution

要将外部文件引入进表中时，需要注意表结构于对应文件的结构是否相同

主键优化

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT);

innodb存储引擎磁盘管理的最小单位是页。

页分裂：页可以为空，也可以填充一般，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。

Tip

一般是在无序插入数据的情况下会产生页分裂，至少包含2条数据是防止产生链表。

在插入一行无序数据时，存储引擎会将这行数据按顺序插入，找到该行顺序的前一个行数据却发现这个页的空间不够了，就会挤走该行后面的行数据并请求一个新的页去存储被挤走的行数据，这就是页分裂，会导致页空间利用不足的情况。

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

MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或创建索引时指定

Note

文字说明不够清晰明了，具体可以看视频里的PPT演示过程：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=90

主键设计原则：

满足业务需求的情况下，尽量降低主键的长度
插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号
业务操作时，避免对主键的修改

Tip

页合并时，会导致合并的两个连接的数据主键值不是连续的,这个时候我在插入一个连续于前面一个数据的主键值，

会产生页分裂吗，这个时候的页分裂又是怎么进行的呢?

order by优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

如果order by字段全部使用升序排序或者降序排序，则都会走索引，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort，如果要优化掉Using filesort，则需要另外再创建一个索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引。

总结：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

Warning

order by 要想走索引，前面select 查询的数据也得是order by 的索引中的key的其中一个且不能出现除覆盖索引外的任何一列的key,否则则不走索引。加where 可以走索引,但是还是using filesort，说明加where 对于order by的优化无效。

group by优化

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

小结

后续优化的内容就不做了，笔记网站上有，直接过到视图之后，不过还是总结一下

SQL优化大多都是围绕着索引去做的，不过在数据量极大的情况下，索引的优化有时也只能让人干瞪着眼，这时候如果是要提取有条件的数据的话只能临时建一张表了(如果以后能出现一次返回多个结果集的话就更好了)，不过如果查询的条件过多的话，这种情况也只是能缓解一下，所以会需要视图或者是临时表(个人觉得视图更好)；

如果查询的是范围查询(如limit) ，比如一共上亿条数据，可我只要其中两千万到两千五百万的数据，但这个时候数据库会把整张表都进行排序，有几种方法，一种是类似于聚合运算，就是在表中保留一个5条记录的小集合，排序更新时无非就是和这5条数据进行比较就好了，只用遍寻一遍表就好了，不过SQL没这种功能，另外不要想着将和hash关联字段做序号化处理,是比hash join快，但SQL使用的是无序集合的概念，所以这种情况也只能仅仅优化喽；

还有就是高并发账户查询的情况，SQL已经没办法达到秒级查询了，即使是主键顺序插入的情况，等待的情况都是要达到数十秒的，毕竟这种高并发的情况下,95万的数据量都需要2s,数据量大又读不进内存，所以已经不能用SQL关系型数据库了，可以用处理高并发的SPL;

看上面已经可以看出，SQL在面对高数据量，高并发的情况下，如果是简单的查询还可以应付，但面对复杂的查询语句，就已经吃不消了，接下来的视图就是很大程度在缓解这种复杂的语句查询(临时表或许也有点用处)，新出现的SPL或许也可以试试，如果java/c++学的特别好的也可以试试，毕竟这类代码可以控制计算机底层的动作，往往能跑出较好的速度，前提是你不嫌工作量大的话；

在缓解数据量大的情况下，对特定查询量次数较多的查询语句可以专门做出视图或者临时表，这两种解决的办法视图会稍微好一点，首先临时表本身都有诸多限制，他只能由创建临时表的客户端查看，在客户端没有DROP权限的情况下，如果与数据库服务器连接丢失，是无法分辨临时表与永久表的，而且临时表是临时的，在会话结束后会自动删除，所以临时表并不是一个用来优化的好选择，如果一定要创建临时表的话，可以使用Memory引擎，因为Memery是存储在内存上的，理论上讲比临时表快；另外视图，从视图中查询数据的性能并不算开，还有可能会慢，在不做任何优化的情况下就只是将查询的数据做成额外的一个虚拟表，1000万的数据中，建立一个100万数据量的视图，如果不做优化，可能也只是比在实际表中稍微快一点而已，达不到我们的目标，所以要对视图进行优化，索引优化(一般)，分区(提高性能)，缓存优化(再次查询相同的视图时，可以直接从缓存中获取到而不用在去执行查询语句)。

优化中出现的三大问题就是对数据量多，范围查询广，并发量高去做优化，实际的优化对于后面两个能做的仅仅也只是优化一下，SQL优化所能优化的东西很少了，但对于数据量多这种情况，表的优化对于这种情况的优化还是稍微比后两种情况优化的好一点；

上面几种优化大都是大体上的优化，也就是表的优化，在细一点可以对创建表时的字段类型进行优化，尽量使用TINY,SMALL,MEDIUM类型的字段，能用varchar别用char，用varchar也要合理规划空间；索引的优化，索引不用多，用的多的数据元素去建立索引，多加not null 避免查询时还要对有没有null值进行判断，long字段建立前缀索引，有些元素大多一起查询的使用联合索引，能走覆盖索引就走覆盖索引，主键值尽量别用uuid，除非这张表不是给大量客户端进行查看的，字符尽量别做主键，索引查询效率不高。

另外一提，如果想换存储引擎的话，想用MyISAM可以换成MongoDB，用Memory可以换成Redis。