MySQL索引与查询优化

概述

           企业应用软件中，在数据存储方面选择为DBMS（数据库管理系统）。当数据量增多后，对数据的查询和分析在速度上会有很大的影响。原因如下：
        1、数据文件存储在磁盘上，每次读取会有I/O消耗。
        2、I/O性能始终是数据读取的瓶颈。数据随机存放，每一次的I/O操作消耗大量的CPU时间。为了降低I/O操作对DBMS查询速度的限制，DBMS引入了索引的概念。

       下面会以MySQL为例来说明,例子如下：
       假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。
 
索引

 

        索引类型

        MySQL的数据库引擎有两种，一是MyISAM另一种是INNODB。对于这两种的区别可以参看以下内容：
 http://www.cnblogs.com/villion/archive/2009/07/09/1893762.html。

        BTree是INNODB的索引算法，m阶的BTree树，满足如下内容
        1、所有节点最多有m个子节点
        2、根节点至少有两个子节点
        3、除根节点和叶子节点外，其它节点至少有m/2取上整个节点
        4、所有叶子节点在同一层
        5、有k个节点的非叶子节点恰好有k-1个关键字。
       其结构如下：

 

 

MySql执行计划

       使用explain关键字，通过该关键字可以查看mysql的执行计划。执行计划的参数如下：
 
 
查询优化经验

               (1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

               (2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

               (3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。这个在实际开发中应该要注意到，避免出现空值。

               (4)如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。

            例如：存在组合索引index_name(name,pwd)。

查询语句select * from t5 where name='1' and pwd='2'能够使用该索引。
查询语句select * from t5 where name='1' 也能够使用该索引。
查询语句select * from t1 where pwd='2'不能够使用该索引，因为没有组合索引的引导列，即，要想使用pwd列进行查找，必需出现name等于某值。
例子如下:

mysql> desc t5;
 +---------+-------------+------+-----+---------+----------------+
 | Field   | Type        | Null | Key | Default | Extra          |
 +---------+-------------+------+-----+---------+----------------+
 | id      | int(11)     | NO   | PRI | NULL    | auto_increment |
 | name    | varchar(20) | NO   | MUL | NULL    |                |
 | pwd     | varchar(20) | NO   |     | NULL    |                |
 | sex     | int(11)     | YES  |     | 0       |                |
 | age     | int(11)     | YES  | MUL | 0       |                |
 | address | varchar(30) | YES  |     | NULL    |                |
 +---------+-------------+------+-----+---------+----------------+
 6 rows in set (0.01 sec)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 mysql> explain select * from t5 where name='n1'\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: ref
 possible_keys: index_name
     key: index_name
    key_len: 62
     ref: const
    rows: 1
   Extra: Using where; Using index
 1 row in set (0.00 sec)
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 mysql> explain select * from t5 where pwd='n1'\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: index
 possible_keys: NULL
     key: index_name
    key_len: 124
     ref: NULL
    rows: 5
   Extra: Using where; Using index
 1 row in set (0.00 sec)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 mysql> explain select * from t5 where name='n1' and pwd='n1'\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: ref
 possible_keys: index_name
     key: index_name
    key_len: 124
     ref: const,const
    rows: 1
   Extra: Using where; Using index
 1 row in set (0.00 sec)

====================================================================================================================================          (5)like查询 

mysql> explain select * from t5 where name like '%p1'\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: ALL
 possible_keys: NULL
     key: NULL
    key_len: NULL
     ref: NULL
    rows: 5
   Extra: Using where
 1 row in set (0.00 sec)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 mysql> explain select * from t5 where name like 'p1%'\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: range
 possible_keys: index_name
     key: index_name
    key_len: 62
     ref: NULL
    rows: 1
   Extra: Using where
 1 row in set (0.00 sec)

 ===================================================================================================================================         (6)null或者not null查询

mysql> explain select * from t5 where address is null\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: ALL
 possible_keys: NULL
     key: NULL
    key_len: NULL
     ref: NULL
    rows: 45
   Extra: Using where
 1 row in set (0.00 sec)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 mysql> explain select * from t5 where name is not null\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: ALL
 possible_keys: index_name
     key: NULL
    key_len: NULL
     ref: NULL
    rows: 45
   Extra: Using where
 1 row in set (0.00 sec)

=====================================================================================================================================(7)or查询，同时有索引，否则失效

mysql> explain select * from t5 where age=0 or address='1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
         type: ALL
possible_keys: index_age
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 45
        Extra: Using where
1 row in set (0.00 sec)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

mysql> explain select * from t5 where age=0 or id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t5
         type: index_merge
possible_keys: PRIMARY,index_age
          key: index_age,PRIMARY
      key_len: 5,4
          ref: NULL
         rows: 6
        Extra: Using union(index_age,PRIMARY); Using where
1 row in set (0.00 sec)

 ===================================================================================================================================          (8)and查询,有索引，就生效

mysql> explain select * from t5 where id=1 and address='11'\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: NULL
    type: NULL
 possible_keys: NULL
     key: NULL
    key_len: NULL
     ref: NULL
    rows: NULL
   Extra: Impossible WHERE noticed after reading const tables
 1 row in set (0.00 sec)

 =====================================================================================================================================(9)如果列是int类型，但是给的是string类型那么mysql会放弃使用索引
 mysql> explain select * from t5 where pwd=1\G
 *************************** 1. row ***************************
      id: 1
   select_type: SIMPLE
   table: t5
    type: ALL
 possible_keys: NULL
     key: NULL
    key_len: NULL
     ref: NULL
    rows: 45
   Extra: Using where
 1 row in set (0.00 sec)

 

优化一般步骤 

 

 (1)定期优化表和检测表

 check table tabelname检测表是否错误
 optimize table sales;
 

(2)慢查询日志

 开启，找到查询慢的语句
 show status like "slow_queries";
      slow_launch_time
      slow_query_log
      query_log_file
 show variables like "%slow%";

 mysql> show variables like "%slow%";
 +---------------------+---------------------------------------------------------
 -----+
 | Variable_name       | Value
   |
 +---------------------+---------------------------------------------------------
 -----+
 | log_slow_queries    | OFF
   |
 | slow_launch_time    | 2
   |
 | slow_query_log      | OFF
   |
 | slow_query_log_file | C:\ProgramData\MySQL\MySQL Server 5.1\Data\Belen-PC-slow
 .log |
 +---------------------+---------------------------------------------------------
 -----+

 show variables like "%long%";慢查询时间
 show status like 'Handler_read%';该值越高说明有好多表需要索引。

 (3)定位执行效率较低的SQL语句