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) 联合索引