mysql的中成插入及插入和替換忽略用法區別

http://www.phpzixue.cn/detail1145.shtml

mysql中insert into和replace into以及insert ignore用法區別:
mysql中常用的三種插入數據的語句:
insert into表示插入數據,數據庫會檢查主鍵,如果出現重複會報錯;
replace into表示插入替換數據,需求表中有PrimaryKey,或者unique索引,如果數據庫已經存在數據,則用新數據替換,如果沒有數據效果則和insert into一樣;
insert ignore表示,如果中已經存在相同的記錄,則忽略當前新數據;
下面通過代碼說明之間的區別,如下:
create table testtb(
id int not null primary key,
name varchar(50),
age int
);
insert into testtb(id,name,age)values​​(1,"bb",13);
select * from testtb;
insert ignore into testtb(id,name,age)values​​(1,"aa",13);
select * from testtb;//仍是1,“bb”,13,因為id是主鍵,出現主鍵重複但使用了ignore則錯誤被忽略
replace into testtb(id,name,age)values​​(1,"aa",12);
select * from testtb; //數據變為1,"aa",12

Simulator 和 Emulator的差異

http://neochung.blogspot.com/2008/09/simulator-emulator.html

行動裝置(mobile device) 自從PDA, Smart Phone上市之後,不斷的發展
除了硬體上的進步外,軟體也為了適應在有限的顯示裝置上提供更好的操作與介面
也是百家爭鳴的一塊大餅,Symbian、Windows Mobile, iPhone OS and Android等

*Symbian是由Ericsson、Panasonic、NOKIA、Simens AG、Ericsson等公司所共同開發並擁用的專為手機硬體而設計的作業系統
*Windows Mobile 是由微軟針對行動裝置而設計的作業系統
*iPhone OS是由Apple公司發售的iPhone手機的作業系統
*Android是由Google針對行動裝置而設計的作業系統

以上都有提供SDK給軟體設計師開發軟體。

軟體開發的時候,各家廠商會提供模擬器讓軟體設計師撰寫應用系統,因此模擬器
對於開發人員來說是很重要的測試工具。正式開發之前,有兩個字大家必須要知道
就是Simulator與Emulator

Simulator : 透過您電腦的硬體環境運作軟體,因此效能會比較好
Emulator : 完完全全的硬體模擬,讓你了解軟體在實機上運作的結果

因此開發軟體後,最好還是透過Emulator測試之後再移到實機上執行,避免不必要的錯誤發生
也就是大家平常開發軟體時常用的Prod環境(實機)或Educ環境(Emulator)

Google Chart API Online

http://imagecharteditor.appspot.com/

SMARTY模板中如何使用get,post,request,cookies,session,server變量

http://limon87.iteye.com/blog/1030489

{$smarty}保留變量不需要從PHP腳本中分配,是可以在模板中直接訪問的數組類型變量,通常被用於訪問一些特殊的模板變量。例如,直接在模板中訪問頁面請求變量、獲取訪問模板時的時間戳、直接訪問PHP中的常量、從配置文件中讀取變量等。

1.在模板中訪問頁面請求變量

我們可以在PHP腳本中,通過超級全局數組$_GET、$_POST、$_REQUEST獲取在客戶端以不同方法提交給服務器的數據,也可以通過$_COOKIE或$_SESSION在多個腳本之間跟踪變量,或是通過$_ENV和$_SERVER獲取系統環境變量。如果在模板中需要這些數組,可以調用Smarty對像中的assign()方法分配給模板。但在Smarty模板中,直接就可以通過{$smarty}保留變量訪問這些頁面請求變量。在模板中使用的示例如下所示:
PHP代碼

{$smarty.get.page} {* PHP方式:$_GET[“page"] *}

{$smarty.post.page} {* PHP方式:$_POST[“page"] *}

{$smarty.cookies.username} {* PHP方式:$_COOKIE[“username"] *}

{$smarty.session.id} {* PHP方式:$_SESSION[“id"] *}

{$smarty.server.SERVER_NAME} {* PHP方式:$_SERVER[“SERVER_NAME"] *}

{$smarty.env.PATH} {* PHP方式:$_ENV[“PATH"]*}

{$smarty.request.username} {* PHP方式:$_REQUEST[“username"] *}
2.在模板中訪問PHP中的變量

在PHP腳本中有系統常量和自定義常量兩種,同樣這兩種常量在Smarty模板中也可以被訪問,而且不需要從PHP中分配,只要通過{$smarty}保留變量就可以直接輸出常量的值。在模板中輸出常量的示例如下所示:
PHP代碼

{$smarty.const._MY_CONST_VAL} {* 輸出PHP腳本中自定義的常量*}

{$smarty.const.__FILE__} {* 通過保留變量數組直接輸出系統常量*}
3.其它
PHP代碼

1.{$smarty.now}變量用於訪問當前時間戳.

可以用date_format調節器格式化輸出. 例如{$smarty.now|date_format:"%Y-%m-%d %H:%M:%S"}

{$smarty.const}

你可以直接訪問PHP常量. 例如{$smarty.const._MY_CONST_VAL}

{$smarty.capture}

可以通過{capture}..{/capture}結構截取的輸出可以使用{$smarty} 變量訪問.

{$smarty.config}

{$smarty}變量可以訪問已經加載的config變量.

例如{$smarty.config.foo}就可以表示{#foo#}.

{$smarty.section}, {$smarty.foreach}

{$smarty} 變量可以訪問’section’和’foreach’循環的屬性.

{$smarty.template} 顯示當前被處理的模板的名字.

{$smarty.version}

顯示smarty模板的版本

{$smarty.ldelim} 顯示左分隔符

{$smarty.rdelim} 顯示右分隔符

Say Goodbye to the Menu Button

Before Android 3.0 (Honeycomb), all Android-powered devices included a dedicated Menu button. As a developer, you could use the Menu button to display whatever options were relevant to the user, often using the activity’s built-in options menu. Honeycomb removed the reliance on physical buttons, and introduced the ActionBar class as the standard solution to make actions from the user options immediately visible and quick to invoke. In order to provide the most intuitive and consistent user experience in your apps, you should migrate your designs away from using the Menu button and toward using the action bar. This isn’t a new concept — the action bar pattern has been around on Android even before Honeycomb — but as Ice Cream Sandwich rolls out to more devices, it’s important that you begin to migrate your designs to the action bar in order to promote a consistent Android user experience.

You might worry that it’s too much work to begin using the action bar, because you need to support versions of Android older than Honeycomb. However, it’s quite simple for most apps because you can continue to support the Menu button on pre-Honeycomb devices, but also provide the action bar on newer devices with only a few lines of code changes.

If I had to put this whole post into one sentence, it’d be: Set targetSdkVersion to 14 and, if you use the options menu, surface a few actions in the action bar with showAsAction="ifRoom".

Don’t call it a menu

Not only should your apps stop relying on the hardware Menu button, but you should stop thinking about your activities using a “menu button” at all. Your activities should provide buttons for important user actions directly in the action bar (or elsewhere on screen). Those that can’t fit in the action bar end up in the action overflow.

In the screenshot here, you can see an action button for Search and the action overflow on the right side of the action bar.

Even if your app is built to support versions of Android older than 3.0 (in which apps traditionally use the options menu panel to display user options/actions), when it runs on Android 3.0 and beyond, there’s no Menu button. The button that appears in the system/navigation bar represents the action overflow for legacy apps, which reveals actions and user options that have “overflowed off the screen.”

This might seem like splitting hairs over terminology, but the name action overflow promotes a different way of thinking. Instead of thinking about a menu that serves as a catch-all for various user options, you should think more about which user options you want to display on the screen as actions. Those that don’t need to be on the screen can overflow off the screen. Users can reveal the overflow and other options by touching an overflow button that appears alongside the on-screen action buttons.

Action overflow button for legacy apps

If you’ve already developed an app to support Android 2.3 and lower, then you might have noticed that when it runs on a device without a hardware Menu button (such as a Honeycomb tablet or Galaxy Nexus), the system adds the action overflow button beside the system navigation.

This is a compatibility behavior for legacy apps designed to ensure that apps built to expect a Menu button remain functional. However, this button doesn’t provide an ideal user experience. In fact, in apps that don’t use an options menu anyway, this action overflow button does nothing and creates user confusion. So you should update your legacy apps to remove the action overflow from the navigation bar when running on Android 3.0+ and begin using the action bar if necessary. You can do so all while remaining backward compatible with the devices your apps currently support.

If your app runs on a device without a dedicated Menu button, the system decides whether to add the action overflow to the navigation bar based on which API levels you declare to support in the <uses-sdk> manifest element. The logic boils down to:

  • If you set either minSdkVersion or targetSdkVersion to 11 or higher, the system will not add the legacy overflow button.
  • Otherwise, the system will add the legacy overflow button when running on Android 3.0 or higher.
  • The only exception is that if you set minSdkVersion to 10 or lower, set targetSdkVersion to 11, 12, or 13, and you do not use ActionBar, the system will add the legacy overflow button when running your app on a handset with Android 4.0 or higher.

That exception might be a bit confusing, but it’s based on the belief that if you designed your app to support pre-Honeycomb handsets and Honeycomb tablets, it probably expects handset devices to include a Menu button (but it supports tablets that don’t have one).

So, to ensure that the overflow action button never appears beside the system navigation, you should set the targetSdkVersion to 14. (You can leaveminSdkVersion at something much lower to continue supporting older devices.)

Migrating to the action bar

If you have activities that use the options menu (they implement onCreateOptionsMenu()), then once the legacy overflow button disappears from the system/navigation bar (because you’ve set targetSdkVersion to 14), you need to provide an alternative means for the user to access the activity’s actions and other options. Fortunately, the system provides such a means by default: the action bar.

Add showAsAction="ifRoom" to the <item> elements representing the activity’s most important actions to show them in the action bar when space is available. For help deciding how to prioritize which actions should appear in the action bar, see Android Design’s Action Bar guide.

To further provide a consistent user experience in the action bar, we suggest that you use action icons designed by the Android UX Team where appropriate. The available icons support common user actions such as Refresh, Delete, Attach, Star, Share and more, and are designed for the light and dark Holo themes. Here they are!

If these icons don’t accommodate your needs and you need to create your own, you should follow the Iconography design guide.

Removing the action bar

If you don’t need the action bar, you can remove it from your entire app or from individual activities. This is appropriate for apps that never used the options menu or for apps in which the action bar doesn’t meet design needs (such as games). You can remove the action bar using a theme such asTheme.Holo.NoActionBar or Theme.DeviceDefault.NoActionBar.

In order to use such a theme and remain backward compatible, you can use Android’s resource system to define different themes for different platform versions, as described by Adam Powell’s post, Holo Everywhere. All you need is your own theme, which you define to inherit different platform themes depending on the current platform version.

For example, here’s how you can declare a custom theme for your application:

<application android:theme="@style/NoActionBar">

Or you can instead declare the theme for individual <activity> elements.

For pre-Honeycomb devices, include the following theme in res/values/themes.xml that inherits the standard platform theme:

<resources>
    <style name="NoActionBar" parent="@android:style/Theme">
        <!-- Inherits the default theme for pre-HC (no action bar) -->
    </style>
</resources>

For Honeycomb and beyond, include the following theme in res/values-v11/themes.xml that inherits a NoActionBar theme:

<resources>
    <style name="NoActionBar" parent="@android:style/Theme.Holo.NoActionBar">
        <!-- Inherits the Holo theme with no action bar; no other styles needed. -->
    </style>
</resources>

At runtime, the system applies the appropriate version of the NoActionBar theme based on the system’s API version.

Summary

    • Android no longer requires a dedicated Menu button, some devices don’t have one, and you should migrate away from using it.
    • Set targetSdkVersion to 14, then test your app on Android 4.0.
    • Add showAsAction="ifRoom" to menu items you’d like to surface in the action bar.

 

    • If the ActionBar doesn’t work for your app, you can remove it with Theme.Holo.NoActionBar or Theme.DeviceDefault.NoActionBar.

 

For information about how you should design your action bar, see Android Design’s Action Bar guide. More information about implementing the action bar is also available in the Action Bar developer guide.

The Latest in HTML 5

http://html5-demos.appspot.com/static/html5-whats-new/template/index.html#1

WebSockets vs Server-Sent Events vs Long-polling

http://dsheiko.com/weblog/websockets-vs-sse-vs-long-polling

Apparently social networking is the banner of the nowadays web. Everybody intends bringing some features into his projects. Some of them require immediate notification. That is getting common, if you open a page with upcoming messages (status feed, notification subsystem, friends-list), you expected them being updated as soon as a new message (status, notification, friend-making action) arrives. As you well know, original web design allowed only one-way client-server communication (one requests, another one responds), though now HTML5 working group doing their best to fix it or rather to patch it. However, the web-projects are still using long-polling trick to emulate server-client communication.

Well, now new web browser versions appear every few months. Besides they update automatically. Thus a huge number of users have the latest browser versions, which support HTML 5 communication APIs. Is that the time to put long-polling away? Let’s find out.

Our test task will be something you may likely need if you have on your site any sort of user communication. That is notification of user actions. In the simplest case when the user gets a private message, the number of unread notifications increases in the user panel. We will solve the task using long-polling, Server-Sent Events and WebSockets. Then we compare the results.

First of all let’s examine the common code used in the examples. We will need configuration file, a library to access DB, a model to retrieve unread notification number and to add a new notification.

Usually such communication API examples don’t include any business logic, but execution delays to emulate the model working. I would like to make it close to the real application, what is meant to help when comparing memory/CPU usage on the server for each of the cases.

So, we will need a dump DB table:

 

CREATE TABLE IF NOT EXISTS `notification` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `recipientUid` int(10) unsigned NOT NULL, `eventId` int(10) unsigned NOT NULL, `isNew` tinyint(1) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `IX_recipientUid` (`recipientUid`), KEY `IX_isNew` (`isNew`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User notifications';

 

Here is the model:

<?php class Model_UserNotification {    private $_db;    public function  __construct(Lib_Db $db)    {         $this->_db = $db;    }    /**     *     * @param int $recipientUid     * @return int     */    public function fetchNumberByRecipientUid($recipientUid)    {        return $this->_db->fetch("SELECT count(*) as count "            . " FROM notification WHERE recipientUid = %d AND isNew = 1"            , $recipientUid)->count;    }    /**     *     * @param int $recipientUid     * @param int $eventId     */    public function add($recipientUid, $eventId)    {        $this->_db->update("INSERT INTO "            . " notification (`id`, `recipientUid`, `eventId`, `isNew`) VALUES (NULL, '%d', '%d', '1')"            , $recipientUid, $eventId);    }    /**     *     * @param int $recipientUid     */    public function removeAll($recipientUid)    {        $this->_db->update("DELETE FROM "            . " notification WHERE recipientUid = %d"            , $recipientUid);    } }

 

Long pulling

 Long-polling technique explanation

 

How it works

Client application (browser) sends a request with event recipient id (here is the user, registered on the page) and current state (the displayed number of unread notification) to the server via HTTP. It creates an Apache process, which repeatedly checks DB until the state is changed in there. When the state eventually changed, the client gets the server response and sends next request to the server.

 

Implementation

Client side contains simple HTML with two input fields to show response data (updated number of unread notifications and time of the response event). JS module sends recipient user id and current state (unread notification number) to the server as XMLHttpRequest. To make possible cross-domain communication, we use JSONP and that means the handler must of the public scope.

 

... <p>Recipient id: <?= $recipientUid ?></p> <p>Notifications: <input id="notificationNum" size="4" name="some" value="<?= $displayedNotificationNum ?>" /></p> <p>Last event arrived at: <input id="time" size="12" name="some" value="0" /></p> <script type="text/javascript"> (function( $ ) { var UID = <?= $recipientUid ?>; $.NotifierLongPolling = (function() {     var _stateNode = $('#notificationNum'), _timeNode = $('#time');     return {         onMessage : function(data) {             _stateNode.val(data.updatedNotificationNum);             _timeNode.val(data.time);             setTimeout($.NotifierLongPolling.send, 3000);         },         send : function() {                        $.ajax({                     'url': 'server.php',                     'type': 'POST',                     'dataType': 'jsonp',                     'jsonpCallback': '$.NotifierLongPolling.onMessage',                     'data': 'recipientUid=' + UID + '&displayedNotificationNum='                         + _stateNode.val()             });         }     } }()); // Document is ready $(document).bind('ready.app', function() {    setTimeout($.NotifierLongPolling.send, 40);  }); })( jQuery ); </script>

 

Server waits 3 second than check if the updated state matches the given one. If the state has changed in the DB, the server responds otherwise it repeats the cycle.

 

//... $recipientUid = (int)$_REQUEST["recipientUid"]; $displayedNotificationNum = (int)$_REQUEST["displayedNotificationNum"]; $secCount = 0; do {     sleep(IDLE_TIME);     $updatedNotificationNum = $model->fetchNumberByRecipientUid($recipientUid); } while ($updatedNotificationNum == $displayedNotificationNum); header("HTTP/1.0 200"); printf ('%s({"time" : "%s", "updatedNotificationNum" : "%d"});'     , $_REQUEST["callback"], date('d/m H:i:s'), $updatedNotificationNum);

 

Client side receives new state from the server, displays and sends to the server the new state to repeat the workflow.

 

 Long-polling technique usage example screenshot

 

Server-Sent Events

 

 Server-Sent Event API explanation

 

How it works

Client (browser) sends a request to the server via HTTP. It creates a process, which fetches latest state in the DB and responds back. Client gets server response and in 3 seconds sends next request to the server.

 

Implementation

HTML on client-size has again two input fields to show response data. JS module opens EventSource and passes though the connection recipient user id.

 

... <p>Recipient id: <?= $recipientUid ?></p> <p>Notifications: <input id="notificationNum" size="4" name="some" value="<?= $displayedNotificationNum ?>" /></p> <p>Last event arrived at: <input id="time" size="12" name="some" value="0" /></p> <script type="text/javascript"> (function( $ ) { var UID = <?= $recipientUid ?>; NotifierSSE = (function() {     var _stateNode = $('#notificationNum'),         _timeNode = $('#time'),         _src,         _handler = {         onMessage : function(event) {                          var data = JSON.parse(event.data);             _stateNode.val(data.updatedNotificationNum);             _timeNode.val(data.time);         }     };     return {         init : function () {         _src = new EventSource("server.php?recipientUid=" + UID);         _src.addEventListener('message', _handler.onMessage, false);         }     } }()); // Document is ready $(document).bind('ready.app', function() {    setTimeout(NotifierSSE.init, 40);  }); })( jQuery ); </script>

 

Server responds into the data stream with the last state (unread notification number) regarding the recipient user id.

 

//... header('Content-Type: text/event-stream'); header('Cache-Control: no-cache'); // recommended to prevent caching of event data. $recipientUid = (int)$_REQUEST["recipientUid"]; function send($updatedNotificationNum) {     printf ("id: %s\n\n", PROC_ID);     printf ('data: {"time" : "%s", "updatedNotificationNum" : "%d"}' . "\n\n"         ,date('d/m H:i:s') , $updatedNotificationNum);     ob_flush();     flush(); } while (true) {     send($model->fetchNumberByRecipientUid($recipientUid));     sleep(IDLE_TIME); }  //...

 

Client gets onMessage event handler invoked, the data displayed. Browser will repeat request every 3 second unless you close the connection (close method). You can change delay between requests by passing into the data stream on the server “retry: 

 

 Server-Sent Event API usage example screenshot

 

WebSockets

 

 WebSocket API explanation

 

How it works

Client notifies web-socket server (EventMachine) of an event, giving ids of recipients. The server immediately notifies all the active clients (subscribed to that type of event). Clients process event when given recipient Id matches the client’s one.

 

Implementation

Here we need an Event Machine supporting WebSockets. The easiest way, I see, is to deployWaterSpout Server . You write your own controller (notification_controller.php) based on locke_controller.php.

Client opens WebSocket connection and subscribes a handler for events on /notification/updates post. Now let’s add a button in the HTML which sends on /notification/presents recipient user and action ids. That will cause broadcast message on the all open connections. So every active client receives the notification. The client event handler checks if the recipient user id matches client’s logged in user id and if so increment unread notification number.

 

<p>Recipient id: <?= $recipientUid ?></p>     <p>Notification: <span id="display"></span></p>     <button id="test">Fire an event</button> <script>     realtime = new realtimeComm(window.location.host + ":20001");     realtime.addListener('/notification/updates', function(response) {         $('#display').html('Client #' + response.data.recipientUid + ' broadcast an action #' + response.data.actionId);     });     $('#test').bind('click', this, function(e){         e.preventDefault();         realtime.send('/notification/presence', {             'actionId': 1,             'recipientUid': <?= $recipientUid ?>         }, function() {});     }); </script>

 

Working a bit on the controller, you can make the system responds exclusively to the client of the given recipient user id.

You’ve probably noticed the example working perfect on Chrome, but for Firefox 6.0 it switches for long-polling. It announced that Firefox 6 supports WebSockets, but you ain’t going to find WebSocket object defined. Well, let’s change WebSocket availability check in the top of realtime.js with following:

 

if (!window.WebSocket) {             window.WebSocket = window.MozWebSocket ? window.MozWebSocket : undefined;  }

 

Oops, it doesn’t work anyway. Firefox 6 supports hybi-07 specification version, when Chrome and WaterSpout – hybi-00 (hixie-76). So, you will hardly able the connection through reverse-proxies and gateways. Besides, the old specification was disabled in Firefox and Opera due to security issues. The only browsers now supporting the latest WebSocket specification are betas of Firefox 7/8 and Chrome 14. As for EventMachine server implementations, I found none.

 

 WebSocket API usage example screenshot

 

Conclusion

 

.. Long-polling Server-Sent Events WebSockets
Browser support Supported by the most of currently used browsers Supported by Chrome 9+, Firefox 6+, Opera 11+, Safari 5+ The latest hybi-10 protocol supported by Chrome 14, Firefox 7 betas, hybi-07 supported by Firefox 6
Server-loading Takes little of CPU resources, but creates idle processes per user expending server memory Works in many ways as long-polling, unless SSE doesn’t need to close connection every time when response is sent The best possible solution. Server has the only process serving any requests. No loops, memory/CPU expense per client, but per client action
Client-loading Depends on implementation, though it always an asynchronous process. Natively implemented in the browser, takes minimum resources Natively implemented in the browser, takes minimum resources
Timeliness Near real-time, though black out period between sending response and making a new request adds some delay Default delay 3 sec., but can be changed True real-time
Complexity of implementation Very easy Even easier Requires an EventMachine server and a custom port being open

GPU Comparisons – Performance Comparison Benchmarks

http://www.hwcompare.com/category/gpu-comparisons/

Android and the HTTP download file headers

http://digiblog.de/2011/04/19/android-and-the-download-file-headers/

MYSQL查詢的性能優化

http://doc.linuxpk.com/66995.html

查詢是數據庫技術中最常用的操作。 查詢操作的過程比較簡單,首先從客戶端發出查詢的SQL語句,數據庫服務端在接收到由客戶端發來的SQL語句後, 執行這條SQL語句,然後將查詢到的結果返回給客戶端。 雖然過程很簡單,但不同的查詢方式和數據庫設置,對查詢的性能將會有很在的影響。

因此,本文就在MySQL中常用的查詢優化技術進行討論。 討論的內容如:通過查詢緩衝提高查詢速度;MySQL對查詢的自動優化;基於索引的排序;不可達查詢的檢測和使用各種查詢選擇來提高性能。

一、 通過查詢緩衝提高查詢速度

一般我們使用SQL語句進行查詢時,數據庫服務器每次在收到客戶端發來SQL後,都會執行這條SQL語句。 但當在一定間隔內(如1分鐘內),接到完全一樣的SQL語句,也同樣執行它。 雖然這樣可以保證數據的實時性,但在大多數時候,數據並不要求完全的實時,也就是說可以有一定的延時。 如果是這樣的話,在短時間內執行完全一樣的SQL就有些得不償失。

幸好MySQL為我們提供了查詢緩衝的功能(只能在MySQL 4.0.1及以上版本使用查詢緩衝)。 我們可以通過查詢緩沖在一定程度上提高查詢性能。

我們可以通過在MySQL安裝目錄中的my.ini文件設置查詢緩衝。 設置也非常簡單,只需要將query_cache_type設為1即可。 在設置了這個屬性後,MySQL在執行任何SELECT語句之前,都會在它的緩衝區中查詢是否在相同的SELECT語句被執行過,如果有,並且執行結果沒有過期,那麼就直接取查詢結果返回給客戶端。 但在寫SQL語句時注意,MySQL的查詢緩衝是區分大小寫的。 如下列的兩條SELECT語句:

  1. SELECT * from TABLE1
  2. SELECT * FROM   TABLE1

上面的兩條SQL語句對於查詢緩衝是完全不同的SELECT。 而且查詢緩衝並不自動處理空格,因此,在寫SQL語句時,應盡量減少空格的使用,尤其是在SQL首和尾的空格(因為,查詢緩衝並不自動截取首尾空格)。

雖然不設置查詢緩衝,有時可能帶來性能上的損失,但有一些SQL語句需要實時地查詢數據,或者並不經常使用(可能一天就執行一兩次)。 這樣就需要把緩衝關了。 當然,這可以通過設置query_cache_type的值來關閉查詢緩衝,但這就將查詢緩衝永久地關閉了。 在MySQL 5.0中提供了一種可以臨時關閉查詢緩衝的方法:

  1. SELECT SQL_NO_CACHE field1 , field2 FROM TABLE1

以上的SQL語句由於使用了SQL_NO_CACHE,因此,不管這條SQL語句是否被執行過,服務器都不會在緩衝區中查找,每次都會執行它。

我們還可以將my.ini中的query_cache_type設成2,這樣只有在使用了SQL_CACHE後,才使用查詢緩衝。

  1. SELECT SQL_CALHE * FROM TABLE1

二、MySQL對查詢的自動優化

索引對於數據庫是非常重要的。 在查詢時可以通過索引來提高性能。 但有時使用索引反而會降低性能。 我們可以看如下的SALES表:

  1. CREATE TABLE SALES
  2. (
  3. ID   INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
  4. NAME   VARCHAR ( 100 ) NOT NULL ,
  5. PRICE   FLOAT NOT NULL ,
  6. SALE_COUNT   INT NOT NULL ,
  7. SALE_DATE   DATE NOT NULL ,
  8. PRIMARY   KEY ( ID ) ,
  9. INDEX   ( NAME ) ,
  10. INDEX   ( SALE_DATE )
  11. ) ;

假設這個表中保存了數百萬條數據,而我們要查詢商品號為1000的商品在2004年和2005年的平均價格。 我們可以寫如下的SQL語句:
SELECT AVG(PRICE) FROM SALES

WHERE ID = 1000 AND SALE_DATE BETWEEN ‘2004-01-01’ AND ‘2005-12-31’;

如果這種商品的數量非常多,差不多佔了SALES表的記錄的50%或更多。 那麼使用SALE_DATE字段上索引來計算平均數就有些慢。 因為如果使用索引,就得對索引進行排序操作。 當滿足條件的記錄非常多時(如佔整個表的記錄的50%或更多的比例),速度會變慢,這樣還不如對整個表進行掃描。 因此,MySQL會自動根據滿足條件的數據佔整個表的數據的比例自動決定是否使用索引進行查詢。

對於MySQL來說,上述的查詢結果占整個表的記錄的比例是30%左右時就不使用索引了,這個比例是MySQL的開發人員根據他們的經驗得出的。 然而,實際的比例值會根據所使用的數據庫引擎不同而不同。

三、 基於索引的排序

MySQL的弱點之一是它的排序。 雖然MySQL可以在1秒中查詢大約15,000條記錄,但由於MySQL在查詢時最多只能使用一個索引。 因此,如果WHERE條件已經佔用了索引,那麼在排序中就不使用索引了,這將大大降低查詢的速度。 我們可以看看如下的SQL語句:

  1. SELECT * FROM SALES WHERE NAME = “ name ” ORDER BY SALE_DATE DESC ;

在以上的SQL的WHERE子句中已經使用了NAME字段上的索引,因此,在對SALE_DATE進行排序時將不再使用索引。 為了解決這個問題,我們可以對SALES表建立復合索引:

  1. ALTER TABLE SALES DROP INDEX NAME , ADD INDEX ( NAME , SALE_DATE )

這樣再使用上述的SELECT語句進行查詢時速度就會大副提升。 但要注意,在使用這個方法時,要確保WHERE子句中沒有排序字段,在上例中就是不能用SALE_DATE進行查詢,否則雖然排序快了,但是SALE_DATE字段上沒有單獨的索引,因此查詢又會慢下來。

四、 不可達查詢的檢測

在執行SQL語句時,難免會遇到一些必假的條件。 所謂必假的條件是無論表中的數據如何變化,這個條件都為假。 如WHERE value < 100 AND value > 200。 我們永遠無法找到一個既小於100又大於200的數。

如果遇到這樣的查詢條件,再去執行這樣的SQL語句就是多此一舉。 幸好MySQL可以自動檢測這種情況。 如我們可以看看如下的SQL語句:

  1. SELECT * FROM SALES WHERE NAME = “ name1 ” AND NAME = “ name2 ”

以上的查詢語句要查找NAME既等於name1又等於name2的記錄。 很明顯,這是一個不可達的查詢,WHERE條件一定是假。MySQL在執行SQL語句之前,會先分析WHERE條件是否是不可達的查詢,如果是,就不再執行這條SQL語句了。 為了驗證這一點。 我們首先對如下的SQL使用EXPLAIN進行測試:

  1. EXPLAIN SELECT * FROM SALES WHERE NAME = “ name1 ”

上面的查詢是一個正常的查詢,我們可以看到使用EXPLAIN返回的執行信息數據中table項是SALES。 這說明MySQL對SALES進行操作了。 再看看下面的語句:

  1. EXPLAIN SELECT * FROM SALES WHERE NAME = “ name1 ” AND NAME = “ name2 ”

我們可以看到,table項是空,這說明MySQL並沒有對SALES表進行操作。

五、 使用各種查詢選擇來提高性能

SELECT語句除了正常的使用外,MySQL還為我們提供了很多可以增強查詢性能的選項。 如上面介紹的用於控制查詢緩衝的SQL_NO_CACHE和SQL_CACHE就是其中兩個選項。 在這一部分,我將介紹幾個常用的查詢選項。

1. STRAIGHT_JOIN:強制連接順序

當我們將兩個或多個表連接起來進行查詢時,我們並不用關心MySQL先連哪個表,後連哪個表。 而這一切都是由MySQL內部通過一系列的計算、評估,最後得出的一個連接順序決定的。 如下列的SQL語句中,TABLE1和TABLE2並不一定是誰連接誰:

  1. SELECT TABLE1 . FIELD1 , TABLE2 . FIELD2 FROM TABLE1 , TABLE2 WHERE …

如果開發人員需要人為地干預連接的順序,就得使用STRAIGHT_JOIN關鍵字,如下列的SQL語句:

  1. SELECT TABLE1 . FIELD1 , TABLE2 . FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL語句可知,通過STRAIGHT_JOIN強迫MySQL按TABLE1、TABLE2的順序連接表。 如果你認為按自己的順序比MySQL推薦的順序進行連接的效率高的話,就可以通過STRAIGHT_JOIN來確定連接順序。

2. 干預索引使用,提高性能

在上面已經提到了索引的使用。 一般情況下,在查詢時MySQL將自己決定是否使用索引,使用哪一個索引。 但在一些特殊情況下,我們希望MySQL只使用一個或幾個索引,或者不希望使用某個索引。 這就需要使用MySQL的控制索引的一些查詢選項。

限制使用索引的範圍

有時我們在數據表裡建立了很多索引,當MySQL對索引進行選擇時,這些索引都在考慮的範圍內。 但有時我們希望MySQL只考慮幾個索引,而不是全部的索引,這就需要用到USE INDEX對查詢語句進行設置。

  1. SELECT * FROM TABLE1 USE INDEX ( FIELD1 , FIELD2 ) …

從以上SQL語句可以看出,無論在TABLE1中已經建立了多少個索引,MySQL在選擇索引時,只考慮在FIELD1和FIELD2上建立的索引。

限制不使用索引的範圍

如果我們要考慮的索引很多,而不被使用的索引又很少時,可以使用IGNORE INDEX進行反向選取。 在上面的例子中是選擇被考慮的索引,而使用IGNORE INDEX是選擇不被考慮的索引。

  1. SELECT * FROM TABLE1 IGNORE INDEX ( FIELD1 , FIELD2 ) …

在上面的SQL語句中,TABLE1表中只有FIELD1和FIELD2上的索引不被使用。

強迫使用某一個索引

上面的兩個例子都是給MySQL提供一個選擇,也就是說MySQL並不一定要使用這些索引。 而有時我們希望MySQL必須要使用某一個索引(由於MySQL在查詢時只能使用一個索引,因此只能強迫MySQL使用一個索引)。 這就需要使用FORCE INDEX來完成這個功能。

  1. SELECT * FROM TABLE1 FORCE INDEX ( FIELD1 ) …

以上的SQL語句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。

3. 使用臨時表提供查詢性能

當我們查詢的結果集中的數據比較多時,可以通過SQL_BUFFER_RESULT.選項強制將結果集放到臨時表中,這樣就可以很快地釋放MySQL的表鎖(這樣其它的SQL語句就可以對這些記錄進行查詢了),並且可以長時間地為客戶端提供大記錄集。

  1. SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

和SQL_BUFFER_RESULT.選項類似的還有SQL_BIG_RESULT,這個選項一般用於分組或DISTINCT關鍵字,這個選項通知MySQL,如果有必要,就將查詢結果放到臨時表中,甚至在臨時表中進行排序。

  1. SELECT SQL_BUFFER_RESULT FIELD1 , COUNT ( * ) FROM TABLE1 GROUP BY FIELD1

六、 結論

在程序設計中同樣存在一個“二八原則”,即20%的代碼用去了80%的時間。 數據庫應用程序的開發亦然。 數據庫應用程序的優化,重點在於SQL的執行效率。 而數據查詢優化的重點,則是使得數據庫服務器少從磁盤中讀數據以及順序讀頁而不是非順序讀頁。