开发者学堂课程【云数据库优化十大典型案例:案例1：索引优化】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/597/detail/8566
案例1：索引优化
内容简介:
一.无索引案例
验证mo字段的过滤性
二.隐式转换案例
为什么索引的过滤性这么差?
三. 索引最佳实践
一.无索引案例
问题描述:用户系统打开缓慢，数据库CPU100%
问题排查:发现数据库中大量的慢sql，执行时间超过了2S
慢SQL（重点关注）:
SELECT uid FROM `user`WHERE mo=13772556391 LIMIT 0,1;
执行计划
mysql> explain SELECT uid FROM `user`WHERE mo=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>SELSCT uid FROM`user`WHERE mo=13772556391 LIMIT 0,1;
Empty set(2.11sec)
如果系统频繁调用 SQL ，那么 SQL 可能出现堆积。慢 SQL 执行时间变慢了，导致出现了堆积，那么如何解决?
表结构
CREATE TABLE`user`(
`uid`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 ",
`mo`char(11) NOT NULL DEFAULT",
PRIMARY KEY(`uid`),
UNTQUE KEY `(`email`),
KEY`pid`(`pid`)
)ENGINE=InnoDB ENGINE=InnoDB AUTO_INCREMENT=972600 DEFAULT
CHARSET=utf8;
看表结构，有主键 uid ，还有唯一索引 email ,还有一个根据pid的这样一个索引。
这个表上有三个索引，但是都没有 mo 自带索引，索引我们要不要考虑在这个 mo 上加一个索引。
那么加索引，我们要考虑索引这个过滤性，假如这个过滤性非常差的字段，那么即使我们加上索引，它也不会使用。所有我们在加索引之前一定要验证这个字段的过滤性。
验证 mo 字段的过滤性
mysql> select count(*) from user where mo=13772556391;
+--------------+
|count(*)|
+--------------+
| 0 |
+--------------+
1 row in set (0.05 sec)
添加索引
mysql> alter table user add index ind_mo(mo);
执行时间
mysql>SELECT uid FROM `user`WHERE mo=13772556391 LIMIT 0,1;
Fmpty set(0.05 se
二.隐式转换案例
为什么索引的过滤性这么差?
mysql> explain extended select uid from`user` where mo=13772556391limit 0,1;
mysql> show warnings;
Warning1:Cannot use `index 'ind_mo` due to type or collation conversion on field 'mo' Note:select`user`.`uid` AS `uid` from`user`where(`use`.`mo`=13772556391)limit 0,1
表结构
CREATE TABLE`user`(
......
`mo` char(11) NOT NULL DEFAULT ",
......
)ENGINE=InnoDB;
mysql> explain SELECT uid FROM `user`WHERE mo='13772556391'LIMIT 0,1\G;
*************************** 1.row*************************
id: 1
select_type: SIMPLE
table:user
type: ref
possible_keys: ind_mo
key: ind_mo
key_len: 33
ref: const
rows: 1
Extra: Using where; Using index
mysql> SELECT uid FROM `user`WHERE mo='13772556391'LIMIT 0,1;
Empty set(0.00 sec)
三.索引最佳实践
1.通过 explain 查看 sql 的执行计划
判断是否使用到了索引以及隐士转换
2.常见的隐式转换
包括字段数据类型类型以及字符集定义不当导致
3.设计开发阶段
避免数据库字段定义与应用程序参数定义出现不一致
不支持函数索引,避免在查询条件加入函数:date(a.gmt_create)
4.SQL审核
所有上线的 sql 都要经过严格的审核，创建合适的索引
