MySQL 查询优化（六） 语句优化

一. 插入数据优化 1.如果有批量插入，建议使用批量插入即一条sql语句插入多条values后边拼接（建议每一批次数据不超过1000条）[减少数据库连接次数] insert into tb_test values(1,'tom'),(2,'gaochao'),(3,'jerry'); 2.建议手动提交事务 start transation； insert into tb_test values(1,'tom'),(2,'gaochao'),(3,'jerry'); insert into tb_test values(4,'kobe'),(5,'james'),(6,'trcy'); insert into tb_test values(7,'jenty'),(8,'wangming'),(9,'zuoquan'); commit; 3.主键顺序插入 [B+Tree结构导致的] 4.大批量插入数据 建议使用load 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：   load 的时候也是建议主键顺序插入，顺序插入的性能会高于乱序插入。 二.主键优化 1.数据组织方式 在InnoDB存储引擎中，表数据都是根据主键顺序组织组成的，这种存储方式的表称为索引组织表（index orgnazied table IOT）. 2.页分裂 页可以为空，也可以填充一半，也可以填充100%.每个页包含了2-n行数据（如果一行数据多大，会行溢出），根据主键排序。 主键顺序插入的情况： 乱序插入的情况：     3.页合并 当删除一行记录时，实际上记录并没有物理删除，只是被标记（flaged）为删除并且它的空间变得允许被其他记录生声明使用。 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。 Tip:MERGE_THRESHOLD:合并页的阈值，可以自己设置，在创建表或者创建索引时指定。 4.主键涉及原则： 1）.满足业务需求的情况下，尽量降低主键的长度。 2）.插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。 3）.尽量不要使用UUID做主键或者其他自然主键，如身份证号。 4）.业务操作时，避免对主键的修改。   三. ORDER BY 排序优化（这里的前提是用到了覆盖索引） ①using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。 ②using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。 Tips:Backward index scan; 都是倒叙的情况，可以理解为，从该索引的所有叶子节点，从后往前遍历就可以了。 一个正序一个倒叙的情况，是不能完全命中索引的，也就是。从数据结构角度是不可能实现的；所以第一个字段的排序走索引成功，第二个字段，是遍历的该索引的叶子节点之前的一层，读取到对应的数据之后，再排序的。 总结来说就是，如果创建联合索引时，不指定升序降序的话，默认是升序；当需要排序时，联合索引的字段，全部升序或者全部降序都是可以走全部索引的。 当需要一个升序一个降序的时候，只能命中一部分索引，此时的解决方案时按照排序规则，重新创建一个索引。 order by 排序的最终总结： 1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。 2.尽量使用覆盖索引。 3.多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）. 4.如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认为256K)。   四. GROUP BY 优化 前提：创建以profession和age的联合索引 1.以两个字段分组，满足最左前缀法则时，命中索引。 2.以age为分组条件时，不满足最左前缀法则 3.当以profession为查询条件时，在以age分组，则又可以满足最左前缀法则   五.LIMIT 优化 一个常见又非常让人头疼的问题就是LIMIT 200000,10,此时需要MySQL排序前2000010条记录，仅仅返回2000000-2000010的记录，其他的记录丢弃，查询排序的代价非常大。 优化思路：一般分页查询时，通过创建 覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询的方式进行优化。 select u.* from tab_user u,(select id from tab_user order by id limit 2000000,10 ) i where a.id =i.id;   六.COUNT优化 1.MyISANM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。 2.InnoDB 引擎比较麻烦，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数。 优化思路：自己计数 count的几种用法： 1.count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。 2.用法：count(*)、count（主键）、count(字段)、count(1) [如果某个值存在null值，去count这个字段时，count出来的总行数并不是数据表的总行数] 几种用法的分析： 1.count(主键) InnoDB 引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。 2.count(字段) 没有not null 约束：InnoDB引擎会遍历整张表把每一行的这个字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。 有not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 3.count(1) InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字1进去，直接按行进行累加。 4.count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 总结：按照效率排行的话，count(字段)   七.UPDATE优化   InnoDB的行锁时针对索引加的锁，不是针对记录加的锁，并且索引不能失效，否则会从行锁升级为表锁，大大降低性能。