current position:Home>[mysql chapter - advanced chapter] index

[mysql chapter - advanced chapter] index

2022-08-06 09:24:00I'm not greedy

索引概述

概念

  • 是帮助mysql高效获取数据的数据结构(有序).在数据之外,Database systems also maintain tree structures that satisfy specific search algorithms,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引.

优点:

  • (1)提高查询效率:提高数据检索的效率,降低数据库的IO成本.
  • (2)提高排序效率:通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗.

缺点:

  • (1)索引列占用空间;
  • (2)降低更新表的速度,如对表进行insert、update、delete时,效率降低.

索引结构:

  • mysql的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
  • (1)B+Tree索引:最常见的索引类型,大部分引擎都支持B+Tree索引.
  • (2)hash索引:底层数据结构使用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询.
  • (3)R-Tree索引:空间索引是MyIsam引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少.
  • (4)Full-Text索引:是一种通过建立倒排索引,快速匹配文档的方式,类似于Lucene、Solr、ES.
    在这里插入图片描述

二叉树

  • 特点:The left side is smaller than the value of the root node,The right side is larger than the value of the root node.
  • 缺点:
    (1)顺序插入时,会形成一个链表,查询性能大大降低.
    (2)One node below the binary tree,It can only contain at most two child nodes;大数据情况下,层级较深,检索速度慢.

红黑树

  • 特点:自平衡的二叉树.
  • Solved when inserting in binary tree order,Disadvantages of forming linked lists.
  • 红黑树是自平衡的二叉树,所以也会存在,大数据情况下,层级较深,检索速度慢.

Btree

  • 又称多路平衡查找树.
  • 特点:
    (1) 在根节点、叶子节点、There is data under non-leaf nodes.
    (2)after splitting,Up-split data,will not exist in leaf nodes.
  • Split evolution address:https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

  • 特点:
    (1)非叶子节点只起到索引的作用,Leaf nodes are used to store data.
    (2)after splitting,Up-split data,It will also exist in leaf nodes.(所有的元素都会出现在叶子节点)
    (3)叶子节点形成一个单向链表.
  • mysql索引数据结构对经典的B+Tree进行了优化.在原B+Tree的基础上,增加了一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能.(即双向链表)
  • Split evolution address:https://www.cs.usfca.edu/~galles/visualization/BTree.html

hash

  • 哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中.
  • 会出现哈希冲突,Hash collisions are resolved with linked lists,Just append an element to the linked list.
  • 特点:
    (1)hash索引只能用于对等比较(=、in);不支持范围查询(between、>、<…);
    (2)无法利用索引完成排序操作;
    (3)查询效率高,Usually only one retrieval is required,效率通常高于B+Tree索引.

面试题:

  • 为什么innodb存储引擎选择使用B+Tree索引结构:

(1)相对于二叉树,层级更少,搜索效率高;
(2)对于BTree,Whether it is a leaf node loves a non-leaf node,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低.
(3)相对于hash索引,B+Tree支持范围匹配及排序操作.
(4)B+Tree,Only store data in leaf nodes,搜索效率稳定;Leaf nodes form a doubly linked list structure,便于范围搜索和排序.
(一页是16K)

索引分类

  • 在mysql数据库,将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引.
    在这里插入图片描述
  • 而在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
    在这里插入图片描述
  • 聚集索引选取规则:

(1)如果存在主键,主键索引就是聚集索引;
(2)如果不存在主键,将使用第一个唯一索引作为聚集索引;
(3)如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowId作为隐藏的聚集索引.

  • Below the leaf node of the clustered index is the data of this row.
  • Under the leaf node of the secondary index is the primary key value corresponding to the field value.
  • When searching according to the secondary index, a back-to-table query will be performed.
  • 回表查询:
  • 先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,称之为回表查询.
  • 索引的语法:
  • 创建索引:
    CREATE {UNIQUE | FULLTEXT} INDEX index_name ON table_name(index_col_name,…);
  • 查看索引:
    SHOW INDEX FROM table_name;
  • 删除索引:
    DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率:
  • 通过show [session/global] status;command to view the current databaseinsert、update、delete的访问频次.(session是查看当前会话;globalis to view global data)
    在这里插入图片描述
  • 通过上面的命令,You can view the current database is mainly query,还是以增删改为主.
慢查询日志
  • 慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志;
  • The command to check whether the slow query log switch is enabled:slow_query_log;
    在这里插入图片描述
  • To enable slow query log,需要在mysql的配置文件(LinuxThe location in the environment is as follows:/etc/my.cnf)中配置如下信息:(Windows环境下找到my.ini文件)

#开启mysql慢查询日志开关
slow_query_log=1
#设置慢查询日志的时间为0.002秒,SQL语句执行时间超过0.002秒,就会视为慢查询,记录慢查询日志
long_query_time=0.002

  • LinuxEnvironment View slow query log information:/var/lib/mysql/localhost-slow.log
  • After the configuration file is added,执行重新启动mysql
    (WindowsThe environment test restarts directly from the service)
    在这里插入图片描述
  • After restarting, check whether the slow query is turned on again:
    在这里插入图片描述
profile
  • show profiles 能够在做SQL优化时,Help us understand where our time is going.
  • 通过命令 select @@have_profiling;查看是否支持profile
    在这里插入图片描述
  • 通过命令 select @@profiling;查看profile开关是否开启(0是未开启,1是已开启.如未开启,就执行命令:SET profiling = 1;进行开启)
    在这里插入图片描述
  • 查看每一条SQL的耗时基本情况:show profiles;
    在这里插入图片描述
  • 查看指定query_id的SQL语句各个阶段的耗时情况:show profile for query query_id; ---->show profile for query 13;
    在这里插入图片描述
  • 查看指定query_id的SQL语句CPU的使用情况:show profile cpu for query query_id; ---->show profile cpu for query 13;
    在这里插入图片描述
explain
  • 直接在select语句前面加上explain关键字
    ![在这里插入图片描述](https://img-blog.csdnimg.cn/5ae2704707194e9bab148cc7bd2466bb.png
  • 各个参数的含义:
    在这里插入图片描述

索引使用规则

验证索引效率
  • According to the following example,可以看出;The performance of queries using indexes is greatly improved.
  • 根据主键索引id来进行查询
    在这里插入图片描述
  • Query based on common fields
    在这里插入图片描述
  • Create normal fields as indexes
    在这里插入图片描述
  • Then query through the secondary index just created
    在这里插入图片描述
最左前缀原则
  • 联合索引,要遵守最左前缀法则.
  • 概念:

查询从索引的最左列开始,并且不跳过索引中的列.如果跳跃某一列,则后面的字段索引失效.

  • 演示:
  • 建立联合索引:idx_name_age_job
    在这里插入图片描述
  • 使用name、age、job进行查询:
    在这里插入图片描述
  • 使用name、age进行查询:
    在这里插入图片描述
  • 使用name、job进行查询:
    在这里插入图片描述
  • 使用age、job进行查询:
    在这里插入图片描述
范围查询
  • 联合索引,出现范围查询(>,<),The index on the right side of the range query is invalid.
  • in the example shown below,根据key_lenTo determine whether all fields are indexed.
  • 在业务允许的情况下,尽可能使用>=或<=这类的范围查询,避免使用>或<
  • 演示:
  • EXPLAIN SELECT * FROM emp WHERE NAME = ‘金庸’ AND age > ‘66’ AND job = ‘总裁’
    在这里插入图片描述
  • EXPLAIN SELECT * FROM emp WHERE NAME = ‘金庸’ AND age >= ‘66’ AND job = ‘总裁’
    在这里插入图片描述
索引失效情况
  • empThe indexes in the table are as follows:
    在这里插入图片描述
  • 索引列运算:不要在索引列上进行运算操作,索引会失效.

(1)No operation was performed on indexed columns,索引未失效
在这里插入图片描述

(2)Perform operations on indexed columns,索引失效
在这里插入图片描述

  • 字符串不加引号:Fields of type string are used,不加引号,索引将失效.

(1)Fields of type string are used,加引号,索引未失效.
在这里插入图片描述

(2)Fields of type string are used,不加引号,索引失效.
在这里插入图片描述

  • 模糊查询:头部模糊匹配,索引失效;尾部模糊匹配,索引不失效.

(1)头部模糊匹配,索引失效
在这里插入图片描述

(2)尾部模糊匹配,索引不失效
在这里插入图片描述
(3)Both head and tail are fuzzy matching,索引失效
在这里插入图片描述

  • orJoin condition query:用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到.
    在这里插入图片描述
  • 数据分布影响:如果MySQL评估使用索引比全表更慢,则不使用索引.
    (如果mysql评估使用索引比全表扫描更慢,The index will be discarded)
    在这里插入图片描述
SQL提示
  • 在SQL语句中加入一些人为的提示来达到优化操作的目的.
  • use index:建议mysql使用哪一个索引完成此次查询
  • 语法:explain select 查询字段 from 表名 use index(索引名) where 条件;
  • ignore index :忽略指定的索引
  • 语法:explain select 查询字段 from 表名 ignore index(索引名) where 条件;
  • force index:强制使用索引
  • 语法:explain select 查询字段 from 表名 force index(索引名) where 条件;
覆盖索引
  • 现在emp表的索引情况:
    在这里插入图片描述
  • 概念:查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到.
  • 演示:

(1)The query field does not exceed the bounds of the union index,Return form query is not performed
>

(2)The query field does not exceed the bounds of the union index,Return form query is not performed
在这里插入图片描述

(3)The query field is out of bounds for the union index,进行了回表查询

(4)The query field is out of bounds for the union index,进行了回表查询

  • Columns of the above four picturesextraInterpretation of field values,前两个一样,The last two are the same
  • Using where; Using Index ------>查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据.
  • Using index condition ---------->查找使用了索引,但是需要回表查询数据.
前缀索引
  • 概念:

当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率.此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率.

  • 语法:
  • create index 索引名 on 表名(column(阿拉伯数字)) ;
    在这里插入图片描述
  • 前缀长度
  • 可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的.
单列索引和联合索引
  • 单列索引:

一个索引只包含单个列.

  • 联合索引:

一个索引包含了多个列.

  • userThe current state of the table's indexes:
    在这里插入图片描述
  • Query based on the columns of two single-column indexes,It was found that only one of the indexes was gone
    在这里插入图片描述
  • Create a joint index on two single-column indexes

CREATE INDEX idx_name ON USER(NAME);

  • 再次进行查询,走了联合索引
    在这里插入图片描述

索引设计原则

  • (1)数据多,查询频繁
  • 针对于数据量较大,且查询比较频繁的表建立索引;
  • (2)Fields that are often used as query conditions
  • 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引;
  • (3)区分度高的列(例如:Gender discrimination is low,不适合做索引)
  • 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高;
  • (4)Create corresponding indexes according to the characteristics of the fields
  • 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引;
  • (5)Joint index can avoid back to the table,提高查询效率
  • 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率;
  • (6)控制索引数量
  • 要控制索引的数量,索引越多,维护索引结构的代价也就越大,会影响增删改的效率.
  • (7)Try to establish corresponding constraints when creating indexes
  • If the index cannot be storednull值,在创建表时使用not null约束它.当优化器知道每列是否包含null值时,It can better determine which index is the most efficient for a query.

copyright notice
author[I'm not greedy],Please bring the original link to reprint, thank you.
https://en.chowdera.com/2022/218/202208060921060502.html

Random recommended