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;(统计去完重复的值的数量)
通过两者的值的比较,验证district列是否有重复的值
前缀索引(只能针对字符串列)
mysql> alter table city add index idx_dis(district(5));#只取district的前5列
7.3 删除索引
查看索引
mysql>show index from city;
删除索引:
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. 压力测试准备:
上传一个表t100w.sql:
[root@web01 ~]# cd /tmp
[root@web01 tmp]# rz -E
上传到数据库:
mysql> use test
mysql> source /tmp/t100w.sql
查看:
mysql>select count(*) from t100w;
myslq>select * from t100w limit 10; #查看前10行
8.1 未做优化之前测试
模拟100个用户连接数据库,总共做了2000次查询
查看键值:
mysql> select * from t100w limit 10;
| id | num | k1 | k2 | dt |
+------+--------+------+------+---------------------+
| 1 | 56914 | Hd | MN89 | 2019-07-09 16:01:41 |
选择当中的其中一个键值来做压力测试:
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 -p123456 -verbose(要等一会)
mysql> show processlist; (可以查看是否当前正在查询)
查询完毕后显示查询所用的时间:
[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 -p123456 -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 索引优化后
索引优化后查询所需要的时间:
mysql>use test
mysql>alter table t100w add index idx_k2(k2);
mysql>desc t100w;创建辅助索引
[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 -p123456 -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
二. 执行计划分析
1.作用
将优化器 选择后的执行计划 截取出来.便于管理员判断语句得执行效率.
2.获取执行
desc SQL语句
explain SQL 语句
2个语句的执行效果是一样的
mysql> desc select * from test.t100w where k2='MN89';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 1027638 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
3.分析执行计划
1.table: 表名
2.type: 查询的类型:
1)全表扫描 : ALL
2)索引扫描 : index,range,ref,eq_ref,const(system),NULL(从左到右依次性能变好)
mysql>use world
mysql>desc city;
2.1)index: 全索引扫描
mysql> desc select countrycode from city;
2.2)range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select * from city where countrycode like 'CH%';
(> like性能比or and好,原因是B*树采用的是双向指针,所以对于连续的查询性能比较高,而or and是非连续查询)
对于辅助索引来讲,!= 和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';
2.3)ref: 辅助索引等值查询
desc
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
2.4)eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件(驱动表类型是ALL,子表类型是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;
2.5)const(system) : 主键或者唯一键的等值查询
mysql> desc select * from city where id=100;
3.possible_key:可能会用到的索引
desc
select * from city where countrycode!='CHN'
4.key: 真正选择了哪个索引
mysql> alter table city add index idx_c_p(countrycode,population);
mysql>desc select * from city where countrycode!='CHN' ;
5.key_len 索引覆盖长度
单列索引
varchar(20) utf8mb4
1)能存20个任意字符
2)不管存储的是字符,数字,中文,都1个字符最大预留长度是4个字节
3)于中文,1个占4个字节
4) 对于数字和字母,1个实际占用大小是1个字节
计算:
varchar(10) : 没有not null(1)+4*10+2=43
char(10) : 没有not null(1)+4*10=41
int : 没有not null(1)+4=5
key_len用途:判断联合索引的覆盖长度,一般情况是越长越好
联合索引: t1(id,k1,k2,k3) idx(k1,k2,k3)
1)在where 条件中都是等值的 where k1=xx k2=xx k3=xxx
无关where条件的顺序,只要把控建索引时,需要把唯一值较多的放在最左侧.
2)在条件查询中没有最左列条件时,没有K1列的查询,都是不走索引的
意外情况: 将表中所有列建立联合,每个列做查询条件都会走索引(了解)
3)如果查询中出现(> < >= <= like)
a= and b<xxx and c=xxx
建索引时,将等值查询条件往前放,不等值的放在最后
4)多子句查询时,应用联合索引 (按照子句的执行顺序建立联合索引)
mysql>desc select * from test where k1='aa' order by k2;
mysql>alter table test add index idx3(k1,k2);
6.Extra: Using filesort
出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
需要关注一下这些排序语句:
order by
group by
distinct
union
可以关注key_len应用的长度来查看联合索引是否合理
1.建立索引的原则
(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列 order by group by join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
(7) 小表不建索引
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) 联合索引