阅读 106

初识Mysql索引-(含笔记)

Mysql索引

简介

是什么

索引是帮助Mysql高效获取数据的排好序的数据结构

缩句:索引是数据结构,简单说索引(Index)是“排好序的快速查找数据结构)

目的:提高查找效率,可以类比字典

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上

复合索引、前缀索引、唯一索引默认都是使用B+树索引,统称索引。除了B+树,还有哈希索引(hash index)

优点

提高数据检索效率,降低IO成本

降低数据排序成本,降低CPU消耗

缺点

会降低表的更新速度,增、改、删操作。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

分类

主键索引

单值索引

即一个索引只包含单个列,一个表可以有多个单列索引

唯一索引

索引列的值必须唯一,但允许有空值

复合索引

即一个索引包含多个列,一个查询可以只使用索引的一部分,但是只能从最左侧开始

基本语法

创建

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX [indexName] (columnname(length))
复制代码

删除

DROP INDEX [indexName] ON mytable;
复制代码

查看

SHOW INDEX FROM table_name\G;
复制代码

使用ALTER命令

结构

红黑树(平衡二叉树)

B+树

非叶子节点不存储data,只存储索引(冗余),可以放更多的索引

叶子节点包含所有索引字段

叶子节点用指针连接,提高区间访问的性能

(一页16KB)

再看非叶子节点,假设主键ID为bigint类型, 那么长度为8B,指针大小在Innodb源码中为6B,-共就是14B,那么一页里就可以存储16K/14=1170个(主键+指针),那么一颗高度 为2的B+树能存储的数据为:

117016=18720条,一 颗高度为3的B+树能存储的数据为: 11701170*16=21902400 (千万级条)。所以在InnoDB中B+树高度一般为1-3层, 它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。所以也就回答了我们的问题,1 页=16k这么设置是比较合适的,是适用大多数的企业的,当然这个值是可以修改的,所以也能根据业务的时间情况进行调整。(可结合上面那张磁盘图一起理解)

bigint 8byte

16KB/14B=1170个索引元素

B树

叶子节点具有相同的深度,叶节点的指针为空

所有索引元素不重复

节点中的数据索引从左到右递增排列

那些情况需要创建索引

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引

3、查询中与其他表关联的字段,外键关系建立索引

4、频繁更新的字段不适合创建索引(因为每次更新不单单是更新记录,还会更新索引)

5、WHERE 条件里用不到的字段不创建索引

6、在高并发下倾向创建组合索引

7、查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

8、查询中统计或者分组字段

那些情况不需要创建索引

1、表记录太少

2、经常增删改的表

3、数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。

​ 注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果

索引的选择性:有2000条记录,表索引列有1980个不同的值那么这个索引的选择性就是1980/2000=0.99.一个索引的选择性越接近于1,这个索引的效率就越高。

存储引擎

存储引擎是形容表的

一、 InnoDB 存储引擎

  InnoDB 存储引擎是MySQL 的默认事物型引擎,是使用最广泛的存储引擎,采用聚簇索引

  1.支持ACID的事务,支持事务的四种隔离级别。

  2.支持行级锁(默认),也支持表级索。

 3. 主键索引采用聚簇索引(索引的数据域存储数据文件本身key+行记录),辅索引的数据域存储主键的值;因此从辅索引查找数据,需要先通过辅索引找到主键值,再访问辅索引;最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。

  1. 不保存表的总行数

二、 MyISAM 存储引擎

   MyISAM 存储引擎是MySQL最早的存储引擎之一,采用非聚簇索引,没有事物和行级锁。MyISAM对整张表加锁,读取时加共享锁,写入时加排他锁。

  1. 不支持事物。

  2. 支持表级锁,不支持行级锁。

 3.采用非聚簇索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本 一致,但是辅索引不用保证唯一性。

  1. 保存总行数,MyISAM:select count() from table,MyISAM只要简单的读出保存好的行数,注意的是,当count()语句包含 where条件时,两种表的操作是一样的

三、聚簇索引与非聚簇索引的区别

  1. 聚簇索引的顺序就是数据的物理存储顺序,所以一个表最多只能有一个聚簇索引。如InnoDB主索引。

  2. 非聚集索引中的逻辑顺序并不等同于表中行的物理顺序,索引是指向表中行的位置的指针。如MyISAM 主索引与辅索引。

性能分析

Mysql Query Optemizer

MySQL常见瓶颈

Explain

是什么

使用EXPLAIN关键字可以模拟优化执行器执行SQL查询语句,从而指导MySQL是如何处理你的SQL语句的。分析你的查询语句是表结构的性能瓶颈

(MySQL中的explain命令可以用来查看sql语句是否使用了索引,用了什么索引,有没有做全表扫描。可以帮助我们优化查询语句。)

能干嘛

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

怎么使用

explain + SQL语句

执行计划包含的信息

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

各字段解释

id

select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

三种情况:①id相同,执行顺序由上到下;②id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行;③id相同不同,同时存在

select_type

种类:simple、primary、subquery、derived、union、union result

查询类型,主要是用于区别普通查询、联合查询子查询等的复杂查询

table

显示这一行数据属于哪个表

type

访问类型排列

显示查询是用来何种类型,从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL

一般,得保证查询至少达到range级别,最好能达到ref

possible_keys
key
key_len
ref
rows
Extra

索引优化

索引分析

单表、两表、三表

索引失效(应该避免)

tips

  1. B+树可以更好的结合磁盘IO原理提高查询效率
  2. Innodb一 定要有主键,没有主键会以唯一索引为主键, 否则会建立一个隐藏主键
  3. Innodb的数据是和主键索引存在一起的(数据在叶子节点中,MyISAM中的叶子节点存储的数据地址)
  4. 建立索引时要考虑已有索引,一个SQL语句只会选择花费最低的一个索引执行
  5. 索引是一种有序的数据结构(B+树) ,一个节点可以存多个有序的元素,所以要利用好最左前缀原则
  6. 真实场景中一颗B+树的高度通常为1-3层

结尾

仍有很多不完善之处,来日方长,我会慢慢改进!

文章分类
后端
文章标签