@[toc]

一 索引

1）索引之无索引案例

问题描述

用户系统打开缓慢，数据库CPU 100%

问题排查

发现数据库中大量的慢SQL，执行时间长超过了 2 s

慢SQL

select id from 'user' where user_no=13772556391 limit 0,1;

执行计划

mysql>explain select id from 'user' where user_no=13772556391 limit 0,1;

mysql>explain SELECTid FROM `user`WHERE user_no=13772556391 LIMITO,1; id:1 select_type:SIMPLE table:user type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:707250 Extra:Using where

关键信息

type：ALL
key:null

说明是全表扫描

执行时间

mysql> select id from 'user' where user_no=13772556391 limit 0,1

mysql>~SELECT id FROM `user' WHERE user_no=13772556391 LIMIT O,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 KEYemail`(email`) 
 KEY`pid`(pid`) 
)ENGINE=InnoDB ENGINE=InnoDB AUTO_INCREMENT=972600 DEFAULT CHARSET=utf8;

查看表结构
所查找的user 列是没有ID
验证字段的过滤性

mysql> select count(*) from user where user_no=13772556391;

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

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;

mysql> explain SELECT id FROM `user`WHERE user_no=13772556391 LIMIT 0,1\G; 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 

2）索引之隐式转换案例：

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

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

表结构

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

由于查询条件user_no=13772556391是没有加引号，是整型，而表结构是字符型，所以涉及到类型转换

改进查询条件

添加引号，可以看到rows:1

索引问题的最佳实践

1. 通过explain查看sql的执行计划

判断是否使用到了索引以及隐式转换

1. 常见的隐式转换

包括字段数据类型以及字符集定义不当导致

1. 设计开发阶段

避免数据库字段定义与应用程序参数定义出现不一致
不支持函数索引，避免在查询条件加入函数：date(a.gmt_create)

1. SQL审核

所有上线的SQL都要经过严格的审核，创建合适的索引