MySQL-索引

气势如虹_

于 2024-01-29 22:07:28 发布

阅读量811

点赞数 21

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_68409964/article/details/135922089

版权

MySQL-索引

索引(Index)是帮助MySQL高效获取数据的数据结构(有序)。

索引优势	索引劣势
提高数据检索的效率，降低数据库的IO成本	索引列也要占用空间
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引大大提高了查询效率，同时也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

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

主要包含以下几种：

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围索引
R-Tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式

平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上(单向链表)，增加一个指向相邻叶子节点的链表指针(双向链表)，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
Hash索引：
- 哈希索引就是采用一定的哈希算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
- 如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决。
- Hash索引特点
  - hash索引只能用于对等比较(=、IN)，不支持范围查询(between、>、<，…)
  - 无法利用索引完成排序操作
  - 查询效率高，通常只需要一次检索就可以了，效率通常较高
存储引擎支持：
- 在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
为什么InnoDB存储引擎选择使用B+Tree索引结构？★★★
- 相对于二叉树，层级更少，搜索效率更高
- 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
- 相对hash索引，B+Tree支持范围匹配及排序操作

索引分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个	\
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
- 聚集索引
  - 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
  - 聚集索引必须有，而且只有一个
- 二级索引
  - 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
  - 二级索引可以存在多个
- 聚集索引选取规则
  - 如果存在主键，主键索引就是聚集索引
  - 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
  - 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

索引-语法

创建索引
- 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执行频率
- MySQL客户端连接成功后，通过show [session|global] status;命令可以提供服务器状态信息，通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次，以此来确定当前数据库是以查询为主还是以增、删、改为主
- SHOW GLOBAL STATUS LIKE 'Com_______';(7条下划线)
慢查询日志
- 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒)的所有SQL语句的日志
- mysql的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息
- ```
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
```
- 配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
- ```
systemctl restart mysqld
tail -f /var/lib/mysql/localhost-slow.log
```

profile详情

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时

#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain执行计划
- EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序
- ```
#语法：
#直接在select语句之前加上关键字explain/desc
explain select 字段列表 from 表名 where 条件;
```
- EXPLAIN执行计划各字段含义：
  - id
    - select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id不同，值越大，越先执行；id相同，执行顺序从上到下)
  - select_type
    - 表示select的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含的子查询)等
  - type
    - 表示连接类型，性能由好到差的连接类型为：NULL、system、const、eq_ref、ref、range、index、all
  - possible_key
    - 显示可能应用在这张表上的索引，一个或多个
  - key
    - 实际使用的索引，如果为NULL，则没有使用索引
  - key_len
    - 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
  - rows
    - MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
  - filtered
    - 表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
  - Extra
    - 额外信息

索引使用-最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃了某一列，索引将部分失效(后面的字段索引失效)
联合索引中，出现范围查询(<、>)，范围查询右侧的列索引失效，可将<、>改为<=、>=可避免失效

索引列运算注意事项

不要在索引列上进行运算操作，索引将失效
字符串类型字段使用时，不加引号 ，索引将失效
如果仅仅是尾部使用模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

索引使用

or连接的条件
- 用or分割开的条件，如果or左右两侧的条件有任何一侧没有索引，那么涉及到的索引都会失效
数据分布影响
- 如果MySQL评估使用索引比全表更慢，则不使用索引
SQL提示
- SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的
- use index：告诉数据库要用哪个索引
  - explain select * from 数据库名 use index(索引名) where ....;
- ignore index：告诉数据库不要用某个索引
  - explain select * from 数据库名 ignore index(索引名) where ....;
- force index：告诉数据库必须走某个索引
  - explain select * from 数据库名 force index(索引名) where ....;
覆盖索引
- 尽量使用覆盖索引(查询使用了的索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *
前缀索引
- 当字段类型为字符串(varchar、text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率
- create index 索引名 on 表名(字段名(n));
- n表示我要提取字符串前面几个字符来创建索引

问：一张表，有四个字段(id,username,password,status)，由于数据量大，需要对以下SQL 语句进行优化，该如何进行才是最优方案？

`select id,username,password from tb_user where username='itcast';`

答：id是主键，id有主键索引，也是聚集索引。为username、password字段创建索引，即为二级索引，此时二级索引中会有主键的值，可避免回表查询。此时性能最优。