MySQL_SQL优化

SQL优化

插入数据优化

1. 顺序插入代替乱序插入

   data:1,2,3,4,5,6,7
   data:4,3,1,6,5,2,7
   

2. 批量插入代替单个数据插入

   INSERT INTO `test` VALUES (1000, '软件工程-1000');
   INSERT INTO `test` VALUES (10000, '软件工程-10000');
   INSERT INTO `test` VALUES (100000, '软件工程-100000');
   
   INSERT INTO `test` VALUES (1, '软件工程-1'),(10, '软件工程-10'),(100, '软件工程-100');
   

3. 手动控制事务

   start transaction;
   	INSERT INTO `test` VALUES (1000, '软件工程-1000');
       INSERT INTO `test` VALUES (10000, '软件工程-10000');
       INSERT INTO `test` VALUES (100000, '软件工程-100000');
   commit;
   

4. load 指令加载大数据量

   • 命令行

     • 客户端连接服务端时，加上参数 -–local-infile

       mysql –-local-infile -u root -p
       

     • 启动从本地加载文件开关

       select @@local_infile 查看开关是否打开
       set global local_infile = 1;
       

       

     • 执行load指令将准备好的数据，加载到表结构中

       load data local infile '文件路径' into table table_name fields terminated by ',' lines terminated by '\n' ;
       

     • 实操（注意，不要去用中文路径）

       load data local infile 'D:\\mysql\\课件\\配套资料\\sql优化\\load_1000w.txt' into table test fields terminated by ',' lines terminated by '\n';
       

主键优化

1. 刚才提到了顺序插入代替乱序插入，这是为啥尼

2. 数据组织方式：

   • innodb 主键是按顺序存放的，乱序插入会导致页分裂

   • 顺序插入

   • 乱序插入出现页分裂的问题（页的元素没有顺序插入）
     

   • 页合并
     

3. 设计主键的原则

   • 去使用自动递增（AUTO_INCREMENT）可以顺序插入，避免页分裂
   • 不要去使用 UUID,电话号码做为主键
   • 不要去修改主键

Order By优化

1. mysql 排序：

   • Using filesort：通过扫描索引或者全表，读取满足的条件行，在排序缓冲区sort buffer 中的，性能低

   • Using index：通过有序索引顺序扫描直接返回有序结果，不需要额外排序，性能高

   • 优化具体操作：

     • 不建立索引的时候
       
     • 建立索引时
       
     • 给两个字段做升序排序
     • 建立联合索引时
     • 一个字段升序，一个字段降序（这里只能使用sql进行创建，因为navicat目前还没有索引排序功能）

       create index ix_p_a on user(phone,age desc)
       

       
     • 两个字段都降序排列

2. 优化原则

   • 排序字段需要建立合适索引，多字段排序，建立联合索引
   • 一个升序一个降序，创建索引的时候要去指定排序规则
   • 如果必须使用 filesort,数据量比较大的时候，可以去增大排序缓冲区的大小 sort_buffer_size(256K)

Group by优化

1. 索引对于分组的影响
   • 没有建立索引的时候（使用临时表分组）
   • 建立索引的时候
   • 多字段分组
   • 建立联合索引后需要遵循最左匹配原则
2. 优化
   • 分组时，也要建立相关索引
   • 使用联合索引时，也遵循最左原则

limit 优化

1. 如果数据量很大的时候，limit 分页查询，越往后速度越慢，效率越低。

2. 查询 test 表中，第900万页的20条数据
   

3. 如何优化

4. limit 优化

   • 通过创建索引，覆盖索引可以提升性能
   • 通过连接查询方式，相当于把查询到的结果作为临时表在进行查询

count 优化

1. 查询表中的数据量

   select count(*) from tablename
   

2. 不同存储引擎

   • innodb count(*) 需要一行一行的从数据库读取数据，累加结果
   • MyiSAM 存储的时候存放了一个总条数的值，直接拿，效率很高。做条件查询的时候，也很慢

3. count 用法

   • count(*) 不需要取值
   • count(主键) 遍历整张表，取id 值
   • count(数字) 不需要取值
   • count(字段) 需要取值，并且还需要判断是否为空

4. 排序效率

   count(*)=count(1) > count(主键) > count(字段)
   

update 优化

1. innodb 引擎有三大特征

   • 事务
   • 外键
   • 行级锁

2. 演示注意点

   1. 如果使用id(主键操作的时候)是可以成功的

      update user set user_name = 'xx' where id = 1;
      
      update user set user_name = '3' where id = 3;
      

   2. 更新名字为曹操的这条数据，把电话号码改成1，再去更新id=4 的数据，把电话号码也改成1

      update user set phone = '1' where user_name = '曹操';
      update user set phone = '1' where id = 4;
      出现阻塞的状态
      

      

3. 优化点

   • innodb 的行锁是针对于索引加的锁，并不是记录加的锁，如果没有索引或者是索引失效，将导致行锁升级为表锁

三范式总结

1. 第一范式：有主键，具有原子性，字段不可分割
2. 第二范式：完全依赖，没有部分依赖
3. 第三范式：没有传递依赖

注意：实际开发中，不是完全遵循三范式，会经常使用冗余字段的。