1、SQL 优化
1.1、插入数据优化
1.1.1、Insert 优化
1、批量插入
插入多条数据时，不建议利用单条的插入语句，而是下面的批量插入：
- INSERT INTO tb_name VALUES (),(),(),...;
2、手动提交事务
MySQL 默认会开启事务，但是默认每实行一次插入语句就开启和关闭一次事务，所以会有大量的事务启动关闭开销；建议利用手动提交事务
- START TRANSACTIONS;
- INSERT INTO tb_name VALUES (),(),(),...;
- INSERT INTO tb_name VALUES (),(),(),...;
- INSERT INTO tb_name VALUES (),(),(),...;
- ...
- COMMIT;
在插入数据时，建议只管次序插入主键，而不是乱序插入：
- 主键顺序插入(高效)：1 2 3 4 5 ...
- 主键乱序插入(低效)：5 3 4 2 1 ...
利用 load 指令将本地磁盘文件中的数据插入到数据库表当中：
- # 客户端连接MySQL服务器时，加上参数 --local-infile
- mysql --local-infile -uroot -p
- # 设置全局参数 local_infile = 1
- set global local_infile = 1;
- # 执行 load 指令将准备好的数据加载到表中
- load data local infile '/path_to_data' into table table_name fields terminated by ',' lines terminated by '\n';
- LOAD DATA [LOCAL] INPATH '/opt/module/data/xxx.txt' TO TABLE table_name;
- 在向分桶表 load 数据的时间，不能从 local 直接 load，而是得先上传到 hdfs 上，再从 hdfs load 到分桶表才行
1.2、主键优化
1.2.1、数据组织方式
在 InnoDB 存储引擎当中，表数据都是根据主键次序组织存放的，这种存储方式的表称为索引组织表（IOT）
之前我们在学习索引的时间知道，InnoDB 存储引擎中的索引可以分为聚集索引和二级索引，而聚集索引正是由主键构成的一颗B+Tree，它的叶子节点存储的是行数据；
接下来，我们看一下当我们往数据库表中插入数据的时间，它的流程是什么样的？
1.2.2、页分裂
page 可以填满，也可以为空，但在 InnoDB 存储引擎规定，在一个 page 中至少包含 2 行数据；
主键次序插入
当主键次序插入时，统统都非常平静：
主键乱序插入
下面，我们看一下主键乱序插入时的情况：
上面，我们的主键都是乱序插入的，可以看到，现在来了一个主键为 50 的 row，按原理它是应该放到主键为 47 的前面，主键为 23 的后面（也就是 page 1）的，但是显然 page 1 现在已经存满了，那么接下来就会发生页分裂：
起首，page 1 （因为主键为 23 的数据当前理应插入到 page 1）会把自己 50% 之后的数据移动到一个新的 page 当中，然后将要主键为 23 的新数据页添加进去，最后将原本页之间的毗连断开，重新建立页的毗连；
所以，不难想到假如是大量数据的场景下，主键乱序插入时会出现频仍的页分裂现象，性能很低；与新增数据相反的是删除数据，删除数据又会引起页归并：
1.2.3、页归并
在 MySQL 中，当删除一行记录时，实际上数据并不会被物理删除，只是记录被标记为删除并且它的空间变得允许被别的记录回收利用；
当页中被删除的记录达到了 50%，InnoDB 会开始探求最靠近的页（前或后），看看能否将两个页归并以优化空间利用；
1.2.4、主键设置原则
- 满意业务需求的情况下，只管低落主键的长度。（因为在一张表中，聚集索引只有一个，而二级索引可以有多个，假如主键很长，二级索引也有很多，那么就会在存储时会消耗大量的磁盘空间，查询时也会消耗大量的磁盘IO）
- 插入数据时，只管选择次序插入，选择利用 AUTO_INCREMENT 自增主键；（不会出现页分裂现象）
- 只管不要利用 uuid 做主键或者是别的自然主键，如身份证号；（主键长度太长，而且无序）
- 只管避免主键的修改；
1.3、order by 优化
- Using filesort：通过表的索引或全表扫描，读取满意条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作；所有不是通过索引直接返回排序效果的排序都是 FileSort 排序；
- Using index：通过有序索引次序扫描直接返回有序数据，这种情况就是 using index，不需要额外排序，操作服从高；
也就是说，我们在优化 order by 的时间，只管优化成 using index；下面我们看一条普通的 order by 语句：
可以看到，在未建立索引时，order by 语句默认走的是 filesort；下面我们为 age 字段建立一个索引：
- CREATE INDEX idx_student_no ON student(no);
注意：这里搜索时，我设置投影的字段是 id 和 no，此中 id 是主键，no 我们刚创建了索引，不能利用 select * ，因为只有利用覆盖索引（查询利用了索引，并且需要返回的列在该索引中全部能够找到对应的值）才气掷中索引，不然索引不生效；
可以看到，纵然是降序排序也是索引也是可以掷中优化的；
现在我们创建一个联合索引：idx_student_no_name，做一个测试：
可以看到，当 order by 的字段次序和联合索引相反时，只有字段 no 能被索引掷中，name 不可以，这是因为违背了最左前缀法则；继续：
可以看到，当 order by 的两个字段分别升序和降序分列时，降序的字段无法被索引掷中，这是因为：
这是因为默认创建索引时，索引字段都是按照升序举行分列的，所以我们可以根据之后的需求在创建索引时，根据排序规则举行创建：
- CREATE INDEX idx_student_no_name_ad ON student(no asc,name desc);
此时，再次查看查询筹划：
可以看到，如许就解决了字段排序规则差异的题目；
注意：
- 只有联合索引需要注意创建时的排序规则，单列索引不需要，因为单列索引默认升序，反向扫描只需要反向扫描即可。
- 假如不可避免 filesort ，大数据排序时，可以适当增长排序缓冲区的巨细 sort_buffer_size （默认 256 K）
1.4、group by 优化
这里我们同样讨论的是索引对于 group by 的影响，因为分组也是通过索引来提高查询服从的：
可以看到，在未建立索引前，利用 group by 语句时服从很低（暂时表），但是创建索引后就可以走索引了；
可以看到，这次当我们用 age 字段 group by 时，又出现了 temporary ，服从依然不够好，这是因为违背了联合索引的最左前缀法则；可当我们同时利用 profession 和 age 举行 group by 时，索引再次掷中；
但是偶然候业务逻辑不需要我们去 group by 那么多的字段怎么办？
实在，就像上面如许，我们也可以通过前面给联合索引左边的字段加个 where 条件，让它满意最左前缀法则即可；
1.5、limit 优化
在大数据场景下，好比 limit 10000000,10 ，此时 MySQL 需要排序前 100000010 条记录（存储引擎会把这 100000010 条记录返回给服务层的缓存），并返回后 10 条记录，别的扬弃，查询排序的代价非常大；这一类题目也叫做深度分页：
MySQL 深度分页是指在分页查询数据量比较大的表时，需要访问表中的某一段数据，而这段数据的位置非常靠后，需要通过较大的 offset 来获取目的数据。
阿里巴巴《Java 开辟手册》：
1.5.1、覆盖索引 + 子查询
1.5.2、inner join 延迟关联
上面这两种方式没什么区别，下面是 inner join，上面是笛卡尔积，但是因为设置的过滤条件，所以等价于一个 inner join；对于 limit 的优化原理，简单来讲就是：控制返回的总页数。
对于上面的 limit 10000000,10 来说，它会返回给服务端 10000010 条记录，然后再根据 offset 挨个扬弃前 10000000 条记录，返回给客户端剩余的 10 条记录。
可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据跟只拷贝行数据里的此中一两个列字段耗时是差异的，这就让原本就耗时的操作变得更加离谱。
因为前面的offset条数据最后都是不要的，就算将完整字段都拷贝来了又有什么用呢，所以我们可以将sql语句修改成下面如许：
- select * from tb_user where id >=(select id from tb_user order by id limit 10000000, 1) order by id limit 10;
但差异的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时照旧比较明显的。
在拿到了上面的id之后，假设这个id恰恰即是10000000，那sql就酿成了
- select * from tb_user where id >=(10000000) order by id limit 10;
关于深度分页，知乎这篇文章讲的很不错，我也是受启发于这篇文章；
1.6、count 优化
- count 是一个聚合函数，对于返回的效果集，一行一行举行判定，只有不是 NULL 才会计数（count(字段)的时间，count(*)或者count(id)依然计算null）；
- SELECT COUNT(*) FROM table_name;
- 对于 MyISAM 而言，它会把表的总行数存到磁盘上，所以不加条件直接查询 count(*) 会直接返回效果，O（1）的时间复杂度；
- 而对 InnoDB 而言，它就只能遍历整张表了，性能很低；
可以看到，假如 count(*) 的查询语句中包含 where 过滤条件，不管是 MYISAM 照旧 InnoDB ，性能都很差，所以我们需要对它举行优化：
1.6.1、count 的几种用法
对于 count，我们利用的无非就是那几种：
- count(主键)
- InnoDB 会遍历整张表，把每一行主键取出来返回给服务层，服务层拿到主键后直接举行累加（主键不大概为空）；
- count(字段)
- InnoDB 会遍历整张表，把每一行字段值取出来返回给服务层，假如字段有 not null 束缚，那么就直接按行累加；假如没有，那么就对非 null 的值举行计数
- count(1)
- InnoDB 遍历整张表，但是不取值。服务层会对返回的每一行放一个数字 1 进去，直接按行举行累加
- count(*)
- InnoDB 同样遍历整张表，但是不会把字段取出来，而是专门做了优化。服务层直接按行举行累加
按照排序服从：count(字段) < count(主键) < count(1) < count(*) ，所以只管利用 count(*)，因为数据库专门对它做了优化；
1.7、update 优化
关于 update 语句需要注意的就是，update 的条件肯定要是索引字段（好比主键），因为只有更新条件的字段是索引列才会是行锁，否则将是表锁
可以看到，当我们的更新条件是 no 字段时（不是索引列），当另一个客户端去更新数据时直接被壅闭，最后乃至超时更新失败；
所以，当我们在利用 update 语句的时间，肯定要注意只管利用索引字段做为更新条件去更新，否则就会出现行锁升级为表锁，并发性能就会低落；因为 InnoDB 的行锁是针对索引加的锁，而不是针对记录加的锁！
总结
- 插入数据
- inset 语句，大数据量（分成500~1000的记录批量插入）建议利用批量插入，而且建议手动事务（避免频仍创建销毁事务开销）、主键次序插入（避免页分裂，次序插入性能高）
- 大批量数据：load data local infile
- 主键优化
- 主键设计应只管短、次序插入（建议 auto_increment 而不是 uuid，好比身份证号，不但长度长，而且无序）
- order by 优化
- using index：直接返回数据（不需要再去服务层缓冲区排序），性能高
- using filesort：需要将查询返回的效果去服务层缓冲区去排序
- 所以在对 order by 举行优化时，实在是利用索引来举行优化的；涉及导排序的字段只管建立索引，同时注意创建索引时的升序降序题目
- 只管利用覆盖索引而不是 select *
- group by 优化
- limit 优化
- count 优化
- count(字段) < count(主键) < count(1) < count(*)
- update 优化
- update 的条件字段只管利用索引字段（只管主键），InnoDB 的行锁是针对索引加的锁，而不是针对记录加的锁！
所以，总之我们在做 SQL 优化的时间，实在基本都是在针对索引举行优化；
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
