DBA(MySQL)学习-索引及执行计划

1.索引

1.1 作用

提供了类似于书中目录的作用,目的是为了优化查询

1.2 索引的种类(算法)

B树索引
Hash索引
R树
Full text
GIS 
image.png

image.png

1.3 B树算法普及

B-tree
B+tree
B*tree

1.4 在功能上的分类

1.4.1 辅助索引(二级索引)(S)怎么构建B树结构的?

(1)辅助索引是基于表的列进行生成的
(2)取出索引列的所有值(取出所有键值)
(3)进行所有键值的排序
(4)将所有的键值按顺序落到BTree索引的叶子节点上
(5)进而生成枝节点和根节点
(6)叶子节点除了存储键值之外,还存储了相邻叶子节点的指针,另外还会保存原表数据的指针

1.4.2 聚集索引(C)怎么构建B树结构的?

(1)建表时有主键列(ID)
(2)表中进行数据存储,会按照ID列的顺序,有序的存储一行一行的数据到数据页上(这个动作叫做聚集索引组织表)
(3)表中的数据页被作为聚集索引的叶子节点
(4)把叶子节点的主键值生成上层的枝节点和根节点

1.4.3 聚集索引和辅助索引构成区别总结?

聚集索引只能有一个,非空唯一,一般是主键
辅助索引,可以有多个,是配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构

1.5 辅助索引的细分

单列的辅助索引
联合多列辅助索引(覆盖索引)
唯一索引

1.6 关于索引树的高度受什么影响?

(1)数据行多              ---> 分表
(2)索引列的字符长度       ---> 前缀索引
(3)char   varchar       --->表设计可理
(4)enum 优化索引高度     --->能用则用

2. 执行计划

2.1 作用

上线新的查询语句之前,进行提前预估语句的性能
在出现性能问题时,找到合理的解决思路

2.2 获取执行计划

mysql [world]>desc select * from oldboy.t100w where k2='OPWX'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t100w
   partitions: NULL
         type: ref
possible_keys: idx_k2
          key: idx_k2
      key_len: 17
          ref: const
         rows: 276
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
  
table: t100w
type: ref               --->索引的应用级别
possible_keys: idx_k2   ---> 可能会使用到的索引
key: idx_k2             ---> 实际上使用的索引
key_len: 17             --->联合索引覆盖长度
rows: 276               --->查询的行数(越少越好)
filtered: 100.00        --->额外的信息

2.3 执行计划的分析

type: ref --->索引的应用级别

(1)ALL:全表扫描,不走索引
     没建索引,不走索引
     建了索引,不走索引
(2)Index:全索引扫描
mysql [munan]>desc select k2 from t100w;
(3)range:索引范围扫描
(辅助索引:> < >= <= lirke in  or)
主键:!=
mysql [munan]>desc select * from world.city where id>3000;

mysql [munan]>desc select * from world.city where id!=3000;

mysql [munan]>desc select * from world.city where countrycode like 'C%';

mysql [munan]>desc select * from world.city where countrycode in ('CHN','USA');

修改:
mysql [munan]>desc select * from world.city where countrycode='CHN' union all select * from world.city where countryycode='USA';

(4)ref:辅助索引
mysql [world]>desc select * from world.city where countrycode='CHN';

(5)eq_ref:在多表连接查询时 on 的条件列是唯一索引或主键
mysql> desc select a.name,b.name ,b.surfacearea 
from city as a 
join country as b 
on a.countrycode=b.code 
where a.population <100;
(6)const,system:主键或唯一键等值查询
(7)Extra:NULL    额外的信息
出现Using filesort
解决方法:


2.4 expla(desc)使用场景(面试题)

题目意思:  我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
(1)应急性的慢:突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist;  获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
(2)一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

3. 索引引用规范

业务
1.产品的功能
2.用户的行为
"热"查询语句 ---> 较慢---> slowlog
"热"数据

3.1 建立索引的原则(DBA运维规范)

3.1.0 说明

为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?

3.1.1 (一定要做)建表时一定要有主键,一般是个无关列

3.1.2 选择唯一性索引

唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话,可能存在同名现象,从而降低查询速度。

优化方案:

(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

3.1.3 (一定要做的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段,

排序操作会浪费很多时间。
where  A B C      ----》 A  B  C
in 
where A   group by B  order by C
A,B,C

如果为其建立索引,优化查询
注:如果经常作为条件的列,重复值特别多,可以建立联合索引。

3.1.4 尽量使用前缀来索引

如果索引字段的值很长,最好使用值的前缀来索引。

3.1.5 限制索引的数目

索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
(2) 修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用

3.1.6 删除不再使用或者很少使用的索引(percona toolkit)

pt-duplicate-key-checker

表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

3.1.7 大表加索引,要在业务不繁忙期间操作

3.1.8 尽量少在经常更新值的列上建索引

3.1.9 建索引原则总结

(1)  必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期

3.1.10 关于联合索引(重点!!!)

(1)where A、GROUP BY B、ORDER BY C --->(A,B,C)
(2)where A B C
          ---> 都是等值,在5.5以前无关索引顺序,把控一个原则唯一值多的列放在联合索引的最左侧
          ---> 如果有不等值,例如select ... ...where A= and B> and c=
          索引顺序,ACB,语句改写为ACB
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 205,033评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 87,725评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,473评论 0 338
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,846评论 1 277
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,848评论 5 368
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,691评论 1 282
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,053评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,700评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 42,856评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,676评论 2 323
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,787评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,430评论 4 321
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,034评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,990评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,218评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,174评论 2 352
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,526评论 2 343

推荐阅读更多精彩内容