提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
SQL优化
当出现大批量插入数据
此时使用insert语句性能较低,推荐使用load语句
如何使用load?
mysql --local-infile -u -root -p
select @@localhost_infile;
查看开关是否开启set global local_infile = 1;
此时就打开了
load data local infile '文件路径' into table 表名 fields terminated by ',' lines terminated by '/n';
表示每个字段用“,”分割,每行用“/n“分割。
主键乱序插入:
此时插入50这个数据会如何操作呢?
因为50这个数据插入不进去1页,所以他会重新区开辟一个页面
将50以及其他元素写入
而此时1数据页下一个页面不再是2数据页
而是3数据页,因此,原本的指针链表会重置。
这种现象就叫做–页分裂
当页中删除记录达到MERGE_THRESHOLD(合并页阈值,可以自己设置)(默认50%)的时候,会开始寻找靠近的页,观察是否能够合并。
show index from 表名
查看总结:
同样的,group by使用索引时也要满足最左前缀法则
因为不满足最左前缀法则,所以使用了临时表,性能降低。
但是,在分组前方,加一个‘过滤’where profession='软件工程'
我们可以看到,索引正常使用马,没有使用到临时表。
如索引为idx_user_pro_age_stat
,则句式可以是select ... where profession order by age
,这样也符合最左前缀法则
常见的问题如limit 2000000, 10
,此时需要 MySQL 排序前2000000条记录,但仅仅返回2000000 - 2000010的记录,其他记录丢弃,查询排序的代价非常大。
优化方案:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化
– 此语句耗时很长
select * from tb_sku limit 9000000, 10;
– 通过覆盖索引加快速度,直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
– 下面的语句是错误的,因为 MySQL 不支持 in 里面使用 limit
select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
– 通过连表查询即可实现第一句的效果,并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;
MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高(前提是不适用where);
InnoDB 在执行 count(*) 时,需要把数据一行一行地从引擎里面读出来,然后累计计数。
优化方案:自己计数,如创建key-value表存储在内存或硬盘,或者是用redis
count的几种用法:
各种用法的性能:
按效率排序:count(字段) < count(主键) < count(1) < count(*),所以尽量使用 count(*)
对于update优化,主要为防止行级锁升级为表级锁
我们在第一个客户端开启事务,更新id为1 的这行字段,此时,在我们未提交事务的时候,就把id为1这一行锁住了。
其他修改无法修改成功这一行,但是对其他行没有修改限制
因为我们name字段没有索引,所以把整张表都锁住了,其他修改语句无法成功。
因此我们在更新的时候,要根据索引字段进行更新。
在InnoDB的行锁时针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行级锁升级为表级锁。