初识Mysql索引-（含笔记）

想做咸鱼吗
470 阅读8分钟

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层

结尾

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

avatar
文章
阅读
粉丝