索引是存储引擎用于快速查找记录的一种数据结构，通过合理的使用数据库索引可以大大提高系统的访问性能，接下来主要介绍在MySql数据库中索引类型，以及如何创建出更加合理且高效的索引技巧。

索引的基本概念

索引是存储引擎用于快速查找记录的一种数据结构，通过合理的使用数据库索引可以大大提高系统的访问性能，接下来主要介绍在MySql数据库中索引类型，以及如何创建出更加合理且高效的索引技巧。

注：这里主要针对的是InnoDB存储引擎的B+Tree索引数据结构

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

索引的优点

大大减轻了服务器需要扫描的数据量，从而提高了数据的检索速度
帮助服务器避免排序和临时表
可以将随机I/O变为顺序I/O

建索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

索引的创建

主键索引

ALTER TABLE 'table_name' ADD PRIMARY KEY 'index_name'('column');

普通索引

ALTER TABLE 'table_name' ADD INDEX 'index_name'('column');

唯一索引

ALTER TABLE 'table_name' ADD UNIQUE 'index_name'('column');

全文索引

ALTER TABLE 'table_name' ADD FULLTEXT 'index_name'('column');

组合索引

ALTER TABLE 'table_name' ADD INDEX 'index_name'('column1','column2',...);

B+ TREE的索引规则

创建一个测试的用户表

DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test{
    id int AUTO_INCREMNET PRIMERY 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;

创建一个组合索引

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

索引的有效查询

全值匹配

全值匹配指的是和索引中的所有列进行匹配，

例如：以上面创建的索引为例，在where条件后可同时查询(user_name，city，age)为条件的数据。
注：与where后查询条件的顺序无关，这里是很多同学容易误解的一个地方。

SELECT * FROM user_test WHERE user_name='louxj424' AND age=26 AND city='广州';

匹配最左前缀

匹配最左前缀是指优先匹配最左索引列

如：上面创建的索引可用于查询条件为：(user_name )、(user_name, city)、(user_name , city , age)

注：满足最左前缀查询条件的顺序与索引列的顺序无关，如：(city, user_name)、(age, city, user_name)

匹配列前缀

指匹配列值的开头部分

例如：查询用户名以lou开头的所有用户:

SELECT * FROM user_test WHERE user_name LIKE 'lou%';

索引的限制

where查询条件中不包含索引列中的最左索引列，则无法使用到索引查询，例如：

SELECT * FROM user_test WHERE city='广州';

或

SELECT * FROM user_test WHERE age=26;

或

SELECT * FROM user_test WHERE city='广州' AND age='26';

即使where的查询条件是最左索引列，也无法使用索引查询用户名以lou结尾的用户

SELECT * FROM user_test WHERE user_name LIKE '%lou';

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

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

高效的索引策略

索引列

索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询。

SELECT * FROM user_test WHERE user_name = concat(user_name, 'fei');

前缀索引

有时候需要索引很长的字符列，这会增加索引的存储空间以及降低索引的效率，一种策略是可以使用哈希索引，还有一种就是可以使用前缀索引，前缀索引是选择字符列的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率。

前缀索引的选择性

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

计算索引前缀的最佳比例

SELECT COUNT(DISTINCT index_column)/COUNT(*) FROM table_name;

其中，index_column代表要添加索引前缀的列，通过以上方式来计算出前缀索引的选择性比值，比值越高说明索引的效率也就越高效。

试探法选择合适的前缀长度

SELECT 
(DISTINCT LEFT(index_cloumn,1))/COUNT(*),
(DISTINCT LEFT(index_cloumn,2))/COUNT(*),
(DISTINCT LEFT(index_cloumn,3))/COUNT(*),
...
FROM table_name;

注：通过以上语句逐步找到最接近于(1)中的前缀索引的选择性比值，那么就可以使用对应的字符截取长度来做前缀索引了。

前缀索引的创建

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

前缀索引的注意点

前缀索引是一种能使索引更小，更快的有效办法，但是MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描。

选择合适的索引列顺序

在组合索引的创建中索引列的顺序非常重要，正确的索引顺序依赖于使用该索引的查询方式，对于组合索引的索引顺序可以通过经验法则来帮助我们完成：将选择性最高的列放到索引最前列，该法则与前缀索引的选择性方法一致，但并不是说所有的组合索引的顺序都使用该法则就能确定，还需要根据具体的查询场景来确定具体的索引顺序。

聚集索引与非聚集索引

聚集索引

聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，如果定义了主键，那么InnoDB会通过主键来聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。

聚集索引可以很大程度的提高访问速度，因为聚集索引将索引和行数据保存在了同一个B-Tree中，所以找到了索引也就相应的找到了对应的行数据，但在使用聚集索引的时候需注意避免随机的聚集索引(一般指主键值不连续，且分布范围不均匀)，如使用UUID来作为聚集索引性能会很差，因为UUID值的不连续会导致增加很多的索引碎片和随机I/O，最终导致查询的性能急剧下降。

非聚集索引

与聚集索引不同的是非聚集索引并不决定数据在磁盘上的物理排序，且在B-Tree中包含索引但不包含行数据，行数据只是通过保存在B-Tree中的索引对应的指针来指向行数据，如：上面在(user_name，city, age)上建立的索引就是非聚集索引。

覆盖索引

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

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

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

索引排序

在排序操作中如果能使用到索引来排序，那么可以极大的提高排序的速度，要使用索引来排序需要满足以下两点即可。

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

通过例子来具体分析
在user_test表上创建一个组合索引

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

可以使用到索引进行排序的案例

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

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

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

SELECT user_name,city,age FROM user_test WHERE user_name='louxj' ORDER BY city;

无法使用索引排序的案例

sex不在索引中

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

排序类的方向不一致

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

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

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

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

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

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

DROP TABLE IF EXISTS user_test_ext;
CREATE TABLE user_test_ext{
    id int AUTO_INCREMENT PRIMERY KEY,
    uid int NOT NULL,
    u_password VARCHAR(64) NOT NULL
}ENGINE=InnoDB,DEFAULT CHARSET = utf8;

走索引排序的使用案例：

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

不走索引排序的使用案例：

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

【MySQL入门篇第五节】索引创建及其优化

【MySQL入门篇第五节】索引创建及其优化

索引的基本概念

索引目的

索引原理

索引的优点

建索引的几大原则

索引的创建

主键索引

普通索引

唯一索引

全文索引

组合索引

B+ TREE的索引规则

索引的有效查询

全值匹配

匹配最左前缀

匹配列前缀

索引的限制

高效的索引策略

索引列

前缀索引

前缀索引的选择性

前缀索引的创建

前缀索引的注意点

选择合适的索引列顺序

聚集索引与非聚集索引

聚集索引

非聚集索引

覆盖索引

索引排序

可以使用到索引进行排序的案例

无法使用索引排序的案例

参考资料

推荐阅读更多精彩内容

【MySQL入门篇 第五节】索引创建及其优化

索引的基本概念

索引目的

索引原理

索引的优点

建索引的几大原则

索引的创建

主键索引

普通索引

唯一索引

全文索引

组合索引

B+ TREE的索引规则

索引的有效查询

全值匹配

匹配最左前缀

匹配列前缀

索引的限制

高效的索引策略

索引列

前缀索引

前缀索引的选择性

前缀索引的创建

前缀索引的注意点

选择合适的索引列顺序

聚集索引与非聚集索引

聚集索引

非聚集索引

覆盖索引

索引排序

可以使用到索引进行排序的案例

无法使用索引排序的案例

参考资料

推荐阅读更多精彩内容

【MySQL入门篇第五节】索引创建及其优化