Mysql索引来了解一下(超详细)

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

一、index是什么?

1. 定义

  1. 简述:索引(Index)是一种帮助mysql高效获取数据的一数据结构。

  2. 详述:除数据本身外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构叫索引。

Mysql索引来了解一下(超详细)
索引数据对应关系示意图
## 1. 创建索引
mysql> create index idx_employee_username on employee(username);
Query OK, 0 rows affected (1.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

## 2. 查询索引。从查询结果来看,索引的类型是B树
mysql> show index from employee;
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          1 | idx_employee_username |            1 | username    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (1.73 sec)

注意:索引本身也很大,不可能全部存储在内存中,因此索引索引往往以索引文件的形式存储到磁盘中;如果没有特定说明,索引对应的数据结构都是B树。

2. B树简介

B树:它的设计思想是,将相关数据尽量集中在一起,以便一次读取多个数据,减少硬盘操作次数。是对二叉查找树的改进。Mysql索引来了解一下(超详细)有以下三个特点:

  1. 一个节点可以容纳多个值。
  2. 除非数据已经填满,否则不会增加新的层。
  3. 子节点中的值,与父节点中的值,有严格的大小对应关系。一般来说,如果父节点有a个值,那么就有a+1个子节点。比如上图中,父节点有两个值(7和16),就对应三个子节点,第一个子节点都是小于7的值,最后一个子节点都是大于16的值,中间的子节点就是7和16之间的值。
  4. 是一种排好序的快速查找数据结构。

二、index的分类及其使用

1. 分类

  1. 单值索引:即一个索引只包含一个列(一个表可包含多个单值索引)。
  2. 复合索引:一个索引包含多个列。
  3. 唯一索引:索引列的值必须唯一,可以为null。

2. 基本语法

  1. 创建:CREATE [UNIQUE] INDEX indexName ON tableName(columnName);ALERT tableName ADD [UNIQUE] INDEX indexName ON (columnName);
  2. 删除:DROP INDEX [indexName] ON tableName;
  3. 查看:SHOW INDEX FROM tableName;

三、index的优缺点?

1. 优点

  1. ==提高数据索引效率,降低数据库的io成本。== 索引采取BTREE数据结构,这种数据结构,非常有利于减少硬盘的读取次数。假定一个节点可以容纳100个值,那么3层的B树可以容纳100万个数据!假定操作系统一次读取一个节点,并且根节点保留在内存中,那么B树在100万个数据中查找目标值,只需要读取两次硬盘。
  2. 通过索引列对数据进行排序,降低数据排序的成本,降低了cpu的消耗。

2. 缺点

  1. 索引本身也是一种表,该表保存了主键和索引字段,并指向实体表的记录,索引也需要占用空间。
  2. 虽然极大的提高了查询速率,但是会降低表的更新速度(update、delete和insert),因为更新表时不仅要保存数据,还要对应更新索引的。
  3. 索引只是提高效率的一个因素,如果有大数据量的表,需要花时间建立最优秀索引。

四、index的使用场景?不适合使用场景?

1. 适用场景(表的数据量较大)

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其它表关联的字段,外键关系建立索引
  4. 查询中排序的字段应该创建索引
  5. 查询中统计或分组字段

2. 不适用场景

  1. 频繁更新的字段或表不适合建立索引(降低更新效率)。
  2. where条件中没有使用到的字段不创建索引(浪费空间)。
  3. 表的数据量少,不适合建立索引。
  4. 数据列重复率高的不适合建立索引,比如性别:男,女。只有这两个字段,建立索引意义不大。

五、性能分析

1. Mysql query Optimizer(mysql查询优化器)

mysql中有专门负责优化SELECT语句的优化器,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提高其默认的最优执行计划(可能和我们认为的最优相违背,这部分耗费时间最长)

2. 常见性能瓶颈

  1. CPU:cpu在饱和的时候数据一般发生在装入内存或者从磁盘上读取数据
  2. IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件性能瓶颈:top,free,iostat和vmstat来查看定位

3. SQL语句问题

Explain解释执行可查看sql运行状态,语法如下:EXPLAIN sql语句

mysql> explain select * from employee where username='jhon';
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_employee_username | idx_employee_username | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


原文始发于微信公众号(全民java空间):Mysql索引来了解一下(超详细)