Open empo007 opened 6 years ago
刚才给表test增加了name列上的索引,但是从输出来看有几个问题:
2017-11-06 14:19:04 70885 [Note] 2017-11-06 14:19:04 70885 [Note] 第2步:开始解析where中的条件:(id
= 1000)
2017-11-06 14:19:04 70885 [Note] show index from test
2017-11-06 14:19:04 70885 [Note] show table status like 'test'
2017-11-06 14:19:04 70885 [Note] select count(*) from ( select id
from test
FORCE INDEX( idx_1 ) order by name DESC limit 10000) test
where (id
= 1000)
2017-11-06 14:19:04 70885 [Note] 第3步:表test的行数:95821,limit行数:10000,得到where条件中(id
= 1000)的选择度:10000
===>选择度应该是1,而不是10000
**2017-11-06 14:19:04 70885 [Note] 第4步:开始验证 字段id是不是主键。表名:test
2017-11-06 14:19:04 70885 [Note] show index from test where Key_name = 'PRIMARY' and Column_name ='id' and Seq_in_index = 1
2017-11-06 14:19:04 70885 [Note] 第5步:字段id不是主键。表名:test
2017-11-06 14:19:04 70885 [Note] 第6步:开始验证 字段id是不是主键。表名:test
2017-11-06 14:19:04 70885 [Note] show index from test where Key_name = 'PRIMARY' and Column_name ='id' and Seq_in_index = 1
2017-11-06 14:19:04 70885 [Note] 第7步:字段id不是主键。表名:test**
===>为什么验证是否是主键做了2次?
2017-11-06 14:19:04 70885 [Note] 第8步:开始验证表中是否已存在相关索引。表名:test, 字段名:id, 在索引中的位置:1
2017-11-06 14:19:04 70885 [Note] show index from test where Column_name ='id' and Seq_in_index =1
2017-11-06 14:19:04 70885 [Note] 第9步:开始输出表test索引优化建议:
2017-11-06 14:19:04 70885 [Note] Create_Index_SQL:alter table test add index idx_id(id)
2017-11-06 14:19:04 70885 [Note] 第10步: SQLAdvisor结束!
mysql> show create table test\G 1. row Table: test Create Table: CREATE TABLE
test
(id
bigint(20) DEFAULT NULL,name
char(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)用下面的存储过程插入100000条数据
create procedure test() begin declare i bigint; set i=0; while i<100000 do insert into test values(i,cast(i as char(10))); set i=i+1; end while; end;//
mysql> SHOW TABLE STATUS\G 1. row Name: test Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 99937 Avg_row_length: 47 Data_length: 4734976 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2017-11-06 09:52:16 Update_time: 2017-11-06 11:53:32 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
[root@szxts10011040 sqladvisor]# cat sql.cnf [sqladvisor] username=graTemp password= host= port=50006 dbname=lych sqls=select * from lych.test where id=1000;
[root@szxts10011040 sqladvisor]# ./sqladvisor -f sql.cnf -v 1 2017-11-06 13:51:35 66459 [Note] 2017-11-06 13:51:35 66459 [Note] 第2步:开始解析where中的条件:(
id
= 1000)2017-11-06 13:51:35 66459 [Note] show index from test
2017-11-06 13:51:35 66459 [Note] 第3步:SQLAdvisor结束!表中没有任何索引