mysql索引测试

项目开发中一直用orm来管理数据库，没太注意索引的相关东西，以为只要不显式的用index来创建索引，表里面就没有索引。看了mysql的索引之后，才发现之前的想法有多可笑。orm在生成数据库版本文件的时候会创建对应的key, mysql会根据key的类型创建相应的索引。mysql的主键会创建PRI索引，unique约束会创建UNI索引，外键会创建MUL索引.
下面是对mysql索引的测试：

mysql的版本：

select version();
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+

索引和key的对应规则：

show create table sk_model;
| sk_model | CREATE TABLE `sk_model` (
  `created_on` datetime DEFAULT NULL,
  `changed_on` datetime DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `full_name` varchar(60) DEFAULT NULL,
  `params` text,
  `created_by_fk` int(11) DEFAULT NULL,
  `changed_by_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `changed_by_fk` (`changed_by_fk`),
  KEY `created_by_fk` (`created_by_fk`),
  CONSTRAINT `sk_model_ibfk_1` FOREIGN KEY (`changed_by_fk`) REFERENCES `ab_user` (`id`),
  CONSTRAINT `sk_model_ibfk_2` FOREIGN KEY (`created_by_fk`) REFERENCES `ab_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |

desc sk_model;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| created_on    | datetime    | YES  |     | NULL    |                |
| changed_on    | datetime    | YES  |     | NULL    |                |
| id            | int(11)     | NO   | PRI | NULL    | auto_increment |
| name          | varchar(60) | NO   | UNI | NULL    |                |
| full_name     | varchar(60) | YES  |     | NULL    |                |
| params        | text        | YES  |     | NULL    |                |
| created_by_fk | int(11)     | YES  | MUL | NULL    |                |
| changed_by_fk | int(11)     | YES  | MUL | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+

单列唯一性索引在查询中的使用

explain select * from sk_model where name='lasso';
+----+-------------+----------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sk_model | NULL       | const | name          | name | 182     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+------+---------+-------+------+----------+-------+

type是const,说明这个查询是通过索引查找的

单列唯一性索引在排序中使用

explain select * from sk_model order by name desc;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | sk_model | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+

唯一性索引在排序中不起作用

单列非唯一性索引在排序中使用

explain select * from sk_model order by created_by_fk;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | sk_model | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+

不用limit 的时候不走索引

explain select * from sk_model order by created_by_fk limit 4;
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sk_model | NULL       | index | NULL          | created_by_fk | 5       | NULL |    4 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-------+
用了limit了之后，查询又用了索引,当limit大于一定的值的时候，查询又不走索引了，很奇怪，不清楚原因

联合唯一性索引

show index from sql_metrics;
+-------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sql_metrics |          0 | PRIMARY       |            1 | id            | A         |        2147 |     NULL | NULL   |      | BTREE      |         |               |
| sql_metrics |          0 | table_id_2    |            1 | table_id      | A         |         258 |     NULL | NULL   | YES  | BTREE      |         |               |
| sql_metrics |          0 | table_id_2    |            2 | metric_name   | A         |        2149 |     NULL | NULL   | YES  | BTREE      |         |               |
| sql_metrics |          1 | created_by_fk |            1 | created_by_fk | A         |          33 |     NULL | NULL   | YES  | BTREE      |         |               |
| sql_metrics |          1 | changed_by_fk |            1 | changed_by_fk | A         |          33 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
table_id_2是联合唯一性索引

联合唯一性索引在查询中的使用

explain select * from sql_metrics where table_id=272;
+----+-------------+-------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sql_metrics | NULL       | ref  | table_id_2    | table_id_2 | 5       | const |   16 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------------+---------+-------+------+----------+-------+

explain select * from sql_metrics where metric_name="count(*)";
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sql_metrics | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2149 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+

explain select * from sql_metrics where table_id=272 and metric_name like "count(*)%";
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table       | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sql_metrics | NULL       | range | table_id_2    | table_id_2 | 1544    | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

联合索引是有顺序的，要么全部使用，要么按顺序使用，不能直接使用第二个或者后面的索引