文档章节

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

吴伟祥
 吴伟祥
发布于 2018/04/24 17:14
字数 1518
阅读 20
收藏 1

在工作之中,由于SQL问题导致的数据库故障层出不穷,索引问题是SQL问题中出现频率最高的,常见的索引问题包括:无索引,隐式转换,索引创建不合理。

当数据库中出现访问表的SQL没创建索引导致全表扫描,如果表的数据量很大扫描大量的数据,执行效率过慢,占用数据库连接,连接数堆积很快达到数据库的最大连接数设置,新的应用请求将会被拒绝导致故障发生。

隐式转换是指SQL查询条件中的传入值与对应字段的数据定义不一致导致索引无法使用。常见隐式转换如字段的表结构定义为字符类型,但SQL传入值为数字;或者是字段定义collation为区分大小写,在多表关联的场景下,其表的关联字段大小写敏感定义各不相同。隐式转换会导致索引无法使用,进而出现上述慢SQL堆积数据库连接数跑满的情况。

索引使用策略及优化

创建索引

  • 在经常查询而不经常增删改操作的字段加索引。
  • order by与group by后应直接使用字段,而且字段应该是索引字段。
  • 一个表上的索引不应该超过6个。
  • 索引字段的长度固定,且长度较短。
  • 索引字段重复不能过多。
  • 在过滤性高的字段上加索引。

使用索引注意事项

  • 使用like关键字时,前置%会导致索引失效。
  • 使用null值会被自动从索引中排除,索引一般不会建立在有空值的列上。
  • 使用or关键字时,or左右字段如果存在一个没有索引,有索引字段也会失效。
  • 使用!=操作符时,将放弃使用索引。因为范围不确定,使用索引效率不高,会被引擎自动改为全表扫描。
  • 不要在索引字段进行运算。
  • 在使用复合索引时,最左前缀原则,查询时必须使用索引的第一个字段,否则索引失效;并且应尽量让字段顺序与索引顺序一致。
  • 避免隐式转换,定义的数据类型与传入的数据类型保持一致。

无索引案例

无索引案例一

  1. 查看表结构。

     
    1. mysql> show create table customers;
     
    1. CREATE TABLE `customers` (
    2. `cust_id` int(11) NOT NULL AUTO_INCREMENT,
    3. `cust_name` char(50) NOT NULL,
    4. `cust_address` char(50) DEFAULT NULL,
    5. `cust_city` char(50) DEFAULT NULL,
    6. `cust_state` char(5) DEFAULT NULL,
    7. `cust_zip` char(10) DEFAULT NULL,
    8. `cust_country` char(50) DEFAULT NULL,
    9. `cust_contact` char(50) DEFAULT NULL,
    10. `cust_email` char(255) DEFAULT NULL,
    11. PRIMARY KEY (`cust_id`),
    12. ) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8
  2. 执行语句。

     
    1. mysql> select * from customers where cust_zip = '44444' limit 0,1 \G;
  3. 执行计划。

     
    1. mysql> explain select * from customers where cust_zip = '44444' limit 0,1 \G;
     
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ALL
    5. possible_keys: NULL
    6. key: NULL
    7. key_len: NULL
    8. ref: NULL
    9. rows: 505560
    10. Extra: Using where

    执行计划看到type为ALL,是全表扫描,每次执行需要扫描505560行数据,这是非常消耗性能的,那么下面将介绍优化方式。

  4. 添加索引。

     
    1. mysql> alter table customers add index idx_cus(cust_zip);
  5. 执行计划。

     
    1. mysql> explain select * from customers where cust_zip = '44444' limit 0,1 \G;
     
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ref
    5. possible_keys: idx_cus
    6. key: idx_cus
    7. key_len: 31
    8. ref: const
    9. rows: 4555
    10. Extra: Using index condition

    执行计划看到type为ref,基于索引的等值查询,或者表间等值连接。

无索引案例二

  1. 表结构同上案例相同,执行语句。

     
    1. mysql> select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name;
  2. 执行计划。

     
    1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;
     
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ALL
    5. possible_keys: NULL
    6. key: NULL
    7. key_len: NULL
    8. ref: NULL
    9. rows: 505560
    10. Extra: Using filesort
  3. 添加索引。

     
    1. mysql> alter table customers add index idx_cu_zip_name(cust_zip,cust_name);
  1. 执行计划。

     
    1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;
     
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers
    4. type: ref
    5. possible_keys: idx_cu_zip_name
    6. key: idx_cu_zip_name
    7. key_len: 31
    8. ref: const
    9. rows: 4555
    10. Extra: Using where; Using index

    order by使用字段,而且字段应该是索引字段。

隐式转换案例

隐式转换案例一

 
  1. mysql> explain select * from customers where cust_zip = 44444 limit 0,1 \G;
 
  1. id: 1
  2. select_type: SIMPLE
  3. table: customers
  4. type: ALL
  5. possible_keys: idx_cus
  6. key: NULL
  7. key_len: NULL
  8. ref: NULL
  9. rows: 505560
  10. Extra: Using where
 
  1. mysql> show warnings;
  2. Warning: Cannot use range access on index 'idx_cus' due to type or collation conversion on field 'cust_zip'

上述案例中由于表结构定义cust_zip字段是字符串数据类型,而应用传入的是数字,导致了隐式转换,无法使用索引。

解决方案:

  1. 将cust_zip字段修改为数字数据类型。
  2. 将应用中传入的字符类型改为数据类型。

隐式转换案例二

  1. 查看表结构。

     
    1. mysql> show create table customers1;
     
    1. CREATE TABLE `customers1` (
    2. `cust_id` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    3. `cust_name` char(50) NOT NULL,
    4. KEY `idx_cu_id` (`cust_id`)
    5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    6. mysql> show create table customers2;
    7. CREATE TABLE `customers2` (
    8. `cust_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    9. `cust_name` char(50) NOT NULL,
    10. KEY `idx_cu_id` (`cust_id`)
    11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  2. 执行语句。

     
    1. mysql> select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x';
  3. 执行计划。

     
    1. mysql> explain select customers1.* from customers2 left join customers1 on customers1.cust_id=customers2.cust_id where customers2.cust_id='x'\G;
     
    1. *************************** 1. row ***************************
    2. id: 1
    3. select_type: SIMPLE
    4. table: customers2
    5. type: ref
    6. possible_keys: idx_cu_id
    7. key: idx_cu_id
    8. key_len: 33
    9. ref: const
    10. rows: 1
    11. Extra: Using where; Using index
     
    1. *************************** 2. row ***************************
    2. id: 1
    3. select_type: SIMPLE
    4. table: customers1
    5. type: ALL
    6. possible_keys: NULL
    7. key: NULL
    8. key_len: NULL
    9. ref: NULL
    10. rows: 1
    11. Extra: Using where; Using join buffer (Block Nested Loop)
  4. 修改COLLATE。

     
    1. mysql> alter table customers1 modify column cust_id varchar(10) COLLATE utf8_bin ;
  5. 执行计划。

     
    1. mysql> explain select cust_id,cust_name,cust_zip from customers where cust_zip = '42222'order by cust_zip,cust_name\G;
     
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers2
    4. type: ref
    5. possible_keys: idx_cu_id
    6. key: idx_cu_id
    7. key_len: 33
    8. ref: const
    9. rows: 1
    10. Extra: Using where; Using index
     
    1. id: 1
    2. select_type: SIMPLE
    3. table: customers1
    4. type: ref
    5. possible_keys: idx_cu_id
    6. key: idx_cu_id
    7. key_len: 33
    8. ref: const
    9. rows: 1
    10. Extra: Using where

    字段的COLLATE一致后执行计划使用到了索引,所以一定要注意表字段的collate属性的定义保持一致。

总结

在使用索引时,我们可以通过explain查看SQL的执行计划,判断是否使用了索引以及发生了隐式转换,创建合适的索引。索引太复杂,创建需谨慎。

本文转载自:

吴伟祥

吴伟祥

粉丝 31
博文 485
码字总数 284154
作品 0
泉州
后端工程师
私信 提问
MySQL----索引基础

原文链接:http://blog.csdn.net/liaodehong/article/details/52190223 什么是索引? 1、索引 索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,...

J星星点灯
2018/01/30
0
0
最全面的 MySQL 索引详解

什么是索引? 1、索引 索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。 2、索引,是数据库中专门用于帮助用户快速查询...

javaer
2016/10/09
37
0
MYSQL索引最佳实践

这是一篇译文,原文请点击:英文原文地址 你做了一个明智的选择 理解索引对开发和dba来说都是极其重要 差劲的索引对产品问题负相当大的一部分责任 索引不是多么高深的问题 MySQL 索引一览表 ...

楠木楠
2016/11/24
48
0
OpenSearch 最佳实践

OpenSearch 最佳实践 OpenSearch : 是一款结构化数据搜索托管服务,为移动应用开发者和网站站长提供简单、高效、稳定、低成本(?)和可扩展的搜索解决方案。 OpenSearch的产品架构在官方文档...

蓝鲸人
2017/12/25
0
0
150倍加速机械盘,UCloud云主机IO加速技术揭秘

现如今CPU的计算能力和磁盘的访问延迟之间的差距逐渐扩大,使得用户云主机的磁盘IO经常成为严重的性能瓶颈,云计算环境下更加明显。针对机械盘IO性能低下的问题,我们通过自研的云主机IO加速...

UCloudTech
2018/12/03
13
0

没有更多内容

加载失败,请刷新页面

加载更多

Go 关闭 channel 的 close 方法

在 Go 中我们所以 close() 来关闭一个 channel 官方的注释如下 The close built-in function closes a channel, which must be either bidirectional or send-only. It should be executed o......

mickelfeng
31分钟前
3
0
语音转文字什么方法比较简单

在很多时候一些比较重要的对话需要录制下来,在录制完成后还需要整理出文字,可是长时间的录音内容想要整理出文字是非常的麻烦的。需要花费大量的时间将录制的声音转换成文字,那么想要简单快...

401恶户
35分钟前
5
0
IIS7配置thinkphp5项目到public目录下

有个项目,tp5写的,要配置到项目的public目录下,一开始报错了...后面删除了配置,重新配置成功了,记录一下过程 1.首先,将网站根目录变为你的public目录下 2.添加解析程序的CGI,这里选择你需要解...

老bia同学
39分钟前
10
0
Redis主从复制的配置和实现原理

Redis的持久化功能在一定程度上保证了数据的安全性,即便是服务器宕机的情况下,也可以保证数据的丢失非常少。通常,为了避免服务的单点故障,会把数据复制到多个副本放在不同的服务器上,且...

TurboSanil
41分钟前
8
0
counsul 集群

1 master节点 cat << EOF > /lib/systemd/system/consul.service[Unit]Description=consul-masterAfter=network-online.target [Service]ExecStart=/bin/sh -c 'consul agent ......

拜了个拜
41分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部