一、个方插入数据优化普通插入：在平时我们执行insert语句的个方时候，可能都是个方一条一条数据插入进去的，就像下面这样。个方 复制INSERT INTO `department` VALUES(1,个方 研发部(RD), 2层), INSERT INTO `department` VALUES(2, 人事部(RD), 1层), INSERT INTO `department` VALUES(3, 后勤部(RD), 4层), INSERT INTO `department` VALUES(3, 财务部(RD), 4层),1.2.3.4.现在我们考虑以下三个方面对insert操作进行优化。 1、个方采用批量插入（一次插入的个方数据不建议超过1000条）, 执行批量插入，一次性插入的个方数据不建议超过1000条，如果要插入上万条数据的个方话，可以将其分割为多条insert语句进行插入。个方 复制INSERT INTO `department` (`id`,个方 `deptName`, `address`) VALUES (1, 研发部(RD), 2层), (2, 人事部(HR), 3层), (3, 市场部(MK), 4层), (4, 后勤部(MIS), 5层), (5, 财务部(FD), 6层);1.2.3.4.5.6.7.2、手动提交事务 因为一条一条insert插入的个方时候，如果是个方自动提交事务，我们的个方MySQL会频繁的开启、执行事务； 所以我们可以考虑在在大段insert单条插入语句执行的个方时候，用手动提交事务的方式来执行。 复制begin; INSERT INTO `department` (`deptName`, `address`)VALUES(研发部(RD), 2层),(人事部(HR), 3层),(市场部(MK), 4层),(后勤部(MIS), 5层); INSERT INTO `department` (`deptName`, `address`)VALUES(研发部(RD), 2层),(人事部(HR), 3层),(市场部(MK), 4层),(后勤部(MIS), 5层); INSERT INTO `department` (`deptName`, `address`)VALUES(研发部(RD), 2层),(人事部(HR), 3层),(市场部(MK), 4层),(后勤部(MIS), 5层); commit;1.2.3.4.5.
3、大批量插入 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。 复制-- 1、云南idc服务商首先，检查一个全局系统变量 local_infile 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的 show global variables like local_infile; -- 2、修改local_infile值为on，开启local_infile set global local_infile=1; -- 3、加载数据 /* 脚本文件介绍 : 每一列数据用","分割", 每一行数据用 \n回车分割 */ load data local infile D:\\sql_data\\sql1.log into table tb_user fields terminated by , lines terminated by \n;1.2.3.4.5.6.7.8.9.10.11.经过测试，导入100万行数据，仅仅耗时16.84s
注意事项：使用load的时候要按主键顺序插入，主键顺序插入的性能要高于乱序插入的性能。 二、主键优化在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。 我们的InnoDB存储引擎的聚集索引结果中，B+Tree的叶子结点下存储的是row，行数据，并且是根据主键顺序存放。免费源码下载所有的数据都会出现在叶子结点，而非叶子结点仅仅起到了索引的作用。
1、满足业务需求的情况下，尽量降低主键的长度 2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键 3、尽量不要使用UUID做主键或者是其他自然主键，如身份证号 4、业务操作时，避免对主键的修改 三、order by优化我们先了解两个概念，前面我们在Explatin详解文章中提到过：SQL性能分析工具Explain详解 Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。 Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高 我们对order by的优化就是尽可能优化为Using index。服务器托管 新建表：employee 复制CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `dep_id` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, `salary` decimal(10,2) DEFAULT NULL, `cus_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name_dep_id_age` (`name`,`dep_id`,`age`) ) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;1.2.3.4.5.6.7.8.9.10.不使用索引情况：
新建联合索引：name,dep_id,age 复制#创建联合索引 CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age); #查询当前索引 show INDEX from employee #删除索引 DROP INDEX idx_name_dep_id_age ON employee;1.2.3.4.5.6.7.如果order by字段全部使用升序排序或者降序排序，则都会走索引.
但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort.
如果要优化掉Using filesort，此时我们可以再创建一个联合索引，即name按倒序，dep_id按升序创建索引，就可以解决。
注意的是虽然我已经创建了覆盖这些列的联合索引 idx_name_dep_id_age2，但 MySQL 优化器仍然可能会决定使用文件排序（filesort）来执行这个顺序的排序操作。 在内存中无法容纳整个结果集时，MySQL 将结果集存储在临时文件中并对其进行排序。这并不一定意味着性能问题，但是可能会影响查询的执行时间，尤其是当处理大量数据时。 总结： 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则尽量使用覆盖索引多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）四、group by优化先删除全部的索引（保留主键id） 复制#删除索引 DROP INDEX idx_name_dep_id_age2 ON employee; #无索引情况 Explain select name,dep_id,age ,count(*)from employee group by name,dep_id,age ;1.2.3.4.5.无索引情况下，分组,出现filesort,type为All出现了全表扫描。
新建联合索引，name,dep_id，age再观察。 复制#创建联合索引 CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age); #有索引情况 Explain select name,dep_id,age,count(*)from employee group by name,dep_id,age ;1.2.3.4.5.可见用到了索引
总结： 在分组操作时，可以通过索引来提高效率分组操作时，索引的使用也是满足最左前缀法则的如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则 五、limit优化语法复习： 复制#0表示起始位置，10表示每一页展示的数据。 select * from student_info limit 0,10;1.2.这条查询执行的速度非常快，但是如果我们将起始位置设置为100000呢？ limit分页查询在大数据量的时候，查询效率同样会非常的慢，例如一个常见又非常头疼的问题就是limit 2000000,10 此时需要MySQL排序前200010条记录，仅仅返回200000-2000010的记录，其他记录丢弃，查询排序的代价非常大。 优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化 例如：利用主键id,先获取要查询的10是个id. 复制explain select * from student_info t,(select id from student_info order by id limit 2000000,10) a where t.id = a.id;1.2.3.
或者使用范围查询方式优化：
六、COUNT优化count是一个聚合函数，用于求取符合条件的总数据量。 MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count（*）的时候会直接返回这个数，效率很高。 InnoDB引擎就比较麻烦，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数。 count的几种用法： count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。 用法：count(*)、count(主键)、count(字段)、count(1)、count(0). count(主键)：InnoDB会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。 count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。 count(1)：InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。 count(*)：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。 按照效率排序:count(字段)<count(主键)<count(1)<count(*) count(*):
count(name):
count(1):
七、UPDATE优化InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。 测试1： 开启两个会话：更新student表的数据，会话1更新id为2的数据，会话2更新id为2的数据
会话1： 复制update student set name = 123 where id = 1;1.由于id有主键索引，所以只会锁id = 1这一行； 会话2: id=2,当然会立马执行结束，不用等待会话1提交事务 复制update student set name = 123 where id = 2;1.测试2： 开启两个会话：更新student表的数据，会话1更新name（name字段无索引）为2的数据，会话2更新id为2的数据 复制update student set name = 123 where name = test;1.
由于name没有索引，所以会把整张表都锁住，导致会话2等待会话1提交事务。 解决方法：给name字段添加索引 记住一点，根据索引字段去更新数据即可！（因为索引字段相当于上的行锁，非索引字段上的表锁）。 |
图片
图片
图片
图片
图片
图片
图片
图片
图片
图片
图片
图片
图片
图片
图片