Mysql第六天 查询 1
其结果如下:
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
| 1 | SIMPLE | biz_pay_task | ref | i_jd_order_id | i_jd_order_id | 8 | const | 1 | |
+—-+————-+————–+——+—————+—————+———+——-+——+——-+
主要是select_type 、key 、rows、type 这几个选项
rows mysql评估的可能需要检索的数量 type对应了查询所使用的类型,比如All代表全表扫描,ref代表索引扫描,还会有范围扫描、唯一索引扫描等等。最好都能够达到ref的级别。
通常可以从如下几个方面进行考虑
没有使用LIMIT,而是查处了大量的数据,只是用了前几行 多表关联时返回全部列,这样会有很多重复列,最好明确指定 默认使用select * 尽量只返回需要的列 是否扫描了额外的记录
这个不是很好确定,通常加上合适的索引之后就能够减少扫描的数量,但是对于分组统计类的sql却不能使用索引的方式了。一般我们可以有如下的方式优化:
这个冲突放大了就是多个简单sql语句,然后在代码里计算,还是用存储过程把所有计算都完成。
以前认为数据库查询计算很快,而跟客户端之间的通信的开销是很大的。但是现在可能会越来越考虑可用性,重用性等,一个复杂查询变得不那么重要了。
每个sql的功能完全一样,但是只是完成一小部分。
最经典的使用方式是分页,也就是使用limit关键字, 可以分页查或者是分页删除。
特别是删除,因为会占用事务日志和锁,因此更有必要使用分页。我们可以用下面的伪代码来表示分页删除:
select * from Student s JOIN grade g on s.gradeId = g.id where s.name="张三"; // 可以改写为: SELECT * FROM Student s where s.name="张三"; SELECT * FROM Grade g where g.id in (#上面查出的结果#);
这样看起来一模一样,并且还会增加连接次数。但是却能带来如下的好处:
在应用层做关联,可以对数据库进行拆分,获得更好的扩展性 使用IN()代替关联查询,本身会比关联查询更高效 在应用层可以重用第一次的查询结果,比如做缓存。 查询执行基础
半双工的通信方式,决定了不能限制流量,发出请求后只能等待结果。
下面的参数能够设置接收包的大小,太小了,可能导致请求失败
通常使用mysql的客户端包,都是从mysql服务器中获取了sql中返回的所有数据,并且缓存,之后操作的都是缓存中的数据。 这样有个问题是如果结果集过大有可能内存溢出。
JDBC可以用如下的办法来不使用这种返回的方式:
stmt.enableStreamingResults(); // 类似利用mysql机制的方法还有:setLocalInfileInputStream ,可以跟LOAD DATA LOCAL INFILE一起快速插入
连接状态
+———+——+———————-+——————–+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+———+——+———————-+——————–+———+——+——-+———————–+
| 1897957 | root | 192.168.147.34:60520 | biz | Sleep | 172 | | NULL |
通过State能够看到连接线程的状态。
Sleep是线程等待客户端发送新请求
Query是正在查询,等等。
生成执行计划。
每个sql语句都可能有多种执行计划,mysql使用预判的方式来估算最小成本的计划。下面的语句可以看一下mysql的估算结果:
SELECT SQL_NO_CACHE COUNT(*) FROM biz_pay_task; SHOW STATUS LIKE Last_query_cost;
返回的是mysql认为的要做多少个页的随机查找才能完成任务。
mysql通常有如下的优化方式:
等价变换规则: 移除恒等,合并比较等等,比如 1=1 AND a 5会转化为 a 5 优化COUNT(), MIN(), MAX()
MIN(),MAX()分别对应B-Tree的索引最前与最后,基本相当于常量引用的效率了
EXPLAIN select MAX(jd_order_id) FROM biz_pay_task; // 结果: Extra : Select tables optimized away
表示启用了此项优化。
COUNT(),需要存储引擎支持,比如有的存储引擎可以直接返回这个变量,不用去数
// 查出一个订单的扩展字段之:第三方订单号。 EXPLAIN SELECT b.id, o.third_order_id FROM biz_pay_task b INNER JOIN order_snap o ON o.virtual_order_id = b.id WHERE b.id = 1;
结果:
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 8 | const | 1 | Using index |
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 61 | Using where |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
可以看到是转化为两次查询,第一次是主键查询,第二次是一个where 查询,第二个where查询的时候会直接使用o.virtual_order_id=1来进行替换。使用常量值。
另外上面说的MIN()的情况也应该属于这一种
比如Limit,到指定的位置就不往下查找了
比如下旬一个不存在的数据,从索引上就直接返回了,不会去查数据
EXPLAIN SELECT b.id FROM biz_pay_task b WHERE b.id = -1; // Extra是:Impossible WHERE noticed after reading const tables
比如NOT EXIST, LEFT JOIN, lift join再来个例子:
查询很有赠品的订单
SELECT b.order_id FROM order LEFT JOIN order_sku o ON order.order_id = o.order_id WHERE o.skuName IS NULL;
这个查询会找到第一个skuName 为NULL之后进入下一个订单,而不会全部扫描。
其实跟Java的return, break; continue这种语法有点像。
IN mysql会对其内容进行排序,使用二分查找的方式,这样比其他的数据库要好,其他基本上都是跟多个OR是等价的。
Mysql中对于关联查询的操作很简单,就是嵌套循环。即先遍历左边中符合条件的,然后根据每一个左表符合条件的去查右表中的内容。
包括子查询 也是使用的这种方式。
Mysql在执行时会把sql语句转化为执行树,是一颗左侧深度优先的数,如下图:
关联查询优化
主要是对于内联的操作。因为有很多情况内联的表的顺序不重要,因此mysql可能会改变遍历顺序优先遍历数据很少的表。
举个栗子:
EXPLAIN SELECT v.id, o.third_order_id FROM virtual_order v INNER JOIN order_snap o ON v.id = o.virtual_order_id; // 这个sql跟 order_snap o INNER JOIN virtual_order v的效果是一样的。执行的时候能够看到:
+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
| 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 344 | |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 8 | virtual_biz.o.virtual_order_id | 1 | Using index |
+—-+————-+——-+——–+—————+———+———+——————————–+——+————-+
能够看到先执行的o表, 因为mysql的优化点在于选择驱动表,其会简单的选择数据少的为驱动表。
但是如果是很多表关联的情况下, 因为组合会很多所以有可能会转为其他的方式进行选择。通常不建议关联很多表
此外我们可以指定连接的顺序,选择驱动表。 使用STRAIGHT JOIN 关键字。 这样我们可以保证驱动表是我们想要的,比如我们要尽量使得排序行为在驱动表中,这样就会使查询更快。
基于索引排序, 使用快排,如果内存不够则先对数据分块,然后每块分别排序,最后merge.
此外有两种排序算法
当不超过max_length_for_sort_data时,使用单次传输,否则时两次传输。
单次传输时新版本才有的,会加载所有的列进行排序,这样减少I/O,增加占用内存
两次传输,第一次加载排序列,排序,排好后再去拿其他数据。这样减少占用空间,但是会增加很多随机I/O。
当关联查询需要排序时,如果在驱动表上,则会先排序。
不在则会先计算关联结果,然后放到临时表中,再进行排序。
生成的执行计划是一个数据结构。
执行过程会通过api调用很多次存储引擎。
返回结果集时会判断能否缓存,如果可以会先缓存。
结果是增量返回的,因此在API端调用的时候可以设置,是否增量接收。
相关文章
- Mysql:is not allowed to connect to this MySQL server
- 【Mysql 学习】memory存储引擎
- 【Mysql 学习】mysql 的使用入门
- 分享三:mysql跨库查询
- Python查询Mysql时返回字典结构的代码
- mysql 必知必会整理—sql 简单语句[二]
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
- mysql explain介绍
- MySQL 首行查询陷阱
- MySQL运维---慢日志与备份
- MySQL数据库order by 主键(索引) 查询慢解决方案
- 【收藏】windows下 Mysql 错误 Can‘t open and lock privilege tables: Table ‘mysql.user‘ doesn‘t exist
- MySQL 性能监控4大指标——第二部分
- MYSQL子查询与连接
- 第41讲:MySQL内置的QL性能分析工具
- mysql将查询结果导出csv文件的方法into outfile
- MySQL----MySQL中因为sql模式设置引起的问题解决办法
- MySQL远程连接报错2003-cant connection to mysql server on ‘IP’(10061 unknown error)
- Mysql基础篇之一条Sql查询语句的前世今生---01
- K8S 使用NFS存储 动态创建 PVC/PV 并通过 Statefulset 部署 MySQL