MySql索引

MySql索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

索引也会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

建立索引会占用磁盘空间的索引文件

所以，索引不是越多也好，我们的目的就是把索引建得恰到好处

Mysql索引相关知识

存储引擎

MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎
同一个数据库也可以使用多种存储引擎的表
在实际开发中使用最多的两种引擎是MyISAM和InnoDB(几乎都是InnoDB)

  • MyISAM
    • MyISAM高速引擎，拥有较高的插入，查询速度
    • MyISAM存储不支持事务、行级锁和外键约束的功能
    • MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点
    • MyISAM的并发插入特性允许同时选择和插入数据
    • MyISAM表是独立于操作系统的
    • 每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件：表定义文件(.frm),数据文件(.MYD)，索引文件(.MYI)
  • InnoDB
    • InnoDB:5.5版本后MySQL的默认数据库，支持事务和行级锁定，比MyISAM处理速度稍慢
    • InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
    • MySQL支持外键的存储引擎只有InnoDB
    • 支持自动增加列AUTO_INCREMENT属性
    • 与其它存储引擎不同，InnoDB表能够自动从灾难中恢复
    • 每当我们建立一个InnoDB引擎的表时，就会在本地磁盘上建立两个文件表定义文件(.frm),数据和索引文件(.IBD)
    • InnoDB的表必须要有主键，如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)
    • InnoDB的表建议使用自增主键，否则每次插入可能导致之前页内容的清除和重写，增加开销，增加碎片

索引结构

mysql中常用两种索引结构(算法)BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样(几乎都是BTree)

  • HASH

    • Hash索引的底层实现是由Hash表来实现的，非常适合以 key-value 的形式查询，也就是单个key 查询，或者说是等值查询
    • Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询
    • 联合索引中，Hash索引不能利用部分索引键查询。 对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的前缀匹配
    • Hash索引还存在Hash冲突的问题，当有多条记录满足某个Hash键值的数据时，还需要通过访问表中的实际数据进行比较，并得到相应的结果

  • BTree

    • Mysql中使用的是B+Tree
    • B+Tree索引可以被用在=,>,>=,<,<=和between这些比较操作符上（范围查询）
    • B+Tree索引还可以用于like操作符，只要它的查询条件是一个不以通配符（%）开头的常量
    • B+Tree非叶子节点不存数据，只存索引，一个节点16K，为的是尽可能多的读到索引到内存进行比较，压缩树的高度，减少IO次数，所以索引字段大小也尽可能的小
    • B+Tree索引根节点一般是常驻内存的，减少一次IO

索引方式

  • 聚集索引

指索引项的排序方式和表中数据记录排序方式一致的索引，每张表只能有一个聚集索引，聚集索引的叶子节点存储了整个行数据(说的就是InnoDB引擎的表)

  • 非聚集索引

非聚集索引中索引的逻辑顺序与磁盘上记录的物理存储顺序不同。一个表中可以拥有多个非聚集索引。叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，还存储了一个指向该记录的聚集索引建的书签(说的就是MyISAM引擎的表)

索引类型

  • 普通索引:最基本的索引，它没有任何限制
  • 唯一索引:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
  • 主键索引:特殊的唯一索引，一个表只能有一个主键，不允许有空值
  • 组合索引:多个字段上创建的索引，使用组合索引时遵循最左前缀匹配原则
  • 全文索引:主要用来查找文本中的关键字，而不是直接与索引中的值相比较，类似搜索引擎
MySql索引底层原理

MyISAM——BTree

MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址,现在索引里面找到相应节点，然后根据节点数据里面的地址，找到记录的物理存储位置，读取记录内容

在MyISAM中，主索引和辅索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅索引的key可以重复

innoDB——BTree

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域，在使用辅助索引查询到data之后，如果需要查询的字段在data里面都存在，则可以直接返回查询结果（覆盖索引），如果需要查询的某些查询字段不在辅助索引的data内，则需要根据主键值去主索引查询该记录的其他字段（回表）

MySql索引优化

explain命令

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈，explain包含以下字段：

1. id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2. select_type //查询类型
3. table //正在访问哪个表
4. partitions //匹配的分区
5. type //访问的类型
6. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
7. key //实际使用到的索引，如果为NULL，则没有使用索引
8. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
9. ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
10. rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
11. filtered //查询的表行占表的百分比
12. Extra //包含不适合在其它列中显示但十分重要的额外信息

MySql索引常见问题
  • 选择性较低的列是否适合加索引
  • 索引选择性等于列中不重复（distinct）的行数量（也叫基数），与记录总数的比值。范围在0-1之间。数值越大，索引越快。对于选择性不同的值，区分看待，选择性高的走索引，选择性低的不走索引
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yanghanwen/p/12270148.html