SQL Server 的欄位設計 v.s. 索引
今天一大清晨，致學老師來我們這邊進行最後的效能調校測試，剛好在中間空檔可以請教問題。( 致學老師人真是太好了，每次來做顧問，都一直被我問問題…… )
今天談到了 SQL Server 如何來使用 Where ，歸納了幾點如下：
1、萬用字元請小心使用 ( 注意別將萬用字元放在搜尋字串的第 1 個字元 )
首先假設我們的 table 是有建立索引的
舉例來說，使用 where columns like ‘AB%’ ，SQL Server 在接到指令會其實是會轉換成類似 columns >= AB and columns < AC 來進行搜尋，如此一來我們就可使用索引來大幅提故搜尋效率；但是，如果是使用 where columns like ‘%AB’ ， 那就抱歉了， SQL Server 的搜尋方式是 table scan ，有幾筆資料就要搜尋幾次 ( 試想有超過百萬筆資料的話…… )
2、必須運算的欄位 ( 如果運算邏輯是一致的，請多加運算後欄位，並將該欄位建置索引 )
同樣舉例來說，我們在資料庫中有一個欄位很常需要做 substring 。 只要一使用 substring 時，就會將該欄位的所有資料，一筆一筆的進行 substring 。 如果每次運算的邏輯是一樣，建議在 insert 資料時，也多 insert 一個運算後的欄位，在將該欄位建立索引，以後如果要搜尋欄位，便可利用索引大幅提升效率。
3、資料庫欄位中如有 status 、 flag 等資料時，擴大判斷數字
這不舉例應該很難理解唄 = =
假設我們有一個 table 是專門存放簡訊收發相關資訊，該 table 有一個欄位是 status ，而 status 有包含 ok (確實收到訊息)、 cn (無訊號)、 ck (使用者簡訊量爆掉) 、cx 、ci 等代表不同種類的回應訊息。 每個種類的回應訊息都有被賦予一個數字，該數字的欄位名稱是 status_number。
重點來了喔………..，
如果數字是連續的 !!!
ok = 1 、 cn = 2 、 …… 依此類推。 試問，如果以後又多了一種回應訊息是代表使用者有收到簡訊，假設是 kk = 6 。 問題來了，管理者想知道所有成功的回應訊息，那不就要 where status_number = 1 or 6 。這代表，table scan 又來了……
所以可以將成功訊息擴大，例如，成功相關訊息是 1~50 ，失敗相關訊息是 51~100 。這樣搜尋語法可改為 where status_number < 50 ，就可以使用索引了。 ( 如果成功訊息大於 50 種的話，俺也認了………….. )
留言與評論
The Zune concentrates on being a Portable Media Player. Not a web browser. Not a game machine. Maybe in the future it'll do even better in those areas, but for now it's a fantastic way to organize and listen to your music and videos, and is without peer in that regard. The iPod's strengths are its web browsing and apps. If those sound more compelling, perhaps it is your best choice.
Ready Or NotKöln buchen http://www.nikolausservice.com
Nikolaus Service KölnThe new Zune browser is surprisingly good, but not as good as the iPod's. It works well, but isn't as fast as Safari, and has a clunkier interface. If you occasionally plan on using the web browser that's not an issue, but if you're planning to browse the web alot from your PMP then the iPod's larger screen and better browser may be important.
Public EnemyI was just looking for this info for some time. After six hours of continuous Googleing, at last I got it in your site. I wonder what is the lack of Google strategy that don't rank this kind of informative web sites in top of the list. Usually the top sites are full of garbage.
SEOwww.quickieseo.com
SEO DoncasterIt’s actually a nice and helpful piece of information. I’m happy that you simply shared this helpful information with us. Please stay us up to date like this. Thanks for sharing.
Internet Marketingvery nice post, i certainly love this website, keep on it
Cool blogs of 2013Its like you learn my thoughts! You seem to know a lot approximately this, such as you wrote the e-book in it or something. I feel that you simply could do with some percent to power the message home a little bit, but other than that, that is excellent blog. A great read. I'll definitely be back.
Hi Bloggersnaturally like your web site but you need to check the spelling on several of your posts. Several of them are rife with spelling issues and I find it very bothersome to tell the truth nevertheless I’ll certainly come back again.
Hi Bloggers本篇文章的留言功能已關閉！