注意，这两种称呼是对建立索引技巧的一种称呼，并非索引的类型 。
前缀索引
如果索引列长度过长，这种列索引时将会产生很大的索引文件，不便于操作，可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点，控制在0.31黄金值即可（大于这个值就可以创建）。
# 什么时候适合创建前缀索引，当以下值 小于 0.31 时适合创建前缀索引
mysql> SELECT COUNT(DISTINCT(LEFT(name,10)))/COUNT(*) FROM students;
+-----------------------------------------+
| COUNT(DISTINCT(LEFT(name,10)))/COUNT(*) |
+-----------------------------------------+
| 1.0000 |
+-----------------------------------------+
1 row in set (0.00 sec)
# 创建前缀索引，根据 name 的前几个字符创建前缀索引
mysql> alter table test add INDEX(name(4));
# 删除前缀索引
mysql> alter table test drop INDEX name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
组合索引
单列字段的记录不唯一，但多个字段的记录组合到一起，是唯一的 。
# 预备表
mysql> desc students;
+---------------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(12) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('M','F') | YES | | M | |
| register_time | datetime | YES | | CURRENT_TIMESTAMP | |
| hobby | varchar(32) | YES | | NULL | |
| phone | varchar(32) | YES | | NULL | |
+---------------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
# 预备表数据
mysql> select * from students;
+----+------+-----+--------+---------------------+----------+--------+
| id | name | age | gender | register_time | hobby | phone |
+----+------+-----+--------+---------------------+----------+--------+
| 1 | wqh | 18 | M | 2020-07-14 22:01:58 | abc | 1 |
| 2 | wzh | 18 | M | 2020-07-14 22:02:04 | wzhhobby | 12 |
| 3 | djd | 18 | M | 2020-07-14 22:02:11 | djdhobby | 123 |
| 4 | tcy | 18 | F | 2020-07-14 22:02:27 | tcyhobby | 1234 |
| 5 | gsp | 18 | M | 2020-07-14 22:02:36 | gsphobby | 12345 |
| 6 | cjq | 18 | M | 2020-07-14 22:02:40 | cjqhobby | 12346 |
| 7 | wzd | 12 | M | 2020-07-17 10:13:45 | wzdhobby | 123467 |
+----+------+-----+--------+---------------------+----------+--------+
7 rows in set (0.00 sec)
# 添加组合（联合）索引
mysql> alter table students add index union_key(gender,hobby,phone);
# 删除组合索引
mysql> alter table students drop index union_key;
# 查看索引
mysql> show index from students;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
| students | 1 | union_key | 1 | gender | A | 7 | NULL | NULL | YES | BTREE | | |
| students | 1 | union_key | 2 | hobby | A | 7 | NULL | NULL | YES | BTREE | | |
| students | 1 | union_key | 3 | phone | A | 7 | NULL | NULL | YES | BTREE | | |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
# 组合索引的使用
前提：使用 where 条件搜索的内容不超过全部记录的 15%
走索引 gender,hobby,phone
# hobby,phone,gender // 可以正序排列成上一条件
gender,hobby
# hobby,gender // 可以正序排列成上一条件
gender
不走索引 gender,phone
# phone,gender // 不能从头正序排列，和上一条件本质相同
hobby,phone
# phone,hobby // 不能从头正序排列，和上一条本件质相同
hobby
phone