8.7. ²¿·ÖË÷Òý

Example 8-1. ÉèÖÃÒ»¸ö²¿·ÖË÷ÒýÒÔÅųýÆÕͨÊýÖµ

¼ÙÉèÄãÔÚÒ»¸öÊý¾Ý¿âÖд洢 web ·þÎñÆ÷µÄ·ÃÎÊÈÕÖ¾£® ´ó¶àÊý·ÃÎÊÊÇ´ÓÄãµÄ×éÖ¯ÄÚ²¿µÄ IP ·¶Î§·¢ÆðµÄ£¬µ«Ò²ÓÐһС²¿·Ö À´×ÔÆäËüµØ·½(±ÈÈçÄÇЩͨ¹ý²¦ºÅ½øÐÐÁª½ÓµÄ¹ÍÔ±)£® Èç¹ûÄãËÑË÷µÄÖ÷ÒªÊÇÀ´×ÔÍⲿ·ÃÎ浀 IP£¬ÄÇôÄã¿ÉÄܾͲ»ÐèÒª¶ÔÄÇЩ ¶ÔÓ¦ÄãµÄ×éÖ¯µÄ×ÓÍøµÄ IP ·¶Î§½øÐÐË÷Òý£®

¼ÙÉè±íÏóÏÂÃæÕâÑù¡Ã

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

Òª´´½¨·ûºÏÎÒÃǵÄÀý×ÓµÄË÷Òý£¬Ê¹ÓÃÏóÏÂÃæÕâÑùµÄÃüÁî¡Ã

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
    WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

Ò»¸ö¿ÉÒÔʹÓÃÕâ¸öË÷ÒýµÄµäÐ͵IJéѯÏóÕâÑù¡Ã

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

Ò»¸ö²»ÄÜʹÓÃÕâ¸öË÷ÒýµÄ²éѯ¿ÉÒÔÊÇ¡Ã

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

ÎÒÃÇͨ¹ý¹Û²ì¿ÉÒÔ¿´³ö£¬ÕâÖÖÀàÐ͵IJ¿·ÖË÷ÒýÒªÇóÆÕͨÊýÖµÊÇ¿ÉÒÔ Ô¤¼ÆµÄ£®Èç¹ûÊýÖµµÄ·Ö²¼ÊǹÌÓеÄ(À´×ÔÓ¦ÓÃ×ÔÉíµÄÐÔÖÊ)²¢ÇÒ ÊǾ²Ì¬µÄ(²»Ëæʱ¼ä¶ø¸Ä±ä)£¬ÄÇ×öµ½ÕâÒ»µã(¸ú×ÙÆÕͨÊýÖµ)²¢²»À§ÄÑ£¬µ«ÊÇ Èç¹ûÆÕͨÊýÖµÖ»ÊÇÒòΪһÖµÄÊý¾Ý×°ÔØ£¬ÄÇôËü¿ÉÄܾÍÒª»¨ºÜ¶àά»¤ÐÔ¹¤×÷£®

Example 8-2. ÉèÖÃÒ»¸ö²¿·ÖË÷ÒýÒÔÅųý²»¸ÐÐËȤµÄÊýÖµ

Èç¹ûÄãÓÐÒ»¸ö±í£¬°üº¬ÒѸ¶¿îºÍδ¸¶¿îµÄ¶¨µ¥£¬¶øδ¸¶¿îµÄ¶¨µ¥ Ö»Õ¾×ܱíµÄһС²¿·Ö²¢ÇÒËüÊǾ­³£Ê¹ÓõIJ¿·Ö£¬ÄÇôÄã¿ÉÒÔͨ¹ý Ö»ÔÚδ¸¶¿î¶¨µ¥ÉÏ´´½¨Ò»¸öË÷ÒýÀ´¸ÄÉÆÐÔÄÜ£®´´½¨Ë÷ÒýµÄÃüÁî¿´ÆðÀ´»áÏóÕâÑù¡Ã

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

¿ÉÄÜÓõ½Õâ¸öË÷ÒýµÄ²éѯ¿´ÆðÀ´Ïó

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

²»¹ý£¬¸ÃË÷ÒýÒ²¿ÉÒÔÓÃÓÚÄÇЩÍêÈ«²»Éæ¼° order_nr µÄ²éѯ£¬±ÈÈ磬

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

Õâ¸ö²éѯ²»ÏóÔÚ amount ×Ö¶ÎÉϵIJ¿·ÖË÷ÒýÄÇôÓÐЧ£¬ ÒòΪϵͳ±ØÐëɨÃèÕû¸öË÷Òý£®µ«ÊÇ£¬Èç¹ûδ¸¶¿îµÄ¶¨µ¥Ïà¶Ô½ÏÉÙ£¬ ÄÇôÓÃÕâ¸ö²¿·ÖË÷ÒýÕÒ³öδ¸¶¿îµÄ¶¨µ¥½«»á¸ü¿ìЩ£®

Çë×¢ÒâÏÂÃæÕâ¸ö²éѯÎÞ·¨Ê¹ÓÃÕâ¸öË÷Òý¡Ã

SELECT * FROM orders WHERE order_nr = 3501;

¶¨µ¥ 3501 ¿ÉÄÜÊÇÒѸ¶¿îÒ²¿ÉÄÜÊÇδ¸¶¿î£®

Example 8-3. ÉèÖÃÒ»¸ö²¿·ÖΨһË÷Òý

¼ÙÉèÎÒÃÇÓÐÒ»¸öÃèÊö²âÊÔÊä³öµÄ±í£®ÎÒÃÇÏ£ÍûÈ·±£ÔÚÒ»¶¨µÄ Ä¿±êºÍ¿ÎÌâµÄ×éºÏÖÐÖ»ÓÐÒ»¸ö"³É¹¦"¼Ç¼£¬ µ«ÊÇ¿ÉÒÔÓÐÈÎÒâÊýÁ¿µÄ"²»³É¹¦"¼Ç¼£®ÏÂÃæÊÇʵÏÖ·½·¨¡Ã

CREATE TABLE tests (subject text,
                    target text,
                    success bool,
                    ...);
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

Èç¹ûÖ»ÓÐÉÙÊý³É¹¦¼Ç¼¶øÓкܶ಻³É¹¦¼Ç¼£¬ÄÇô ÕâÊÇÒ»Öַdz£ÓÐЧµÄʵÏÖ·½·¨£®