SQL经典题目
摘要：涉及的表 drop database exercise; create database exercise default charset utf8; use exercise; create table student( s_id varchar(10), s_name varchar(20),
阅读全文
posted @
2024-05-17 19:44
溪水静幽
阅读(1)
推荐(0) 编辑
年龄段分组计数
摘要：在统计人群特征数据时，需要将用户年龄分组来计数，mysql 里的 case when 可以直接实现的 SELECT age, count( * ) AS cnt FROM ( SELECT CASE WHEN info_t1 <= 20 THEN '20' WHEN info_t1 <= 30 AN
阅读全文
posted @
2023-03-25 10:25
溪水静幽
阅读(35)
推荐(0) 编辑
SQL优化--关联查询
摘要：关联查询，要确保ON或者USING子句中的列上有索引，并且在建立索引时需要考虑到关联的顺序。只需要在关联顺序中的第二个表的相应列上创建索引。例如，当表A和表B用列c关联的时候，假设关联的顺序是B、A，那么就不需要在B表的c列上建立索引。同时，确保任何的GROUP BY和ORDER BY中的表达式只涉
阅读全文
posted @
2023-03-25 08:18
溪水静幽
阅读(111)
推荐(0) 编辑
相关子查询 和 非相关子查询
摘要：相关子查询：先执行主查询，再针对主查询返回的每一行数据执行子查询，如果子查询能够返回行，则这条记录就保留，否则就不保留。 --举例1：相关子查询查询:查询所有是领导的员工信息 select * from emp e1 where exists (select * from emp e2 where
阅读全文
posted @
2022-04-21 21:03
溪水静幽
阅读(563)
推荐(0) 编辑
多版本并发控制(MVCC)
摘要：多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。 多版本并发控制 的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数
阅读全文
posted @
2021-11-14 19:46
溪水静幽
阅读(960)
推荐(0) 编辑
MySQL并发更新数据时的处理方法
摘要：执行SELECT语句时没有加锁，只有在执行UPDATE时才进行加锁的。所以才会出现并发操作时的更新数据不一致 a = SELECT * FROM table1 WHERE id=1; UPDATE table1 SET num = a.num + 1 WHERE id=1; 解决的方法可以有2种：
阅读全文
posted @
2021-11-07 20:01
溪水静幽
阅读(984)
推荐(0) 编辑
mysql 锁机制
摘要：乐观锁 数据版本（Version）记录机制实现是乐观锁最常用的一种实现方式。即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当提交更新的时候，判断数据库表对应记
阅读全文
posted @
2021-11-07 19:51
溪水静幽
阅读(68)
推荐(0) 编辑
SQL优化--琐碎
摘要：小单位大数额避免出现小数 元->分 状态值，不容易使用到索引 如性别、支付状态等状态值字段往往只有极少的几种取值可能，这种字段即使建立索引，往往利用不上。这是因为，一个状态值可能匹配大量的记录，这种情况MySQL会认为利用索引比全表扫描的效率低，从而弃用索引。 选择合适的字段类型 在创建表的时候，为
阅读全文
posted @
2021-09-20 00:18
溪水静幽
阅读(18)
推荐(0) 编辑
SQL--explain
摘要：示例 select_type: SELECT 查询的类型 SIMPLE, 表示此查询不包含 UNION 查询或子查询 PRIMARY, 表示此查询是最外层的查询 UNION, 表示此查询是 UNION 的第二或随后的查询 partitions: 匹配的分区 type: 判断查询是否高效的重要依据依据
阅读全文
posted @
2021-09-10 23:29
溪水静幽
阅读(64)
推荐(0) 编辑
锁
摘要：表锁使用场景 第一种：事务更新大部分或全部数据，表比较大，如果使用默认行锁，不仅事务执行效率低，可能造成其他事务长时间锁等待和锁冲突，考虑使用表锁来提高事务的执行速度。 第二种：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚
阅读全文
posted @
2021-09-01 23:21
溪水静幽
阅读(62)
推荐(0) 编辑
SQL优化--LIKE
摘要：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。
阅读全文
posted @
2021-08-28 18:53
溪水静幽
阅读(92)
推荐(0) 编辑
SQL优化--OR、IN、NULL
摘要：or，两边条件都有索引可用 一但有一边无索引可用就会导致整个SQL语句的全表扫描 使用in代替or MySql中，IN()先将自己列表中的数据进行排序，然后通过二分查找的方式确定列的值是否在IN()的列表中，时间复杂度是O(logn)。如果换成OR操作，则时间复杂度是O(n)。对于IN()的列表中有
阅读全文
posted @
2021-08-28 18:03
溪水静幽
阅读(1291)
推荐(0) 编辑
SQL优化--union
摘要：除非确实需要服务器消除重复的行，否则一定要使用UNION ALL。如果没有ALL关键字，MySql会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。
阅读全文
posted @
2021-08-28 18:01
溪水静幽
阅读(194)
推荐(0) 编辑
SQL优化--函数
摘要：不在索引列上做任何操作：计算、函数、类型转换，这些操作会导致索引失效，进而进行全表扫描。 MIN()和MAX() 添加索引可以优化MIN()和MAX()表达式。例如，要找到某一列的最小值，只需要查询对应B-Tree索引的最左端的记录即可。类似的，如果要查询列中的最大值，也只需要读取B-Tree索引的
阅读全文
posted @
2021-08-28 17:44
溪水静幽
阅读(89)
推荐(0) 编辑
SQL优化--分解大查询
摘要：将一个大查询切分成多个小查询执行，每个小查询只完成整个查询任务的一小部分，每次只返回一小部分结果。 删除旧的数据分解 如果只用一条语句一次性执行一个大的删除操作，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源、阻塞很多小的但重要的查询。将一个大的删除操作分解成多个较小的删除操作可以将服务
阅读全文
posted @
2021-08-28 17:33
溪水静幽
阅读(278)
推荐(0) 编辑
分库分表
摘要：常见的分库分表 如果是因为表多而导致数据多，可以采用垂直方式，根据业务切分成不同的库； 方式一： 把一个实例中的多个数据库拆分到不同的实例，电商系统， DB集群中有 订单、用户、促销 三个数据库 订单节点的写入压力比较大，你这样拆也解决不了问题 如果是因为单表数据量太大，就可以采用水平方式，即把表的
阅读全文
posted @
2021-04-11 21:33
溪水静幽
阅读(102)
推荐(0) 编辑
SQL--Order by
摘要：order by使用Using index的情况 1、order by语句使用索引的最左匹配原则 2、使用where子句与order by子句条件组合满足最左匹配原则 使用索引扫描来优化排序 1、ORDER BY的索引优化。如果一个SQL语句形如： SELECT [column1],[column2
阅读全文
posted @
2021-01-06 21:32
溪水静幽
阅读(441)
推荐(0) 编辑
索引优化之一
摘要：1、索引列上不能使用表达式或者函数 select .... from t_order where to_days(out_date) - to_days(current_date) < = 30 在out_date建立了 B树索引，因为使用了函数to_days，无法走索引，改造 select ...
阅读全文
posted @
2020-12-29 21:59
溪水静幽
阅读(106)
推荐(0) 编辑
MySql日志&二进制日志binlog
摘要：MySQL服务层日志 二进制日志: 记录更改数据的语句 慢查询日志:记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询 MySQL默认不开启慢查询日志 慢查询相关的参数 log_queries_not_using_indexes：如果SQL没有使用索引，则会被记录到
阅读全文
posted @
2020-12-13 19:31
溪水静幽
阅读(183)
推荐(0) 编辑
大表和大事务的常用处理方案
摘要：影响mysql的性能因素： 超高的QPS和 TPS QPS（Queries Per Second 每秒处理的查询量） ： 假设处理一个SQL 需要 10ms , 1s 最多也就处理100个，那么QPS < = 100 ,如果 100ms处理一个呢？ 那 QPS <=10 ,可以推断出SQL的执行效率
阅读全文
posted @
2020-11-29 18:53
溪水静幽
阅读(427)
推荐(0) 编辑
