Mysql-SQL优化

1：插入数据优化

insert：建议批量插入、手动控制事务、主键顺序插入（防止页分裂））

大批量插入：开启参数local infile，执行sql：load data local infile '/Users/jun.zhao/code/load.sql' into table user fields terminated by ',' lines terminated by '\n';

2：主键优化

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

• 满足业务需求的情况下，尽量降低主键的长度（聚集索引一个，二级索引有多个）；
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键（防止页分裂）；
• 尽量不要使用UUID做主键或者是其他自然主键，例如身份证号（二级索引有多个）；
• 业务操作时，尽量避免对主键的修改。

    理解（页分裂，页合并）

3：order by优化

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

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

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

4：group by优化

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

5：limit优化

一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySql 排序为啥呢2000010记录，仅仅返回 2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，创建覆盖索引能够比较好的提高效率，可以通过覆盖索引加子查询形式进行优化。

select * from tb_user t,(select id from tb_user order by id limit 2000000,10) a where t.id=a.id

5：count优化

• MyISAM引擎把一个表的总行数存在了磁盘上，一次执行count(*)的时候会直接返回这个数，效率很高；
• InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行的从引擎里面读出来，然后累计计数。

count 的几种用法

• count(主键)：InnoDB引擎会遍历整张表，把每一行的主键ID的值都取出来，返回给服务层，服务层拿到主键后，直接进行累加（主键不可能为null);
• count(字段)：InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层判断是否为null,不为null，计数累加。有not null约束，InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务层，服务层拿到主键后，直接进行累加;
• count(1)：InnoDB引擎会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接进行累加；
• count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接进行累加。

按照效率排序的话：count(字段)<count(1)=(约等于)count(*) 所以尽量使用count(*)

6：update优化

update student set no='2000100100' where id=1;

update student set no='1000010000' where name='张三';

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