Mysql和Oracle的个人总结
一、Mysql
(一)查询语句
二、Oracle
(一)查询语句
三、常识
(一)DROP TABLE和TRUNCAET TABLE的区别
相同点:TRUNCATE TABLE和DELETE都可以删除整个数据库表的记录。
不同点:
①.DELETE
- DML语言
- 可以回退
- 可以有条件的删除
DELETE FROM 表名 WHERE 条件
②.TRUNCATE TABLE
- DDL语言
- 无法回退
- 默认所有的表内容都删除
- 删除速度比delete快
TRUNCATE TABLE 表名
③.DROP TABLE
- 用于删除表(表的结构、属性以及索引也会被删除);
DROP TABLE 表名
DDL(Data Definition Language)数据定义语言,DML(Data Manipulation Language)数据操作语言。
①、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令Delete将被撤销,而TRUNCATE则不会被撤销。
②、TRUNCATE是一个DDL语言,向其他所有的DDL语言一样,他将被隐式提交,不能对TRUNCATE使用ROLLBACK命令。
③、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时,经过TRUNCATE操作后的表比Delete操作后的表要快得多。
④、TRUNCATE不能触发任何Delete触发器。
⑤、当表被清空后表和表的索引讲重新设置成初始大小,而delete则不能。
⑥、不能清空父表。
在oracle里,使用delete删除数据以后,数据库的存储容量不会减少,而且使用delete删除某个表的数据以后,查询这张表的速度和删除之前一样,不会发生变化。
因为oralce有一个HWM高水位,它是oracle的一个表使用空间最高水位线。当插入了数据以后,高水位线就会上涨,但是如果你采用delete语句删除数据的话,数据虽然被删除了,但是高水位线却没有降低,还是你刚才删除数据以前那么高的水位。除非使用truncate删除数据。那么,这条高水位线在日常的增删操作中只会上涨,不会下跌,所以数据库容量也只会上升,不会下降。而使用select语句查询数据时,数据库会扫描高水位线以下的数据块,因为高水位线没有变化,所以扫描的时间不会减少,所以才会出现使用delete删除数据以后,查询的速度还是和delete以前一样。
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。
DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。
如果想保留标识计数值,请改用 DELETE。
如果要删除表定义及其数据,请使用 DROP TABLE 语句。
在Oracle类型数据库中删除普通表,并未真正删除表,只是把该表放在回收站中。如drop table table_name ;或者删除带约束的表drop table table_name cascade constraints ;回收站知识点补充:在oracle中,当删除一个表时,oracle并没有真正删除该表,而是把该表重命名,然后扔到回收站中,使用purge命令来清空回收站,将会释放表占用的空间,把这些空间返回给表空间,成为表空间中的自由空间。一旦空间吃紧,oracle会自动清空回收站中的对象。在oracle清空前,可以找回被删除的对象,就是把被删除的对象从回收站中捡回来,即所谓的闪回技术。
(二)先创建表和索引再插入数据,还是先创建表插入数据再创建索引?
(三)数据库(MySQL)规范
1. 基础规范
(1) 所有环境的MySQL版本使用5.6社区版,64位版本;
(2) 使用INNODB存储引擎;
(3) 数据库字符集默认为utf8,字符集默认校验规则为utf8_general_ci;
(4) 所有表、字段都需要添加注释;
(5) 单表数据量控制在2000W以内;
(6) 隔离线上和线下,禁止线下直接连接线上库,禁止在已上线库上做压力测试;
2. 命名规范
(1) 库名、表名、字段名建议不超过32个字符,可用单词简写,但须见名之意;
(2) 库名、表名、字段名须使用小写字母,“_”分割。
(3) 库名、表名、字段名禁止使用MySQL保留字;
(4) 临时库、表名建议以tmp为前缀,并以日期为后缀;
(5) 备份库、表建议以bak为前缀,并以日期为后缀;
(6) 使用时间分表,表名后缀建议使用特定格式,比如按年分表user_2016按月分表user_201602、按日分表user_20160209;
3. 库、表、字段设计规范
(1) 控制单库的表数量,建议在400张表左右;
(2) 表字段数少而精,字段数量上限控制在50个;
(3) 字段数据类型长度选择遵守够用最小原则;
(4) 拆分大字段和访问频率低的字段,分离冷热数据,拆分成两张表;
(5) 用HASH进行散表,表名后缀使用十进制数,下标从0开始;
(6) 采用合适的分库分表策略,例如十库百表等;
(7) 展示型价格字段可使用FLOAT和DOUBLE类型存储,用于计算的价格字段建议用DECIMAL类型存储;
(8) 所有字段建议均定义为NOT NULL,NULL字段难于查询优化,NULL字段的索引需要额外空间;
(9) 使用UNSIGNED存储非负整数;
(10) 使用VARBINARY存储大小写敏感的变长字符串;
(11) 禁止在数据库中存储明文密码,把密码加密后存储;
(12) 存储ip最好用int存储而非char(15)或者varchar(15);
(13) 存储精确到秒的时间建议使用DATETIME,DATETIME和TIMESTAMP都是精确到秒,优先选择DATETIME,因为TIMESTAMP只有4个字节,存储的最大值为2038年的某一天,而DATETIME 占用8个字节。
(14) 尽量使用tinyint类型代替enum类型;
(15) 尽可能不使用TEXT/BLOB类型,varchar的性能会比TEXT/BLOB高很多,实在避免不了TEXT/BLOB,请拆分表;
(16) 数据库中禁止存储大文件,或者照片,可以将大对象放到磁盘上,数据库中存储它的路径;
4. 索引规范
5. SQL规范
(1) SQL语句尽可能简单,大的SQL想办法拆成小的sql语句,可充分利用多核CPU;
(2) 事务要简单,整个事务的时间长度不要太长;
(3) 尽量避免使用触发器、函数、存储过程;
(4) 降低业务耦合度,为sacle out、sharding留有余地;
(5) 避免在数据库中进行数学运算(MySQL不擅长数学运算和逻辑判断);
(6) 禁止用select *,查询哪几个字段就select这几个字段,减少cpu、IO和网卡的压力;
(7) in里面数字的个数建议控制在1000以内;
(8) Limit分页注意效率,Limit越大,效率越低;
(9) 避免使用大表的join;
(10) 使用group by 分组、自动排序;
(11) 对数据的更新要打散后批量更新,不要一次更新太多数据;
(12) 减少与数据库的交互次数;
(13) SQL语句不可以出现隐式转换,比如 select id from tb where id=’1’;
(14) 在SQL语句中,禁止使用前缀是%的like,无法使用索引;
(15) 不使用负向查询,如not in和%前缀模糊查询,导致全表扫描;
(16) 关于分页查询:程序里建议合理使用分页来提高limit效率,offset较大要配合子查询使用;
(17) 禁止使用order by rand();
(18) 禁止单条SQL语句同时更新多个表,易造成死锁;
(19) 禁止在应用程序端显式加锁;
6. 流程规范
(1) 不在业务高峰期批量更新、查询数据库或者导入导出数据;
(2) 所有的建表操作需要提前告知,建议在建表的时候,添加对应列的索引;
(3) 所有的改表结构、加索引操作都需要将涉及到所改表的查询SQL发工单提前告知DBA等相关人员,核心业务表或者大数据量表需要在业务低峰期进行;
(4) 批量导入、导出数据建议提前通知DBA,并在业务低峰期进行;
(5) 禁止有super权限的应用程序账号存在;
(6) 推广活动前通知DBA即将到来的流量评估;
...
__EOF__

本文链接:https://www.cnblogs.com/VveYoung-Chen/p/17008004.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库