Mysql知识学习

一、概述
    本文意在跟大家分享一些开发过程中遇到的常见Mysql问题和sql的优化技巧,有兴趣的朋友可以留言一起探讨进步。
 
二、Mysql常见优化技巧
1.使用联合(union)来代替手动创建的临时表
  • MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
2.优化查询语句方法
  • 在建有索引的字段上尽量不要使用函数进行操作,如在一个DATE类型的字段上使用YEAR()函数时会花费更长时间(因为会使索引不生效),如下:
           select * from users where YEAR(`created_at`) > 2019;      //会进行全表扫描
改成以下命令可以合理使用索引:  select * from users where `created_at` > ‘2019-01-01’;
  • explain select * from `order_exts_3` where `attr_name` like “%base_%”;  和
           explain select * from `order_exts_3` where `attr_name` > "base_" and `attr_name` < "base_z”; 为什么一样?    因为字符索引是逐个查询
  • explain select * from `lfq_config` where `attr` > "sms_action_" and `attr` < "sms_action_9”; 和
           explain select * from `lfq_config` where `attr` like "sms_action_%”;  为什么一样?
           因为字符索引是逐个查询,但是如果是%sms_action,则该语句没有走索引
3.对于多张大数据量的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差
  • 如果要用,一般是放到一个搜索引擎里面搜数据(如:opensearch)
4.优化字段设计
  • 数字型比字符串型索引更高效、查询更快、占用空间更小
  • 优先使用enum或set,(需要注意的是enum一旦确定,最好不要修改,否则当数据量大时,会锁表较久)
  • 避免使用null字段、text、blob,不在数据库里面存图片
5.合理使用索引
  • 索引个数最好不超过字段数20%
  • 不在索引列使用数学运算或者函数运算,如where id+1 = 10;     改成 where id = 10 -1;
  • 外键的使用会有额外开销,高并发时容易死锁
6.sql语句设计
  • 尽量拆成简单sql,简单sql缓存命中率更高,一条sql只能在一个cpu运算,减少锁表时间
  • 保持事务/DB连接短小,即用即连,尽可能少使用存储过程、触发器
  • 减少使用sql函数对结果进行处理,多个字段时,将or改成union
  • 避免负向查询,如不使用!=、<>、not in
  • sql最终都会转化为同数据类型的列值进行比较,因为sql有隐式转换
7.在数据量较大时,需要考虑可能出现的查询条件组合是否会导致慢查询等问题
  • 如:订单表,根据updated_at条件进行数据查询,但是根据created_at进行排序,这样当updated_at的顺序跟created_at时间差异较大的话,会导致明显的查询变慢
8.查询缓存
  • 定义:查询缓存为sql文本和查询结果的映射,如果第二次查询的sql和第一次查询的sql完全相同且开启了查询缓存,则第二次查询就直接从查询缓存中取结果
  • show variables like “%query_cache%”
 
三、索引专辑
1.原理
  • Innodb一般是使用B+树作为查询,索引的值一般是放在叶子节点(如果是非主键索引,则叶子节点的内容还会包括主键的值)
2.索引类型
  • 聚簇索引:主键索引,不是单独的索引类型,是一种数据存储方式,在InnoDB中,表数据文件本身就是按B+Tree组织一个索引结构,这棵树的data域保存了完整的数据记录。
  • 非聚簇索引:相对于聚簇索引来说,又称为辅助索引或者二级索引,InnoDb的二级索引data域存储的是主键的值,而不是数据
3.回表
  • 定义:回表指通过索引查到主键索引后,再次根据主键索引查询数据表的具体记录。
  • 主要应用场景:当根据某个非主键索引查询某个非索引字段时,mysql是先根据索引查询出主键索引,然后根据主键索引查处记录所在位置,从而获取到数据。(也就是说会有两次查询,第二次查询就是我们所谓的回表操作)
 
四、sql专辑
1.having子句
  • sql会在分组之前计算where子句,在分组之后计算having子句
  • 如:select user_id,count(*) as num from `orders` group by user_id having num > 10;
2.判断子句case when then
  • 多条件判断,属于select子句,case函数只返回第一个符合条件的值,剩下的case部分将会被自动忽略
  • 如:select case when age > 55 then 老人 when age > 20 then 中年 else 少年 end
3.if 条件语句
  • if(expression, true, false)
  • 如:select *,if (book_name=‘java’, ‘已卖完’, ‘有货’) as product_status from book where price = 50
4.聚合函数
  • select count(case when age > 18 then id end) as num from users;
5.子查询
6.按时间分组
  • DATE_FORMAT(date, format)函数
  • 如:select count(1) as num,DATE_FORMAT(created_at, "%Y-%m-%d %H:%i:00") as cr_time from orders where `created_at` > ‘2019-11-01 13:00‘ and status = 10 group by cr_time order by cr_time desc
 7.把查询内容当成一个表进行连表查询
  • select * from (select id, count(1) as a from B group by a order by a desc) as temp, article where temp.id = article.b_id;
 
五、binLog、redoLog和undoLog
1.binLog
  • 定义:记录所有数据库表结构变更及数据变更的二进制日志
2.redoLog
  • 将事务中操作的数据(记录的是最新的数据),备份到一个地方,通常是物理日志,记录的是数据页的物理修改
3.undoLog
  • 在操作任何数据之前,将需要操作的数据备份到一个地方,用于在事务期间别的查询使用, 一般是逻辑日志
PS:redoLog和undoLog都是事务日志(用于保证事务的一致性) 
 
 
问题讨论
1.count(*) vs count(col) vs count(1)
  • count(col),如果col是允许为null,则该命令会搜索全表,然后忽略字段为null的记录
  • 执行效率:count(主键) > count(1) > count(col),count(*)mysql有底层优化,所以如果有主键索引的时候,count(*)和count(主键差不多)

 

参考文章:

1.临时表使用场景:http://www.sohu.com/a/283283740_120047065

2.explain命令详解:https://blog.csdn.net/UFO___/article/details/80951869

3.sql常见优化技巧:https://www.cnblogs.com/ka-bu-qi-nuo/p/7396647.html

4.事物日志介绍:

Mysql知识学习

上一篇:关于MySQL死锁检测机制参数innodb_deadlock_detect设置的一点思考


下一篇:UIDynamic - 推动行为: UIPushBehavior