做网站编辑怎么样,wordpress文章商品模板下载,软件系统设计流程,石材网站源码建表优化
1#xff09;数据库范式
第一范式#xff08;1NF#xff09;#xff1a;强调的是列的原子性#xff0c;即列不能够再分成其他几列。 如电话列可进行拆分—家庭电话、公司电话第二范式#xff08;2NF#xff09;#xff1a;首先是 1NF#xff0c;另外包含两…建表优化
1数据库范式
第一范式1NF强调的是列的原子性即列不能够再分成其他几列。 如电话列可进行拆分—家庭电话、公司电话第二范式2NF首先是 1NF另外包含两部分内容一是表必须有主键二是没有包含在主键中的列必须完全依赖于主键而不能只依赖于主键的一部分。 第三范式3NF首先是 2NF另外非主键列必须直接依赖于主键不能存在传递依赖。 比如Student表学号姓名年龄性别所在院校院校地址院校电话 这样一个表结构就存在上述关系。 学号– 所在院校 -- (院校地址院校电话)
这样的表结构我们应该拆开来如下 学号姓名年龄性别所在院校–所在院校院校地址院校电话
满足这些规范的数据库是简洁的、结构明晰的 同时不会发生插入insert、删除delete和更新update操作异常。
2数据类型选择
l 数字类型 Float和double选择尽量选择float有效位7位而double是15位 区分开TINYINT / INT / BIGINT能确定不会使用负数的字段建议添加 unsigned定义 能够用数字类型的字段尽量选择数字类型而不用字符串类型的
l 字符类型 char,varchar,TEXT的选择非万不得已不要使用 TEXT 数据类型定长字段建议使用 CHAR 类型填空格不定长字段尽量使用 VARCHAR自动适应长度超过阶段且仅仅设定适当的最大长度
l 时间类型 按选择优先级排序DATE精确到天、TIMESTAMP1970开始的秒、DATETIME精确到时间
l ENUM 对于状态字段可以尝试使用 ENUM 来存放但是用枚举值是数字可能会出现问题最好不用。
l 避免使用NULL字段很难查询优化且占用额外索引空间
3字符编码
同样的内容使用不同字符集表示所占用的空间大小会有较大的差异所以通过使用合适的字符集可以帮助我们尽可能减少数据量进而减少IO操作次数。
纯拉丁字符能表示的内容选择 latin1 字符编码中文可选用utf-8MySQL的数据类型可以精确到字段所以当我们需要大型数据库中存放多字节数据的时候可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量进而降低 IO 操作次数并提高缓存命中率
Sql优化 只返回需要的数据 a) 不要写SELECT *的语句 b) 合理写WHERE子句不要写没有WHERE的SQL语句。 尽量少做重复的工作可以合并一些sql语句 适当建立索引不是越多越好但以下几点会进行全表扫描 a) 左模糊查询’%…’ b) 使用了不等操作符 c) or使用不当or两边都必须有索引才行 可以使用union all 来链接两句sql d) In 、not in 根据情况可以用between and e) Where子句对字段进行表达式操作 f) 对于创建的复合索引从最左边开始组合查询条件用到的列必须从左边开始不能间隔。否则无效复合索引的结构与电话簿类似 g) 全文索引当于对文件建立了一个以词库为目录的索引文件大全文索引比模糊匹配效果好 能在char、varchar、text类型的列上面创建全文索引 MySQL 5.6 Innodb引擎也能进行全文索引 搜索语法MATCH (列名1, 列名2,…) AGAINST (搜索字符串 [搜索修饰符]) 如果列类型是字符串但在查询时把一个数值型常量赋值给了一个字符型的列名name那么虽然在name列上有索引但是也没有用到。 使用join代替子查询 使用union代替手动创建临时表
索引优化
一、 创建索引以下情况不适合建立索引
表记录太少经常插入、删除、修改的表数据重复且分布平均的表字段
二、 复合索引 如果一个表中的数据在查询时有多个字段总是同时出现则这些字段就可以作为复合索引
索引
索引是对数据库表中一列或多列的值进行排序的一种结构。Btree索引结构 原理 就是为无序存放的数据建立一个有序的对应关系通过这个关系可以快速查找数据。
优点
大大加快数据的检索速度创建唯一性索引保证数据库表中每一行数据的唯一性可以加速表和表之间的连接 缺点索引需要占物理空间。当对表中的数据进行增加、删除和修改的时候索引也要动态的维护降低了数据的维护速度。
索引分类
sql语句创建索引
CREATE INDEX index_name ON table_name (column_name) 可以创建unique index索引。ALTER TABLE table_name ADD INDEX index_name (column_name) 第二种方法可以创建primary key和unique、index三种索引 注意如果是字符串要指定长度column长度drop index index_name;删除索引 三种索引解释普通索引unique 唯一索引索引列的值必须唯一但允许有空值primary key主键索引它是一种特殊的唯一索引不允许有空值。还可以进行组合索引
事务
数据库事务(Database Transaction) 是指作为单个逻辑工作单元执行的一系列操作要么完全地执行要么完全地不执行。
四大特征
(1)原子性 事务必须是原子工作单元对于其数据修改要么全都执行要么全都不执行。
(2)一致性 事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
(3) 隔离性关于事务的隔离性数据库提供了多种隔离级别 一个事务的执行不能干扰其它事务。即一个事务内部的操作及使用的数据对其它并发事务是隔离的并发执行的各个事务之间不能互相干扰。
(4)持久性 事务完成之后它对于数据库中的数据改变是永久性的。该修改即使出现系统故障也将一直保持。
在介绍数据库提供的各种隔离级别之前我们先看看如果不考虑事务的隔离性会发生的几种问题
脏读 脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。不可重复读幻读 幻读和不可重复读都是读取了另一条已经提交的事务,不可重复读重点在于update和delete而幻读的重点在于insert。
在可重复读中该sql第一次读取到数据后就将这些数据加锁其它事务无法修改这些数据就可以实现可重复 读了。但这种方法却无法锁住insert的数据所以当事务A先前读取了数据或者修改了全部数据事务B还是可以insert数据提交这时事务A就会 发现莫名其妙多了一条之前没有的数据这就是幻读不能通过行锁来避免。需要Serializable隔离级别 读用读锁写用写锁读锁和写锁互斥这么做可以有效的避免幻读、不可重复读、脏读等问题但会极大的降低数据库的并发能力。
现在来看看MySQL数据库为我们提供的四种隔离级别 ① Serializable (串行化)可避免脏读、不可重复读、幻读的发生。 ② Repeatable read (可重复读)可避免脏读、不可重复读的发生。 # 同一个查询sql执行多次都和第一次保持一致 ③ Read committed (读已提交)可避免脏读的发生。 # 当前读取到最新的 ④ Read uncommitted (读未提交)最低级别任何情况都无法保证。
在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。 在实际开发中订单并发库存问题 使用乐观锁要使用到Read committed (读已提交)这个隔离级别。其他事务下单了对库存进行更改并提交了并行的当前事务读取第二遍的时候跟第一次读取的结果保持一致但是读取已提交第二遍或接下来的读取会读取到最新的。 select origin_stock from tb_sku where idsku_id; # 这里记录原始的库存需要保证最新 update tb_sku set stocknew_stock, salesnew_sales where idsku_id and stockorigin_stock;
锁模式包括
l 共享锁:读取操作创建的锁。其他用户可以并发读取数据但任何事物都不能获取数据上的排它锁直到已释放所有共享锁。
l 排他锁X锁对数据A加上排他锁后则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据又能修改数据。
l 更新锁: 更新 (U) 锁可以防止通常形式的死锁。如果两个事务获得了资源上的共享模式锁然后试图同时更新数据则两个事务需都要转换共享锁为排它 (X) 锁并且每个事务都等待另一个事务释放共享模式锁因此发生死锁。 若要避免这种潜 在的死锁问题请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源则更新 (U) 锁转换为排它 (X) 锁。否则锁转换为共享锁。
锁的粒度主要有以下几种类型 l 行锁 粒度最小并发性最高 l 页锁一次锁定一页。25个行锁可升级为一个页锁。 l 表锁粒度大并发性低 l 数据库锁控制整个数据库操作
乐观锁 相对悲观锁而言乐观锁假设认为数据一般情况下不会造成冲突所以在数据进行提交更新的时候才会正式对数据的冲突与否进行检测如果发现冲突了则让返回用户错误的信息让用户决定如何去做。一般的实现乐观锁的方式就是记录数据版本。
悲观锁 顾名思义就是很悲观每次去拿数据的时候都认为别人会修改所以每次在拿数据的时候都会上锁这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制比如行锁表锁等读锁写锁等都是在做操作之前先上锁。
引擎 MyISAM、InnoDB区别
l MyISAM类型不支持事务处理等高级处理而InnoDB类型支持。 l MyISAM表不支持外键InnoDB支持 l MyISAM锁的粒度是表级而InnoDB支持行级锁定。 l MyISAM支持全文类型索引而InnoDB不支持全文索引。(mysql 5.6后innodb支持全文索引) MyISAM相对简单所以在效率上要优于InnoDB小型应用可以考虑使用MyISAM。当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主相比较而言更新和写 入比较少并且业务方面数据完整性要求不那么严格就选择mysiam表。
MyISAM和InnoDB索引实现
MyISAM索引实现 MyISAM索引文件和数据文件是分离的索引文件仅保存数据记录的地址。
l 主索引 MyISAM引擎使用BTree作为索引结构叶节点的data域存放的是数据记录的地址。 l 辅助索引 在MyISAM中主索引和辅助索引Secondary key在结构上没有任何区别只是主索引要求key是唯一的而辅助索引的key可以重复。 MyISAM中索引检索的算法为首先按照BTree搜索算法搜索索引如果指定的Key存在则取出其data域的值然后以data域的值为地址读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现
然InnoDB也使用BTree作为索引结构但具体实现方式却与MyISAM截然不同.
l 主索引 InnoDB表数据文件本身就是主索引。 InnoDB主索引同时也是数据文件的示意图可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集所以InnoDB要求表必须有主键MyISAM可以没有如果没有显式指定则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整形。
l 辅助索引 InnoDB的所有辅助索引都引用主键作为data域。 聚集索引这种实现方式使得按主键的搜索十分高效但是辅助索引搜索需要检索两遍索引首先检索辅助索引获得主键然后用主键到主索引中检索获得记录。
不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助例如知道了InnoDB的索引实现后就很容易明白 1、为什么不建议使用过长的字段作为主键因为所有辅助索引都引用主索引过长的主索引会令辅助索引变得过大。 2、用非单调的字段作为主键在InnoDB中不是个好主意因为InnoDB数据文件本身是一颗BTree非单调的主键会造成在插入新记录时数据文件为了维持BTree的特性而频繁的分裂调整十分低效而使用自增字段作为主键则是一个很好的选择。
InnoDB索引和MyISAM索引的区别 l 一是主索引的区别InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。 l 二是辅助索引的区别InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。
红黑树 B树 B树 B-树
二叉查找树BST
二叉排序树或者是一棵空树或者是具有下列性质的二叉树 l 若左子树不空则左子树上所有结点的值均小于它的根结点的值。 l 若右子树不空则右子树上所有结点的值均大于它的根结点的值。 l 左、右子树也分别为二叉排序树。 l 没有键值相等的节点因此插入的时候一定是叶子节点。 删除算法 l 要删除节点是叶子节点。 l 要删除的节点只有一个孩子左孩子或右孩子这种情况比较简单只需要将该孩子连接到当前节点的父节点即可。 l 要删除的节点有两个孩子这个时候的算法就比较复杂相比较于只有一个孩子的情况。首先我们需要找到待删除节点的左子树上的最大值节点或者右子树上的最小值节点然后将该节点的参数值与待删除的节点参数值进行交换最后删除该节点这样需要删除的参数就从该二叉树中删除了。
红黑树
红黑树Red Black Tree 是一种自平衡二叉查找树 l 每个节点或者是黑色或者是红色。 l 根节点是黑色。 l 每个叶子节点是黑色。 l 如果一个节点是红色的则它的子节点必须是黑色的。 l 从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。
红黑树的各种操作的时间复杂度是Ologn。
红黑树 vs AVL 红黑树的查询性能略微逊色于AVL树因为他比avl树会稍微不平衡最多一层也就是说红黑树的查询性能只比相同内容的avl树最多多一次比较但是红黑树在插入和删除上完爆avl树avl树每次插入删除会进行大量的平衡度计算而红黑树为了维持红黑性质所做的红黑变换和旋转的开销相较于avl树为了维持平衡的开销要小得多
B树与B-树
B-tree树即B树B即Balanced平衡的意思。因为B树的原英文名称为B-tree而国内很多人喜欢把B-tree译作B-树其实这是个非常不好的直译很容易让人产生误解。如人们可能会以为B-树是一种树而B树又是另一种树。而事实上是B-tree就是指的B树。
m阶B树是一棵平衡的m路搜索树。它是空树或者是满足下列性质的树
l 根结点的儿子数为[2, M] l 除根结点以外的非叶子结点的儿子数为[M/2, M] M/2向上取整 l 每个结点存放至少M/2-1取上整和至多M-1个关键字 l 非叶子结点的关键字个数指向儿子的指针个数-1 l 非叶子结点的关键字K[1], K[2], …, K[X-1]且K[i] K[i1] l 非叶子结点的指针P[1], P[2], …, P[X]其中P[1]指向关键字小于K[1]的子树P[X]指向关键字大于K[X-1]的子树其它P[i]指向关键字属于(K[i-1], K[i])的子树 l 所有叶子结点位于同一层
B树
B树是B-树的变体也是一种多路搜索树 其定义基本与B-树同除了 l 非叶子结点的子树指针与关键字个数相同 l 非叶子结点的子树指针P[i]指向关键字值属于[K[i], K[i1])的子树B-树是开区间 l 为所有叶子结点增加一个指针链 l 所有关键字都在叶子结点出现
基本SQL操作
SELECT * FROM table ORDER BY field DESC; (ASC|DESC)SELECT DISTINCT field from table where 范围INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);UPDATE table_name SET column1value1,column2value2,...
WHERE some_columnsome_value;DELETE FROM table_name WHERE some_columnsome_value;LIKE 操作符
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;IN 操作符
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);BETWEEN 操作符
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN JOIN 左连接右连接内连接 left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录。 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录。 inner join(等值连接) 只返回两个表中联结字段相等的行。默认
UNION 操作符 UNION 操作符用于合并两个或多个 SELECT 语句的结果集可选ALL 重复也显示。
SELECT country, name FROM Websites WHERE countryCN
UNION
SELECT country, app_name FROM apps WHERE countryCN ORDER BY country;创建视图
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE conditionSQL函数 Avg() Count() Max() Min() Sum()
Group By():
GROUP BY 语句用于结合聚合函数根据一个或多个列对结果集进行分组。
SELECT column_name, aggregate_function(column_name)
FROM table_name WHERE column_name operator value
GROUP BY column_name;HAVING 子句可以让我们筛选分组后的各组数据。
SELECT column_name, aggregate_function(column_name)
FROM table_name WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;如何查询数据库表结构主键
desc tabl_name;建表
CREATE TABLE 表名称(列名称1 数据类型,....)
