大揭秘！MySQL数据库之索引

1、概述

索引是储备引擎用于快速查寻记载的一种数据构造，通过合理的使用数据库索引可以大大提高系统的拜访机能，接下来主要介绍在

MySql数据库中索引类型，乃至怎样创立出愈加合理且高效的索引技巧。

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

2、索引的长处

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

帮忙效劳器幸免排序和暂时表

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

3、索引的创立

3.1、主键索引

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

3.2、独一索引

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

3.3、一般索引

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

3.4、全文索引

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

3.5、组合索引

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

4、B+Tree的索引规则

创立一个测试的会员表

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;

创立一个组合索引： ALTER TABLE user_test ADD INDEX idx_user(user_name , city , age);

4.1、索引有效的查询

4.1.1、全值匹配

全值匹配指的是和索引中的所有列停止匹配，如：以上面创立的索引为例，在where前提后可同时查询（user_name，city，age）为

前提的数据。

注：与where后查询前提的次序无关，这里是许多同学容易曲解的一个地方

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

4.1.2、匹配最左前缀

匹配最左前缀是指优先匹配最左索引列，如：上面创立的索引可用于查询前提为：（user_name ）、（user_name, city）、（user_name , city , age）

注：知足最左前缀查询前提的次序与索引列的次序无关，如：（city, user_name）、（age, city, user_name）

4.1.3、匹配列前缀

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

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

4.1.4、匹配范畴值

如：查询会员名以feinik开头的所有会员，这里使用了索引的第一列

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

4.2、索引的限制

1、where查询前提中不包括索引列中的最左索引列，则没法使用到索引查询，如：

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

或

SELECT * FROM user_test WHERE age= 26;

或

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

2、即便where的查询前提是最左索引列，也没法使用索引查询会员名以feinik结尾的会员

SELECT * FROM user_test WHERE user_name like '%feinik';

3、假如where查询前提中有某个列的范畴查询，则其右侧的所有列都没法使用索引优化查询，如：

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

5、高效的索引战略

5.1、索引列不克不及是表达式的一部分，也不克不及作为函数的参数，不然没法使用索引查询。

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

5.2、前缀索引

有时候需要索引很长的字符列，这会增添索引的储备空间乃至落低索引的效力，一种战略是可以使用哈希索引，还有一种就是可以使

用前缀索引，前缀索引是选中字符列的前n个字符作为索引，这样可以大大节省索引空间，从而提高索引效力。

5.2.1、前缀索引的选中性

前缀索引要选中足够长的前缀以包管高的选中性，同时又不克不及太长，我们可以通过以下方式来运算出适宜的前缀索引的选中长度值：

（1）

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

注：通过以上方式来运算出前缀索引的选中性比值，比值越高说明索引的效力也就越高效。

（2）

SELECTCOUNT(DISTINCT LEFT(index_column,1))/COUNT(*),COUNT(DISTINCT LEFT(index_column,2))/COUNT(*),COUNT(DISTINCT
 LEFT(index_column,3))/COUNT(*)
 ...FROM table_name;

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

5.2.2、前缀索引的创立

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

5.2.3、使用前缀索引的留意点

前缀索引是一种能使索引更小，更快的有效方法，但是MySql没法使用前缀索引做ORDER BY 和 GROUP BY乃至使用前缀索引做覆盖

扫描。

5.3、选中适宜的索引列次序

在组合索引的创立中索引列的次序非常重要，准确的索引次序依靠于使用该索引的查询方式，关于组合索引的索引次序可以通过经历

规则来帮忙我们完成：将选中性最高的列放到索引最前列，该规则与前缀索引的选中性办法一致，但并不是说所有的组合索引的次序

都使用该规则就能肯定，还需要按照详细的查询场景来肯定详细的索引次序。

5.4 汇集索引与非汇集索引

1、汇集索引

汇集索引决议数据在物理磁盘上的物理排序，一个表只能有一个汇集索引，假如定义了主键，那么InnoDB会通过主键来汇集数据，如

果没有定义主键，InnoDB会选中一个独一的非空索引代替，假如没有独一的非空索引，InnoDB会隐式定义一个主键来作为汇集索

引。

汇集索引可以很大程度的提高拜访速度，由于汇集索引将索引和行数据留存在了统一个B-Tree中，所以寻到了索引也就响应的寻到了

对应的行数据，但在使用汇集索引的时候需留意幸免随机的汇集索引（一样指主键值不持续，且分布范畴不平均），如使用UUID来作

为汇集索引机能会很差，由于UUID值的不持续会致使增添许多的索引碎片和随机I/O，终究致使查询的机能急剧下落。

2、非汇集索引

与汇集索引不一样的是非汇集索引并不决议数据在磁盘上的物理排序，且在B-Tree中包括索引但不包括行数据，行数据只是通过留存在

B-Tree中的索引对应的指针来指向行数据，如：上面在（user_name，city, age）上创立的索引就是非汇集索引。

5.5、覆盖索引

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

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

由于要查询的字段（user_name, city, age）都包括在组合索引的索引列中，所以就使用了覆盖索引查询，查看可否使用了覆盖索引可

以通过施行方案中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高拜访机能。

5.6、怎样使用索引来排序

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

1、ORDER BY子句后的列次序要与组合索引的列次序一致，且所有排序列的排序标的目的（正序/倒序）需一致

2、所查询的字段值需要包括在索引列中，及知足覆盖索引

通过例子来详细剖析

在user_test表上创立一个组合索引

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

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

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不在索引列中

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

2、排序列的标的目的不一致

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

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

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

4、where查询前提后的user_name为范畴查询，所以没法使用到索引的其他列

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

5、多表连接查询时，只要当ORDER BY后的排序字段都是第一个表中的索引列（需要知足以上索引排序的两个规则）时，方可使用索

引排序。如：再创立一个会员的扩展表user_test_ext，并创立uid的索引。

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);

走索引排序

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;

不走索引排序

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

6、总结

本文主要讲了B+Tree树构造的索引规则，不一样索引的创立，乃至怎样准确的创立出高效的索引技巧来尽大概的提高查询速度，当然了

关于索引的使用技巧不单单只要这些，关于索引的更多技巧还需平常不竭的积存相关经历。

以上就是大揭秘！MySQL数据库之索引的具体内容，更多请关注百分百源码网其它相关文章！