SQL语句优化
-
插入数据优化
-
批量插入
注：通过测试，若插入一千万条数据，不使用批量插入，每条一个insert需要耗费时间10分钟左右，但若是通过批量插入耗费时间为十几秒左右 ，由此可见使用批量插入可以极大的提高性能，同时减少多次访问数据库对数据库造成的压力。
注：若在springboot或springcloud中使用批量插入，需要在写连接到数据库的配置时将批量插入的开关打开，否则即使你在使用 insert in() 时也不会使用批量插入。
如果一次性需要插入大量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load命令进行插入。
前提：本地需要插入数据的文件中文件的内容需要符合规范。每个字段使用逗号分隔。（这里的逗号可以是别的符号，但必须保持一致）
在数据库管理工具中使用批量插入的语句：
#客户端连接服务端时，加上参数 --local-infile mysql --local-infile -u root -p #设置全局参数local——infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1; #执行load指令将准备好的数据，加载到表结构中 load data local infile '/rooot/sql1.log' （文件名） into table 'tb_user'（表名） fileds terminated by '，'(设置字段间分割符) lines terminated by '\n'；（设置换行符号） -
手动提交事务
若不手动控制事务，每条sql语句都会是一个单独的事务，降低性能，而开启手动后可以多条sql语句一起提交，优化性能。
- 主键顺序插入（与b+tree结构有关，涉及到页分裂）
-
-
主键优化
-
数据组织方式
在InnoDB引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索组织表。（想想B+Tree底层叶子结点结构，
之前的文章里有，也可去百度B+Tree结构模型）
所以建议主键存储时使用自增，在符合要求的情况下，尽量降低主键的长度，且使用自增，不要使用UUID随机生成，尽量避免产生页分裂。
-
-
order by 优化
-
Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫做FileSort排序。该方法进行排序性能相对较低。
-
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
所以我们在进行优化时尽量使进行排序时使用Using index
例：
#没有创建索引时 select id,age,phone from tb_user order by age,phone （Using filesort） #创建索引 create index idx_user_age_phone_aa on tb_user（age,phone) #创建索引后，根据age，phone进行升序排序 select id,age,phone from tb_user order by age,phone （Using index) #创建索引后，根据age，phone进行降序排序 select id，age,phone from tb_user order by age desc,phone desc (Using index) #根据age，phone进行排序，一个使用升序，一个使用降序 select id，age,phone from tb_user order by age asc,phone desc； （Using filesort) #创建索引 create index idx_user_age_phone_ad on tb_user（age asc,phone desc); (创建索引时age根据升序创建，phone根据降序创建) #根据age，phone进行排序，一个降序，一个升序 select id，age,phone from tb_user order by age asc,phone desc； (Using index)- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。（前面的文章有介绍最左前缀法则）
- 尽量使用覆盖索引。
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC)
- 如果不可避免的出现filesort，大量数据排序时，可以适当增加排序缓冲区大小sort_buffer_size(默认256k)。
-
-
group by 优化
#执行分组 操作，根据profession字段进行排序 select profession，count（*） from tb_user group by profession (Using temporary 使用临时表，性能低) #创建索引 create index idx_user_pro_age_sta on tb_user(profession,age,status); #执行分组操作，根据profession字段分组 select profession，count(*) from tb_user group by profession （Using index） #执行分组操作，根据age字段分组 select age，count(*) from tb_user group by age (Using temporary 不满足最左前缀法则) #执行分组操作，根据profession进行筛选，age字段排序 select age，count(*) from tb_user where profession = ‘软件工程’ group by age；（Using index 满足最左前缀法则）- 在分组操作时，可以通过索引来提高效率。
- 分组操作时，索引的使用也是满足最左前缀法则的。
-
limit 优化
-
一个常见又非常头疼的问题就是 limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
-
通过覆盖索引加子查询的方式优化：
例：
select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id #此条sql语句如果看不懂可以先了解下多表查询中的子查询和自连接查询
-
-
count 优化
-
MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
-
InnoDB引擎就很麻烦了，他执行count(*)的时候，需需要把数据一行一行的从引擎里面读出来，然后累积计数。
-
优化思路：自己计数
-
count的几种用法：
-
count是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加一，否则不加，最后返回累计值
-
用法：count（*）、count(主键)、count（字段）、count(1)
-
count(主键)： InnoDB引擎会遍历整张表，把每一行的主键id取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。
-
count（字段）：
- 没有NOT null约束：InnoDB引擎会遍历整张表把每一行的字段都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
- 有NOT null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接进行累加。
-
count（1）：InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字1进去，直接按行进行累加。
-
count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
所以性能：count(字段)<count(主键)<count(1)≈count(*)
-
-
-
update 优化
update student set no='20000100' where id = 1 #id为主键，必然有索引，所以加的是行锁update student set no='199999987' where name = '韦一笑' #（若是name没有设置索引，那么在修改时会加表锁，降低性能）InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁