MySQL索引

@航空母舰

已于 2022-10-11 11:16:58 修改

阅读量852

点赞数

CC 4.0 BY-SA版权

分类专栏： MYSQL 面试文章标签： mysql 数据库数据结构

于 2012-01-13 09:58:20 首次发布

本文链接：https://blog.youkuaiyun.com/hudeyong926/article/details/99540464

MYSQL 同时被 2 个专栏收录

62 篇文章

订阅专栏

面试

50 篇文章

订阅专栏

1、索引是什么

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。常见的索引结构有: B 树， B+树和 Hash。索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

2、优势

类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本。通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

①　索引大大减少了服务器需要扫描的数据量。
②　索引可以帮助服务器避免排序和临时表。
③　索引可以将随机I/O 变为顺序 I/O 。

3、劣势

① 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。
② 虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。

4、索引的分类

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

PS:不懂的同学可以暂存疑，慢慢往下看，后面会有答案的，也可以自行搜索。

唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，
因为只取前几个字符。
全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

在MySQL中，索引分为两大类：聚簇索引和非聚簇索引。

聚集索引

表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。从物理文件也可以看出 InnoDB（聚集索引）的数据文件只有数据结构文件.frm和数据文件.idb 其中.idb中存放的是数据和索引信息是存放在一起的。

非聚集索引

表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。从物理文件中也可以看出 MyISAM（非聚集索引）的索引文件.MYI和数据文件.MYD是分开存储的是相对独立的

聚簇索引能够提高多行检索的速度，而非聚簇索引则对单行的检索速度很快。

Mysql表文件截图

在这两大类的索引类型下，还可以将索引分成几个小类：

①普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

②唯一索引：与普通索引类型，不同的是唯一索引的列值必须唯一，但允许为空值。

主键索引：特殊的唯一索引，不可以有null，表中只有一个

③全文索引：全文索引（FULLTEXT）仅可以适用于InnoDB（MySQL5.6.24支持）和MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

④组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

⑤空间索引: MySQL在5.7之后的版本支持了空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值

大多数MySQL索引（主键索引，唯一索引，全文索引）是存储在Btree结构里。

前缀索引

通过索引开始的部分字符，提高索引效率且可以节约索引空间，但会降低索引的选择性(选择性:不重复的索引值和数据表的记录总数)，索引选择性越高则查询效率越高。对于BLOB、text或者很长的varchar类型的列必须使用前缀索引。

alter table user add INDEX idx_name(vc_Name(10))

建表时，vc_Name 长度为 50，这里为什么用 10 呢？这就是前缀索引，因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。

5、MySQL索引结构

1）BTree索引

B-Tree索引可以被用在像=，>，>=，<，<=和BETWEEN，!=， <>，IS NULL这些比较操作符上。而且还可以用于LIKE操作符只要它的查询条件是一个不以通配符开头的常量。像下面的语句就可以使用索引

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

第一条是因为它以通配符开头，第二条是因为没有使用常量。

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第一条是因为它以通配符开头，第二条是因为没有使用常量。

btree通常意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同.下图是innodb索引工作示意图，myisam使用的结构有所不同，但基本思想类似

btree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限，最终存储引擎要么是找到对应的值，要么是该记录不存在。有时候mysql不会使用索引，即使这个在可用的情况下。例如当mysql预估使用索引会读取大部分的行数据时。（在这种情况下，一次全表扫描可能比使用索引更快，因为它需要更少的检索）。然而，假如语句中使用LIMIT来限定返回的行数，mysql则会使用索引。因为当结果行数较少的情况下使用索引的效率会更高。索引还可以用于查询中的order by操作，一般来说，如果btree可以按照某种方式查找的值，那么也可以按照这种方式用于排序，所以，如果order by子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求

2）Hash索引

而哈希索引的示意图则是这样的

其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端

①Hash 索引仅仅能满足"=","IN"和"<=>"(注意，<>和<=>是不同的操作)查询，不能使用范围查询。
②优化器不能用hash索引来为ORDER BY操作符加速。
③Hash 索引不能利用部分索引键查询。对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用
④Hash 索引在任何时候都不能避免表扫描。
⑤Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

3）full-text全文索引

4）R-Tree索引

6、哪些情况需要创建索引

①主键自动建立唯一索引

②频繁作为查询条件的字段应该创建索引

③查询中与其他表关联的字段，外键关系建立索引

④频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引

⑤WHERE条件里用不到的字段不创建索引

⑥单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)

⑦查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

⑧查询中统计或者分组字段

7、哪些情况不要创建索引

①表记录太少

②经常增删改的表

提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。

③注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

④数据变化不大的列。如XX类型，是否有效，状态等

使用索引时，有以下一些技巧和注意事项：
（1）索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
（2）使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
（3）索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
（4）like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
（5）不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
select * from users where adddate<‘2007-01-01';
（6）不使用NOT IN和<>操作

8、mysql索引命中规则

最左匹配原则

当创建(a,b,c)复合索引时，相当于创建了(a)单列索引，(a,b)组合索引以及(a,b,c)组合索引

想要索引生效的话,只能使用a和a,b和a,b,c三种组合,所谓最左前缀原则你可以认为联合索引是闯关游戏的设计:那么a就是第一关 b是第二关， c就是第三关,你必须通过第一关,才能匹配第二关

注：在mysql中执行查询时，只能使用一个索引，如果我们在a,b,c上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

实例：以下是常见的几个查询：

mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' ;
mysql>SELECT `a`,`b`,`c` FROM A WHERE `b`='bb' AND `a`='cc';
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' AND `b`='bb' AND `c`='cc';

想要索引最大化的使用需要至少建几个索引

需要建立两个复合索引：a,c、b,c

在用MySQL查询数据库的时候，连接了很多个用，发现非常慢。例如：

SELECT ... WHERE p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN (472,474)

这样查询需要20多秒，虽然在各个字段上都建立了索引。用分析Explain SQL一分析，发现在第一次分析过程中就返回了几万条数据：
WHERE p.languages_id = 1 ，然后再依次根据条件，缩小范围。

而我改变一下WHERE 字段的位置之后，速度就有了明显地提高：

WHERE p.products_id IN (472,474) AND p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1

这样，第一次的条件是p.products_id IN (472,474)，它返回的结果只有不到10条，接下来还要根据其它的条件来过滤，自然在速度上有了较大的提升。
经过实践发现，不要以为WHERE中的字段顺序无所谓，可以随便放在哪，应该尽可能地第一次就过滤掉大部分无用的数据，只返回最小范围的数据。希望能帮到有同样遭遇的朋友。