文档章节

索引及其explain效果

LoSingSang
 LoSingSang
发布于 01/04 15:05
字数 1130
阅读 13
收藏 0

如需转载请注明出处https://my.oschina.net/feistel/blog/2996921

先看个上个索引效果的实例。

mysql>explain select * from comment_infos 
where article_id=212 and status=1
order by submit_time desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comment_infos
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 353
     filtered: 1.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)


mysql>create index article_id_idx_status on comment_infos(article_id,status);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>explain select * from comment_infos 
where article_id=212 and status=1
order by submit_time desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: comment_infos
   partitions: NULL
         type: ref
possible_keys: article_id_idx_status
          key: article_id_idx_status
      key_len: 10
          ref: const,const
         rows: 5
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

删除索引
mysql>alter table comment_infos drop index article_id_idx_status;


怎么知道建了索引有没用,效果怎么样?
explain,查看执行计划,在查询语句前面加上explain。

1.type为ALL表示全表扫描,为ref是表示使用索引
2.key为NULL表示没有使用索引
3.rows表示扫描的行数,只是一个估算值,使用索引后从353降低到5行。
4.Extra中的Using index condition表示使用ICP优化(默认开启,把数据过滤放到存储引擎层,减少访问基表和Server层访问引擎的次数)
    相应的还有MRR(默认开启,索引中查找的字段存在相同值,排序其主键,达到顺序IO)
    BKA(默认关闭,读取join表记录时使用顺序IO)

----------------------------------------------------------------------------------------------------------------

1

索引是对数据库表的一列或多列的值进行排序的一种结构。

屁话:索引相当于书的目录,可以快速...

人话:对于表的一个字段(列),用该字段(或字段们)上的值构建成B+树,当where该字段(字段们)时就在该B+树上查找,而主键会自动创建索引,这就是为什么最好用整形作为主键的原因。那为什么B+树快呢?

神话:先说BST树(二分查找树),为了能动态查找(可以插入删除)而存在。像什么折半查找、分块查找属于静态的。但是,BST上的树查找效率仍然不够高,最坏情况下需要的查找次数是树的高度,这时AVL树(平衡二叉树),通过平衡因子动态降低树的高度从而提高查找效率。而B树,更是降低了树的高度,一个节点可以包含多个关键字,并且树的度数(分叉数)不一定为2,对于B+数查找一次需要遍历一遍树(树的高度)。能同时满足动态查找和高效查找的B+树,岂不乐哉。

----------------------------------------------------------------------------------------------------------------

2

主键会自动创建索引,那么主键和索引的区别?

1.主键是索引,但是索引不是主键。
2.主键索引是聚集索引,而不是主键的字段,就叫作非聚集索引或普通索引。(区别仅为名字不同)
3.主键索引必须唯一,索引没有这个限制,在不重复的列中建立索引就是唯一索引。
4.主键索引不能包含null值,索引没有这个限制。
5.表中可以有多个索引,但是主键只有一个。

以上
主键索引聚集索引,其余的都叫作非聚集索引普通索引
唯一索引,其值不能包含重复的

----------------------------------------------------------------------------------------------------------------

3

覆盖索引

B+树中的叶子节点除了带有索引的值外,还包含有主键的值,当使用索引查找时,实际上是查找该叶子节点上的主键值,再用主键值返回原表中查找相应的行。

当select只包含有主键值时(比如select id这样的),索引完后直接返回,而不再需要到原表中获取其他的字段值。

覆盖索引的效率非常之高。

----------------------------------------------------------------------------------------------------------------

4

联合索引

两个或两个以上的字段创建的索引,就叫联合索引,毫无波澜。

最左前缀原则:比如我建立的联合索引(B,C,D),那么其实也自动建立了普通索引B和联合索引(B,C)。如果 where C and B 那么将不走索引。

----------------------------------------------------------------------------------------------------------------

5

前缀索引
哈希索引

顾名思义。

----------------------------------------------------------------------------------------------------------------
至此,共勉,如需转载请注明出处https://my.oschina.net/feistel/blog/2996921

© 著作权归作者所有

共有 人打赏支持
LoSingSang
粉丝 3
博文 53
码字总数 16173
作品 0
深圳
程序员
私信 提问
mysql索引优化

原始sql如下 sql执行时间如下 explain分析如下 我对user_id加索引后,性能提升显著,explain分析如下 求问:我的理解,user_id后面用的是in,所以理应上对user_id家索引,不会有太明显的提升...

求是科技
2018/09/27
255
3
DML、DDL、DCL区别

总体解释: DML(data manipulation language): 它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言 DDL(data definition languag...

Airship
2016/12/11
10
0
mysql增加索引后查询时间增加

今天做sql优化时发现个问题。增加了索引后sql扫描行数减少,但是查询时间增加了。 原始sql查询时间如下 原始sql的explain分析 单独对community_id加索引后的查询时间 单独对community_id加索...

求是科技
2018/09/27
188
1
mysql性能优化(一)

mysql性能优化、慢查询分析、优化索引和配置 一.每项的基本思路步骤 1.性能瓶颈定位:show命令、慢查询日志、explain分析查询、profiling分析查询、 2.索引及查询优化 3.配置优化 二.mysql是...

攀岩人生
2017/06/16
0
0
MySQL EXPLAIN:Impossible WHERE noticed after re...

mysql 的 explain命令用于查看执行效果,好久没有来分析sql了(ps:有段时间没写SQL了)无解……还好,有google。 mysql> create table t_william(id int primary key,name char(2))engine=i...

NILYANG
2013/02/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

大数据教程(11.9)hive操作基础知识

上一篇博客分享了hive的简介和初体验,本节博主将继续分享一些hive的操作的基础知识。 DDL操作 (1)创建表 #建表语法CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name ...

em_aaron
59分钟前
0
0
OSChina 周四乱弹 —— 我家猫真会后空翻

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @我没有抓狂 :#今天听这个# 我艇牛逼,百听不厌,太好听辣 分享 Led Zeppelin 的歌曲《Stairway To Heaven》 《Stairway To Heaven》- Led Z...

小小编辑
今天
1
0
node调用dll

先安装python2.7 安装node-gyp cnpm install node-gyp -g 新建一个Electron-vue项目(案例用Electron-vue) vue init simulatedgreg/electron-vue my-project 安装electron-rebuild cnpm ins......

Chason-洪
今天
3
0
scala学习(一)

学习Spark之前需要学习Scala。 参考学习的书籍:快学Scala

柠檬果过
今天
3
0
通俗易懂解释网络工程中的技术,如STP,HSRP等

导读 在面试时,比如被问到HSRP的主备切换时间时多久,STP几个状态的停留时间,自己知道有这些东西,但在工作中不会经常用到,就老是记不住,觉得可能还是自己基础不够牢固,知识掌握不够全面...

问题终结者
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部