mysql 索引

最新推荐文章于 2024-10-13 20:14:57 发布

jiucai0214

最新推荐文章于 2024-10-13 20:14:57 发布

阅读量70

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/jiucai0214/article/details/130373348

版权

sql的优化大部分是对索引的优化

1.索引的概述：

索引是帮助sql高效获取数据的数据结构并且是有序的，在数据之外数据库系统还维护着一些高级查找算法的数据结构，这些数据结构以某种方式指向数据可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引的优点：高效获取数据降低了io成本通过索引列对数据进行排序可以降低数据排序成本降低cpu的消耗

劣势：索引需要维护占用空间提高了查找速度但是也降低了表的更新速度

2.索引的结构

索引是在存储引擎层实现，根据不同的引擎有不同的索引结构

B+ 树索引最常见大部分都支持重点了解

hash 底层数据结构是用hash实现的只有精确匹配索引列查询才有效不支持范围查询

R - 树：空间索引 myisam常使用主要用于地理空间数据类型通常使用较少

full text ：全文索引。建立倒排索引快速匹配文档的方式少用

3. Btree

二叉树的缺点：顺序插入时会形成一个链表查询性能大大降低大数据量的情况下层级较深检索速度慢

红黑树：自平衡的二叉树存在同样问题

B树多路平衡查找树：（n ）阶树每个节点最多存储n-1个key和n个指针，插入数据时超过阶则插入后的中间数据向上裂变，左小右大

B+树：B树的变种

所有元素都会在叶子节点，叶子节点存放数据，叶子节点形成单向链表，上面的节点存放指针

中间节点向上裂变的时候，原来的元素仍然保持在叶子节点，左边指向右边，分裂会分成左右两个连接

mysql中的b+树索引，对经典的B+ 树索引进行了优化在原有的B+ 树的基础上增加了一个指向相邻叶子节点的链表指针（双向指针，头尾相指）就形成了带有顺序指针的B+ 树，提高区间访问的性能

4.hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中先对需要hash索引的那一列的值算出 hash值在根据hash内部函数落在hash的hash槽位上，每个槽位包含值和hash值，可能会出现hash冲突hashmap对这种用链表追加，冲突就是多个值落到一个槽位

特点：hash索引只能用于对等比较（=，in），不支持范围查找

无法利用索引完成排序操作，查询效率较高，通常只需要一次检索（如果出现hash碰撞需要去链表查询），效率高于B+ 树索引

只有memeroy支持，在mysql中innodb具有自适应hash功能，hash索引是存储引擎根据B+ 树索引在指定条件下自动构建

5.思考innodb选择B+树的原因

B+ 树相对于二叉树层级更少搜索效率更高

相对于B树无论是叶子结点还是非叶子结点，都会保存数据，这样导致一页中存储的键值更少，指针跟着减少，要同样保存大量数据智能增加树的高度，导致性能降低，B+ 树叶子结点形成双向链表，便于范围查找

hash树：B+ 树支持范围匹配和排序操作

6.索引的分类

主键索引

针对表中主键创造的索引，只有一个 primary

唯一索引

避免同一个表中某列数据重复，可以有多个，unique

常规索引

快速定位特定数据

全文索引

查找关键词而非比较索引中的值 fulltext

innodb的索引分类

在innodb中根据索引的形式又可以分两种

聚集索引：将数据存储与索引放在一起，索引结构的叶子结点保存了数据，必须有且唯一，叶子挂的是这一行的数据

二级索引：将数据与索引分开，索引结构的叶子结点关联的是对应的主键可以存在多个叶子挂的是对应的id

聚集索引选取规则：

如果存在主键则选择主键作为聚集索引，如果不存在主键将使用第一个唯一索引作为聚集索引

如果都没有，则innodb会自动生成一个row ID作为隐藏的聚集索引总之聚集索引一定存在

例子： select * from user where name= "a"，一张表有 id name 和其他信息，id为主键并且是聚集索引，name则为二级索引

于是会先根据name 比较二级索引的B+ 树，找到对应的ID在根据id 去查聚集索引，找到id对应的拿一行信息，所以把二级索引也称为辅助索引，该查找也称为回表查询

7.思考innodb主键索引的B+ 树有多高

一页16K，每个结点落在磁盘上会存在页当中

假设：一行数据大小为一页，一页中最多可以存储16行这样的数据，innodb的指针占用6个字节的空间，主键即是为bigint，占用字节数为8 ，int4个字节

如果高度为2: n*8+ （n+1）*6= 16*1024 n约为1170 每个结点key的个数，1171个指针，每个指针指向下一行数据，存储的数据为1171*16，如果树的高度为3 ，约为1171* 1171*16

8.索引语法

如何创建索引

create …加索引类型… index 索引名称 on 关联到表（表字段）

查看索引

show index from 表名

删除索引

drop index 索引名称 on表名

9.sql性能分析

sql执行频率

用指令可以查看当前数据看看增删改查的访问频次模糊查询一个下划线代表一个字符 show global status like'com----'

慢查询日志

对于select 查询次数高借助慢查询日志去查询，慢查询日志记录了所有执行时间超过了指定参数的所有sql语句的日志，mysql的慢查询日志默认没有开启，需要在mysql的配置文件中配置如下信息：

show-query-log=1 开启mysql慢日志查询开关

设置慢日志的时间为2 秒，sql语句执行时间超过2miao，就会视为慢查询记录慢查询日志

配置完通过指令重启mysql 查看信息

profile详情

少于2秒的执行不会记录在慢查询日志，1.9几秒的这种可以借助profile

用指令打开prifile 再去执行然后查询耗时在哪里

explain 执行计划：是否使用索引使用索引情况性能

expalin 或者desc命令获取mysql 如何执行select语句信息，包括在select语句执行过程中表如何选择连接和连接的顺序

explain select 语句或则desc select 语句

explain 执行计划各字段：

id： select查询序列号表示查询中执行的select子句或者是操作表的顺序（ID相同，执行顺序从上到下；id不同，值越大，越先执行）

select type：

表示select的类型，常见的取值有simple （简单表，即不使用表连接或则子查询），primary （主查询，外层查询） uinion（uinion中的第二个或者后面查询语句） subquery（select where之后包含的子查询）

type 重要表示连接类型，性能由好到差排序： NULL （不访问任何表）， system（访问系统表）， const（主键唯一索引访问）， eq-ref，ref（非唯一性索引），range，index（用了索引但会遍历索引），all

possible key

可能用到的索引

key：实际使用索引null表示没有

key-len：索引长度

rows：执行查询的行数 innodb中是预估值

filtered：越大越好，返回结果行数占需要读取行数的百分比

10.索引的使用

创建索引就是构建B+树数据结构的过程

最左前缀法则：最重要

如果索引了多列，就遵循该法则，查询从索引的最左列开始，不跳过索引中的列如果跳跃某一列，索引部分就会失效

范围查询：联合索引中出现范围查询范围查询右侧的列索引失效

索引失效的情况：

不要在索引上进行运算操作，否则会失效

自负串类型的字段不加引号

模糊查询：尾部%走索引，头部则失效

or连接必须两侧都是索引，否则失效

数据分布影响：如果评估走索引慢就不会使用索引

sql提示

sql提示是优化数据库的一个重要手段在sql语句中添加一些人为提示来达到优化操作的目的

use index：建议使用什么索引

ignore index：建议忽视该使用

force index：强制用什么索引

覆盖索引不需要回表查询

尽量使用覆盖索引，这样不会回表查询，查询使用了该索引，并且需要返回的列，在该索引中全部能找到，减少select *，因为select* 很容易出现回表查询，除非* 包含所有字段的联合索引（比如a是主键索引，bcd是联合索引二级索引存储的是a的值，根据bcd依次查找到a，就不用回表查找了）联合索引就是一个二级索引

就是一次索引扫描都能查到不需要回表，比如查找 id 和name 根据name去做二级索引查找，name下面挂的就是id 一下子都返回来，不要再去聚集索引去查找了