mysql索引

准备

- 数据库与表

CREATE DATABASE demo;

CREATE TABLE `t_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `x` varchar(50) DEFAULT NULL,
  `a` varchar(50) DEFAULT NULL,
  `b` tinyint(1) DEFAULT NULL,
  `c` varchar(50) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `y` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_a_b_c_d` (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

关于索引

原理

可以把索引理解为一本书的目录，可以通过目录迅速找到自己想要的章节，本质上就是为了提高查询速度。

分析

通常使用explain来分析一条sql语句中索引使用情况。后期会有章节专门介绍explain命令的使用方法。

用法：EXPLAIN SQL

mysql> explain select * from t_users where id=1890 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_users
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

索引创建原则

对于重复度比较高的字段不适合做索引，比如性别，充其量就是男，女，其他，做索引作用不大。
创建合适的唯一索引
遵循最左匹配原则
对于经常用于排序（order by），分组（group by）的字段，我们可以建立索引
索引所在的列不能参与计算，不然会失效
尽量扩展索引，不要新增加索引。

普通索引（简单索引）

最常见的索引之一，没有任何我约束。

CREATE INDEX index_name ON table_name(column);
 
-- eg:
CREATE INDEX index_a ON t_test(a);

测试：

mysql> explain select * from t_test where a='123' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_test
   partitions: NULL
         type: ref
possible_keys: index_a
          key: index_a
      key_len: 153
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

联合索引（符合索引）

将多个字段用于创建索引。联合索引最重要的就是最左原则，他决定索引是否生效。

1	CREATE INDEX index_name ON table_name(column1,column2,column3...);

索引最左原则

1	CREATE INDEX index_name ON table_name(column1, column2);

比如表t_test拥有字段x,a,b,c,d,y, 现在将字段a,b,c,d创建为联合索引，实际上是建立了a，a,b，a,b,c，a,b,c,d四个索引。

1	CREATE INDEX index_a_b_c_d ON t_test(a,b,c,d);

对于联合索引，mysql会依照创建索引时的字段顺序向右匹配，直到遇到范围查询（>,<,between,like）就停止匹配。
1
SELECT * FROM t_test WHERE a=1 AND b=2 AND c>3 AND d=6;
此时c 无法使用索引。
对于查询中的=和in，只要WHERE里面对于索引字段的是a，a,b，a,b,c，a,b,c,d四种情况之一，那么字段条件后的字段顺序是不要求的， mysql会按照你创建索引字段顺序去比对。
1
2
SELECT * FROM t_test WHERE a=1 AND b=2 AND c=3 AND d=6;
SELECT * FROM t_test WHERE c=3 AND d=6 AND a=1 AND b=2;

有几个疑问

对于上面（a,b,c,d）组成的联合索引

A：以下索引会生效吗？

1	SELECT a,b,c,d,x FROM t_test WHERE b=1 AND x=5;

答案是不会生效

mysql> EXPLAIN SELECT a,b,c,d,x FROM t_test WHERE b=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

B：以下索引会生效吗？

1	SELECT a,b,c,d FROM t_test WHERE b=1 AND x=5;

答案是会生效

mysql> EXPLAIN SELECT a,b,c,d FROM t_test WHERE b=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_test
   partitions: NULL
         type: index
possible_keys: NULL
          key: index_a_b_c_d
      key_len: 313
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
 
ERROR:
No query specified

B生效的原因在于查询条件中只有包含索引的字段，查询内容也全部在索引字段中，所以它只需要操作索引表即可。

A不生效是因为除了操作索引表之外，还需要从数据表中拿到额外的字段数据。

唯一索引

1	CREATE UNIQUE INDEX index_name ON table_name(column);

主键索引

CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` varchar(50) NOT NULL ,
    PRIMARY KEY (`id`)
);

全文索引

1	CREATE FULLTEXT INDEX index_name ON table_name(column)

注意事项

使用索引的时候尽量不要使用like, 在语句like value%时索引会生效，但是like %value%不会生效

mysql> explain select * from t_test where a like "a%" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_test
   partitions: NULL
         type: range
possible_keys: index_a_b_c_d
          key: index_a_b_c_d
      key_len: 153
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_test where a like "%a%"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_test
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

在索引列运算会导致索引失效

如果where后面的条件字段数据类型与匹配值的类型不一致，可能会导致索引失效

mysql> explain select * from t_test where a="1"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_test
   partitions: NULL
         type: ref
possible_keys: index_a_b_c_d
          key: index_a_b_c_d
      key_len: 153
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_test where a=1 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_test
   partitions: NULL
         type: ALL
possible_keys: index_a_b_c_d
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

索引所在列的值不能为null。只有该列包含null值，则该索引列会失效。