Mysql十大经典案例索引

╰半夏微凉° 2023-10-10 03:56 10阅读 0赞

# 案例一索引 #

*  ## 无索引案例 ##

**问题描述：**用户系统打开缓慢，数据库 CPU 100%

**问题排查：**发现数据库中大量的慢sql，执行时间超过了2S

**慢SQL：**

SELECT id FROM `user` WHERE user_no=13772556391 LIMIT 0,1;

**执行计划：**

*************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: user
    type: ALL
    possible_keys: NULL
    key: NULL
    key_len: NULL
    ref: NULL
    rows: 707250
    Extra: Using where

**执行时间：**

mysql> SELECT id FROM `user` WHERE user_no=13772556391 LIMIT 0,1; 
    Empty set (2.11 sec)

**表结构：**

CREATE TABLE `user` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
        `pid` int(11) unsigned NOT NULL DEFAULT '0' ,
        `email` char(60) NOT NULL ,
        `name` char(32) NOT NULL DEFAULT '',
        `user_no` char(11) NOT NULL DEFAULT '' ,
        PRIMARY KEY (`id`),
        UNIQUE KEY `email` (`email`),
        KEY `pid` (`pid`)
    ) ENGINE=InnoDB ENGINE=InnoDB AUTO_INCREMENT=972600 DEFAULT CHARSET=utf8;

**验证mo字段的过滤性：**

mysql> select count(*) from user where user_no=13772556391;
    +----------+
    | count(*) |
    +----------+
    | 0 |
    +----------+
    1 row in set (0.05 sec)

**添加索引：**

mysql> alter table user add index ind_user_no (user_no);

**执行时间：**

mysql>SELECT id FROM `user` WHERE user_no=13772556391 LIMIT 0,1;
    Empty set (0.05 sec)

**执行计划：**

mysql> explain SELECT id FROM `user` WHERE user_no=13772556391 LIMIT 
    0,1\G;
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: user
    type: index
    possible_keys: ind_user_no
    key: ind_user_no
    key_len: 33
    ref: NULL
    rows: 707250
    Extra: Using where; Using index

## 隐式转换案例 ##

**为什么索引的过滤性这么差？**

mysql> explain extended select id from`user` where user_no =13772556391 limit 0,1;
    mysql> show warnings;
    Warning1：Cannot use index 'ind_user_no ' due to type or collation conversion on field 'user_no' 
    Note：select `user`.`id` AS `id` from `user` where (`user`.`user_no` = 13772556391) limit 0,1

**表结构：**

CREATE TABLE `user` (
    ……
    `user_no` char(11) NOT NULL DEFAULT '' ,
    ……
    ) ENGINE=InnoDB;

mysql> explain SELECT id FROM `user` WHERE user_no='13772556391' LIMIT 0,1\G;
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: user
    type: ref
    possible_keys: ind_user_no
    key: ind_user_no
    key_len: 33
    ref: const
    rows: 1
    Extra: Using where; Using index
    mysql> SELECT id FROM `user` WHERE user_no='13772556391' LIMIT 0,1;
    Empty set (0.00 sec)

## 索引最佳实践 ##

**1、通过explain查看sql的执行计划 **  
    判断是否使用到了索引以及隐士转换  
**2、常见的隐式转换**  
    包括字段数据类型类型以及字符集定义不当导致  
**3、设计开发阶段**  
    避免数据库字段定义与应用程序参数定义出现不一致  
    不支持函数索引,避免在查询条件加入函数:date(a.gmt\_create)  
**4、SQL审核**  
    所有上线的sql都要经过严格的审核，创建合适的索引