"MySQL优化器-誉天hcie-r&s面试宝典v3.0(原版) 面试必备"
MySQL优化器是数据库管理系统中关键的部分,它负责选择最佳的执行计划以提高查询效率。当查询涉及到MIN或MAX操作且索引列可用时,优化器能够直接利用索引信息,无需检索实际数据行,如在EXPLAIN的Extra信息中看到"Select tables optimized away",这意味着查询优化器已经避免了对物理记录的扫描。
在MySQL优化方面,有以下几个重要的注意事项和建议:
1. 索引列不应包含表达式或函数,因为MySQL不支持这样的索引。例如,`WHERE column = some_function(value)`将无法利用索引。
2. 对于InnoDB存储引擎,非主键索引存储的是主键的值,而非记录的完整地址。因此,整型主键(如自增ID)通常带来更好的性能。通过主键访问数据是最高效的,使用二级索引可能需要两次索引查找。
3. 数据导入时最好按照主键顺序进行,这样能保持数据的物理顺序,减少碎片。若数据导入无序,可能需要运行`OPTIMIZE TABLE`命令来优化表结构。
4. 索引应具有高选择性,即索引列的基数(不同值的个数)相对较高,这将减少索引的重复值,提高查询效率。但基数低的列,如性别这种只有少数几个值的列,创建索引可能意义不大。然而,即使基数小,如果数据分布极度不均匀,某些值的记录数非常少,也可能适合创建索引来加速查找。
5. 使用前缀索引可以节省空间,特别是对于大字段,如VARCHAR类型。通过测试不同前缀长度的独特值比例,可以确定最合适的前缀长度,以确保大部分值是唯一的。例如,可以使用以下SQL来衡量不同前缀长度的唯一值比例:
```sql
SELECT COUNT(DISTINCT LEFT(col_name, 3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(col_name, 4))/COUNT(*) AS sel4,
COUNT(DISTINCT LEFT(col_name, 5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(col_name, 6))/COUNT(*) AS sel6,
COUNT(DISTINCT LEFT(col_name, 7))/COUNT(*) AS sel7
FROM table_name;
```
6. 创建过多的索引会占用更多存储空间,并可能降低数据更新速度。避免创建冗余索引,因为MySQL允许在相同列上创建多个索引。某些MySQL分支有工具可检测未使用的索引,而在官方版本中,可能需要手动检查并清理。
7. 对于确保唯一性的列,创建唯一索引不仅有助于防止数据重复,也有助于优化查询。唯一索引可以提高查询效率,因为它们减少了冲突的可能性。
以上内容摘自《MySQL DBA修炼之道》,作者陈晓勇,书中详细介绍了MySQL的各个方面,包括入门、开发和测试等,旨在帮助读者深入理解MySQL并提升其在数据库管理方面的技能。