mysql--索引

时间：2022-03-27 01:48:39

01 索引类型

索引类型分为

  • NORMAL：这是最基本的索引，它没有任何限制，默认的BTREE类型的索引，是我们大多数情况下用到的索引。
  • UNIQUE：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复。例如身份证号用作索引时，可设置为unique。Unique(要求列唯一)和Primary Key(primary key = unique not null 列唯一)约束均为列或列集合中提供了唯一性的保证，Primary Key是拥有自动定义的Unique约束，但是每个表中可以有多个Unique约束，但是只能有一个Primary Key约束。
  • FULLTEXT：表示全文搜索的索引，仅可用于 MyISAM引擎 表。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。但是在检索的时候数据量比较大的时候，现将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多。
  • SPATIAL：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

02 索引实现

索引主要由两种实现：B-TREE和HASH。

B-TREE

  • B-TREE以B 树结构存储数据，大大加快了数据的查询速度
  • B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）
  • 全值匹配的查询SQL，如 where act_id= ‘1111_act‘
  • 联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
  • 匹配模糊查询的前匹配，如where act_name like ‘11_act%‘
  • 匹配范围值的SQL查询，如where act_date > ‘9865123547215‘（not in和<>无法使用索引）
  • 覆盖索引的SQL查询，就是说select出来的字段都建立了索引

HASH

  • Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引
  • 存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询
  • Hash索引无法用于排序
  • Hash不适用于区分度小的列上，如性别字段
  • Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
  • Hash 索引在任何时候都不能避免表扫描。
  • Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

03 使用索引

  • 查询频繁的列，在where，group by，order by，on从句中出现的列
  • where条件中<，<=，=，>，>=，between，in，以及like 字符串 通配符（%）出现的列
  • 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
  • 离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高。
  • 尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。

可以通过 explain 查看sql的执行计划，有没有用到索引。

explain select * from test_table;

 

04 索引失效

  • 使用负向查询（not ，not in， not like ，<> ,!= ,!> ,!< ） 不会使用索引。
  • WHERE字句的查询条件里使用了函数。WHERE DAY(column)=…）。
  • JOIN查询中，只有在主键和外键的数据类型相同时才能使用索引，否则不使用。
  • 如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。比如说，如果查询条  件是LIKE ‘abc%‘,MYSQL将使用索引；如果条件是LIKE ‘

相关文章