唯一索引由两个字段 其中一个字段查询可以使用_Mysql 索引原理(优化)

在上一篇文章中，我们了解了几种关于Mysql索引的模型，我们接着来看下索引有哪些种类，sql语句执行过程中如何验证使用了索引，以及如何使sql在执行过程中搜索速度最快

索引种类

• 主键索引：
  • 作用：加速查询 + 列值唯一 + 不可以为null + 表中只有一个
  • 使用方法：alter table $table add PRIMARY KEY($column)

• 普通索引：
  • 作用：仅加速查询
  • 使用方法：create index $indexname on $table_name($column_name)

• 唯一索引：
  • 作用：加速查询 + 列值唯一 + 可以为null + 表中只有一个
  • 使用方法：create unique index $indexname on $tablename(column_name)

• 组合索引：
  • 作用：多列组合成一个索引，用于加速查询
  • 使用方法: create index $indexname on $table_name(column1， column2)

• 全文索引：
  • 作用：对文本内容进行分词，用于全文搜索
  • 使用方法：alter table message add FULLTEXT KEY(name)

如何验证SQL执行过程中使用了索引

使用MySQL提供explain命令可以获取SQL语句执行过程中的执行计划，可以帮助我们分析，使用方法如下图，在select语句前面加一个explain即可得到SQL的执行计划。

SQL执行计划

SQL执行计划字段分析：

id：id越大，越先执行；id相同，从上往下执行。

select_type：表示查询的类型。

* SIMPLE: 简单的select，没有使用union或者子查询

* PRIMARY: 子查询中最外的查询，查询中若包含任何复杂的子部分，最外层的部分被标记为PRIMARY

table：显示这一步所访问的数据库表名称

partitions：该表使用的哪些分区

type：显示是用什么方式访问的该表的。all：代表的是全表扫描，扫描物理表；index：代表扫描索引树；range指定范围来搜索，该字段需要建索引；ref：非唯一索引扫描；eq_red：唯一索引扫描；const：表中最多有一行匹配，主要用于主键和唯一索引匹配

possible_keys：可能使用的索引，多个使用分号隔开

key：实际过程中使用的索引，多个使用分号隔开

key_len：where条件中使用到的索引长度，排序和分组就算用到了索引也不会被计算到其中

ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows：估算mysql执行时需要扫描的行数，扫描行数越少越好

filtered：返回的行占扫描到的总行的百分比

Extra：额外信息的说明，很值得参考的一个字段。using where：代表使用where进行了条件过滤；using index：代表仅从索引树种找数据即可满足搜索需求，即索引覆盖；unsing index condition：与using index相比，查找出来的数据不足以满足需要，需要再去根据主键补全相关字段，即回表；using filesort：需要排序，如果设置的内存参数不足以满足排序要求，则使用临时文件进行排序。

sql在执行过程中如何让搜索速度更快

下面所有讲解都基于此SQL：

create 

• 回表
  • 定义：在查询过程中，使用的查询索引所对应的索引数上并没有字段，需要回到其他索引树或者其他树结构上再次查询，这就是回表
  • 举例：例如使用该SQL语句时便会需要回表查询【explain select user_name from testsql where age!=12】，在Extra表格中出现using index condition一般是回表的情况，如果我把!=改为=也是回表情况，但是Extra字段什么内容都没有，这个需要自己判断
  • 回表过程：在where条件中，使用了age对数据进行过滤，也就是说使用了索引字段age，但是在索引字段age上却没有我所需要的name字段，需要通过索引age字段查询到主键id，通过主键id再去查name字段

• 索引覆盖
  • 定义：在查询过程中，使用了索引字段查询，在索引字段树上查询的时候，索引字段树上就已经有了所需查询的字段，便不需要其他操作即可返回所有数据，针对回表查询少了回表的步骤，所以会比回表查询更快
  • 举例：例如使用该SQL【explain select id from testsql where age=12;】便会出现索引覆盖的情况，一般来说具体表现就是在Extra字段出现using index标记

索引覆盖

• • 索引覆盖过程：在查询数据过程中，索引树上有所需查询字段即索引就覆盖了全部的查询字段
• 最左前缀原则
  • 定义：针对某一个字段单独建立一个索引又比较浪费，或者说想根据某一个字段去查另外一个字段更方便，引出了联合索引，联合索引可以根据最左前缀的字段快速找出对应的数据
  • 举例：比如我把最上面创建的那个sql，用user_name和age做一个联合索引，然后用该SQL【explain select age from testsql where user_name like 'y%'】去查，便是利用了最左前缀原则

使用最左前缀原则查询SQL

• • 最左前缀查询：索引会使用联合索引(user_name,age)，然后根据最左前缀原则，快速找到user_name字段中以y开头的数据进行遍历，如果遍历不是以y开头，便结束遍历，返回数据即可
• 索引下推
  • 定义：根据联合索引，在使用索引的时候便把对索引的条件进行判断
  • 举例：使用SQL【explain select age from testsql where user_name like 'y%' and age>10】便可以使用索引下推在单个查询内更精准的查数据，如果需要和别的查询连接的话，数量也是最少的
• 扫描行数最少
• 索引长度在保证区分度的情况下，长度越短越好
• 索引转换，针对区分度不好的字段，可以使用哈希法存储哈希字段进行查找