概述

为了在数据库中快速查找我们要的记录，通常会使用索引。合理的使用索引能大大的提高数据库的访问性能。本文主要介绍如何在MySQL数据库中使用索引，以及如何创建高效的索引。

索引的优点

大大减轻了服务器需要扫描的数据量，从而提高了数据的检索速度

帮助服务器避免排序和临时表

可以将随机I/O变为顺序I/O

索引类型

1.主键索引
1
ALTER TABLE '表名' ADD PRIMARY KEY '索引名' ('column');
2.唯一索引
1
ALTER TABLE 'table_name' ADD UNIQUE 'index_name' ('column');
3.普通索引
1
ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column');
4.全文索引
1
ALTER TABLE 'table_name' ADD FULLTEXT 'index_name' ('column');
5.组合
1
ALTER TABLE 'table_name' ADD INDEX 'index_name' ('column1', 'column2', ...);

有效索引

首先创建一个测试表

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test(
    id int AUTO_INCREMENT PRIMARY KEY,
    user_name varchar(30) NOT NULL,
    sex bit(1) NOT NULL DEFAULT b'1',
    city varchar(50) NOT NULL,
    age int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在创建一个组合索引

1
ALTER TABLE user_test ADD INDEX idx_user (user_name , city , age);
1.全值匹配

全值匹配指的是和索引中的所有列进行匹配，如：以上面创建的索引为例，在where条件后可同时查询（user_name，city，age）为条件的数据。

1
SELECT * FROM user_test WHERE user_name = 'feinik' AND age = 26 AND city = '广州';
2.匹配列前缀

指匹配列值的开头部分，如：查询用户名以feinik开头的所有用户

1
SELECT * FROM user_test WHERE user_name LIKE 'feinik%';

注：如果where查询条件中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询

1
SELECT * FROM user_test WHERE user_name = 'feinik' AND city LIKE '广州%' AND age = 26;

高效索引策略

1.前缀索引

如果索引的字符列很长，会造成索引失效。解决方法之一；使用哈希索引；之二：使用前缀索引。前缀索引是选择字符列的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率。

首先：确定前缀索引的长度

前缀索引要选择足够长的前缀以保证高的选择性，同时又不能太长，我们可以通过以下方式来计算出合适的前缀索引的选择长度值：

1
SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name; -- index_column代表要添加前缀索引的列

注：比值越大，索引的效率越高

确定前缀索引长度后，创建前缀索引

1
ALTER TABLE table_name ADD INDEX index_name (index_column(length));

注：MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描

2.覆盖索引

如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：

1
SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' AND age > 25;

因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。

使用索引排序

使用索引来排序极大的提高了排序速度，其需要满足以下条件即可:

  • 1、ORDER BY子句后的列顺序要与组合索引的列顺序一致，且所有排序列的排序方向（正序/倒序）需一致
  • 2、所查询的字段值需要包含在索引列中，及满足覆盖索引

举个栗子：

首先创建一个组合索引

1
ALTER TABLE user_test ADD INDEX index_user(user_name , city , age);

那么我们就可以使用索引排序了

1
2
3
4
5
6
7
1、SELECT user_name, city, age FROM user_test ORDER BY user_name;
2、SELECT user_name, city, age FROM user_test ORDER BY user_name, city;
3、SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;
4、SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

注：第4点说明—-如果where查询条件为索引列的第一列，且为常量条件，那么也可以使用到索引

但下面的例子就会使索引排序失效

1、sex不在索引列中

1
SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;

2、排序列的方向不一致

1
SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;

3、所要查询的字段列sex没有包含在索引列中

1
SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;

4、where查询条件后的user_name为范围查询，所以无法使用到索引的其他列

1
SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

5、多表连接查询时，只有当ORDER BY后的排序字段都是第一个表中的索引列（需要满足以上索引排序的两个规则）时，方可使用索引排序。如：再创建一个用户的扩展表user_test_ext，并建立uid的索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE IF EXISTS user_test_ext;
CREATE TABLE user_test_ext(
    id int AUTO_INCREMENT PRIMARY KEY,
    uid int NOT NULL,
    u_password VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE user_test_ext ADD INDEX index_user_ext(uid);

索引排序有效

1
SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY u.user_name;

索引排序无效

1
SELECT user_name, city, age FROM user_test u LEFT JOIN user_test_ext ue ON u.id = ue.uid ORDER BY ue.uid;

总结

本文讲解了索引规则，不同索引的创建，以及如何正确的创建出高效的索引技巧来尽可能的提高查询速度，当然了关于索引的使用技巧不单单只有这些，关于索引的更多技巧还需平时不断的积累相关经验。