mysql基础应用-索引和分析执行计划

7. 索引的命令操作

7.1  查询索引 
desc city;
PRI   ==> 主键索引 
MUL   ==> 辅助索引
UNI   ==> 唯一索引 

mysql> show index from city\G

7.2 创建索引

单列的辅助索引:
mysql> alter table city add index idx_name(name);

多列的联合索引:
mysql> alter table city add index idx_c_p(countrycode,population);

唯一索引: 
mysql> alter table city add unique index uidx_dis(district);

mysql> select count(district) from city;
mysql> select count(distinct district) from city;

前缀索引
mysql> alter table city add index idx_dis(district(5));

7.3 删除索引 
mysql> alter table city drop index idx_name;
mysql> alter table city drop index idx_c_p;
mysql> alter table city drop index idx_dis;

8. 压力测试准备:
mysql> use test
mysql> source /tmp/t100w.sql

8.1 未做优化之前测试
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='test' \
--query="select * from test.t100w where k2='MN89'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='test' \
> --query="select * from test.t100w where k2='MN89'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 755.861 seconds
    Minimum number of seconds to run all queries: 755.861 seconds
    Maximum number of seconds to run all queries: 755.861 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

8.2 索引优化后
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 1.678 seconds
    Minimum number of seconds to run all queries: 1.678 seconds
    Maximum number of seconds to run all queries: 1.678 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

9.3 分析执行计划
9.3.1 table                
表名
9.3.2 type                
查询的类型:
全表扫描         : ALL 
索引扫描        : index,range,ref,eq_ref,const(system),NULL

index: 全索引扫描
mysql> desc select countrycode  from city;

range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select  * from city where countrycode like 'CH%';

对于辅助索引来讲,!= 和not in等语句是不走索引的
对于主键索引列来讲,!= 和not in等语句是走range

===
mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';
mysql> desc select  * from city where countrycode in ('CHN','USA');

一般改写为 union all 
desc 
select  * from city where countrycode='CHN' 
union all 
select  * from city where countrycode='USA';

ref: 辅助索引等值查询
desc 
select  * from city where countrycode='CHN' 
union all 
select  * from city where countrycode='USA';

eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件
A join B 
on a.x = B.y 

desc select b.name,a.name ,a.population  
from city as a 
join country as b 
on a.countrycode=b.code 
where a.population<100;

const(system) : 主键或者唯一键的等值查询
mysql> desc select * from  city where id=100;

possible_key
key
key_len
Extra

5.2 不走索引的情况（开发规范）
(1) 没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
(2) 查询结果集是原表中的大部分数据，应该是25％以上。
(3) 索引本身失效，统计数据不真实
面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
mysql> desc select * from city where id-99=1;
(5) 隐式转换导致索引失效.
(6) <> ，not in 不走索引（辅助索引）
(7) like "%aa" 百分号在最前面不走
(8) 联合索引