1、索引的使用场景
哪些情形使用索引:
1、主键自动确立唯一索引 2、频仍作为查询条件的字段应该建立索引 where 3、多表关联查询中,关联字段应该建立索引on双方都要建立索引 select * from user left join order on user.id = order.userid 4、查询中排序的字段,应该建立索引B + tree 有顺序 5、笼罩索引 利益是?不需要回表组合索引 user表 组合索引(name,age) select * from user ——- 全表扫描,没有使用索引 select name,age from user —- 索引笼罩,不需要回表。 6、统计或者分组字段,应该建立索引
哪些情形不适用索引:
1、表纪录太少 索引是要有存储的开销 2、频仍更新 索引要维护 3、查询字段使用频率不高
为什么使用组合索引
由多个字段组成的索引 使用顺序就是建立的顺序
ALTER TABLE ‘table_name’ ADD INDEX index_name(col1,col2,col3) 在一颗索引树上由多个字段 优势:
效率高、省空间、容易形成笼罩索引
使用: 遵照最左前缀原则
1、前缀索引 like 常量% 使用索引 like %常量 不使用索引 2、最左前缀 从左向右匹配直到遇到局限查询 > < between 索引失效
2、索引失效
查看索引设计:EXPLAIN 下令
可以通过explain下令深入领会MySQL的基于开销的优化器,还可以获得许多可 能被优化器考虑到的接见计谋的细节,以及当运行SQL语句时哪种计谋预计会被优化器接纳。 eg: explain select * from user where id = 1
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY 各列的寄义如下: id: SELECT 查询的标识符. 每个 SELECT 都市自动分配一个唯一的标识符. select_type: SELECT 查询的类型. table: 查询的是哪个表 partitions: 匹配的分区 type: join 类型 possible_keys: 此次查询中可能选用的索引 key: 此次查询中确切使用到的索引. ref: 哪个字段或常数与 key 一起被使用 rows: 显示此查询一共扫描了若干行. 这个是一个估计值. fifiltered: 示意此查询条件所过滤的数据的百分比 extra: 分外的信息
id
每个单元查询的SELECT语句都市自动分配的一个唯一标识符,示意查询中操作表的顺序, 有四种情形: id相同:执行顺序由上到下 id差别:若是是子查询,id号会自增,id越大,优先级越高。 id相同的差别的同时存在 id列为null的就示意这是一个效果集,不需要使用它来举行查询。
select_type(主要)
单元查询的查询类型,好比:通俗查询、团结查询(union、union all)、子查询等庞大查询。 包罗
simple
示意不需要union操作或者不包罗子查询的简朴select查询。有毗邻查询时,
外层的查询为simple,且只有一个
primary 一个需要union操作或者含有子查询的select,位于最外层的单元 查询的select_type即为primary。且只有一个 union union毗邻的两个select查询,第一个查询是dervied派生表,除 了第一个表外,第二个以后的表select_type都是union dependent union 与union一样,泛起在union 或union all语句中,然则这个查询要 受到外部查询的影响 union result 包罗union的效果集,在union和union all语句中,由于它不需要参 与查询,以是id字段为null subquery 除了from字句中包罗的子查询外,其他地方泛起的子查询都可能 是subquery dependent subquery 与dependent union类似,示意这个subquery的查询要受到外部表 查询的影响 derived from字句中泛起的子查询,也叫做派生表,其他数据库中可能叫 做内联视图或嵌套select
table
显示的单元查询的表名,有如下几种情形:
若是查询使用了别名,那么这里显示的是别名
若是不涉及对数据表的操作,那么这显示为null
若是显示为尖括号括起来的就示意这个是暂且表,后边的N就是执行设计中的id,示意效果来自于这个查询发生。
若是是尖括号括起来的,与类似,也是一个暂且表,示意这个效果来自于union查询的id为M,N的效果集。
type(主要)
显示的是单元查询的毗邻类型或者理解为接见类型,接见性能依次从好到差: system、const、eq_ref、ref、fulltext、ref_or_null、unique_subquery、index_subquery、 range、index_merge、index、ALL
注意事项:
- 除了all之外,其他的type都可以使用到索引 - 除了index_merge之外,其他的type只可以用到一个索引 - 最少要使用到range级别
possible_keys
此次查询中可能选用的索引,一个或多个 key查询真正使用到的索引,select_type为index_merge时,这里可能泛起两个以上的索引,其他的 select_type这里只会泛起一个。
key_len
用于处置查询的索引长度,若是是单列索引,那就整个索引长度算进去,若是是多列索引,那么查 询纷歧定都能使用到所有的列,详细使用到了若干个列的索引,这里就会盘算进去,没有使用到的 列,这里不会盘算进去。 留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。 另外,key_len只盘算where条件用到的索引长度,而排序和分组就算用到了索引,也不会盘算到 key_len中。
ref
若是是使用的常数等值查询,这里会显示const 若是是毗邻查询,被驱动表的执行设计这里会显示驱动表的关联字段 若是是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
rows
这里是执行设计中估算的扫描行数,不是正确值(InnoDB不是正确的值,MyISAM是正确的值,主要原 因是InnoDB内里使用了MVCC并发机制)
extra(主要)
这个列包罗不适合在其他列中显示单十分主要的分外的信息,这个列可以显示的信息异常多,有几十 种,常用的有:using fifilesort(主要)、using index(主要)、using index condition(主要)、 using temporary、distinct、using where(主要) using fifilesort(主要):
排序时无法使用到索引时,就会泛起这个。常见于order by和group by语句中
说明MySQL会使用一个外部的索引排序,而不是根据索引顺序举行读取。
MySQL中无法行使索引完成的排序操作称为“文件排序”
索引失效剖析
原文链接:https://www.cnblogs.com/vindycxy/p/12685130.html
本站声明:网站内容来源于网络,若有侵权,请联系我们,我们将及时处置。