mysql索引
目录
一、概述
1.索引是什么?
索引是帮助MYSQL高效获取数据的数据结构,索引的目的在于提高查询效率,可以类比字典。
2.索引分类
- 单值索引:即一个索引只包括一列,一个表可以有多个单值索引
- 唯一索引:索引列的值必须唯一,即添加唯一索引的列中的值不能重复,但允许有空值
- 复合索引:一个索引包括多个列
3.索引语法
3.1 索引的创建及删除
//创建单值索引
create index [index_name] on [table_name](column_name)
//ename_index(索引名称),emp(表名),ename(字段名称)
eg:create index ename_index on emp(ename)
//删除单值索引
drop index [index_name] on [table_name]
//ename_index(索引名),emp(表名)
eg:drop idex ename_index on emp
//创建唯一索引
create unique index [index_name] on [table_name](column_name)
//empno_index(索引名称),emp(表名),empno(字段名称)
eg:create unique index empno_index on emp(empno)
//删除唯一索引
drop index [index_name] on [table_name]
//emmpno_index(索引名),emp(表名)
eg:drop index empno_index on emp
3.2 查看索引
//整张表的索引查看
show index from [table_name]
//emp(表名)
eg:show index from emp
4.索引结构
4.1 BTree索引
5.性能分析
5.1 explain分析
SQL的索引是为了帮助查找和排序,那么如何知道某个SQL语句的查询效率呢?
可以使用explain,在查询语句前加上"explain",就可以看到这个SQL的执行计划
//平常的查询语句
select * from emp;
//加上explain之后
explain select * from emp;
5.2 执行信息解释
下面对于上面表中的字段进行解释
id
id是select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。当一个查询SQL语句中包含不只一个表时,那么查询表的顺序就不会一样,如下:
- 当id相同时,执行顺序按照"table"的顺序,由上到下依次执行
//当执行这个语句时,先查询emp表,然后查询dept表
explain select * from emp,dept where emp.deptno=dept.deptno
- 当id不相同时,id值越大优先级越高,越先被执行
//当执行这个SQL,会导致id不相同(子表嵌套查询)
explain select * from
emp where emp.deptno=(select deptno from dept where deptno=10 )
- 当id有相同也有不相同时,先执行id大的,当id相同的时候,按照"table"的顺序执行
//临时表查询
explain select * from
(select * from emp where sal between 1000 and 2000 ) t1,emp
where t1.deptno=emp.deptno
select_type
"select_type"是查询类型,主要是区别“普通查询”、“联合查询”、“子查询”等的复杂类型查询,常用的类型有以下几个:
- SIMPLE:简单查询,即单表查询,查询中不包括子查询或UNION
- PRIMARY:查询中若包括任何复杂的子部分,最外层(即最后执行的表)被标记为PRIMARY
- SUBQUERY:在select或where中包括了子查询
- DERIVED:在from中包含的子查询(即临时表或虚表)被标记为DEVIRED(衍生)
- UNION:若第二个select出现在union之后,则被标记为union
- UNION RESULT:从union表获取的结果
table
显示这一行数据是关于哪张表的
type
访问类型排列,显示查询使用了何种类型,从最好到最差依次是:
- system:表只有一行记录(等于系统表),这是const类型的特列,平时也不会出现,这个可以忽略不计
- const:表示通过一次索引就找到了,const用于比较primary key或者union索引。如果将主键进行where查询,那么只会查询一行数据,就是const,如下:
//将主键放在where判断中,就只会匹配到一行数据,就是const
//在emp表中empno为主键
explain select * from emp where empno=7369
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引。
//如下面SQL,不但要求where后的两个字段是唯一索引或主键,还要求最终查的数据只
//有一条,才会出现"type"为"eq_ref"的情况
//以下的emp_test的deptno和dept_test的deptno都是主键
explain select *
from emp_test,dept_test where emp_test.deptno=dept_test.deptno
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,但是它可能会找到多个符合条件的行
//emp表中的deptno有索引
explain select * from emp where deptno=20
- range:只检索给定范围的行,使用一个索引来选择行,在where之后的判断条件的列必须有索引,否则就是全表扫描。
//emp表中的sal字段必须有索引,且在800-1000之间的数据过多时也会成为全表扫描
explain select * from emp where sal between 800 and 1100
- index:将索引树进行扫描,而不是全表,比全表扫描效率高
//emp表中的sal字段有索引
explain select sal from emp
- ALL:检索类型为全表扫描,当数据量过大时,必须要进行索引优化
//进行全部查询,则为全表扫描
explain select * from emp
possible_keys
显示可能应用在这张表上的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引会被列出,但不一定会被使用。
//emp表中empno字段不仅为主键,而且还加了索引
explain select * from emp where empno=7369
key
实际使用的索引,如果为null,则没有使用索引。
查询中若使用了覆盖索引,则该索引仅出现在key列表中。
- 覆盖索引:当possible key为null时,key有索引,这种情况会发生在刚好建了一个复合索引,复合索引的数量和字段刚好是要查询的字段,那么会称为覆盖索引。
//emp表中empno字段不仅为主键,而且还加了索引
//primary key有两个索引,key只有一个
explain select * from emp where empno=7369
key_len
表示索引使用的字节数,可通过该列计算查询中使用的索引长度,在不损失精度的情况下,长度越短越好。
key_len显示的值为索引字段的最大可能长度,并非实际长度,即key_len是通过表定义计算而得,不是通过表内检索出的。
ref
显示索引的哪一列被使用,如果可能的话是一个常数。哪些列或常量被用于查找索引列上的值。
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数。
如果where后有索引,那么会大幅度减少查询的行数
//emp表中sal有索引,所以只需要查很少行就可以找到
//如果没有索引,那就需要全表扫描
explain select * from emp where sal=20
extra
包含不适合在其他列中显示但十分重要的额外信息,如下:
- Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作叫做“文件排序”。简单地说,有Using filesort的话,自己的索引有一部分mysql没有用,而是内部自己建的索引。
- Using temporary:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常用于排序order by和分组group by。
- Using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错!如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
- Using where:表示使用了where筛选
- Using join buffer:使用了连接缓存
- impossible where:where子句的值总是false,不能用来获取任何元组
二、优化案例
查询category_id为1且comments大于1的情况下,views最多的author_id
//以上是article表的内容
//获取最多的views使用order by正排序,并且拿到第一个值
explain select author_id
from article
where category_id=1 and comments>1 order by views desc limit 1
【不足】:
以上这种SQL会出现“全表扫描”、“文件内排序”的问题,如下
【优化】:
方案一:在进行查询时使用了三个字段,那么可以将这三个字段添加为”复合索引“,再次进行查询:
//添加索引
create index index_union on article(category_id,comments,views)
添加索引之后的执行信息如下,解决了全表扫描的问题,由于comments是一个范围,索引失效导致文件内排序:
由于以上问题,方案一不能使用!
方案二:方案一由于comments是个范围,导致索引失效,那这次复合索引中不使用添加comments
//索引
create index index_union on artilce(category_id,views)
这次的索引没有任何问题,优化成功!
三、索引底层原理
1.几个问题
mysql数据存储在什么地方?
mysql数据存储在磁盘当中。
查询数据比较慢,一般情况下卡在哪里?
卡在IO上,即内存从磁盘中拿mysql数据。那么如何提高IO的效率?
可以从两个方面提高:第一,减少IO的次数;第二,减少IO的量。
去磁盘读取数据的时候,是用多少读取多少吗?
先普及几个概念:
- 局部性原理:数据和程序都有聚集成群的倾向,同时之前被查询过的数据很有可能再次被查询,空间局部性、时间局部性。
- 磁盘预读:内存跟磁盘进行交互的时候,一般情况下是有一个基本的逻辑单元,称之为"页",datapage,页由操作系统决定是多大,一般是4k或8k,而我们在进行数据交互的时候,可以取页的整数倍来读取。Inonodb存储引擎一次读取数据16kb。
索引存储在哪里?
索引也是和数据一样存储在磁盘当中的,查询数据的时候会优先将索引加载到磁盘当中。
索引在存储的时候需要什么信息?需要存储什么字段值?
需要存储的信息有:
- key:实际数据行中存储的值
- 文件地址:指向存储数据文件的地址
- offset:偏移量
这种格式的数据要使用什么格式的数据结构来存储?
mysql使用B+树来存储数据。
- OLAP:联机分析处理,对海量历史数据进行分析,产生决策性的影响,数据仓库(Hive)
- OLTP:联机事务处理,要求在很短的失效内返回对应的结果,关系型数据库
为什么mysql存储数据要使用B+树,而不是下列这些数据结构?
-
为什么不是哈希表?
1、哈希冲突,如果散列分布不均匀,会产生大量的线性查询,比较浪费时间。散列分布不均匀也会导致一个数组下标中的链表长度过长,其他的位置节点太少,导致内存空间浪费。
2、不支持范围查询,要进行范围查询的时候,必须要挨个遍历。如果要查询id为1到3的数据,就要将1到3的数据挨个进行遍历。
3、对于内存空间的要求比较高。
-
为什么不是二叉树(O(logN))?
1、当插入的数据是递增或者递减的,那么二叉树就会成为链式结构,查询时间复杂度又变成O(N)
-
为什么不是AVL(平衡二叉树)?
1、平衡二叉树的最短子树和最长子树的高度相差不能超过1,当超过1时,会进行左旋或右旋保持平衡。通过插入性能的损失来弥补查找性能的提升。
2、当mysql的写少读多时,这种数据结构还好,但是当写多读少时,这种数据结构的性能就降低。
3、随着数据的插入,树的深度会变深,树的深度越深,意味着IO次数越多,影像数据读取的效率
4、每个树只有两个子树,使得深度容易增大。
2.B+树存储数据
B+树只在叶子节点中存储数据,在非叶子节点中存储索引
- 聚簇索引:是否是聚簇索引取决于数据跟索引是否放在一起的,InnoDB只能有一个聚簇索引,但是可以有很多非聚簇索引。向InnoDB插入数据的时候,必须要包含一个索引的key值,这个索引的key值,可以是主键,如果没有主键,那么就是唯一键,如果没有唯一键,那么就是一个自生成的6字节的rowid
- 非聚簇索引:
3.名词解释
回表
//假设有一张表,有id、name、age、gender四个字段,id为主键,name是索引列
//有以下这个SQL
select * from table where name='zhangsan'
以上场景,由于name是索引列,所以会先根据name索引查询id,再根据id查询整行的记录,一共走了两颗B+树,此时这种现象叫做回表。即回表是根据普通索引查询到聚簇索引的key值之后,然后通过key值在聚簇索引中获取所有行数据。
当name为索引列时,底层会创建一个B+树,可以通过那么来查找聚簇索引的key值
根据name找到聚簇索引key值之后,然后再通过聚簇索引的key值找到整行数据。
索引覆盖
如果是下面这个场景:
//假设有一张表,有id、name、age、gender四个字段,id为主键,name是索引列
//有以下这个SQL
select id,name from table where name='zhangsan'
以上场景,name是索引列,根据name查到聚簇索引的key值(即id)之后,就直接返回,不需要在聚簇索引查询任何数据,此时叫做索引覆盖。
最左匹配
主键不一定只有一个列,也有联合主键,索引也是,也有联合索引。
//有如下场景
//假设有一张表,有id、name、gender、age,id是主键,name和age是联合索引(name在前,age在后)
//以下哪些SQL会用到联合索引
select * from table where name=?//使用
select * from table where age=?//不使用
select * from table where name=? and age=?//使用
select * from table where age=? and name=?//使用
在MySQL中,当有联合索引时会先匹配最左边的索引列,但是上面第三个SQL也可也使用联合索引是因为MySQL内部中有优化器,会调整对应的顺序。
索引下推
MySQL5.7之后默认的一个特点,
//mysql有三层,分别为client、server、存储引擎
//client就是可视化客户端/JDBC代码
//server就是我们要启动的server服务
//存储引擎就是存入数据的磁盘
select * from table where name=? and age=?
以上场景,在没有索引下推之前先是在存储引擎中查找符合name的数据,然后再server层对age进行过滤。在有索引下推之后,根据name和age直接在存储引擎中获取对应的数据。
四、MySQL调优
1.性能监控
1.1 时间监控(高精确度)
当我们运行SQL语句时会看到有一个时间(如下图),显示这个SQL执行的时间,通常为看到是0,但并不是说这个SQL的执行时间为0,而是精度只能为小数点后两位,当我们想要看到更精确的时间时就要设置一些属性。
//首先要设置属性
set profiling=1;
//设置完属性之后再运行SQL,然后输入以下指令就可以显示精确执行时间,如下图
show profiles;
//除了看SQL的整体运行时间之外,还可以看这个SQL的开始时间、检查时间、初始化等
show profile;
五、问题
1.为什么主键ID最好自增?
MySQL的InnoDB存储引擎底层数据结构用的是B+树,若主键ID随机、不连续,会使B+树继续分裂,使效率降低。