1、获取执行计划返回信息可以使用如下命令
desc select * from t_100w where k2='pf';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t_100w | NULL | ALL | NULL | NULL | NULL | NULL | 2992074 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
or
>explain select * from t_100w where k2='pf';
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t_100w | NULL | ALL | NULL | NULL | NULL | NULL | 2992074 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
重点关注:
table ----查询的表
type ---查询的类型(全表扫描 or 使用索引扫描)
possiable_keys ---可能会用的索引名
key ---- 真正使用的索引名
extra --- 额外的信息
select_type : 查询类型
--- type 查询的类型:
ALL: 全表扫描,不走索引
1、没有索引时
2、查询条件出现以下语句(辅助索引列)
1、select * from city where countrycode <> 'CHN';
2、select × from city where countrycode not in ('USA','CHN');
3、select * from city where countrycode like '%CH%';
4、selct × from city where id <> 10;
注意: 对于聚集索引列,使用以上语句,依然会走索引
INDEX:
全索引扫描
1、查询需要获取这个索引列的值时
desc select countrycode from countrycode from city;
2、联合索引中,任何一个非最左列作为查询条件时
idx_a_b_c (a,b,c)
select a,b,c from table where a= and b= ;
RANGE:
索引范围扫描:
辅助索引:> < >= <= like in or
alter table t_100w add index idx_k1_k2(k1,k2);
@192.168.1.141:[db1] >desc select * from t_100w where k1 in ('Bu','rX');
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t_100w | NULL | range | idx__k1_k2 | idx__k1_k2 | 9 | NULL | 1661 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
主键: <> not in
ref
非唯一性索引,等值查询
alter table t_100w add index idx__k1_k2(k1,k2);
Query OK, 0 rows affected (21.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
@192.168.1.141:[db1] >commit
-> ;
Query OK, 0 rows affected (0.00 sec)
@192.168.1.141:[db1] >desc select * from t_100w where k1='r5';
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_100w | NULL | ref | idx__k1_k2 | idx__k1_k2 | 9 | const | 790 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
eq_ref:
在多表链接时,连接条件使用了唯一索引(uk pk)
select b.name, a.name from city as a
join country as b
on a.countrycode=b.code
where a.population < 1000;
desc country;
system, const:
唯一索引或主键的等值查询
desc select * from city where id=10;
NULL:
extra:
filesort, 文件排序 (需要对group by order by 后的列创建索引)
结论:
1、当我们看到执行计划extra 位置出现filesort ,说明有文件系统排序出现
2、观察需要排序(order by,group by ,distinct)的条件没有索引
3、根据字句的执行顺序,去创建联合索引
注意: having 及其后面的条件列都不在使用索引。把有having的SQL拆分;
alter table t1 add index idx_k1_k2(k1,k2);
始终要遵守索引的最左原则!!!!!
注意:
1、创建联合索引时把分散度大的列放在最前面。
2、在使用联合索引时,条件先使用等值条件在使用范围条件。
3、如果查询中出现多字句
我们要按照字句的顺序创建索引
where k2= order by k1;
alter table t_100w add index idx(k2,k1);
alter table t_100w add index idx_
索引应该规范
业务
1、产品功能
2、用户的行为
3、”热“ 查询语句----->较慢
4、建表时一定要有主键,一般是个无关列
5、选择唯一列创建索引
6、经常需要的where order by group by
7、列长度比较长时使用前缀索引
8、索引并不是越多越好
9、大表加索引最好在业务闲置时操作;
10、经常变动的列不要创建索引
11、查询结果超过全表的25%时,会走全表扫描(大量查询结果使用分页查询)。
12、索引本身失效,统计数据不真实;
索引有自我维护的能力
对于表内容变化比较频繁的情况下,有可能会出现索引失效。
一般是删除重建
现象:有一条select 语句平常查询时很快,突然有一天很慢,会是什原因?
DML: ----> 锁冲突
13、查询条件使用函数或计算时,不会走索引。
select * from city where id-1=9;
14 in 语句改成 union all 语句
15、 like '%ddd' 百分号% 在最前面不走索引
create table t1 (id int,telnum char(20));
alter table t1 add index idx_telnum(telnum);
select * from t1 where telnum='110'; 走索引
select * from t1 where telnum=110; 不走索引
面试题:
题目意思: 我们公司业务较慢,请从数据库的角度分析原因
1、mysql 出现性能问题,我总结有两种情况:
(1)应急情况: 数据库hang (卡了,资源耗尽)
1 show prcesslist or show full processlist;
show full processlist;
+----+------+---------------------+------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+------+---------+------+----------+-----------------------+
| 2 | root | 192.168.1.141:56430 | db1 | Query | 0 | starting | show full processlist |
+----+------+---------------------+------+---------+------+----------+-----------------------+
2 使用kill 杀死找到慢进程
(2)explain 分析sql 的执行计划,有没有走索引,索引的类型情况
(3)建立索引,改语句
show prcesslist;
使用kill 杀死找到慢进程
2、一段时间慢(持续性的慢)
(1) 记录慢日志slowlog 分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句