Mysql和Oracle的个人总结
一、Mysql
(一)查询语句
-- 查询Mysql所有库中所有表的系统信息 SELECT * FROM INFORMATION_SCHEMA.TABLES ; -- 查询Mysql具体表的最新更新时间 SELECT `TABLE_NAME`, `UPDATE_TIME` FROM `information_schema`.`TABLES` WHERE `information_schema`.`TABLES`.`TABLE_SCHEMA` = 'baseName' AND `information_schema`.`TABLES`.`TABLE_NAME` = 'tableName';
二、Oracle
(一)查询语句
Oracle比较特殊; Mysql的show tables等价于Oracle的select table_name from user_tables; Mysql的show databases等价于Oracle的select username from dba_users; -- 模糊查询多表表名(严格区分大小写) SELECT table_name FROM user_tables WHERE table_name LIKE '%TMP_CWY%' ; select table_name,tablespace_name,temporary from user_tables where table_name like '%PROV%' 分析: table_name:表名(varchar2(30)); tablespace_name:存储表名的表空间(varchar2(30)); temporary:是否为临时表(varchar2(1))。 4种通配符的含义 % 表示零个或多个字符 _ 表示单个字符 [] 表示范围[a-f]或集合[abcdef]的任何单个字符 [^] 表示不属于指定范围的[a-f] 或集合[abcdef]的单个字符 通常表示[^a-f] or [^abcdef]
三、常识
(一)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清空前,可以找回被删除的对象,就是把被删除的对象从回收站中捡回来,即所谓的闪回技术。
查看回收站中的对象:select * from recyclebin ;
清空回收站中的所有表:purge recyclebin ;
一次性彻底删除表:drop table table_name purge ;不会又放回收站的步骤,而是直接删除,注意使用。
从回收站中还原被删除的表:flashback table table_name to before drop ;
(二)先创建表和索引再插入数据,还是先创建表插入数据再创建索引?
问题: 1、新建一个表结构,先创建索引,再将百万或千万级的数据使用insert导入该表。 2、新建一个表结构,将百万或千万级的数据使用isnert导入该表,再创建索引。 这两种效率哪个高呢?或者说用时短呢? 我感觉无论先建还是后建索引,当有数据时都需要update索引数据,问题是有索引的情况下插数据与有数据后的情况下建立索引,各自的消耗。 实验: 100w记录, 1、先创建表和索引,再插入数据,大约1.3min。 2、先插数据,再建立两个索引,create table xxx as select * from t where 1<>1;大约13秒,建立两个索引大约26秒和35秒。 总结: 如果先建立索引再插入数据,每次插入数据还需要修改索引信息。实战还要看业务要求,有的业务可能还需要索引和约束对数据进行校验,这样就应该先建立索引了。如果是一次性处理,原则上先插数据后建索引。
(三)数据库(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. 索引规范
(1)索引命名:
非唯一索引建议以 idx_字段1_字段2命名,唯一所以建议以unq_字段1_字段2命名,索引名称建议全部小写; (2)索引的数量要控制: a 单张表中索引数量尽量不超过5个,避免过多索引影响update、insert、delete的性能; b 单个索引中的字段数尽量不超过5个; c 对字符串使用前缀索引,前缀索引长度不超过30个字符,短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作; d 建议优先考虑前缀索引,必要时可添加伪列并建立索引,多条字段重复的语句,要修改语句条件字段的顺序,为其建立一条联合索引,但也避免冗余索引; (3)主键准则: a 建议每张表都要有自增主键id int(11) unsigned,且与业务无关,not null auto_increment; b 不使用更新频繁的列作为主键; c 尽量不选择字符串列作为主键; d 不使用UUID MD5 HASH这些作为主键; e 默认使用非空的唯一键作为主键; (4)多表JOIN的字段注意以下: a 区分度最大的字段放在前面; b 核心SQL优先考虑覆盖索引; c 避免冗余和重复索引; d 索引要综合评估数据密度和分布以及考虑查询和更新比例; (5)索引禁忌: a 不在低基数列上建立索引,例如“性别”,“类型”等字段; b 不在索引列进行数学运算和函数运算,会导致索引失效而进行全表扫表; (6)尽量不使用外键: a 外键用来保护数据一致性和完整性,由应用端实现; b 对父表和子表的操作会相互影响,降低可用性; (7)索引字段的默认值尽量不为NULL,要改为其他的默认值或者空串; (8)能使用唯一索引尽量使用唯一索引,提高查询效率;
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即将到来的流量评估;
...