Mysql 暫存資料表+視圖

標籤：select   test   tmp   理解   sql   hang   提升   image   錯誤   

學習內容：

暫存資料表和視圖的基本操作...

暫存資料表與視圖的使用範圍...

1.暫存資料表

  暫存資料表：暫存資料表，想必大家都知道這個概念的存在。。。但是我們什麼時候應該使用到暫存資料表呢？當一個資料庫存在著大量的資料的時候，我們想要擷取到這個資料集合的一個子集，那麼我們就可以使用暫存資料表來儲存我們想要的資料。。然後對暫存資料表進行操作就可以了...使用暫存資料表必然是有原因的。。使用暫存資料表會加快資料庫的查詢效能....

create temporary table tmp_table  //建立一個暫存資料表(    name varchar(10) not null,    value integer not null,);暫存資料表的建立很簡單，只需要加一個temporary關鍵字就完成了表的建立...暫存資料表在我們與資料庫中斷連線的時候，系統將會自動的刪除暫存資料表並釋放其佔用空間..除了這種方式，我們還可以手動進行刪除。。。drop table tmp_table //與我們正常刪除表的語句一樣...create temporary table tmp_table select *　from table_name;//將我們查詢的資料直接插入到暫存資料表當中...alter table tmp_table rename ttmp_table;//使用alter來重新命名暫存資料表...rename table tmp_table to ttmp_table;//rename在暫存資料表中是不允許使用的...會發生錯誤。。。

  並不是我們使用了暫存資料表資料庫的查詢效能一定就會得到提升，當我們的資料使用了很好的索引的時候，暫存資料表的速度可能並不快...那麼是不是任何時候都可以使用暫存資料表呢？暫存資料表的使用也是有以下限制的：

i.暫存資料表只能使用在memory,myisam,merge,innodb儲存引擎下。。。

ii.暫存資料表不支援mysql簇。。

iii.在同一個query語句中，我們只能查詢一次暫存資料表。。

iv.show table語句不會列舉暫存資料表資訊..

v.不能使用rename來重新命名一個暫存資料表，但是我們可以使用alter table語句來代替...

再舉一個實際的例子：

create table user_info(   user_id int not null,   user_name varchar(50) not null);insert into user_info values(1,‘aa‘),(2,‘bb‘).......//假設我們插入了10000條資料資訊...那麼我們想要查詢id>5000 and id<8000的資料資訊，那麼我們就可以使用一個暫存資料表了...drop procedure if exists query_performance_test; //如果存在這個預存程序則刪除掉...delimiter $$ //設定$$符號為mysql的結束符，而不是分號了...create procedure query_performance_test()begin    //以begin開始       declare begintime;  //聲明變數       declare endtime;       set begintime=curtime();  //設定為目前時間...       drop temporary if exists userinfo_tmp;  //如果暫存資料表存在則刪除掉當前暫存資料表...       create temporary table userinfo_tmp//建立暫存資料表       (            i_userid int not null,            v_username varchar(50) not null       ) engine=memory;       insert into userinfo_tmp(i_userid,v_username) select user_id,user_name from userinfo where userid>5000 and userid<8000;  //將我們想要查詢的資料放置到暫存資料表中...       select * from userinfo_tmp;       set endtime=curtime();       select endtime-begintime;end  //預存程序定義完畢，以end結束...delimiter; //將結束符號重新定義為預設的分號....call query_profromance_test(); //調用預存程序....

最後的結果就會輸出id在5000—8000之間的資料資訊，並且還會輸出查詢的時間資訊....

2.視圖

  視圖分為普通視圖和物化視圖，普通視圖是虛擬表，就是把資料庫中的基礎資料表的資料進行重新歸類，更便於使用和理解。物化視圖是實體表，除了把視圖資料進行視圖儲存外，其他類似普通視圖，但查詢速度一般要比普通視圖快，一般用於大資料量的視圖。

優點：

1.安全性：一般來時當我們建立一個資料庫的時候有一些重要的資訊是不希望使用者看見的，那麼我們就可以建立一個視圖來設定一個許可權，使得使用者只能查看自己的基本資料...更重要的資料使用者是無法得到的...

2.查詢的效能有所改善...

3.對於複雜查詢的需求，可以進行問題分解，然後將建立多個視圖擷取資料。將視圖聯合起來就能得到需要的結果了。比如進行多表查詢時，我們希望使用一個統一的方式進行查詢，那麼我們建立一個視圖，將每一個表的資料都放入到視圖當中去。。。最後我們對視圖進行操作，我們就可以得到想要的資料資訊了....

CREATE TABLE student (stuno INT ,stuname NVARCHAR(60))CREATE TABLE stuinfo (stuno INT ,class NVARCHAR(60),city NVARCHAR(60))INSERT INTO student VALUES(1,‘wanglin‘),(2,‘gaoli‘),(3,‘zhanghai‘)INSERT INTO stuinfo VALUES(1,‘wuban‘,‘henan‘),(2,‘liuban‘,‘hebei‘),(3,‘qiban‘,‘shandong‘)-- 建立視圖CREATE VIEW stu_class(id,NAME,glass) AS SELECT student.`stuno`,student.`stuname`,stuinfo.`class`FROM student ,stuinfo WHERE student.`stuno`=stuinfo.`stuno`SELECT * FROM stu_class//顯示視圖的結果...+----+----------+--------+| id | NAME     | glass  |+----+----------+--------+|  1 | wanglin  | wuban  ||  2 | gaoli    | liuban ||  3 | zhanghai | qiban  |+----+----------+--------+describe stu_class;//顯示視圖的基本資料...show table status like ‘stu_class‘//使用show方法顯示視圖的資訊....show create view stu_class //顯示視圖的詳細資料。。。顯示視圖名稱基本資料+視圖中內部操作的代碼等等....修改視圖：1.使用create or replacemysql> delimiter //mysql> create or replace view `stu_class` as select     -> `student`.`stuno` as `id` from(`student` join `stuinfo`) //join將兩個表格進行聯合...    -> where (`student`.`stuno`=`stuinfo`.`stuno`)//delimter;desc stu_class; // desc 是descbibe的縮寫，在資料庫中寫哪個都允許...select * from stu_class;  2.使用alter 。。。alter view stu_class as select stuno from student;更新視圖。。。UPDATE stu_class SET stuname=‘xiaofang‘ WHERE stuno=2//很簡單，沒什麼過多的東西。。。。刪除視圖：drop view if exists stu_class;

更新的注意事項：

（1）視圖中包含基本中被定義為非空的列

（2）定義視圖的SELECT語句後的欄位列表中使用了數學運算式

（3）定義視圖的SELECT語句後的欄位列表中使用彙總函式

（4）定義視圖的SELECT語句中使用了DISTINCT、UNION、TOP、GROUP BY 、HAVING子句

那麼我們到底什麼時候能使用到視圖呢？

  在我們定義預存程序的時候，我們為了加快查詢的效率，對資料進行複雜處理時，將代碼封裝在預存程序中，當我們調用預存程序的時候即可完成我們的查詢操作...通過代碼來完成對資料的操作....以便於我們在每次使用這類查詢時只需要調用預存程序即可....預存程序的結構屬於一個集合...

  而視圖的結構是一個表，一個虛表，不進行實際的儲存，但是我們在操作視圖的同時，那麼也就代表著操作著這個視圖的基表...視圖讓我們看到的是我們想要的資料資訊...給我的感覺，使用視圖還是由於他的安全性，可以儲存重要的資訊。。使用者只能通過視圖來對自己的那部分資訊進行操作，而沒有許可權對其他重要訊息進行操作....

Mysql 暫存資料表+視圖