(三)、SQL优化
1.SQL优化_插入数据
(1).建议批量插入
相对于单条SQL语句地插入，我们每次添加地时候都需要连接初始化等操作，这些操作也是消耗时间的。所以我们建议使用批量插入的语句可以降低这些消耗，建议批量插入的数据不超过1000条。
(2).建议手动提交事务
因为相对于自动提交事务而言，我们每次编写一个数据库语句都要进行数据的提交，这样来回的事务提交也是消耗时间的，所以我们建议手动提交事务。
start transaction'; insert into tb_user values(1,'Tom'); ... commit;
(3).建议主键顺序插入
相对于主键乱序插入，主键顺序插入我们进行遍历的时候顺序查找效率更高。
(4).大批量插入数据(三部曲)
如果一次性需要插入大批量数据(2000+)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。
将磁盘文件的信息加载到我们的MySQL中，每个字段用逗号分割且每行的最后一个字段不用加逗号但要换行。
- 第一步: 客户端连接服务端时
加上参数: --local-linfile
mysql --local-linfile -u root -p
- 第二部:设置全局local_linfile为1
设置全局参数开启从本地加载文件都如数据的开关
set global local_infile=1;
- 执行load指令将准备号的数据，加载到表结构中
linux系统下需要使用 '/'。window下需要使用 '//' 指向路径
load data local infile '路径/文件名.log' into table 表名 fields terminated by ';' lines terminated by '\n';
(5).测试大批量插入数据
1.表的框架结构
create table tb_user( id int(4) not null auto_increment, username varchar(50) not null, password varchar(50) not null, name varchar(20) not null, birthday date default null, sex char(1) default null, primary key(id), unique key `unique_user_username` (username) );
# 1. 服务端连接 mysql --local-infile -u root -p # 2. 查看我们是否自动开启 select @@local_infile; +----------------+ | @@local_infile | +----------------+ | 1 | +----------------+ -- 3.加载test_load.sql文件的数据，每个字段,分割，每一条用换行 load data local infile '/home/test_load.sql' into table tb_user fields terminated by ',' lines terminated by '\n'; -- 4.查询是否插入成功 ⭐ select *from tb_user; +----+-------------+-------------+----------+------------+------+ | id | username | password | name | birthday | sex | +----+-------------+-------------+----------+------------+------+ | 1 | sdsdsdsdsd | sddsdsdsds | sdsdsds | 2001-12-17 | | | 2 | sdsdsdsdsd2 | sddsdsdsd2s | sdsdsds2 | 2001-12-17 | | +----+-------------+-------------+----------+------------+------+
中文插入不进去:
2.SQL优化_主键优化
(1).数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。
(2).页分裂_乱序插入
页可以空，也可以填充一半，也可以填充100%.每个页面包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
主键乱序插入的时候，会出现页分裂。
(3).叶合并_顺序插入
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。(比如说我们常见的主键递增的时候，删除某一行之后，主键不会替换原来的主键值，而是继续累加)。
当页中删除的记录达到 50%，innodb会开始寻找最靠近的页(前或后)看看是否可以将两个页合并并以优化空间使用。
(4).主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
- 插入数据时，尽量选择顺序插入，选择使用Auto_increment自增主键。
- 尽量不要使用UUID做主键或者其他自然主键，如身份证号等。
3.SQL优化_order by优化
(1). order by优化
- using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区 sort buffer中完成排序操作，
所有不是通过索引直接返回排序结果的排序都叫FileSort排序。 - using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index,不需要额外排序，
操作效率高。
create index idx_user_age_phone on tb_user(age,phone); -- 1.符合最左前缀法则: 两面都升序:Using index ⭐(除了这个效率高) explain select id,age,phone from tb_user order by age,phone; -- 2.符合最左前缀法则: 前面升序,后面降序->Using index; Using filesor explain select id,age,phone from tb_user order by age ,phone desc; -- 3.符合最左前缀法则: 前面降序后面升序 ->Using index; Using filesor explain select id,age,phone from tb_user order by age desc,phone; -- 4.符合最左前缀法则: 前面和后面都降序->Using index; Using filesor explain select id,age,phone from tb_user order by age desc,phone desc; -- 违背最左前缀法则4种情况全是 :Using index; Using file explain select id,age,phone from tb_user order by phone,age; -- 再次创建排序索引 create index idx_user_age_phone_ad on tb_user(age asc,phone desc); -- 假如再次查询，那么发现变成 Using index explain select id,age,phone from tb_user order by age ,phone desc;
(2).order by 优化总结
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序，一个升序一个降序，此时需要注意联合索引再创建时的规则(asc/desc)
- 如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size (默认256k)。
4.SQL优化_group by优化
(1).group by介绍
- 在分组操作时，可以通过索引来提高效率。
- 在分组操作时，索引的使用也是满足最左前缀法则的。
(2).group by示列
-- 1.先把所有的索引全部删除，留一个主键索引 +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -- 2.查看无索引状态下 执行计划 (效率偏低Using temporary) explain select profession,count(*) from tb_user group by profession; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 | Using temporary; Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ -- 3.创建联合索引 执行计划 (效率偏高⭐) create index idx_user_pro_age_sta on tb_user(profession,age,status); explain select profession,count(*) from tb_user group by profession; +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tb_user | NULL | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 24 | 100.00 | Using index | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+-------------+ -- 4.未使用最左前缀法则(执行计划效率偏低) explain select age,count(*) from tb_user group by age; +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+ | 1 | SIMPLE | tb_user | NULL | index | idx_user_pro_age_sta | idx_user_pro_age_sta | 54 | NULL | 24 | 100.00 | Using index; Using temporary; Using filesort | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+----------------------------------------------+ -- 5. 使用最左前缀法则 (执行效率偏高⭐) explain select age,count(*) from tb_user where profession ='软件工程' group by age; +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_sta | idx_user_pro_age_sta | 47 | const | 4 | 100.00 | Using where; Using index | +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+------+----------+--------------------------+
5.SQL优化_limit 优化
(1).limit介绍
首页=(页码-1)*10。
一个常见又非常头疼的问题就是 limit 200000,10; 此时需要MySQL排序前2000010记录，仅仅返回 2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案: 主键排序索引+子查询
(2).具体优化
--1.当查询大数据的时候，排序会占用很长的时间 耗时: 19.86s select *from tb_user limit 100000,10; 优化方案: 子查询+索引 (1)首先查询出id,根据我们的主键索引, select id from tb_user order by id limit 10000,10; (2).然后进行子查询的操作 select t_u.* from tb_user t_u,(select id from tb_user order by id limit 10000,10) t2_u2 where t_u.id=t2_u2.id;
6.SQL优化_count 优化
(1).count介绍
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高。
- InnoDB 引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累计计数。
优化思路: redis计数器。
(2).count地几种用法
- count()是一个聚合函数，对于返回地结果集，一行行地判断，如果count函数地参数不是NULL,累计值就加1，否则不加，最后返回累计值。
count(*): innodb引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。count(主键):innodb引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加。count(字段):
- 没有not null约束:innodb引擎会遍历整张表把每一行字段都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。
- 有not null约束:innodb引擎会遍历整张表把每一行的字段都取出来，返回给服务层，直接按行进行累加。
count(1): innodb引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
效率排序: count(字段)<count(主键 id)<count(1)≈count(*),所以尽量使用count(*)。
7.SQL优化_update优化
(1).update优化_介绍
当我们更新的时候，where后面的判断条件不是索引字段，那么innodb会转变为表锁。如果判断条件是索引字段那么innodb会转化为行锁。
suoy
(2).update优化_示列
- innodb支持行锁，所以能够同时对一张表的不同行进行修改。
- 假如我们更新的时候的判断条件是非索引字段，那么会变成表锁，锁整张表。只要当这个表锁释放后其他数据才能继续改变。
注意:我们在更新数据的时候，所以条件一定要是索引字段，并且该索引字段不能失效，如果失效行锁就会升级为表锁。
