笔记大全

MySQL中怎么使用索引优化

更新时间：2023-11-26

索引优化：从查询角度优化

在开发中，使用索引优化是提高数据库查询效率最重要的手段之一。在实际应用中，我们需要从查询的角度考虑索引优化。

1. 索引的选择

    -- 创建表并添加索引
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `address` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_age` (`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

上述代码创建了一张user表，并给age字段添加了索引。查询时如果查询user表中age字段等于20的记录，则需要使用到索引，提高查询效率。

2. 索引覆盖查询

    -- 对有索引的字段进行覆盖查询，加快查询效率
    SELECT id FROM user WHERE age=20;

上述代码使用SELECT语句查询user表中age字段等于20的记录，但只返回了id字段，这种查询方式称为索引覆盖查询，可以减少IO，提高查询效率。

索引优化：从索引角度优化

除了从查询角度优化外，我们还需要从索引角度考虑索引优化。

1. 索引的类型

    -- 创建表并添加索引
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `address` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_name` (`name`),
      KEY `index_age` (`age`) USING HASH -- 指定索引类型为HASH
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

上述代码创建了一张user表，并给name字段添加了普通索引，给age字段添加了HASH索引。在实际应用中，我们需要根据具体的场景来选择合适的索引类型。

2. 索引的多列使用

    -- 创建表并添加索引
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `address` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_name_age` (`name`, `age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

上述代码创建了一张user表，并给name和age字段添加了联合索引。在使用多列索引时，需要确保查询条件和索引列的顺序完全一致，这样才能达到最佳的查询效果。

索引优化：从数据角度优化

除了从查询角度和索引角度优化外，我们还需要从数据角度考虑索引优化。

1. 数据类型

    -- 创建表并添加索引
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `age` TINYINT(4) UNSIGNED DEFAULT NULL, -- 使用无符号小整数类型，减少存储空间
      `address` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_name` (`name`),
      KEY `index_age` (`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

上述代码创建了一张user表，并给name和age字段添加了索引。在实际应用中，我们需要根据场景选择合适的数据类型，确保数据类型的正确性同时避免浪费存储空间。

2. 数据量

    -- 创建表并添加索引
    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `address` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `index_name` (`name`),
      KEY `index_age` (`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

上述代码创建了一张user表，并给name和age字段添加了索引。在实际应用中，数据量较大时，我们需要考虑将数据分到多张表中，以避免单张表过大导致索引失效的问题。

总结

在进行索引优化时，我们需要从查询角度、索引角度和数据角度进行优化。在使用索引时，需要根据具体的场景选择合适的索引类型，采用合适的数据类型，确保数据类型的正确性和存储空间的高效利用。在数据量较大时，需要考虑将数据分到多张表中，以避免单张表过大导致索引失效的问题。

总之，索引优化是提高数据库查询效率最重要的手段之一，需要在实际应用中灵活运用，以达到最佳的查询效果。

c语言编程笔录