MySQL索引

标签: 算法  数据库  大数据  python  mysql

MySQL索引

1. 定义

索引是帮助MySQL高效获取数据的数据结构。索引内部存在一个键值和对应数据的物理地址,当数据很多的时候,索引文件会很大,所以一般以文件的形式存储于磁盘中,后缀名为.myi

2. 常用索引类型

  • 聚集索引
  • 次要索引
  • 覆盖索引
  • 复合索引
  • 前缀索引
  • 唯一索引

3. 索引的优势

  • 提高数据检索效率,降低了数据库的IO成本
  • 对数据进行排序,降低了数据排序成本,降低了CPU的功耗
  • 其作用为:排序和查找。

4. 索引的劣势

  • 占用内存空间
  • 降低了写操作的速度
  • 开发者的难度增加

5. MySQL索引分类

  • 单值索引。一个索引包含单个列,一个表可以有多个单值索引
  • 唯一索引。索引列的值必须唯一,单允许有空值,如约束:unique
  • 复合索引。一个索引包含多个列。

6. 基本语法

# 创建索引
create [unique] index indexName on table(columnName(length)...)
alter table add [unique] index indexName on(columnName(length)...)
#删除
drop index indexName on table
#查看
show index from tableName

7. 索引数据结构之B树

7.1 B+树结构

B+Tree索引是非常普遍的一种数据库索引结构。其特点是定位高效、利用率高、自我平衡。

这是一个BTree数据结构图。具体的实现思路是:存在一个根节点存放数据的范围(该范围可以存在多个),其支节点存放的该根节点所在层的具体值,然后支节点的叶子节点中存放的是具体的数据。值得一提的是,其叶子节点为双向链表,保存邻近的叶子节点的地址。

下面模拟一下查找56的过程。

  • 先到根节点,查找56所在的区间范围
  • 然后确定支节点的地址,寻找56所在的范围
  • 然后找到具体的数据存储地址

注意的几个点:

  • BTree的三层架构可以抗住1000万数据,即100条数据和1000万条数据所需要消耗的IO相同。7.

7.2 B树平衡扩张

B树的每一个节点都有一个固定的层级大小。那么必然会出现的情况是,根节点所在层满了,无法继续添加数据。这个时候怎么办?这个时候索引会进行拆分处理,分配两个数据块A,B,如果新添加的数据大于当前最大的元素,则将该元素放于B,其他的全部放入A;如果新添加的元素小于最大元素则平分数据。刚开始的根节点扩大之前的数据范围,此时其层节点将不再变满。扩张结束。

7.3 数据删除导致查找到废弃节点

当数据删除的时候其索引中的数据是不会删除的,所以此时如果想要获取最大数据,就会找到一个废弃的节点,这个时候,就发现内部没有数据。由于叶子节点之间是双向链表,所以会寻找当前值邻近的节点数据。此时花费的时间就会增加。而解决这种情况的方法就是重新构建索引。

8. 索引的使用时机

8.1 什么时候使用索引

  • 主键(唯一索引)
  • 频繁查询的字段
  • 外键
  • 需要排序的字段
  • 需要分组的字段

8.2 什么时候不适用索引

  • where条件中不使用的字段
  • 频繁更新的字段
  • 表记录很少的时候
  • 经常写操作的表
  • 数据重复且分布比较平均的字段

9. SQL性能分析

当客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给 MySQL Query Optimizer(查询优化器),MySQL Query Optimizer 首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接将值换算为常量值。并对Query中的查询条件进行简化和转换,如去掉一些无用或者显而易见的条件、结构调整等。然后分析Query中的Hint信息,看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划,则会读取所设计对象的信息,根据Query进行写相应的计算分析,然后在得出最后的执行计划。

MySQL的架构中的服务层中存在一个SQL语句优化的模块。他的主要功能是:通过计算分析系统手机到的统计信息,为客户端请求的Query提供他认为最优的执行计划。

此时就会延生出一个问题:开发者自己写的SQL与MySQL优化器执行的过程不一样。这种情况之下就会浪费很多的时间。

9.1 MySQL性能瓶颈

  • CPU饱和。常常发生在将数据加载到内存中或者从磁盘中读取数据的时候。
  • IO饱和。常常发生在装入数据远大于内存容量的时候。

9.2 EXPLAIN

MySQL通过explain 关键字模拟优化器执行SQL语句的过程,从而对SQL语句进行优化。

9.2.1 如何使用
  • explain SQL

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra 
9.2.2 id

select 查询的***,表示了执行select查询的顺序或操作表的顺序。

可能出现的情况:

  • 出现的ID都相同。(按照顺序从上到下执行,执行顺序和我们写的表顺序不一定相同)

  • 出现ID都不同。(ID值越大,就先被执行)

  • 出现的ID既有相同的也有不同的。(先执行ID值最大的,然后ID值相同的就按照顺序执行)

  • derived2这个代表一张临时表,2为生成临时表的ID即t3

9.2.3 select_type

查询类型。用来区分普通查询,联合查询,子查询等的复杂查询

  • SIMPLE。简单的select查询,查询中不包括子查询或者union
  • PRIMARY。查询中若包含任何复杂的子部分,最外层被标记为primary
  • SUBQUERY。在SELECT或者WHERE列表中中包含了子查询,被标记为subquery
  • DERIVEd。在From列表中包含的子查询被标记为derived(衍生表)。
    • MySQL会递归执行这些子查询,将结果放置于临时表中
  • UNION。若第二个SELECT出现在union之后,则被标记为union
    • 如果union包含在from子句的子查询中,外层的select被标记为derived
  • union result。从union表中获取结果的select
9.2.4 table

显示这一行数据是关于哪一张表的

9.2.5 type

查询的访问类型,查找到需要的数据的访问方法

# 从最好---->最差 
system -> const -> eq_ref -> ref -> range -> index -> all
  • system。表中只有一条记录的查询。速度最快,在生产中一般不会出现
  • const。通过索引仅仅查找一次就找到了。用于primary keyunique索引,数据唯一。

  • eq_ref。表中仅仅存在一个值与之相对应。

  • ref。非唯一索引,返回满足该值的所有行。

  • range。仅仅检索指定范围的行,使用一个索引来选择行。如使用了between,<>,in等的查询条件
  • index。仅仅依靠索引查询。
  • all。遍历全表,不使用索引。

小结:system是表中仅仅一条记录;const是表中有多条记录,其查询条件可以视作为常量的值,子查询也算常量处理;eq_ref查询条件为变量,另一个表中仅仅存在一条记录与之对应;ref是另一个表中存在多条记录与之匹配;range是获取指定范围的值,不需要全表扫描;index通过索引扫描数据;all进行全表扫描数据;

9.2.6 possible_keys

这次查询可能使用到的索引。理论计算得出,实际可能并未使用;

9.2.7 key

实际使用的索引

9.2.8 key_len

使用索引所占的字节大小,越少越好。条件越复杂其字节数越大。

9.2.9 ref

引用其他表的字段

9.2.10 rows

查询到所需要的数据扫描的行数。

9.2.10 partitions

是否为分区表

9.2.11 extra

包含不适合在其他列中显示但十分重要的额外信息

  1. Using filesort。对数据使用一个外部的索引排序,而不是按照表内索引的顺序进行排序。

出现这种情况的场景为:一般是联合索引,进行分组或者排序的字段的顺序和构建索引时的字段顺序不同,导致内部排序的时候需要再次进行一次排序。非常影响性能。

  • 目前t1表中的联合索引

  • 仅仅根据一个字段进行分组。出现using filesort

  • 根据两个字段进行分组。此时就没有再次进行排序了。

  1. Using temporary使用了临时表保存数据,常见于group Byorder by。和上面的原因相同。非常影响性能。
  2. Using index 表示在 进行select操作的时候使用了覆盖索引,避免访问了表的数据行,增强了性能。如果同时出现了using where则表明索引用来读取数据而不是进行查找操作。

  1. using where 表示使用索引进行过滤数据
  2. using join buffer表示使用了连接缓存
  3. impossible where 表示该语句永远不能获取数据
  4. select tables optimized away表示在没有分组的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化Count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
  5. distinct优化distinct。找到第一组匹配的值以后就不再查找。

开发中经常需要考虑的就是避免Using filesortUsing temporary操作,增加Using index操作。

10. 索引优化

10.1 索引优化方法

  • 进行左连接的时候,将右表的字段作为索引;右连接使用左表的字段作为索引。
    • 原因:左连接的时候会加载左表的全部数据,所以将左表作为驱动表,右表不需要加载全部数据,所以作为被驱动表。右连接也是相同。
  • 查询条件均为索引字段
  • 查询的字段最好使用覆盖索引,这个时候不需要查询表,直接在索引中拿数据即可

10.2 索引失效

10.2.1 最佳左前缀原则
  • 创建的索引。复合索引为name,age,deptId

  • 查询name,age,deptId的条件,使用了索引

  • 查询name,age字段的条件,使用了索引

  • 查询name个字段的条件,使用了索引

索引失效的情况:

  • 查询age,deptId,没有使用索引

  • 查询age,没有使用。

使用了部分索引的情况

  • 查询name,deptId,使用了部分索引。仅仅在查询name的时候使用了索引

查询name和查询两个字段的字节数相同。

10.2.2 在索引列使用了计算,函数,类型转换的操作
  • 进行了函数取位的操作

  • 字符串没有加引号,导致MySQL底层自动类型转换

10.2.3 查询条件使用了范围计算
  • where中使用了between and, <> in等范围修饰符。使用了部分索引,仅仅对name使用了索引

10.2.4 使用!=,<>,is null ,not is null
  • 使用上面的这些运算符都会导致索引失效

10.2.5 like通配符
  • %like%此时索引失效

  • %like此时索引失效

  • like%此时使用了部分索引

  • 可以将%like看做一种范围查询
10.2.6 or的使用
  • 使用or以后也会导致索引失效

10.3 白话索引优化与失效

其实索引失效的起因就是无法从已经排序的内容拿到数据。比如最佳左前缀法则,其索引排序为name,age,deptId即树上的排序就是先排name相同的,然后到age,再到deptId,即,此时的agedeptId的单独顺序已经被name打乱。

举个栗子:

name age
111	 12
112  11

此时在数据结构中的体现就是先111 12再到112 11 ,这个时候其age的顺序就被打乱了。所以不能使用索引对没有name开头的进行查询了。

至于说对数据列进行操作,引发其数据本身变化,这样的操作导致B树中的数据和索引中的数据不一样,肯定不能使用索引进行查询了。

10.4 Order By和Group By对索引影响

  • 看一种情况。这个时候仅仅使用了一个索引进行查询,但实际上索引都用了,只不过age,deptId用来进行排序了,没有用来查找

  • 如果我们将排序条件逆序,这个时候必然出现文件内排序

  • 再次添加一个条件age = 12 即另age等于一个常量,所以此时没有进行文件内排序

  • group by对索引字段进行排序,此时分组顺序正序,直接使用索引数据进行分组排序

  • group by 对索引字段进行排序,此时分组顺序逆序。出现文件内排序,并使用临时表

10.5 索引使用的建议

  • 对于单值索引,尽量选择对查询过滤最好的字段。
  • 在组合索引中,查询过滤中效果最好的字段位置越靠前越好
  • 组合索引中,最好包含更多的where条件的字段值。(当然避免范围查询字段索引)
  • 通过分析SQL来判断当前索引是否符合当前的目的
  • 对于like而言,其只要以%开头其索引就无法使用。

11. 查询优化

11.1 小表驱动大表

永远使用数据集小的表去驱动数据集大的表

#假设现在存在一张表A数据多于B,此时需要找到表A中与B重合字段的数据,仅仅需要A的数据
#这是in的写法
select * from A where id in (select id from B) ;

#这是exist 的写法
select * from B where exists(selct 1 from A where A.id = B.id)

下面看一下两者执行顺序

  • in方案执行。将子查询的数据放到主查询中。即将B表的数据检索结果放在A表的结果中

  • exist方案执行。将主查询的数据放到子查询中,于是子查询的SQL执行类型变为了eq_ref

可以看到对于A表进行了全表扫描,然后对是否输出A表数据,进行了判断

**小总结:**如果仅仅需要获取A表中的数据且该数据和B表中的为共有,除了使用join以外,也可以使用in和exists。两者使用的区别是:in将子查询的数据放置在主查询中作为条件,比较适用于主表数据多于从表数据;而exists是将主表查询结果放置于子查询中,比较适用于主表数据少于从表数据。

11.2 Order By

MySQL存在两种排序的算法,FileSortIndex排序,其中FileSort的效率比较低

11.2.1 Index

使用索引进行排序。出现这种排序的场景为

  • ORDER BY使用索引最左列排序
  • 使用where字句与order by字句满足索引最左前列。如,排序字段为第二个索引字段,而第一个字段在where条件中为常量,此时会使用Index排序
11.2.2 FileSort

使用文件内排序,采用的算法主要有多路排序和单路排序

  • 多路排序。MySQL4.1之前使用双路排序,即扫描两次磁盘,首先读取一个指针和需要排序的列,然后写入buffer中,排序完成以后,再次获取所有的列;即进行了两次IO
  • 单路排序。改进多路算法,主要思想是第一次扫描磁盘的时候就将所有需要的数据获取,然后排序。它使用的空间更多了。

存在的问题:

单路算法也延伸了一个问题,其占用空间很大,有可能超过了sort_buffer的最大容量,所以只能进行分片处理,这个时候其IO量就会增加。

解决办法:

  • 增大sort_buffer_size参数的值
  • 增大max_length_for_sort_data参数
  • 在实际开发中,如果添加的数据量大于max_length_for_sort_data则使用多路算法,否则使用单路算法

11.3 排序使用索引

  • MySQL的两种排序方式:文件内排序和有序索引排序
  • MySQL能为排序和查询使用相同的索引
key a_b_c(a,b,c) # 创建一个名为key的复合索引在a_b_c表中

order by # 可以使用索引的左前缀
order by a 
order by a , b
order by a,b ,c
order by a desc , b desc , c desc 

order by #如果where的左前缀为常量,则可以使用索引
where a = const order by b , c
where a = const and b = const order by c 
where a = const and b <const order by b,c

#不能使用索引进行排序
order by a asc , b desc  #排序不一致
where g = const order b ,c ; #丢失a索引
where a = const order c ; #丢失b索引
where a = const order by a ,d # d不是索引
where a in () order by b,c #范围查询

12. 慢查询日志

12.1 简介

MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阙值的语句,具体指运行时间操作long_query_time值的SQL,会被记录到慢查询日志中

long_query_time默认为10,运行时间在10秒以上的SQL

12.2 使用慢查询日志

临时改变日志的方式,当MySQL服务重启以后该修改就失效了

  • 默认慢查询日志是关闭的
#查看当前数据库的慢查询开启情况和日志存放位置
mysql> show variables like '%slow_query_log%' ;
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /opt/mysql/log/slow_query.log |
+---------------------+-------------------------------+
2 rows in set (0.08 sec)

#开启慢查询
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)

#默认慢查询界定时间大于这个值的时候被记录
mysql> show variables like 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

#设置慢查询时间
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)


#查询当前设置的慢查询时间,如果不添加global则需要在另一个会话中才可以查询到当前的改变
mysql> show global variables like 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

#查询当前SQL中慢查询的条数
mysql> show global status like '%Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.06 sec)

配置文件版

在my.cnf文件中添加
slow_query_log=1;
slow_query_log_file=/opt/mysql/日志名字
long_query_time=3;
log_output=FILE

12.3 日志分析工具mysqldumpshow

s:是表示按何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式,大小写不敏感的

13. show profiles

MySQL中提供给开发者的分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优

默认状态为关闭状态。且默认保存15条SQL

# 查询当前数据库的profile状态
mysql> show variables like 'profiling' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

# 开启数据库profile
mysql> set global profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

#查看最近执行的SQL
mysql> show profiles ;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        1 | 0.03361600 | show variables like 'profiling'              |
|        2 | 0.00012075 | select name from t_emp group by age          |
|        3 | 0.00037275 | select name,age from t_emp group by age,name |
|        4 | 0.00018950 | select name from t_emp group by name         |
+----------+------------+--------------

# 查看指定SQL的系统消耗信息
show profile 参数 for query_id 
  • 可以添加的参数

  • 样例查询

日常开发中需要注意的事情:

  • converting heap to myisam查询结果太大,内部不够用存放于磁盘中
  • creating tmp table创建了临时表,用完删除
  • copying to tmp table on disk将内存中临时表复制到磁盘中
  • locked加锁

14. 全局查询日志

在MySQL中的配置文件中,配置

# 开启全局查询日志
general_log=1
#记录日志文件的路径
general_log_file=/opt/mysql/log
#输出格式
log_output=file

命令行中配置,MySQL服务器重启以后失效

set global general_log=1;
set global log_ouput = 'TABLE';
此后所有的SQL都将被记录到mysql.general_log系统表中
select * from mysql.general_log;

不建议使用这个功能,可以直接使用profile功能更加强大。

版权声明:本文为qq_42760613原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_42760613/article/details/109573626

智能推荐

Qt 之 Query Model Example 解析

总体概括 Query Model Example主要演示了怎么使用QSqlQueryModel这个数据库查询模型类。其中包括创建普通的数据库查询模型、可编辑的数据库查询模型和自定义的数据库查询模型。普通(默认)的数据库查询模型是只读的(不可再模型中编辑数据,模型只通过视图展示数据);可编辑的数据库查询模型重写了QSqlQueryModel的flags()方法和setData()方法;自定义的数据库...

Flutter:Scaffold.of() called with a context that does not contain a Scaffold.

Flutter:Scaffold.of() called with a context that does not contain a Scaffold. 当我第一次点击按钮想要弹出底部消息时出现了如下错误 当BuildContext在Scaffold之前时,调用Scaffold.of(context)会报错。这时可以通过Builder Widget来解决,代码如下:...

【机器学习基础】线性回归

                                                        &nbs...

08-Vue实现书籍购物车案例

书籍购物车案例 index.html main.js style.css 1.内容讲解 写一个table和thead,tbody中每一个tr都用来遍历data变量中的books列表。 结果如下: 在thead中加上购买数量和操作,并在对应的tbody中加入对应的按钮。结果如下: 为每个+和-按钮添加事件,将index作为参数传入,并判断当数量为1时,按钮-不可点击。 结果如下: 为每个移除按钮添加...

堆排序

堆排序就是利用堆进行排序的方法,基本思想是,将代排序列构造成一个大根堆,此时整个序列的最大值就是堆顶的根节点。将它与堆数组的末尾元素交换,此时末尾元素就是最大值,移除末尾元素,然后将剩余n-1个元素重新构造成一个大根堆,堆顶元素为次大元素,再次与末尾元素交换,再移除,如此反复进行,便得到一个有序序列。 (大根堆为每一个父节点都大于两个子节点的堆) 上面思想的实现还要解决两个问题: 1.如何由一个无...

猜你喜欢

基础知识(变量类型和计算)

一、值类型 常见的有:number、string、Boolean、undefined、Symbol 二、引用类型 常用的有:object、Array、null(指针指向为空)、function 两者的区别: 值类型暂用空间小,所以存放在栈中,赋值时互不干扰,所以b还是100 引用类型暂用空间大,所以存放在堆中,赋值的时候b是引用了和a一样的内存地址,所以a改变了b也跟着改变,b和a相等 如图: 值...

Codeforces 1342 C. Yet Another Counting Problem(找规律)

题意: [l,r][l,r][l,r] 范围内多少个数满足 (x%b)%a!=(x%a)%b(x \% b) \% a != (x \% a) \% b(x%b)%a!=(x%a)%b。 一般这种题没什么思路就打表找一下规律。 7 8 9 10 11 12 13 14 15 16 17 18 19 20 28 29 30 31 32 33 34 35 36 37 38 39 40 41 49 50...

[笔记]飞浆PaddlePaddle-百度架构师手把手带你零基础实践深度学习-21日学习打卡(Day 3)

[笔记]飞浆PaddlePaddle-百度架构师手把手带你零基础实践深度学习-21日学习打卡(Day 3) (Credit: https://gitee.com/paddlepaddle/Paddle/raw/develop/doc/imgs/logo.png) MNIST数据集 MNIST数据集可以认为是学习机器学习的“hello world”。最早出现在1998年LeC...

哈希数据结构和代码实现

主要结构体: 实现插入、删除、查找、扩容、冲突解决等接口,用于理解哈希这种数据结构 完整代码参见github: https://github.com/jinxiang1224/cpp/tree/master/DataStruct_Algorithm/hash...