MySQL_day6

一、索引

1.简介
索引为mysql中最基础的优化,作用是提供了类似目录的作用,优化查询性能。
2.索引算法种类
B树索引
hash索引
R树索引
gis索引
3.功能分类
辅助索引的B树结构构建:

 **基于索引列生成**
 - 辅助索引是基于表中的某一列生成的(一般为主键)
 - 取出索引列的所有值(键值)
 - 将取出的键值进行排序
 - 将所有键值按顺序落到B树索引的叶子节点上(每页16KB)
 - 将每页的最小值向上生成枝节点
 - 叶子节点除了存储键值外还存储了相邻叶子节点的指针和指向原表数据的指针

聚集索引的B树结构构建
将整行数据作为节点
一般情况下将后端的整表数据作为聚集索引的叶子节点
4.聚集索引和辅助索引的区别

  • 聚集索引只能有一个 且非空唯一 一般为主键
  • 辅助索引可以有多个,配合聚集索引使用
  • 聚集索引的叶子节点就是磁盘上数据行存储的数据页
  • MySQL是根据聚集索引,组织存储数据 数据存储时就是按照聚集索引的顺序进行

5.辅助所以细分

  • 单列的辅助索引
  • 联合多列的辅助索引(覆盖索引)
  • 唯一索引 (索引中每一列都是唯一)

6.影响索引树高度的因素
索引树的高度控制在4层以内

  • 数据行 当数据量大时采用分表
  • 索引列字符长度 字符过长是采用前缀索引
  • char varchar 合理设计表
  • enum 可以减少索引树高度 能用则用

7.执行计划
作用:上线新的查询语句之前,提前预估语句的性能,在出现性能问题时,提供解决思路。

    id: 1
select_type: SIMPLE
    table: t100w     表名
 partitions: NULL
     type: ref                      索引的应用级别
possible_keys: idx_k2      可能会使用到的索引
      key: idx_k2               实际使用的索引
  key_len: 17                  联合索引覆盖长度(越多越好)
      ref: const
     rows: 244                  查询的行数(越少越好)              
 filtered: 100.00
    Extra: NULL              额外信息

type 索引的应用级别

  • all
    全表遍历扫描,不使用索引
    辅助索引条件中 出现!= 不等式 不使用索引
    条件不是索引列时 不使用索引
    %like% % 在前模糊查找 不使用索引
    语句中有函数 不使用索引
  • index(重点)
    全索引扫描
    获取了整列索引的数据
  • range
    索引范围扫描
  • ref
    辅助索引等值查询
  • eq_ref
    在多表连接查询是on的条件时,是唯一索引或主键
  • const,system
    主键或唯一键等值查询
  • null
    查询不到数据

extra 额外信息
若出现using filesort是因为索引设置不合理

二、索引应用规范

1.建立索引的原则
为了使索引的效率更高,创建索引时,必须考虑在那些字段上创建索引,和常见类型的索引。

  • 建表时一定要有主键,一般是无关列
  • where order by group by join on 等操作字段,排序操作会浪费很多时间。where 尽量不要出现不等值
  • 常作为条件的列,如果重复值多,可以建立联合索引
  • 如果索引字段值很长,最好使用前缀索引
  • 最好使用唯一值多的列作索引,
  • 降低索引条目,一方面不要创建没用的索引,不常使用的索引清理掉
  • 索引维护期要避开业务繁忙期
  • 索引会中断在不等式前,因此语句中需要将不等式放在末尾
  • 经常更新的表不适合做索引
  • 尽量少在经常更新值得列上建立索引

2.索引数目过多可能产生的问题

  • 每个索引都需要磁盘空间,索引越多,需要的磁盘空间就越大。
  • 修改表时,对索引的重构和更新很麻烦,越多的索引,会使更新表很慢
  • 优化器的负担会很重,有可能影响到优化器的选择。

3.工具
percona toolkit 可以分析索引是否有用

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

推荐阅读更多精彩内容