mysql-索引的类型的实践

1、探讨：eq_ref 和 ref的区别

CREATE TABLE `idg_1` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `key` varchar(100) NOT NULL DEFAULT '',
   `min_id` bigint(20) NOT NULL,
   `max_id` bigint(20) NOT NULL DEFAULT '0',
   `step` int(11) NOT NULL DEFAULT '3',
   `batch_size` int(11) NOT NULL DEFAULT '3000',
   UNIQUE KEY `id` (`id`),
   UNIQUE KEY `idx_key` (`key`)
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

CREATE TABLE `idg_2` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `key` varchar(100) NOT NULL DEFAULT '',
   `min_id` bigint(20) NOT NULL,
   `max_id` bigint(20) NOT NULL DEFAULT '0',
   `step` int(11) NOT NULL DEFAULT '3',
   `batch_size` int(11) NOT NULL DEFAULT '3000',
   UNIQUE KEY `id` (`id`),
   UNIQUE KEY `idx_key` (`key`)
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
CREATE TABLE `idg_3` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `key` varchar(100) DEFAULT '',
   `min_id` bigint(20) NOT NULL,
   `max_id` bigint(20) NOT NULL DEFAULT '0',
   `step` int(11) NOT NULL DEFAULT '3',
   `batch_size` int(11) NOT NULL DEFAULT '3000',
   UNIQUE KEY `id` (`id`),
   UNIQUE KEY `idx_key` (`key`)
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

 

 

测试：

 

EXPLAIN SELECT * FROM idg_1 a,idg_2 b WHERE a.key = b.key ;

 

 

 

 

测试：

 

EXPLAIN SELECT * FROM idg_1 a,idg_3 b WHERE a.key = b.key;

 

 

 

结论：

唯一索引：访问的时候 是：eq_ref ，

但是 连接的字段不能为null，字段为null 会影响mysql 通过索引检索的方式

 

2、唯一索引的特殊指出

CREATE TABLE `idg` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `key` varchar(100) NOT NULL DEFAULT '',
   `max_id` bigint(20) NOT NULL DEFAULT '0',
   `step` int(11) NOT NULL DEFAULT '3000',
   `min_id` bigint(20) NOT NULL DEFAULT '0',
   UNIQUE KEY `id` (`id`),
   KEY `idx_key_step` (`step`,`key`),
   KEY `idx_key` (`key`)
 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8

 区别：idg： key 是普通索引；idg1:key 是唯一索引

初始化一条数据库：key=nts

测试：

EXPLAIN SELECT * FROM idg t WHERE t.key = 'A';

 

 

EXPLAIN SELECT * FROM idg_1 t WHERE t.key = 'A';

 

 

EXPLAIN SELECT * FROM idg_1 t WHERE t.key = 'nts';

 

 

结论：

唯一索引的情况，key 对应的值 表里有数据，是访问类型是const，没有的话extra 会告诉你没有数据

普通索引：需要索引检查

没有索引：全表扫描了

 

测试：一百万的数据量：有索引或者唯一索引耗时几乎为0，没有索引的话：3.5s左右

 

3、index_merge

 

mysql> EXPLAIN SELECT * FROM  i_order_inddd t WHERE t.client_ip > 'F12345' or t.qr_user_name = 'd15313'\G

注意：client_ip  qr_user_name 为独立索引 

结果：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index_merge
possible_keys: index_client_ip,qr_username_index
          key: index_client_ip,qr_username_index
      key_len: 93,153
          ref: NULL
         rows: 2
        Extra: Using sort_union(index_client_ip,qr_username_index); Using where
1 row in set (0.00 sec)

 

结论：两个独立索引 通过or 连接的时候会有 index_merge，表示对结果进行merge

 

但是这一切取决于数据的构成：

mysql> EXPLAIN SELECT * FROM  i_order_inddd t WHERE t.client_ip < 'F12345' or t.qr < 'd15313'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: index_client_ip,qr_username_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1006423
        Extra: Using where
1 row in set (0.05 sec)

 没错！！

仅仅是表达方式的不同，type变为了全表扫描

 

 

4、覆盖索引

 索引如下：KEY `Policy_Filter_Idx` (`flight_type`,`policy_start`,`policy_expired`)

 

sql为：EXPLAIN SELECT t.policy_start FROM InternationalPolicy t WHERE t.flight_type=3

 

 

如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

(1)索引项通常比记录要小，所以MySQL访问更少的数据；

(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

 

使用EXPLAIN时，可以在Extra一列中看到“Using index”

 

 在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但是，InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了primary key的值。因此，sakila.actor表使用InnoDB，而且对于是last_name上有索引，所以，索引能覆盖那些访问actor_id的查询，如：

 

mysql> EXPLAIN SELECT actor_id, last_name

 

-> FROM sakila.actor WHERE last_name = 'HOPPER'\G

 

*************************** 1. row ***************************

           id: 1

 select_type: SIMPLE

        table: actor

         type: ref

possible_keys: idx_actor_last_name

          key: idx_actor_last_name

      key_len: 137

          ref: const

         rows: 2

        Extra: Using where; Using index

5、利用索引进行排序 以及filesort 

MySQL中，有两种方式生成有序结果集：

一是使用filesort

二是按索引顺序扫描。

利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。

 

举例：

 

CREATE TABLE `idg` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `key` varchar(100) NOT NULL DEFAULT '',
   `max_id` bigint(20) NOT NULL DEFAULT '0',
   `step` int(11) NOT NULL DEFAULT '3000',
   `min_id` bigint(20) NOT NULL DEFAULT '0',
   UNIQUE KEY `id` (`id`),
   KEY `idx_key_step` (`step`,`key`),
   KEY `idx_key` (`key`)
 ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8

 

 

执行： EXPLAIN SELECT * FROM idg t WHERE t.key = 'nts' ORDER BY t.key;

 

 

执行：EXPLAIN SELECT * FROM idg t WHERE t.key = 'nts' ORDER BY t.max_id;

 

 

结论：如果order by 的字段有索引，那么走索引排序，否则排序为：Using filesort

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）