十年JAVA搬砖路——MYSQL优化基础知识

忘是亡心i 2023-10-14 19:45 82阅读 0赞

### 一、MYSQL索引失效可能情况 ###

**1. 索引列未被查询使用**：如果查询没有使用索引列，MySQL将无法利用索引进行快速查找，导致索引失效。

**2. 索引列上使用了函数**：如果在索引列上使用了函数（如LOWER、UPPER等），MySQL无法使用索引进行快速查找，导致索引失效。

\*\*3. 索引列数据类型不匹配：\*\*如果查询条件中的数据类型与索引列的数据类型不匹配，MySQL可能无法使用索引进行查找，导致索引失效。

\*\*4. 数据量过小：\*\*对于非唯一索引，如果数据量太小，MySQL可能会选择全表扫描而不是使用索引，导致索引失效。

（1） 表的大小：如果表中的数据量非常小，比如只有几行或几十行，MySQL可能会选择全表扫描而不是使用索引。这是因为对于非常小的表来说，使用索引进行查找可能会导致额外的开销，而全表扫描可能更快。 
     
    （2） 索引的选择性：索引的选择性是指索引中不同值的数量与表中总行数的比率。如果索引的选择性非常低，即索引列上的值重复较多，MySQL可能会认为使用索引进行查找并不高效，而选择全表扫描。 
     
    （3） 查询优化器的判断：MySQL的查询优化器会根据统计信息和查询复杂度等因素来决定是使用索引还是全表扫描。如果查询优化器认为全表扫描更高效，即使数据量较小，也可能选择全表扫描。

**5. 索引列存在NULL值**：对于普通索引，如果索引列存在大量NULL值，MySQL可能放弃使用索引，而选择全表扫描。

\*\*6. 索引列顺序不匹配：\*\*对于联合索引，如果查询条件的列顺序与索引定义的列顺序不匹配，MySQL可能无法使用索引进行查找，导致索引失效。

### 使用explain 查看SQL使用索引情况 ###

**Type 列意思**  
\*\*1. system：\*\*这是最高级别的查询类型，表示只有一行数据（例如系统表）。

\*\*2. const：\*\*这是最好的查询类型，表示MySQL在查询过程中能够使用常量来匹配单个行。例如，使用主键或唯一索引进行等值查询时，MySQL可以使用const查询类型。

\*\*3. eq\_ref：\*\*这是非常好的查询类型，表示MySQL在查询过程中使用了连接索引来匹配单个行。例如，在连接查询中使用主键或唯一索引进行等值查询时，MySQL可以使用eq\_ref查询类型。

\*\*4. ref：\*\*这是较好的查询类型，表示MySQL在查询过程中使用了非唯一索引来查找匹配的行。例如，在查询中使用非唯一索引进行等值查询或IN查询时，MySQL可以使用ref查询类型。

\*\*5. range：\*\*这是一种较好的查询类型，表示MySQL在查询过程中使用了索引范围查找匹配的行。例如，在查询中使用非唯一索引进行范围查询时，MySQL可以使用range查询类型。

\*\*6. index：\*\*这是一种一般的查询类型，表示MySQL在查询过程中使用了索引来查找匹配的行。例如，在查询中使用非唯一索引进行LIKE查询时，MySQL可以使用index查询类型。

\*\*7. all：\*\*这是最差的查询类型，表示MySQL在查询过程中需要扫描整个表来查找匹配的行。例如，在查询中没有使用索引或使用了不适合的索引时，MySQL可能会使用all查询类型。

**Extra 列值及含义**

**1. Using index:** 表示查询正在使用索引来检索数据，通常是一个好的标志。这意味着查询可以通过索引结构快速访问所需的行。

**2. Using where:** 表示查询正在使用WHERE子句进行过滤条件。虽然这是必要的结果过滤，但仅有此标志并不能提供太多关于性能的信息。

**3. Using temporary:** 表示MySQL需要创建一个临时表来处理查询。这可能发生在存在复杂的GROUP BY、DISTINCT或ORDER BY子句时。尽管它可能会影响性能，但通常不是一个重大问题。

**4. Using filesort:** 表示查询需要执行额外的排序操作来满足ORDER BY子句。这可能发生在查询的ORDER BY子句无法通过索引满足时。文件排序可能消耗大量资源，尤其是对于大结果集而言，会影响性能。

**5. Using join buffer:** 表示MySQL正在使用连接缓冲区来执行表之间的连接操作。通常是高效的，但如果连接缓冲区大小太小，可能会导致基于磁盘的操作，从而影响性能。

**6. Using index condition**: 表示MySQL正在使用索引下推，它使用索引来评估WHERE子句的一部分。它可以通过减少需要检查的行数来提高性能。

**7. Impossible where:** 表示查询的WHERE子句包含相互矛盾的条件，使得无法满足查询。这可能导致结果集为空。

### 索引分类 ###

**1. 聚簇索引：**

*  聚集索引确定表中行的物理顺序。
 *  每个表只能创建一个聚集索引。
 *  MySQL中的InnoDB存储引擎默认在主键上使用聚集索引。
 *  当表具有聚集索引时，实际数据行存储在索引本身的叶节点中。
 *  聚集索引的叶节点包含整行数据，因此可以有效地按主键检索整行。
 *  聚集索引的顺序会影响磁盘上数据的物理顺序。

**2. 非聚集索引：**

*  非聚集索引是独立于实际数据的结构。
 *  每个表可以创建多个非聚集索引。
 *  非聚集索引包含索引列的副本以及指向实际数据的指针。
 *  非聚集索引的叶节点仅包含索引列和指向相应数据行的指针。
 *  非聚集索引对于特定列的有效搜索和排序非常有用，但可能需要额外的查找才能检索整行数据