新手入门MySQL——篇2

SQL优化

insert优化

语法1: [批量插入]
例子1: insert into 表名 values(xx1, xx2), (xx3, xx3), (xx5, xx6)
语法2: [手动提交事务] start transaction; insert into 表名 values(),(),(); commit
语法3: [主键顺序插入]
语法4: [大批量插入数据] set global local_infile = 1; mysql--local-infile -u root -p 
load data local infile 文件 into table 表名 fields terminated by 分隔符(,|\t), lines terminated by 分隔符(\n)

主键优化

数据组织方式: InnoDb存储引擎中,表数据根据主键顺序组织存放,这种方式的表称为{索引组织表} index organized table IOT
语法1：[主键乱序——页分裂]
语法2: [页合并——merge_threshold默认50%]
语法3: [主键设计原则]
 - 满足业务需求的情况下,尽量降低主键的长度
 - 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键
 - 尽量不要使用UUID做主键或者是其他自然主键,如身份证号

order by 优化

 - using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer 中完成排序操作,所以不是通过索引直接返回排序结果的排序都叫FileSort
 - using index：通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高
1——根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
2——尽量使用覆盖索引
3——多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
4——如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)

group by 优化

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

limit 优化

使用覆盖索引+子查询

例子1: explain select * from tb_user t1, (select id from tb_sku order by id limit 2000000, 10) t2 where t1.id=t2.id

count 优化

- MyISAM引擎把一个表的总行存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高
- InnoDB引擎麻烦,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累计计数
1——count(*),总记录数;不取值,直接累加
2——count(主键),总记录数
3——count(字段),某个字段NOT NULL的个数
4——count(1|0|-1|...),查询返回的每一条记录放一个1进去,然后在服务层对数据进行累加
效率比较: count(字段) < count(主键id) < count(1) ~= count(*),尽量使用count(*)

update 优化

InnoDb针对索引加锁,不是针对记录加锁,并且该索引不能失效,否则会从行锁升级为表锁(条件一定要有索引)