【mysql篇-进阶篇】SQL优化

插入数据

• insert：如果一次性向数据库插入多条数据记录，可以从以下三个方面进行优化。

• 优化方案一：
  批量插入数据：insert into 表名 values(值1,值2…),(值1,值2…),(值1,值2…);

• 优化方案二：
  手动控制事务
  start transaction;
  insert into 表名 values(值1,值2…),(值1,值2…),(值1,值2…);
  commit;

• 优化方案三：
  主键顺序插入，性能高于乱序插入。

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

• 客户端连接服务端时，加上参数 --local-infile
  mysql --local-infile -uroot -p
  
• 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
  set global local_infile=1;
  
  

• 执行load指令将准备好的数据，加载到表结构中
  load data local infile ‘/root/sql1.log’ into table 表名 fields terminated by ‘,’ lines terminated by ‘\n’ ;
  

主键优化

order by优化

• mysql的排序，有两种方式：

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

• 实例：

• （1）先查看这个表中有哪些索引；
  

• （2）根据普通字段进行查询，显示为Using filesort

• （3）创建联合索引

• （4）再次进行查询，走了索引；显示Using index
  

• （5）联合索引也要遵循最左前缀原则
  

group by优化

• （1）查看表中索引
  

• （2） 执行没有索引的字段
  

• （3）建立联合索引
  

• （4） 使用联合索引执行
  

• 分组操作进行下面的优化，来提升性能：
  （1）通过索引提高效率；
  （2）联合索引的使用满足最左前缀原则。

limit优化

• 数据量较大时，如果进行limit分页查询；在查询时，分页查询效率会越来越低。

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

count优化

• 按照效率排序：count(字段) < count(主键 id) < count(1) ≈ count(），所以尽
  量使用 count()。
  
  
• innodb与myisam的区别：

• myisam：把一个表的总行数存在了磁盘上，执行count(*)的时候，效率高；
• innodb：执行count(*)时，需要把数据一行一行的从引擎里面读出来，让然后累加计数。

update优化(避免行锁升级为表锁)

• 当执行update语句的时候，如果条件为主键索引，则是行锁。事务提交之后，行锁释放。
• 当执行update语句的时候，如果条件为二级索引，则会升级为表锁，性能降低。