MySql索引相关-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45262221/article/details/109735605

1.索引介绍

索引是关系型数据库中给数据库表中一列或者多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引引用的是B+树索引。

Mysql索引类型：唯一索引、主键（聚集）索引、非聚集索引、全文索引

1.1聚集索引

聚集索引也叫聚簇索引。
定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

注意：聚集索引做查询可以直接获取对应的全部列的数据，所以聚集查询较快。

1.2非聚集索引

定义：索引的逻辑顺序与磁盘上行的物理存储顺序不同。一个表中可以拥有多个非聚集索引。除了聚集索引以外的索引都是非聚集索引。分为普通索引、唯一索引和全文索引。

注意：非聚集索引查询在索引没覆盖到对应列的时候需要进行二次查询，索引非聚集查询较慢。

1.2.1如何解决非聚集索引的二次查询问题

复合索引（覆盖索引）
建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行二次查询。如index(col1,col2),执行下面的语句

select col1,col2 from 表名 where col1=‘xxx’;

要注意使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用.

2.索引的存储机制

无索引的表，查询时，是按照顺序存储的方法扫描每个记录来查询复合条件的记录，这样效率很低。

聚集索引和非聚集索引的根本区别在于表记录的排列顺序和索引的排列顺序是否一致。

3.建立索引的原则

定义主键的数据列一定要建立索引
定义有外键的数据列一定要建立索引
对于经常查询的数据列最好建立索引。
对于需要在指定范围内的快速或频繁查询的数据列
对于经常存取的列避免建立索引
限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

4.索引什么时候失效

如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
对于多列索引，不是使用的第一部分，则不会使用索引
like查询是以%开头
如果列类型是字符串，那一定要在条件中将数据库使用引号引用起来，否则不使用索引
如果mysql估计使用全表扫描要比使用索引快，则不使用索引

5.B-Tree、B±Tree、Hash

5.1B-Tree

定义：

根节点至少包括两个孩子
树中每个节点最多含有m个孩子(m>=2)
除根节点和叶节点外,其他每个节点至少有ceil(m/2)个孩子
所有叶子节点都位于同一层

5.2 B+树

非叶子节点的子树指针与关键字个数相同
非叶子节点的子树指针p[i]，指向关键字值[k[i],k[i+1])子树
非叶子节点仅用来索引，数据都保存在叶子节点中
所有叶子节点均有一个链指针指向下一个叶子节点

B+树更适合做存储索引：
磁盘读写代价更低
查询效率更加稳定
更有利于数据库的扫描

5.3 Hash索引

在这里插入图片描述
由于字段值所对应的数组下标是哈希算法随机计算出来的，所以可能会出现哈希冲突。

哈希表的特点就是可以快速的精确查询，但不支持范围查询。

6.存储引擎

数据库存储引擎：是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。

查看存储引擎：show engines;

Mysql主要有两种存储引擎：InnoDB（有且仅有一个密集索引，索引和数据存储在一个文件）和MyISAM（全部是稀疏索引，索引和数据存储在不同文件）

6.1 InnoDB

InnoDB是MySQL的默认数据库(5.5之后)，InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全，是MySQL上第一个提供外键约束的表引擎。

InnoDB支持Auto_increment，自动增长列的值不能为空，并且值必须唯一。MySQL中规定自增列必须为主键。
InnoDB支持外键FOREIGN KEY，外键所在的表叫子表，外键所依赖的表叫做父表。父表中，被子表外键关联的字段必须为主键，当删除或更新父表的某条信息时，子表也必须有相应的改变，这是数据库参照完整性规则。
InnoDB主索引是聚簇索引，在索引中保存了数据，避免直接读取磁盘，对查询性能有很大提升。
InnoDB支持真正的在线热备份，其他存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制，缺点是读写效率较差，占用的数据空间相对较大。

6.2 MyISAM

MyISAM曾经是MySQL的默认数据库(版本5.5之前)，不支持事务，不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入。

MyISAM可以手动或自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据的丢失，而且修复操作是非常慢的。
MyISAM的优势在于占用空间小，处理速度快，缺点是不支持事务的完整性和并发性。

6.3 总结

InnoDB：支持事务，支持外键，支持行级锁，支持崩溃后的安全修复，但不支持全文索引。如果需要对事务的完整性要求比较高，要求实现并发控制，InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库也可以选择InnoDB，因为支持事务的提交commit和回滚rollback。
MyISAM：不支持事务，不支持外键，不支持行级锁，不支持崩溃后的安全修复，支持全文索引，支持延迟更新索引，支持压缩表。在表有读取查询的同时，支持往表中插入新纪录。

7.如何定位并优化慢查询SQL

7.1根据慢日志定位

慢日志的定义：用来记录我们执行的比较慢的SQL

我们可以通过如下语句查询MySQL对数据库慢查询的设置

show variables like '%query%'

7.2 使用explain等工具分析SQL

explain select name from slowquerytest order by name desc

7.3 修改SQL或者尽量让SQL走索引

8.索引创建

8.1使用CREATE INDEX语句

用于在表已存在添加索引

语法：

CREATE INDEX <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

说明：

索引名：指定索引名。一个表可以创建多个索引，但每个索引名在表中唯一
表名：指定在哪个表中创建索引
列名：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列，可以是一列也可以是多列，一列的称之为单列索引，多列成为组合索引
长度：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引
ASC | DESC：可选项。ASC指定索引按照升序来排列,DNSC指定索引按照降序来排列，默认为ASC

8.2使用CREATE TABLE语句

用于在创建表（CREATE TABLE）时创建，在CREATE TABLE 语句下添加

语法：

表示在创建新表的同时创建该表的索引

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

表示在创建新表的同时创建该表的唯一性索引

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

8.3 使用ALTER TABLE语句

在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项

语法：

在修改表的同时为该表添加索引

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

在修改表的同时为该表添加唯一性索引

ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)