查询性能优化

1 优化数据访问基本原则

  应用程序只获得自己需要的数据，不应访问更多的行或列。

  Mysql服务器不应分析超过需要的行。

 

    查询如果向服务器请求了不需要的数据，就会消耗多余的CPU，内存，网络带宽等资源。

    如无需使用select * from table等语句查询所有字段，而仅仅只指定需要的字段。

 

2  经常需要反省自己Mysql是否检索了太多的数据，其中可以指出开销的三个指标。

   执行时间

   检查行数

   返回行数

这三个指标会被写入慢查询日志，作为大致反映Mysql在内部执行查询的时候要访问多少数据，以及查询时间等。

mysql> show index from test1\G;

*************************** 1. row ***************************
       Table: test1
  Non_unique: 1
    Key_name: a1
Seq_in_index: 1
Column_name: a1
   Collation: A
Cardinality: 4
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------
mysql> explain select * from test1 where a1=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: a1
          key: a1
      key_len: 5
          ref: const
         rows: 15
        Extra: Using where; Using index
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------
 
drop index之后
 
mysql> drop index a1 on test1 ;
Query OK, 65537 rows affected (0.03 sec)
Records: 65537  Duplicates: 0  Warnings: 0
 
再次执行
mysql> explain select * from test1 where a1=10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 65537
        Extra: Using where
1 row in set (0.00 sec)
 
其中两次的type：类型一次为全表扫描，一次为ref并且使用索引。导致查询的行数量一个味15行，一个行65537行。
 
1  对索引使用where查询来消除不匹配的行，发生在存储引擎层。
2  可以使用覆盖索引来避免访问行，并且从索引取得数据后，过滤掉不匹配的行。
3  从表中检索数据，然后过滤掉不必要的行。这发生在服务器端，并且在过滤之前需要读这些行。