MySQL索引

分类 Mysql 标签 Mysql 评论:
  1. MySQL索引
    1. 索引是什么
    2. 优势
    3. 劣势
    4. 索引的分类
      1. 单列索引
      2. 组合索引
      3. 全文索引
      4. 空间索引
    5. 基本语法
    6. 哪些情况需要创建索引
    7. 哪些情况不要创建索引

MySQL索引

索引是什么

索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以理解为“排好序的快速查找数据结构”在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据,这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优势

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

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。虽然索引大大提高了查询速度，同时确会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。都会调整因为更新所带来的键值变化后的索引信息。

索引的分类

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

  • MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换
  • MEMORY/HEAP存储引擎：支持HASH和BTREE索引

索引我们分为四类来讲，单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引；

单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

  • 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值；
  • 主键索引：是一种特殊的唯一索引，不允许有空值。

组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引

只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有”你是个大煞笔，二货 …” 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思，全文索引很少使用；

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。
在创建空间索引时，使用SPATIAL关键字。
需要使用引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

基本语法

创建多列（组合）索引
ALTER TABLE t1 ADD INDEX index_c1_c2 (c1,c2);

查看执行计划可以看到索引是否被使用；

mysql> explain select * from t1 where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • id：SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出 现。在次语句中，select就只有一个，所以是1.
  • select_type：所使用的SELECT查询类型，SIMPLE表示为简单的SELECT，不实用UNION或子查询，就为简单的SELECT。也就是说在该SELECT查询时会使用索引。其他取值，PRIMARY：最外面的SELECT.在拥有子查询时，就会出现两个以上的SELECT。UNION：union(两张表连接)中的第二个或后面的select语句 SUBQUERY：在子查询中，第二SELECT。
  • table：数据表的名字。他们按被读取的先后顺序排列，这里因为只查询一张表，所以只显示book
  • type：指定本数据表和其他数据表之间的关联关系，该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。(注意，个人这里不是很理解，百度了很多资料，全是大白话，等以后用到了这类信息时，在回过头来补充，这里不懂对后面的影响不大。)可能的取值有 system、const、eq_ref、index和All
  • possible_keys：MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引，year_publication
  • key：实际选用的索引
  • key_len：显示了mysql使用索引的长度(也就是使用的索引个数)，当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。这里就使用了1个索引，所以为1，
  • ref：给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的是1990，就是常量。
  • rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
  • extra：提供了与关联操作有关的信息，没有则什么都不写。

哪些情况需要创建索引

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
  • WHERE条件里用不到的字段不创建索引
  • 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
  • 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段

哪些情况不要创建索引

  • 表记录太少
  • 经常增删改的表
    提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。
    因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
    数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。
  • 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com

©2016-2023 Daryl