AI 助理
备案控制台
文档
输入文档关键字查找
首页 云数据库 RDS RDS MySQL数据库 实践教程 最佳实践 为应用选择和创建最佳索引，加速数据读取

为应用选择和创建最佳索引，加速数据读取

更新时间：
产品详情
相关技术圈
我的收藏

本文介绍如何创建最佳索引，提高数据库的读取性能。

背景信息

在数据库使用过程中，由SQL问题导致的数据库故障层出不穷，其中索引问题是SQL问题中常见的一种，例如：无索引，隐式转换，索引创建不合理。

  • 无索引：使用没有创建索引的SQL访问数据库中的表时，系统会进行全表扫描。如果表的数据量很大，则SQL执行效率会非常慢，同时会占用数据库连接数，当达到数据库的最大连接数限制时，新的应用请求将会被拒绝导致出错。

  • 隐式转换：指SQL查询条件中传入的值与目标字段的数据类型不一致导致索引无法使用，引发慢SQL堆积导致数据库连接数超出限制。

视频教程

表和索引的优化实战视频，请参见MySQL表和索引优化实战。

注意事项

  • 使用like关键字时，前置%会导致索引失效。

  • 使用null值会被自动从索引中排除，索引一般不会建立在有空值的列上。

  • 使用or关键字时，如果or左右的字段有一个没有索引，则有索引字段也会失效。

  • 使用!=操作符时，将放弃使用索引。因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描。

  • 不要在索引字段进行运算。

  • 在使用复合索引时需遵循最左前缀原则，查询时必须使用索引的第一个字段，否则索引失效。字段顺序应尽量与索引顺序一致。

  • 避免隐式转换，定义的数据类型与传入的数据类型需保持一致。

索引使用策略

  • 在经常查询而不经常增删改操作的字段加索引。

  • order by与group by后应直接使用字段，而且字段应该是索引字段。

  • 一个表上的索引不应该超过6个。

  • 索引字段的长度应固定，且不宜过长。

  • 索引字段不宜有过多的重复。

  • 在过滤性高的字段上加索引。

无索引优化案例1

  1. 在数据库中执行show create table customers;查看表结构。

    说明

    customers表中有20条数据。

     CREATE TABLE `customers` (
   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
   `cust_name` char(50) NOT NULL,
   `cust_address` char(50) DEFAULT NULL,
   `cust_city` char(50) DEFAULT NULL,
   `cust_state` char(5) DEFAULT NULL,
   `cust_zip` char(10) DEFAULT NULL,
   `cust_country` char(50) DEFAULT NULL,
   `cust_contact` char(50) DEFAULT NULL,
   `cust_email` char(255) DEFAULT NULL,
   PRIMARY KEY (`cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8;

  2. 执行explain select * from customers where cust_zip = '44444' limit 0,1;查看目标SQL语句的执行计划。

                id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 10.00
        Extra: Using where
    说明

    从执行计划可以看到type为ALL，即全表扫描，每次执行需要扫描20行数据，数据库的性能消耗非常大。

  3. 执行alter table customers add index idx_cus(cust_zip);添加索引。

  4. 重新执行explain select * from customers where cust_zip = '44444' limit 0,1;查看执行计划。

               id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ref
possible_keys: idx_cus
          key: idx_cus
      key_len: 31
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
    说明

    此时type已变更为ref，即基于索引的等值查询或者表间等值连接，扫描行数为1行，大幅优化了查询速度。

无索引优化案例2

  1. 执行drop table if exists customers;删除之前创建的表customers。

  2. 在数据库中执行如下语句，创建测试表customers。

     CREATE TABLE `customers` (
   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
   `cust_name` char(50) NOT NULL,
   `cust_address` char(50) DEFAULT NULL,
   `cust_city` char(50) DEFAULT NULL,
   `cust_state` char(5) DEFAULT NULL,
   `cust_zip` char(10) DEFAULT NULL,
   `cust_country` char(50) DEFAULT NULL,
   `cust_contact` char(50) DEFAULT NULL,
   `cust_email` char(255) DEFAULT NULL,
   PRIMARY KEY (`cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8;

  3. 在customers表中插入20条数据。

    INSERT INTO customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES
('John Smith', '123 Main St', 'New York', 'NY', '10001', 'USA', 'John Doe', 'john@example.com'),
('Jane Doe', '456 Elm St', 'Los Angeles', 'CA', '90001', 'USA', 'Jane Smith', 'jane@example.com'),
('Bob Johnson', '789 Oak St', 'Chicago', 'IL', '60601', 'USA', 'Bob Smith', 'bob@example.com'),
('Alice Brown', '987 Maple Ave', 'Houston', 'TX', '77001', 'USA', 'Alice Smith', 'alice@example.com'),
('Michael Davis', '654 Pine St', 'San Francisco', 'CA', '94101', 'USA', 'Michael Smith', 'michael@example.com'),
('Sarah Wilson', '321 Cedar St', 'Seattle', 'WA', '98101', 'USA', 'Sarah Smith', 'sarah@example.com'),
('David Lee', '876 Birch St', 'Boston', 'MA', '02101', 'USA', 'David Smith', 'david@example.com'),
('Karen Taylor', '543 Willow St', 'Miami', 'FL', '33101', 'USA', 'Karen Smith', 'karen@example.com'),
('Steven Miller', '210 Oak St', 'Denver', 'CO', '80201', 'USA', 'Steven Smith', 'steven@example.com'),
('Lisa Anderson', '876 Elm St', 'Atlanta', 'GA', '30301', 'USA', 'Lisa Smith', 'lisa@example.com'),
('Matthew Wilson', '567 Pine St', 'Dallas', 'TX', '75201', 'USA', 'Matthew Smith', 'matthew@example.com'),
('Emily Johnson', '654 Cedar St', 'Phoenix', 'AZ', '85001', 'USA', 'Emily Smith', 'emily@example.com'),
('James Davis', '321 Birch St', 'San Diego', 'CA', '92101', 'USA', 'James Smith', 'james@example.com'),
('Olivia Brown', '987 Willow St', 'Portland', 'OR', '97201', 'USA', 'Olivia Smith', 'olivia@example.com'),
('Daniel Wilson', '543 Oak St', 'Las Vegas', 'NV', '89101', 'USA', 'Daniel Smith', 'daniel@example.com'),
('Emma Taylor', '210 Elm St', 'Philadelphia', 'PA', '19101', 'USA', 'Emma Smith', 'emma@example.com'),
('Christopher Miller', '876 Maple Ave', 'Austin', 'TX', '78701', 'USA', 'Christopher Smith', 'christopher@example.com'),
('Sophia Anderson', '567 Pine St', 'San Antonio', 'TX', '78201', 'USA', 'Sophia Smith', 'sophia@example.com'),
('Jacob Wilson', '654 Cedar St', 'Nashville', 'TN', '37201', 'USA', 'Jacob Smith', 'jacob@example.com'),
('Ava Johnson', '321 Birch St', 'Charlotte', 'NC', '28201', 'USA', 'Ava Smith', 'ava@example.com');

  4. 执行explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;查看目标SQL语句的执行计划。

               id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 10.00
        Extra: Using where; Using filesort

  5. 执行alter table customers add index idx_cu_zip_name(cust_zip,cust_name);添加索引。

  6. 重新执行explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;查看执行计划。

               id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ref
possible_keys: idx_cu_zip_name
          key: idx_cu_zip_name
      key_len: 31
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index

隐式转换优化案例1

  1. 执行drop table if exists customers;删除之前创建的表customers。

  2. 在数据库中执行如下语句，创建测试表customers。

     CREATE TABLE `customers` (
   `cust_id` int(11) NOT NULL AUTO_INCREMENT,
   `cust_name` char(50) NOT NULL,
   `cust_address` char(50) DEFAULT NULL,
   `cust_city` char(50) DEFAULT NULL,
   `cust_state` char(5) DEFAULT NULL,
   `cust_zip` char(10) DEFAULT NULL,
   `cust_country` char(50) DEFAULT NULL,
   `cust_contact` char(50) DEFAULT NULL,
   `cust_email` char(255) DEFAULT NULL,
   PRIMARY KEY (`cust_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8;

  3. 在customers表中插入20条数据。

    INSERT INTO customers (cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES
('John Smith', '123 Main St', 'New York', 'NY', '10001', 'USA', 'John Doe', 'john@example.com'),
('Jane Doe', '456 Elm St', 'Los Angeles', 'CA', '90001', 'USA', 'Jane Smith', 'jane@example.com'),
('Bob Johnson', '789 Oak St', 'Chicago', 'IL', '60601', 'USA', 'Bob Smith', 'bob@example.com'),
('Alice Brown', '987 Maple Ave', 'Houston', 'TX', '77001', 'USA', 'Alice Smith', 'alice@example.com'),
('Michael Davis', '654 Pine St', 'San Francisco', 'CA', '94101', 'USA', 'Michael Smith', 'michael@example.com'),
('Sarah Wilson', '321 Cedar St', 'Seattle', 'WA', '98101', 'USA', 'Sarah Smith', 'sarah@example.com'),
('David Lee', '876 Birch St', 'Boston', 'MA', '02101', 'USA', 'David Smith', 'david@example.com'),
('Karen Taylor', '543 Willow St', 'Miami', 'FL', '33101', 'USA', 'Karen Smith', 'karen@example.com'),
('Steven Miller', '210 Oak St', 'Denver', 'CO', '80201', 'USA', 'Steven Smith', 'steven@example.com'),
('Lisa Anderson', '876 Elm St', 'Atlanta', 'GA', '30301', 'USA', 'Lisa Smith', 'lisa@example.com'),
('Matthew Wilson', '567 Pine St', 'Dallas', 'TX', '75201', 'USA', 'Matthew Smith', 'matthew@example.com'),
('Emily Johnson', '654 Cedar St', 'Phoenix', 'AZ', '85001', 'USA', 'Emily Smith', 'emily@example.com'),
('James Davis', '321 Birch St', 'San Diego', 'CA', '92101', 'USA', 'James Smith', 'james@example.com'),
('Olivia Brown', '987 Willow St', 'Portland', 'OR', '97201', 'USA', 'Olivia Smith', 'olivia@example.com'),
('Daniel Wilson', '543 Oak St', 'Las Vegas', 'NV', '89101', 'USA', 'Daniel Smith', 'daniel@example.com'),
('Emma Taylor', '210 Elm St', 'Philadelphia', 'PA', '19101', 'USA', 'Emma Smith', 'emma@example.com'),
('Christopher Miller', '876 Maple Ave', 'Austin', 'TX', '78701', 'USA', 'Christopher Smith', 'christopher@example.com'),
('Sophia Anderson', '567 Pine St', 'San Antonio', 'TX', '78201', 'USA', 'Sophia Smith', 'sophia@example.com'),
('Jacob Wilson', '654 Cedar St', 'Nashville', 'TN', '37201', 'USA', 'Jacob Smith', 'jacob@example.com'),
('Ava Johnson', '321 Birch St', 'Charlotte', 'NC', '28201', 'USA', 'Ava Smith', 'ava@example.com');

  4. 执行explain select * from customers where cust_zip = 44444 limit 0,1;查看目标SQL语句的执行计划。

               id: 1
  select_type: SIMPLE
        table: customers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
     filtered: 10.00
        Extra: Using where

隐式转换优化案例2

  1. 在数据库中执行show create table customers1;和show create table customers2;查看表结构。

     CREATE TABLE `customers1` (
   `cust_id` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
   `cust_name` char(50) NOT NULL,
   KEY `idx_cu_id` (`cust_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    CREATE TABLE `customers2` (
   `cust_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
   `cust_name` char(50) NOT NULL,
   KEY `idx_cu_id` (`cust_id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  2. 执行explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';查看目标SQL语句的执行计划。

     *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers2
          type: ref
 possible_keys: idx_cu_id
           key: idx_cu_id
       key_len: 33
           ref: const
          rows: 1
         Extra: Using where; Using index
     *************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers1
          type: ALL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 1
         Extra: Using where; Using join buffer (Block Nested Loop)
    说明

    两个表中，cust_id字段的字符集未保持一致，无法使用索引。

  3. 执行alter table customers1 modify column cust_id varchar(10) COLLATE utf8_bin;将customers1中cust_id字段的字符集修改为utf8_bin，保证和customers2中的cust_id字段一致。

    说明

    执行该语句会同步修改cust_id字段的CHARACTER SET为utf8。

  4. 重新执行explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';查看执行计划。

     *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers2
          type: ref
 possible_keys: idx_cu_id
           key: idx_cu_id
       key_len: 33
           ref: const
          rows: 1
         Extra: Using where; Using index
     *************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: customers1
          type: ref
 possible_keys: idx_cu_id
           key: idx_cu_id
       key_len: 33
           ref: const
          rows: 1
         Extra: Using where
    说明

    表字段的COLLATE一致后执行计划成功使用了索引。

MySQL表和索引优化实战

上一篇：设置RDS MySQL循环执行事件下一篇：倚天版（ARM架构）性能&价格力观测
文档推荐