2. 索引问题

索引问题

索引是数据库优化中最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数 的 SQL 性能问题。

本文属于SQL 优化系列篇

1. 索引的存储分类

MyISAM 存储引擎的表的数据和索引是自动分开存储的, 各自是独立的一个文件; InnoDB 存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成。 MySQL 中索引的存储类型目前只有两种(BTREEHASH) ,具体和表的存储引擎相关: MyISAMInnoDB 存储引擎都只支持 BTREE 索引;MEMORY/HEAP 存储引擎可以支持 HASHBTREE 索引。下面是创建前缀索引的一个例子:

mysql> create index ind_company2_name on company2(name(4)); 
Query OK, 1000 rows affected (0.03 sec)
Records: 1000  Duplicates: 0  Warnings: 0

2. MySQL 如何使用索引

索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作性能的最佳途径。

查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那么只有查询条件使用了多列关键字最左边的前缀时, 才可以使用索引, 否则将不能使用索引。

2.1 使用索引

MySQL 中,下列几种情况下有可能使用到索引。

  1. 对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用, 举例说明如下。

    首先按 company_idmoneys 的顺序创建一个复合索引,具体如下:

    mysql> create index ind_sales2_companyid_moneys on sales2(company_id,moneys); 
    Query OK, 1000 rows affected (0.03 sec) 
    Records: 1000  Duplicates: 0  Warnings: 0 
    

    然后按 company_id 进行表查询,具体如下:

    mysql> explain select * from sales2 where company_id = 2006\G; 
    *************************** 1. row *************************** 
               id: 1
      select_type: SIMPLE 
            table: sales2 
             type: ref 
    possible_keys: ind_sales2_companyid_moneys 
              key: ind_sales2_companyid_moneys 
          key_len: 5 
              ref: const 
             rows: 1 
            Extra: Using where 
     1 row in set (0.00 sec) 
    

    可以发现即便 where 条件中不是用的 company_idmoneys 的组合条件,索引仍然能用到,这就是索引的前缀特性。但是如果只按 moneys 条件查询表,那么索引就不会被用到,具体如下:

    mysql> explain select * from company2 where name like '%3'\G; 
    *************************** 1. row *************************** 
               id: 1
      select_type: SIMPLE 
            table: sales2 
             type: ALL 
    possible_keys: NULL
              key: NULL
          key_len: NULL 
              ref: NULL
             rows: 1000 
            Extra: Using  where 
    1 row in set (0.00 sec) 
    
  2. 对于使用 like 的查询,后面如果是常量并且只有号不在第一个字符,索引才可能会被使用,来看下面两个执行计划:

    mysql> explain select * from company2 where name like '%3'\G; 
               id: 1
      select_type: SIMPLE 
            table: company2 
             type: ALL 
    possible_keys: NULL
              key: NULL
          key_len: NULL 
              ref: NULL
             rows: 1000 
            Extra: Using  where 
    1 row in set (0.00 sec)
    
    mysql> explain select * from company2 where name like '3%'\G;  
    *************************** 1. row *************************** 
               id: 1
      select_type: SIMPLE 
            table: company2 
             type: range
    possible_keys: ind_company2_name 
              key: ind_company2_name 
          key_len: 11
              ref: NULL
             rows: 103
            Extra: Using where 
     1 row in set (0.00 sec) 
    

    可以发现第一个例子没有使用索引,而第二例子就能够使用索引,区别就在于“%”的位置 不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。

    另外,如果如果 like 后面跟的是一个列的名字,那么索引也不会被使用。

  3. 如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%...%’

  4. 如果列名是索引,使用 column_name is null 将使用索引。如下例中查询 namenull 的记录就用到了索引:

    mysql> explain select * from company2 where name is null\G; 
    *************************** 1. row *************************** 
    	       id: 1
      select_type: SIMPLE 
            table: company2 
             type: ref
    possible_keys: ind_company2_name 
              key: ind_company2_name 
          key_len: 11
              ref: const
             rows: 1
            Extra: Using where 
     1 row in set (0.00 sec) 
    

2.2 存在索引但不使用索引

在下列情况下,虽然存在索引,但是 MySQL 并不会使用相应的索引。

  1. 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如如果列 key_part1 均匀分布在 1100 之间,下列查询中使用索引就不是很好:

    SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90; 
    
  2. 如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

  3. or分割开的条件, 如果or前的条件中的列有索引, 而后面的列中没有索引, 那么涉及到的索引都不会被用到,例如:

    mysql> show index from sales\G; 
    *************************** 1. row *************************** 
           Table: sales 
      Non_unique: 1 
        Key_name: ind_sales_year 
    Seq_in_index: 1 
     Column_name: year
       Collation: A 
     Cardinality: NULL 
        Sub_part: NULL 
          Packed: NULL 
            Null:  
      Index_type: BTREE 
         Comment:  
    1 row in set (0.00 sec) 
    

    从上面可以发现只有year 列上面有索引,来看如下的执行计划:

    mysql> explain select * from sales where year = 2001 or country = 'China'\G; 
    *************************** 1. row *************************** 
    		   id: 1
      select_type: SIMPLE 
            table: sales 
             type: ALL
    possible_keys: ind_sales_year 
              key: NULL 
          key_len: NULL
              ref: NULL
             rows: 12
            Extra: Using where 
     1 row in set (0.00 sec) 
    

    可见虽然在year这个列上存在索引ind_sales_year, 但是这个SQL语句并没有用到这个索引, 原因就是 or 中有一个条件中的列没有索引。

  4. 如果不是索引列的第一部分,如下例子:

    mysql> explain select * from sales2 where moneys = 1\G; 
    *************************** 1. row *************************** 
    		   id: 1
      select_type: SIMPLE 
            table: sales2 
             type: ALL
    possible_keys: NULL 
              key: NULL 
          key_len: NULL
              ref: NULL
             rows: 1000
            Extra: Using where 
     1 row in set (0.00 sec) 	
    

    可见虽然在 money 上面建有复合索引,但是由于 money 不是索引的第一列,那么在查询中这个索引也不会被 MySQL 采用。

  5. 如果 like 是以开始,例如:

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

    可见虽然在 name 上建有索引,但是由于 where 条件中 like 的值的“%”在第一位了,那么 MySQL 也不会采用这个索引。

  6. 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为,MySQL 默认把输入的常量值进行转换以后才进行检索。 如下面的例子中company2表中的name字段是字符型的, 但是 SQL 语句中的条件值 294 是一个数值型值,因此即便在 name 上有索引, MySQL 也不能 正确地用上索引,而是继续进行全表扫描。

    mysql> explain select * from company2 where name = 294\G; 
    *************************** 1. row *************************** 
    		   id: 1
      select_type: SIMPLE 
            table: company2 
             type: ALL
    possible_keys: ind_company2_name 
              key: NULL 
          key_len: NULL
              ref: NULL
             rows: 1000
            Extra: Using where 
    1 row in set (0.00 sec) 
    
    mysql> explain select * from company2 where name = '294'\G; 
    *************************** 1. row *************************** 
    		   id: 1
      select_type: SIMPLE 
            table: company2 
             type: ref
    possible_keys: ind_company2_name 
              key: ind_company2_name 
          key_len: 23
              ref: const
             rows: 1
            Extra: Using where 
    1 row in set (0.00 sec) 
    

    从上面的例子中可以看到,第一个 SQL 语句中把一个数值型常量赋值给了一个字符型的列 name,那么虽然在 name 列上有索引,但是也没有用到;而第二个 SQL 语句就可以正确使用索引。

3. 查看索引使用情况

如果索引正在工作,Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描, Handler_read_rnd_next 的值较高, 则通常说明表索引不正确或写入的查询没有利用索引, 具体如下。

mysql> show status like 'Handler_read%'; 
+-----------------------+-------+ 
| Variable_name         | Value | 
+-----------------------+-------+ 
| Handler_read_first    | 0     | 
| Handler_read_key      | 5     | 
| Handler_read_next     | 0     | 
| Handler_read_prev     | 0     | 
| Handler_read_rnd      | 0     | 
| Handler_read_rnd_next | 2055  | 
+-----------------------+-------+ 
6 rows in set (0.00 sec) 

从上面的例子中可以看出,目前使用的 MySQL 数据库的索引情况并不理想。

展开阅读全文
©️2020 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值