MySQL |
您所在的位置:网站首页 › mysql不自动提交更新 › MySQL |
SQL优化
mysql除了可以通过创建合适的索引来进行优化,还可以通过对SQL语句进行合理的设计来优化性能 一、插入数据优化 1.Insert插入优化Insert是我们在插入数据的时候会使用到的关键字,以下是几个常见的优化手段 批量插入 #一条语句一条语句的插入 insert into tb_test values(1,'tom'); insert into tb_test values(2,'cat'); insert into tb_test values(3,'jerry'); #批量插入 insert into tb_test values(1,'tom'),(2,'cat'),(3,'jerry');如果每次只插入一条数据,插入多次,这样就需要与数据库建立多次连接,这样势必会降低效率。 此外,批量插入的数据条数也不能太多,最好控制在 500-1000 条数据。如果要插入几万条数据,建议分批次,多次批量插入。 手动提交事务 start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,Jerry"); insert into tb_test values(4,'Tom),(5,'Cat'),(6,lerry'); insert into tb_test values(7,'Tom),(8,'Cat'),(9,Jerry'); commit;如果不设置手动提交事务,那么数据库将频繁的开启事务与提交事务,性能也将降低。 主键顺序插入 主键顺序插入效率远高于主键乱序插入,详细原因将在后面主键优化的内容中讲到。 2.load指令如果一次性需 要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。操作如下: 左边是文件,右边是表结构: 第三条指令的意思是,将root目录下的 sql1.log 文件插入表 tb_user,sql1.log中的数据由 逗号“,” 分割,每行数据由换行符 “\n” 分割; 注意: 在使用load指令的时候,也需要使用主键顺序插入; 二、主键优化在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table I0T)。 如下图所示: 页分裂是主键乱序插入时会发生的现象 页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据过大, 会行溢出),根据主键排列。(页分裂的详细情况可以观看视频理解学习) 页分裂 2.页合并当删除一行记录时, 实际上记录并没有被物理删除,只是记录被标记(flaged) 为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到MERGE_THRESHOLD (默认为页的50%) , InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。(页合并的详细情况可以观看视频理解学习) 页合并 3.主键设计原则1.满足业务设计原则的情况下,尽量降低主键的长度 因为二级索引的叶子节点保存的是对应行数据的主键,如果主键长度过长,且二级索引过多,会占用过多的磁盘空间。 2.插入数据时尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。 使用顺序插入可以避免页分裂的情况 3.尽量不要使用UUID做主键,或者其它自然主键,如身份证,因为这些主键通常情况下是无序的,且长度也较长 4.在业务操作时,避免对主键进行修改,因为修改主键会修改对应的索引,代价太大。 三、order by优化 1.排序方式在mysql中有两种排序方式: Using filesort:通过表的索引或者全表扫描,读取满足条件的数据行,然后再排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,效率高。 2.举例下面我们就通过一些例子来了解这两种排序方式 首先这里有一张表,其中有主键id,和两个字段age和phone。 我们对它按照age进行排序 explain select id,age,phone from tb_user order by age;因为当前表中是没有age的索引的,所以排序使用的是全表扫描,然后在排序缓冲区sort buffer中进行排序,效率较低。 并且我们使用explain对这条排序sql进行分析后可以看到 : 这条sql没有使用索引,在extra信息处也显示我们的排序方式是filesort 按照phone进行排序同理 那么现在我们对age,和phone创建联合索引。 create index idx_user_age_phone on tb_user(age,phone);创建了联合索引之后我们在使用上面的两个排序sql,结果如下: 可以看到此时我们使用了索引 idx_user_age_phone ,并且在额外信息中显示的是using index 下图的结果显示,仍然使用了联合索引,且extra中是using index,其中的Backward index scan是指我们是根据这两个条件进行倒序的,所以要从尾向头索引(B+树的叶子节点是双向链表) 想想结果会是什么? 让我们看看以下这种情况: 先按照age升序排,再按照phone降序排 explain select id ,age,phone from tb_user order by age asc,phone desc;
可以看到在collection那一列全是A,即代表Asc 此时我们再执行这条sql语句,结果如下,它用的是我们新创建的索引,并且extra中只有 using index explain select id ,age,phone from tb_user order by age asc,phone desc;在执行查询语句时,我们通常会用到group by进行分组 首先我们可以使用explain对没有索引的group by语句进行分析 explain select profession,count(*) from tb_user group by profession;在extra中信息为Using temporary,意为使用了临时表,且type为All,做了全表扫描,这样的效率是很低的 那么如果我们创建一个合适的索引又会发生什么呢? 这里我们创建的是联合索引。 create index idx_user_pro_age_sta on tb_user(profession,age,status);再次执行上面的sql,我们可以看到Extra中的信息已经变成了Using index,这代表这条sql使用了索引,效率大大提升了。 思考一下此时会使用索引吗? 答案是我也不知道,看结果吧,具体什么原因造成的我也说不清,可以去看看关于不满足最左前缀法则也会使用联合索引的文章。大意就是因为select的列都在索引中可以获取,形成了覆盖索引,索引还是会走索引。
这个会使用联合索引吗?答案是会的,where条件和group by条件组合起来满足最左前缀法则。 这条sql意为查询0开始往后数十条的数据,查询速度很快,通常不到1秒 但是如果是下面这条呢?这就会耗时十多秒不等,这样的查询效率是很低的。 因为此时mysql会查询出9000010条数据,再选择最后10条数据。这样太笨了。 select * from tb_sku limit 9000000,10;MySQL官方为此提供一个优化方案:通过覆盖索引加子查询进行优化 #可以将上面的sql优化为两张表联查的形势进行查询 select * from tb_sku,(select id from tb_sku limit 900000,10) a where tb_sku.id = a.id;更多的优化方式可以参考这篇文章 limit优化 六、count优化 explain select count(*) from tb_user; MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;InnoDB引擎就麻烦了,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。 具体的优化思路:自己对数据行进行计数,比如使用内存级别的key-value数据库如redis进行计数,在插入时+1,删除时-1
InnoDB的三大特性:事务,外键,行级锁。 update的优化就要围绕行级锁来进行优化。 这里有一张表。 首先创建事务A update course set name = 'javaEE' where id = 1;事务A会锁住id为1的这行数据,执行sql后先不commit; 事务B也能执行成功,因为事务A锁住的是id为1的对应行数据,事务B操作的是id为4的对应行数据。 如果上面的例子中,事务B操作的数据行id为1,则事务B执行时会等待,直到事务A提交后才会执行。 3.行锁升级称为表锁的情况如果事务A执行下面这条语句,且暂时不提交 update course set name = 'javaEE' where name = 'PHP';事务B执行下面这条语句,按理来说事务A操作的是id=2的行数据,事务B操作的是id = 1 的行数据,两个事务是不会冲突的。但是实际情况是事务B将会阻塞,等待事务A提交后再执行。 原因是:事务A的sql语句中的where后面的条件字段为name,而name并没有索引,所以事务A会将行锁升级为表锁,导致整张表被锁住,事务B就暂时无法对这张表中的其它行数据进行操作。 update course set name = 'spring' where id = 1;如何解决呢?只需要对name字段添加一个索引就可以避免行锁升级为表锁了。 总结:InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁,影响并发事务性能。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |