Mysql数据库
文章平均质量分 79
数据库原理及Mysql使用
学而不思则忘
一切都可以
展开
-
高性能索引策略
1. 独立的列独立的列是指索引列不能是表达式的一部分,也不能是函数的参数。下面SQL查询都无法使用索引:SELECT id FROM user WHERE id + 1 = 5;SELECT ... WHERE DAYS(date) - DAYS(col) <= 10;2. 前缀索引和索引选择性有时候需要索引很长的字符列,这会让索引变得大且慢。一个策略是使用模拟哈希索引。可以索引开始的部分字符,提高查询效率,节约索引空间。但会降低索引的选择性。索引的选择性:不重复的索引值(也原创 2021-08-31 10:49:22 · 193 阅读 · 0 评论 -
MySQL数据类型
VARCHARVARCHAR用于存储变长字符串,是最常见的字符串数据类型。比定长类型更节省时间,因为它仅使用必要的空间,所以越短的字符串使用空间越少。除非MySQL表使用ROW_FORMAT=FIXED创建,每一行都会使用定长存储,就会浪费空间。VARCHAR需要使用1到2个额外字节保存字符串的长度。如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。一个VARCHAR(10)的列需要11个字节的存储空间,VARCHAR(1000)的列需要1002个字节,因为需要2个字节存储长原创 2021-07-21 11:37:27 · 100 阅读 · 1 评论 -
数据库的事务隔离级别总结
一、事务及四大特性(ACID)事务:访问并可能更新数据库中各种数据项的一个程序执行单元(unit),它通常由高级数据库操纵语言或编程语言(如SQL,C++或Java)书写的用户程序的执行所引起。当在数据库中更改数据成功时,在事务中更改的数据便会提交,不再改变。否则,事务就取消或者回滚,更改无效。原子性(Atomicity):指事务包含的所有操作要么全部成功,要么全部失败回滚。因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。一致性(Consistency):一致性原创 2021-07-20 15:38:39 · 111 阅读 · 0 评论 -
LIMIT分页优化查询
在进行分页查询的时候,通常会使用LIMIT加偏移量的办法实现,但当偏移量非常大的时候,例如:LIMIT 10000, 20这样的查询,这是MySQL需要查询10020条记录然后只返回20条,前面的10000条都将会抛弃。这样的代价就非常高。优化上述查询,有两种方案供选择:在页面中限制分页的数量优化大偏移量的性能使用索引覆盖扫描,不查询所有的列,然后根据需要进行一次关联,再返回所需的列例如下面查询:SELECT film_id, description FROM sakila.film O.原创 2021-07-19 19:18:30 · 943 阅读 · 0 评论 -
MySQL的explain命令
各列的含义如下:id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.select_type: SELECT 查询的类型.table: 查询的是哪个表partitions: 匹配的分区type: join 类型possible_keys: 此次查询中可能选用的索引key: 此次查询中确切使用到的索引.ref: 哪个字段或常数与 key 一起被使用rows: 显示此查询一共扫描了多少行. 这个是一个估计值.filte转载 2021-06-28 15:14:28 · 132 阅读 · 0 评论 -
B树和B+树的区别
1. B树m为树的阶层每个节点最多有m-1个关键字(可以存有的键值对)。根节点最少可以只有1个关键字。非根节点至少有m/2个关键字。每个节点中的关键字都按照从小到大的顺序排列,每个关键字的左子树中的所有关键字都小于它,而右子树中的所有关键字都大于它。所有叶子节点都位于同一层,或者说根节点到每个叶子节点的长度都相同。每个节点都存有索引和数据,也就是对应的key和value。2. B+树和B树的相同点:根节点至少一个元素非根节点元素范围:m/2 <= k <= m-1原创 2021-06-20 22:51:41 · 663 阅读 · 0 评论 -
关于学生课程分数的SQL查询
1. 建表有如下4张表:学生表:student(s_id,s_name,s_birth,s_sex) ——学生编号,学生姓名, 出生年月,学生性别课程表:course(c_id,c_name,t_id) ——课程编号, 课程名称, 教师编号教师表:teacher(t_id,t_name)——教师编号,教师姓名成绩表:score(s_id,c_id,s_s_score) ——学生编号,课程编号,分数1.2 学生表-- 1 创建表格CREATE TABLE IF NOT EXISTS stude转载 2021-04-06 13:27:57 · 6571 阅读 · 0 评论 -
Hash索引和B+树索引的区别
1. Hash索引和B+树索引的实现原理hash索引底层就是hash表,进行查询时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现原理是多路平衡查找树,对于每一次的查询都是从根节点出发,查询到叶子节点方可以获得所查键值,然后查询判断是否需要回表查询.2. Hash索引与B+树索引区别2.1 Hash索引hash索引进行等值查询更快(一般情况下)但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能原创 2021-04-05 21:08:02 · 247 阅读 · 0 评论 -
MySQL分库分表、读写分离
1. 分库分表分库分表解决了数据库的存储压力。1.1 分库假设数据库中有两张表分别是用户表和订单表。如果要分库的话现在你需要买两台机子,搞两个数据库分别放在两台机子上,并且一个数据库放用户表,一个数据库放订单表。这样存储压力就分担到两个服务器上了,但是会带来新的问题,所以东西变复杂了都会有新的问题产生。连表查询问题也就是join了,之前在一个数据库里面可以用上join用一条sql语句就可以联表查询得到想要的结果,但是现在分为多个数据库了,所以join用不上了。就比如现在要查注册时间在2019年之转载 2021-04-03 16:23:10 · 427 阅读 · 0 评论 -
MySQL锁的类型
1. MySQL锁的类型1.1 行锁与表锁行锁就是锁定某行表锁就是对整张表进行加锁各引擎对锁的支持情况如下:行锁表锁页锁MyISAM√InnoDB√√BDB√√行锁A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For suc转载 2021-04-03 11:49:14 · 320 阅读 · 0 评论 -
MySQL幻读详解及解决方法
1. 什么是幻读?建立一张表如下:按照下面步骤执行两个事务。事务1:事务2:由此,可以把幻读理解为:select 某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入,此时就发生了幻读。2. 解决幻读2.1 RR(可重复读)级别下防止幻读RR级别下只要对SELECT操作手动加**排他锁(X锁)**即可实现类似SERIALIZABLE级别(会对SELECT隐式加锁),代码如下:# 这里需要用 X锁, 用 LOCK IN SHARE MODE 拿到原创 2021-04-03 10:05:10 · 5908 阅读 · 6 评论 -
MySQL的MVCC
1. MVCC简介MVCC (multiversion concurrency control),多版本并发控制,主要是通过在每一行记录中增加三个字段,与 undo log 中相关记录配合使用,同时加上可见性算法,使得各个事务可以在不加锁的情况下能够同时地读取到某行记录上的准确值(这个值对不同的事务而言可能是不同的)。使用 MVCC,在不加锁的情况下也能读取到准确的数据,大大提高了并发效率。2. 更新操作在数据库表的记录中,每一个记录都会添加三个字段:DBTRXID:6个字节,表示最近一次修改本记转载 2021-03-20 10:16:32 · 177 阅读 · 0 评论 -
MySQL存储引擎
1. InnoDB存储引擎InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,InnoDB是默认的MySQL引擎。如果需要对事务的完整性要求比较高(比如银行),要求实现并发控制(比如售票),那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库,也可以选择InnoDB,因为支持事务的提交(commit)和回滚(rollback)。主要特性为MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎。InnoDB支持外键完整性约束,原创 2021-03-19 16:35:18 · 82 阅读 · 0 评论 -
MySQL索引详解
1. 索引的类型索引分为两大类:聚簇索引和非聚簇索引。聚簇索引:按照数据存放的物理位置为顺序的,聚簇索引能够提高多行检索的速度。非聚簇索引:索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。对单行的检索速度很快。组合索引:在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合UNIQUE(唯一索引):索引列的值必须唯一,但允许有空值。若是组合索引,则列值的组合必须唯一。PRIMARY原创 2021-03-13 16:31:59 · 292 阅读 · 0 评论 -
GROUP BY语句与HAVING语句的使用
GROUP BYGROUP BY语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。例如:有一个学生表格(student),包含学号(id),课程(course),分数(score)等等多个列,我们想通过查询得到每个学生选了几门课程,此时我们就可以联合使用COUNT函数与GROUP BY语句来得到这一结果:SELECT id, COUNT(course) as numcourse转载 2020-12-30 10:45:24 · 288 阅读 · 0 评论 -
SQL怎么实现模糊查询
模糊查询的语句一般如下:SELECT 字段 FROM 表 WHERE 某字段 LIKE 条件;关于条件部分,有以下匹配模式:1. %:表示零个或多个字符。可以匹配任意类型和任意长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。select * from user where username like '%张%';将会把user这张表里面,列名username中含有张的记录全部查询出来。如果需要找到user这张表里面,字段username中既有张,又有李的记录,可以使用and原创 2020-12-06 17:25:42 · 29753 阅读 · 1 评论 -
Mysql三大日志bin log、redo log和undo log
日志是 mysql 数据库的重要组成部分,记录着数据库运行期间各种状态信息。mysql日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。这篇文章介绍的是二进制日志( binlog )和事务日志(包括redo log 和 undo log )一、bin log用于记录数据库执行的写入性操作(不包括查询)信息,以二进制的形式保存在磁盘中。bin log 是 mysql的逻辑日志,并且由 Server层进行记录,使用任何存储引擎的 mysql数据库都会记录 binlog 日志。逻转载 2020-12-06 16:53:39 · 143 阅读 · 0 评论 -
Mysql的JOIN, LEFT JOIN,RIGHT JOIN 的区别
文章建表语句来自:https://www.jianshu.com/p/1f7ea7d1de4e建立两张表:#Table User CREATE TABLE `user` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `pwd` varchar(255) DEFAULT NULL, PRIMARY KEY (`uid`)) ENGINE=InnoDB AUTO_INCREMENT=7原创 2020-12-06 15:57:42 · 230 阅读 · 0 评论 -
数据库的事务隔离级别总结
一、事务及四大特性(ACID)事务:访问并可能更新数据库中各种数据项的一个程序执行单元(unit),它通常由高级数据库操纵语言或编程语言(如SQL,C++或Java)书写的用户程序的执行所引起。当在数据库中更改数据成功时,在事务中更改的数据便会提交,不再改变。否则,事务就取消或者回滚,更改无效。原子性(Atomicity):指事务包含的所有操作要么全部成功,要么全部失败回滚。因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。一致性(Consistency):一致性原创 2020-11-30 16:31:31 · 161 阅读 · 0 评论 -
Mysql为什么使用B+树来做索引,对比B树优缺点如何?
多叉树——B-tree每个节点维护两个数据,并指向最多 3 个子节点。如图 3 个子节点的数据分别为:小于 17, 17 ~ 35 ,大于 35。假设,从上图中查找 10 这个数,步骤如下:找到根节点,对比 10 与 17 和 35 的大小,发现 10 < 17 在左子节点,也就是第 2 层节点;从根节点的指针,找到左子节点,对比 10 与 8 和 12 的大小,发现8 < 10 < 12,数据在当前节点的中间子节点,也就是第 3 层节点;通过上步节点的指针,找到中转载 2020-11-30 15:57:22 · 3648 阅读 · 1 评论 -
MySQL创建商品入库和出库后库存更新的触发器
在Navicat 上建立 mysql 的触发器遇到了不少的坑,这篇文章记录正确的建立mysql触发器的查询语句,同时整理一下创建过程遇到的坑:入库需求:对入库表,在商品入库登记后,库存要进行更新。建立触发器如下:DELIMITER $$ -- 首尾设置$$符,是让sql语句将其之间作为一个整体执行CREATE TRIGGER `stocking_into` AFTER INSERT ON `stocking` FOR EACH ROW -- 触发器语法BEGIN-- new表 是mysql5.原创 2020-05-27 14:17:32 · 6673 阅读 · 5 评论 -
Mysql创建视图时出现字段相同的冲突解决
由于product和repository两张表中有两个相同的字段 id 和 name,所以在创建视图时会出现 “duplicate column name id“ 的报错。解决方法是对相同字段名字起一个别名即可。SELECT `product`.`id` AS `pid`, `product`.`name` AS `pname`, `repository`.`id` AS `rid`, `repository`.`name` AS `rname`, `stock`.`amount` AS .原创 2020-05-27 13:47:55 · 2683 阅读 · 0 评论 -
关系模式中的闭包及其求法——数据库原理
以下内容都是从百度知道中收集而来,希望能对大家有用关于闭包易懂的理解方法闭包就是由一个属性直接或间接推导出的所有属性的集合,例如: f={a->b,b->c,a->d,e->f}由a可直接得到b和d,间接得到c,则a的闭包就是{a,b,c,d} 以下是写的比较科学规范的顶一记求解方法设X和Y均为关系R的属性集的子集,F是R上的函数依赖集,若对R的任...转载 2020-02-08 17:43:55 · 6280 阅读 · 3 评论 -
求解关系模式的候选码——数据库原理
首先来看候选码的定义:若关系中的某一属性组的值能唯一地标识一个元组,则称该属性组为候选码。若W是候选键,则必须满足两个条件:W的闭包是U;W没有冗余。设关系模式R中U=ABC.......等N个属性,U中的属性在FD中有四种范围:求候选码的简单方法方法: (1)如果有属性不在函数依赖集中出现,那么它必须包含在候选码中;(2)如果有属性不在函数依赖集中任何函数依赖的右边出现,那么它必须包...转载 2019-11-25 20:50:12 · 3507 阅读 · 0 评论