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即将到来的流量评估；
...
