浅学mysql索引及优化

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+树继续分裂，使效率降低。