Archive for 一月 2012

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的執行效率。 而數據查詢優化的重點,則是使得數據庫服務器少從磁盤中讀數據以及順序讀頁而不是非順序讀頁。

Learn CSS Positioning in Ten Steps

http://www.barelyfitz.com/screencast/html-training/css/positioning/

訂閱JavaScript及HTML5每星期的最新資訊

A free, once–weekly e-mail round-up of JavaScript news and articles.
http://javascriptweekly.com/

Top browser technology news and links straight to your inbox, weekly
http://html5weekly.com/

jQuery的.bind()、.live()和.delegate()之間區別

http://developer.51cto.com/art/201103/249694.htm

jQuery的.bind()、.live()和.delegate()之間的區別並非總是那麼明顯的,然而,如果我們對所有的不同之處都有清晰的理解的話,那麼這將會有助於我們編寫出更加簡潔的代碼,以及防止在交互應用中彈出錯誤。

AD:

 

基本要素

jQuery

51CTO推薦專題: jQuery從入門到精通

DOM樹

首先,可視化一個HMTL文檔的DOM樹是很有幫助的。 一個簡單的HTML頁面看起來就像是這個樣子:

jQuery

事件冒泡(又稱事件傳播)

當我們點擊一個鏈接時,其觸發了鏈接元素的單擊事件,該事件則引發任何我們已綁定到該元素的單擊事件上的函數的執行。

  1. $( ‘a’ ).bind( ‘click’ , function () { alert( “That tickles!” ) });

因此一個單擊操作會觸發alert函數的執行。

jQuery

click事件接著會向樹的根方向傳播,廣播到父元素,然後接著是每個祖先元素,只要是它的某個後代元素上的單擊事件被觸發,事件就會傳給它。

jQuery

在操縱DOM的語境中,document是根節點。

現在我們可以較容易地說明.bind()、.live()和.delegate()的不同之處了。

.bind()

  1. $( ‘a’ ).bind( ‘click’ , function () { alert( “That tickles!” ) });

這是最簡單的綁定方法了。 JQuery掃描文檔找出所有的$(‘a’)元素,並把alert函數綁定到每個元素的click事件上。

.live()

  1. $( ‘a’ ).live( ‘click’ , function () { alert( “That tickles!” ) });

JQuery把alert函數綁定到$(document)元素上,並使用’click’和’a’作為參數。 任何時候只要有事件冒泡到document節點上,它就查看該事件是否是一個click事件,以及該事件的目標元素與’a’這一CSS選擇器是否匹配,如果都是的話,則執行函數。

live方法還可以被綁定到具體的元素(或“context”)而不是document上,像這樣:

  1. $( ‘a’ , $( ‘#container’ )[0]).live(…);

.delegate()

  1. $( ‘#container’ ).delegate( ‘a’ , ‘click’ , function () { alert( “That tickles!” ) });

JQuery掃描文檔查找$(‘#container’),並使用click事件和’a’這一CSS選擇器作為參數把alert函數綁定到$(‘#container’)上。 任何時候只要有事件冒泡到$(‘#container’)上,它就查看該事件是否是click事件,以及該事件的目標元素是否與CCS選擇器相匹配。 如果兩種檢查的結果都為真的話,它就執行函數。

可以注意到,這一過程與.live()類似,但是其把處理程序綁定到具體的元素而非document這一根上。 精明的JS’er們可能會做出這樣的結論,即$(‘a’).live() == $(document).delegate(‘a’),是這樣嗎?嗯,不,不完全是。

為什麼.delegate()要比.live()好用

基於幾個原因,人們通常更願意選用jQuery的delegate方法而不是live方法。 考慮下面的例子:

  1. $( ‘a’ ).live( ‘click’ , function () { blah() });
  2. //或者
  3. $(document).delegate( ‘a’ , ‘click’ , function () { blah() });

速度

後者實際上要快過前者,因為前者首先要掃描整個的文檔查找所有的$(‘a’)元素,把它們存成jQuery對象。 儘管live函數僅需要把’a’作為串參數傳遞以用做之後的判斷,但是$()函數並未“知道”被鏈接的方法將會是.live()。

而另一方面,delegate方法僅需要查找並存儲$(document)元素。

一種尋求避開這一問題的方法是調用在$(document).ready()之外綁定的live,這樣它就會立即執行。 在這種方式下,其會在DOM獲得填充之前運​​行,因此就不會查找元素或是創建jQuery對象了。

靈活性和鏈能力

live函數也挺令人費解的。 想想看,它被鏈到$(‘a’)對象集上,但其實際上是在$(document)對像上發生作用。 由於這個原因,它能夠試圖以一種嚇死人的方式來把方法鏈到自身上。 實際上,我想說的是,以$.live(‘a’,…)這一形式作為一種全局性的jQuery方法,live方法會更具意義一些。

僅支持CSS選擇器

最後一點,live方法有一個非常大的缺點,那就是它僅能針對直接的CSS選擇器做操作,這使得它變得非常的不靈活。

欲了解更多關於CSS選擇器的缺點,請參閱Exploring jQuery .live() and .die()一文。

更新:感謝Hacker News上的pedalpete和後面評論中的Ellsass提醒我加入接下來的這一節內容。

為什麼選擇.live()或.delegate()而不是.bind()

畢竟,bind看起來似乎更加的明確和直接,難道不是嗎?嗯,有兩個原因讓我們更願意選擇delegate或live而不是bind:

Ÿ 為了把處理程序附加到可能還未存在於DOM中的DOM元素之上。 因為bind是直接把處理程序綁定到各個元素上,它不能把處理程序綁定到還未存在於頁面中的元素之上。

Ÿ 如果你運行了$(‘a’).bind(…),而後新的鏈接經由AJAX加入到了頁面中,則你的bind處理程序對於這些新加入的鏈接來說是無效的。 而另一方面live和delegate則是被綁定到另一個祖先節點上,因此其對於任何目前或是將來存在於該祖先元素之內的元素都是有效的。

Ÿ 或者為了把處理程序附加到單個元素上或是一小組元素之上,監聽後代元素上的事件而不是循環遍歷並把同一個函數逐個附加到DOM中的100個元素上。 把處理程序附加到一個(或是一小組)祖先元素上而不是直接把處理程序附加到頁面中的所有元素上,這種做法帶來了性能上的好處。

停止傳播

最後一個我想做的提醒與事件傳播有關。 通常情況下,我們可以通過使用這樣的事件方法來終止處理函數的執行:

  1. $( ‘a’ ).bind( ‘click’ , function (e) {
  2. e.preventDefault();
  3. //或者
  4. e.stopPropagation();
  5. });

不過,當我們使用live或是delegate方法的時候,處理函數實際上並沒有在運行,需要等到事件冒泡到處理程序實際綁定的元素上時函數才會運行。 而到此時為止,我們的其他的來自.bind()的處理函數早已運行了。