Mysql十大经典案例 索引

案例一索引

• 无索引案例

问题描述：用户系统打开缓慢，数据库 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都要经过严格的审核，创建合适的索引