Fork me on GitHub

Mysql中的索引

Posted on | In
Words count in article: 2.4k | Reading time ≈ 8

索引的概念

    索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

​ 例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。

    如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。

MySQL中索引的优点和缺点和使用原则

优点：

  1. 索引可以降低服务需要扫描的数据量，减少了IO次数
  2. 索引可以帮助服务器避免排序和使用临时表
  3. 索引可以帮助将随机I/O转为顺序I/O

缺点：

  1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
  2. 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值
  3. 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用原则：
    通过上面说的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。

  1. 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，
  2. 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
  3. 在一同值少的列上(字段上)不要建立索引，比如在学生表的”性别”字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

索引类型：

  1. B+ TREE、 HASH、 R TREE

  2. 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起

  3. 主键索引、二级（辅助）索引

  4. 稠密索引、稀疏索引：是否索引了每一个数据项

  5. 简单索引、组合索引

    ​ 左前缀索引：取前面的字符做索引
    ​ 覆盖索引：从索引中即可取出要查询的数据，性能高

B+TREE索引

1、B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

2、可以使用B+Tree索引的查询类型：

  • 全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
  • 匹配最左前缀：即只使用索引的第一列，如：姓wang
  • 匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
  • 匹配范围值：如：姓ma和姓wang之间
  • 精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的
  • 只访问索引的查询

3、B+Tree索引的限制：

  • 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
  • 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
  • 如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引：如：姓wang,名x%,年30，只能利用姓和名上面的索引

特别提示：

  • 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
  • 为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash索引

1、Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
2、Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
3、适用场景：只支持等值比较查询，包括=, <=>, IN()
4、不适合使用hash索引的场景

  • 不适用于顺序查询：索引存储顺序的不是值的顺序
  • 不支持模糊匹配
  • 不支持范围查询
  • 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

空间数据索引R-Tree（ Geospatial indexing ）

1、MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多

2、InnoDB从MySQL5.7之后也开始支持

全文索引(FULLTEXT)

1、在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
2、InnoDB从MySQL 5.6之后也开始支持

冗余和重复索引：

1、冗余索引：（A），（A，B）

2、重复索引：已经有索引，再次建立索引

索引优化建议

  1. 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
  2. 尽量使用短索引，如果可以，应该制定一个前缀长度
  3. 对于经常在where子句使用的列，最好设置索引
  4. 对于有多个列where或者order by子句，应该建立复合索引
  5. 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
  6. 尽量不要在列上进行运算（函数操作和表达式操作）
  7. 尽量不要使用not in和<>操作

管理索引

1、创建索引

1
2
creata index index_name ON tbl_name (index_col_name[(length)],...);
help create index;

例：

1
2
3
4
MariaDB [hellodb]> create index index_name_age on testlog(name,age);   
Stage: 1 of 1 'altering table'      0% of stage   
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、删除索引

1
drop index index_name ON tbl_name;

例：

1
MariaDB [hellodb]> drop index index_name_age on testlog;

3、查看索引

1
SHOW INDEXES FROM [db_name.]tbl_name;

例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [hellodb]> show indexes from testlog\G;
*************************** 1. row ***************************
        Table: testlog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 100076
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: No query specified

4、优化表空间

1
OPTIMIZE TABLE tb_name;

5、查看索引的使用

1
2
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

EXPLAIN

    通过EXPLAIN来分析索引的有效性

1
EXPLAIN SELECT clause

​ 获取查询执行计划信息，用来查看查询优化器如何执行查询 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MariaDB [hellodb]> explain select * from testlog where id=99999;
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | testlog | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [hellodb]> explain select * from testlog where age=99999;
+------+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | testlog | ALL  | NULL          | NULL | NULL    | NULL | 100076 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

这两个表对比就会发现，上面那张表使用了索引，而下面的则没有

输出信息说明：

  1. id：当前查询语句中，每个SELECT语句的编号

    复杂类型的查询有三种：

  • ​ 简单子查询
  • ​ 用于FROM中的子查询
  • ​ 联合查询：UNION

注意：UNION查询的分析结果会出现一个额外匿名临时表

  1. select_type

    简单查询为SIMPLE

    复杂查询：

    ​ SUBQUERY 简单子查询

    ​ PRIMARY 最外面的SELECT

    ​ DERIVED 用于FROM中的子查询

    ​ UNION UNION语句的第一个之后的SELECT语句

    ​ UNION RESULT 匿名临时表

  2. table：SELECT语句关联到的表

  3. type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高

    ALL: 全表扫描

    index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描

    range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点

    ref: 根据索引返回表中匹配某单个值的所有行

    eq_ref：仅返回一个行，但与需要额外与某个参考值做比较

    const, system: 直接返回单个行

  4. possible_keys：查询可能会用到的索引

  5. key: 查询中使用到的索引

  6. key_len: 在索引使用的字节数

  7. ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值

  8. rows：MySQL估计为找所有的目标行而需要读取的行数

  9. Extra：额外信息

    Using index：MySQL将会使用覆盖索引，以避免访问表

    Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤

    Using temporary：MySQL对结果排序时会使用临时表

    Using filesort：对结果使用一个外部索引排序

---------------- The End ----------------
坚持原创技术分享，您的支持将鼓励我继续创作！