Mysql索引和查询优化

1 索引

1.1 什么是索引

索引是数据库中用于提高数据检索速度的一种数据结构。它类似于图书馆的目录，可以根据关键字快速找到对应的记录。
在数据库中，索引由一个或多个列组成，每个列都存储着索引的键值和指向实际数据存储位置的引用。通过创建索引，数据库系统可以按照索引列的顺序对数据进行排序，并使用快速搜索算法来加速数据查询操作。
索引的存在可以极大地减少数据库的扫描量，提高数据检索的效率。当我们执行一条查询语句时，数据库系统会首先检查是否存在适用的索引。如果存在，它将使用索引来定位数据，而不是遍历整个数据表。这样可以大大减少数据访问的时间和资源消耗。
然而，索引也会占用额外的存储空间，并且在插入、更新和删除数据时需要维护索引结构，会带来一定的开销。因此，在设计数据库时，需要权衡索引的数量、列和类型，以及对查询和修改操作的影响，选择适合的索引策略来平衡性能和存储的需求。

1.2 索引的分类

1. 存储形式

• 聚簇索引：主键索引
• 非聚簇索引：普通索引

2. 数据约束

• 主键索引
• 唯⼀索引
• ⾮唯⼀索引

3. 索引列的数量

• 单列索引
• 组合索引
  在MYSQL数据库表的多个字段组合上创建的索引 , 称为组合索引也叫联合索引
  • 组合索引的使用，需要遵循左前缀原则
  • 一般情况下，建议使用组合索引代替单列索引（主键索引除外）

4. innoDB可以创建的索引

• 主键索引：是一种特殊的唯一索引，不允许有空值
• 唯⼀索引：索引列中的值必须是唯一的，但是允许为空值
• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
  索引相关SQL语法 :
  1). 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

2). 查看索引

SHOW INDEX FROM table_name ;

3). 删除索引

DROP INDEX index_name ON table_name ;

1.3索引的底层数据结构

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只⽀支持B+ TREE索引， 也就是说默认使用BTREE，不能够更换MEMORY/HEAP存储引擎：支持HASH和BTREE索引
1、二叉查找树

2、btree

3、b+tree

4、聚簇索引

5、非聚簇索引


磁盘IO数：辅助索引3次+获取记录回表3次
覆盖索引和回表查询：
回表查询是指在使用非覆盖索引时进行查询时，当需要查询结果所需的数据列不在索引中时，mysql需要通过索引的指针回到主索引的数据列。回表查询会增加磁盘IO次数。
回表查询的优化可以从多个方面入手，如使用聚合索引、覆盖索引、分页机制、合理使用缓存和优化查询语句等方法，从而减少回表查询的次数，提高查询效率。
覆盖索引是指在查询过程中，索引包含了查询所需的所有数据列，无需回表查询索引或数据页。换句话说，覆盖索引能够直接提供查询所需的数据，而不需要再去访问主索引或数据页，从而提高查询性能和效率。
覆盖索引的好处主要体现在以下⼏个⽅⾯：

• 提⾼查询性能：由于覆盖索引能够直接提供查询所需的数据，减少了磁盘的随机访问和额外的回表查询操作，从⽽加快了查询的执⾏速度。
• 减少磁盘 I/O：回表查询需要进⾏额外的磁盘读取操作，⽽覆盖索引可以减少磁盘 I/O 操作，降低系统的磁盘负载。
• 减少内存消耗：覆盖索引可以减少需要加载到内存中的数据量，节省了内存的使⽤，提⾼了查询的效率

1.4 什么情况下索引会失效 ?

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，编写合理化的SQL能够提高
SQL的执行效率

1. 在列上使用函数和进行运算会导致索引失效
2. 使用 != 或 not in或 <> 等否定操作符会导致索引失效
3. 使用 > , < 等比较运算符号 , 比较运算符后面的条件索引会失效
4. 当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
5. like 语句的索引失效问题，like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like“%value%” 这样的方式，执行全表查询

1.5 什么样的字段需要建索引, 什么样的字段不需要 ?

需要创建索引情况

1. 主键自动建立主键索引
2. 频繁作为查询条件的字段应该创建索引
3. 多表关联查询中，关联字段应该创建索引 (on 两边都要创建索引)
4. 查询中排序的字段，应该创建索引
5. 频繁查找字段 , 应该创建索引
6. 查询中统计或者分组字段，应该创建索引
   不要创建索引情况
7. 表记录太少
8. 经常进⾏行行增删改操作的表
9. 频繁更新的字段
10. where条件里使用频率不高的字段
11. 区分度不高的字段

2 查询优化

方法

1. 优化查询语句：

• 使⽤恰当的SQL语句：根据查询需求选择合适的SQL语句，避免冗余或复杂的查询操作。
• 减少数据返回量：只选择需要的列，避免返回不必要的数据，减少⽹络传输和结果集处理开
  销。

2. 创建适当的索引：

• MySQL索引是⼀种⽤于加快数据检索速度和提⾼查询性能的数据结构。它类似于书籍的⽬
  录，通过按照某个或多个列的值进⾏排序和存储，使得数据库可以更快地定位和访问 特定的
  数据⾏。

3. 优化数据模型和表结构：

• 正规化数据模型：遵循数据库设计的规范，消除数据冗余，提⾼查询效率。
• 合理划分表和分区：将⼤表划分为更⼩的表或使⽤分区技术，提⾼查询效率和数据维护性
  能。

4. 监测和分析查询性能：

• 使⽤性能监控⼯具：监测数据库的性能指标，如查询响应时间、锁等待时间等，及时发现性
  能瓶颈。
• 分析执⾏计划：使⽤EXPLAIN语句分析查询的执⾏计划，查看索引使⽤情况和性能瓶颈，优
  化查询 语句和索引设计

5. 定期维护和优化：

• 定期收集统计信息：通过收集表的统计信息，优化查询优化器的决策，提⾼查询计划的准确
  性和性能。
• 定期重建索引：当索引碎⽚化严重时，定期重建索引，提⾼索引的效率。
  SQL查询优化是⼀个综合性的⼯作，需要综合考虑数据库结构、索引设计、查询语句、系统配置等多个⽅⾯。通过不断优化查询性能，可以提⾼数据库的响应速度和系统的整体性能。

3 Explain

下面我们用EXPLAIN 这个命令来对 SQL语句进行优化

通过查看EXPLAIN输出，我们可以判断查询是否使用了合适的索引、是否进行了全表扫描以及是否存在潜在的性能瓶颈。我们可以根据这些信息来优化查询，例如添加缺失的索引、重构查询语句等。

执行EXPLAIN查询后，将返回一个结果集，其中包含了查询的执行计划信息。以下是一些常见的列和其含义：

id：表示查询计划中每个操作的唯一标识符。
select_type：表示查询的类型。常见的类型包括SIMPLE（简单查询）、PRIMARY（主查询）和SUBQUERY（子查询）等。
table：表示要访问的表。
type：表示访问表的方式，通常有以下几种类型：ALL（全表扫描）、INDEX（使用索引扫描）、range（范围扫描）、ref（基于索引的等值查询），const（使用唯一索引或者主键）等。
possible_keys：表示可能应用于此查询的索引。
key：表示实际选择的索引。
rows：表示估计需要检查的行数。
Extra：提供额外的有关查询执行方法的信息，如Using where（表示过滤条件使用了WHERE子句）、Using index（表示覆盖索引）等。
Explain命令、

select_type

type：查询性能从上到下依次是最好到最差

extra