談談 mysql和oracle的使用感受 -- 差異

等你歸去來發表於2020-09-13
MySqlOracle

　　之前一直使用mysql作為儲存資料庫，雖然中間偶爾使用sqlite作為本地資料庫儲存，但沒有感覺多少差別。事實上，我們往往聽說SQL-92標準之類的云云！

　　後來遇上了oracle，且以其作為主要儲存，這下就不得不好好了解其東西了。oracle作為商業資料庫裡的佼佼者，肯定有其過人之處的。

　　本文僅從使用的角度來說說感受，並無其他意思喲。（理解上也並不深入）

 

1. 自增主鍵

　　mysql中要使用自增主鍵非常方便，只需要在建表時增加 auto_increment 關鍵字即可，樣例如下：

　　create table tb1 (id int(11) unsigned not null auto_increment);

 

　　而在oracle中則不一樣了，它需要使用另一個概念：序列號；我們可以簡單將其理解為只有一個列的表，這個表提供了 nextval 的方法，輔助我們生成自增id，樣例如下：

　　-- 1. 建普通表
　　create table tb1(id number not null);
　　-- 2. 建立序列，引數比較多，自行查閱資料
　　create sequence seq_tb1 increment by 1 start with 1 minvalue 1 maxvalue 999999999
　　-- 3. 插入使用
　　insert into tb1 (id) values (seq_tb1.nextval)

　　可以看到，oracle的操作明顯多了許多。當然了，自增這個屬性，在許多資料庫中確實也是不提供的，尤其是分散式資料庫遞增更難做。所以，要支援這功能，繞路也就在所難免了。

　　

2. 建立索引

　　索引的目的自然是為了提高查詢效率，mysql中想要新增索引可以在建表時操作，也可以在後期更改；樣例如下：

    -- 1. 建表時指定
    create table tb1 (username varchar(50), index username (username));
    -- 2. 後期更改
    alter table tb1 add index username (usrrname);

 

　　而在oracle中則不一樣，它只能在建表完成之後操作；樣例如下：

    CREATE INDEX tb1_username ON tb1(username);

　　看起來差異不大，但oracle的索引是全域性的，即所有表的索引名都不能重複，比如大家都有id索引，但卻不能都叫id。

 

3. 欄位表註釋

　　我們建一張表時，肯定都需要註釋的，否則過兩天連我們自己都不認識其含義了。mysql中在建表或增加欄位時直接指定，樣例如下：

    -- 1. 建表時指定
    create table tb1(username varchar(50) comment '使用者名稱標識') comment '測試建表';
    -- 2. 修改表結構時指定
    alter table tb1 add column nickname varchar (100) '暱稱';

 

　　而oracle中則不太一樣，它只能在建表之後和建立欄位之後才能進行註釋；樣例如下：

    -- 表註釋
    comment on table tb1 is '測試建表';
    -- 欄位註釋
    comment on tb1.username is '使用者名稱稱標識';
    -- 刪除表註釋，置空
    -- 刪除列註釋，置空

　　我不是說它這設計不好，但是就感覺太煩了。

 

4. 分頁實現

　　mysql中的分頁，使用limit，這也是大多數資料庫的選擇，樣例如下：

    select username from tb1 limit 50, 100;

 

　　而在oracle中則不太一樣，它使用行號去定位記錄，一般需要使用巢狀子查詢；樣例如下：

    select * from (select t.*,rownum num from tb1 t where rownum<=100 ) where num>50

　　效能比limit怎麼樣我不清楚，反正是寫得挺煩的。

 

5. 查詢執行計劃

　　查詢執行計劃，可以看出哪些語句是需要優化的，這個工作實際上還是比較專業的。但如果想簡單看看情況，mysql中可以這樣做：

    explain select * from tb1 where username='xx' order by id limit 10;

　　

　　而oracle中要檢視執行計劃，則需要藉助工具或者自己寫，樣例如下：

    -- 1. 執行查詢執行計劃語句
    explain plan for select * from tb1 where username='xx';
    -- 2. 檢視執行計劃結果
    select * from table(dbms_xplan.display());

　　oracle還有其他許多種檢視執行計劃的方式，就不列舉了。也沒啥好壞之分，能檢視就行。

 

6. 客戶端視覺化工具

　　這個簡單說說，mysql有很多工具，sqlyog，navicat，mysqlworkbench。。。

　　oracle也有很多，plsql，navicat。。。

　　而具體操作上的差異則根據客戶端工具的差異來，無可厚非。

 

7. 對超長文字的處理

　　mysql中對超長文字使用text和longtext型別進行處理，和其他欄位並沒有太多差別（不能建有效索引除外）

　　而oracle中則使用CLOB型別進行存在超長字元，但它有許多限制，普通查詢無法顯示clob，分號限制等等。但它可以容納上G的資料。

 

8. 日期欄位查詢

　　都支援date，timestamp資料型別。

　　mysql支援直接使用字串日期進行條件過濾，預設格式為：yyyy-MM-dd HH:ii:ss 比如：

    select * from tb1 where dt>'2020-09-13 12:15:01';

 

　　而oracle則要求嚴格些，要求必須都是日期老式string格式才能比較；

    select * from tb1 where dt>to_date('2020-09-13 12:15:01', 'yyyy-MM-dd hi24:mi:ss');

　　雖然加這麼個格式東西也不復雜，但總感覺不爽。

 

9. 修改欄位型別

　　mysql中修改欄位型別，直接改就好，但有可能失敗。

    alter table tb1 change column f1_old f1_new int(11) comment 'xxx';

　　

　　而oracle中則分情況處理，空欄位直接改，不允許修改有值欄位型別，如果硬要改那就相當麻煩，如下：

    -- 空欄位型別修改，可任意修改
    alter table modify (f1_old number);
    -- 非空欄位型別修改，分型別匹配與不匹配情況
    -- 如果型別匹配，可直接改，如nchar(20) -> nvarchar(20)
    -- 否則不允許修改，只能主動新建欄位替換回來，關鍵是不一定能成功
    alter table tb rename column name to name_tmp;
    /*增加一個和原欄位名同名的欄位name*/
    alter table tb add name varchar2(40);
    /*將原欄位name_tmp資料更新到增加的欄位name，可算可能失敗*/
    update tb set name=trim(name_tmp);
    /*更新完，刪除原欄位name_tmp*/
    alter table tb drop column name_tmp;

　　很顯然，oracle的做法更嚴謹，不允許更改欄位名稱，改型別必須保證正確；哎，但總感覺不爽；

 

10. group by 聚合

　　group by可以按照某欄位去重一些資料，並按需要聚合資料，mysql與oracle都差不多，差別點在於oracle不允許返回group by外的其他欄位（或者說不能準確描述的欄位），而mysql則會隨機返回一個group by的欄位值。mysql如下：

    select username, avg(score), grade from tb1 group by grade;

　　

　　oracle中則要求必須確定某值：

    select max(username), avg(score), grade from tb1 group by grade;

　　看起來oracle是更嚴謹一些的。

 

11. 分割槽表建立

　　分割槽表的目的，在於提高查詢速度和方便隔離管理。

　　mysql 建立分割槽表，Mysql不能自動建立分割槽，且要求分割槽欄位必須是主鍵的一部分，如果想自動建立分割槽，需要使用mysql event事件的方式自動建立分割槽. 樣例如下：

    create table tb1 (
        id int(11),
        day datetime not null
        prmary key (id, day)
    )
    PARTITION BY RANGE (TO_SECONDS(day))
    (PARTITION p20200912 VALUES LESS THAN (TO_SECONDS('20200912')) ENGINE = InnoDB,
    PARTITION p20200913 VALUES LESS THAN (TO_SECONDS('20200913')) ENGINE = InnoDB);

 

　　oracle 中建立分割槽表

    create table tb1 (
        id NUMBER(20) not null,
        create_time DATE
    )
    PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
    (partition part_t01 values less than(to_date('2020-09-12', 'yyyy-mm-dd')));

　　明顯 oracle 支援得更好些呢。

 

12. with as 用法

　　在做一些大型資料資料分析sql時，with as sql 非常有用，在mysql低版本中是不支援的，只能自己寫臨時表進行處理。

　　而oracle則支援該通用語法：

    with a as (
      select * from tb1 where dt = '20200912'
    ),
    b as (
      select * from tb2 where dt = '20200912'
    )
    select a.id aid, b.id bid from a join b on a.pid = b.id;

 

13. 事務支援

　　在rdb中，通常事務是指對一批操作的原子性，一致性，隔離性，永續性的體現。大體上mysql與oracle表現是一致的。

　　mysql是分儲存引擎，如innodb，myisam，每個引擎的事務支援能力不同，原則不同，鎖實現不同，如innodb鎖行，而myisam 鎖表等。

　　oracle 中在建表時就可以指定事務槽數

    -- 建表時指定事務槽數
    create table t3 (id int, num int ) INITRANS 6;
    -- 建立索引時指定事務槽數
    create unique index tb1_username_idx on tb1 (username) initrans 6;

 

14. 程式資訊查詢

　　查詢正在執行的任務情況，可用於查詢慢查詢的利器。

　　mysql 中 直接使用 show full processlist 即可；但帶條件的查詢需要查表：

    show full processlist;
    select * from information_schema.`PROCESSLIST` where duration > 5;

 

　　oracle 程式資訊：

SELECT b.sid oracleID,  
       b.username Oracle使用者,  
       b.serial#,  
       spid 作業系統ID,  
       paddr,  
       sql_text 正在執行的SQL,  
       b.machine 計算機名  
FROM v$process a, v$session b, v$sqlarea c  
WHERE a.addr = b.paddr  
   AND b.sql_hash_value = c.hash_value;

　　反正我是記不住這麼長的sql的。

 

15. 資料同步

　　mysql 中使用 binlog 可以方便的將資料同步到其他地方;

　　oracle, 好像很複雜的樣子, 待研究。

 

相關文章