MySQL普通索引性能试验

首先使用如下node.js脚本创建两张表，并为这两张表各自生成10000条数据：

var fs = require('fs');

var nameS = "赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康";

var sql, content = "";
// drop table
sql = "drop table if exists my_test_table1;";
content += sql + "\r\n";
sql = "drop table if exists my_test_table2;";
content += sql + "\r\n";
// create table
sql = "create table my_test_table1 ( id integer, name varchar(10), age integer, address varchar(100) );";
content += sql + "\r\n";
sql = "create table my_test_table2 ( id integer, name varchar(10), age integer, address varchar(100) );";
content += sql + "\r\n";
// insert
for (var i = 1; i <= 10000; i ++) {
    var id = i;
    var name = nameS[i%nameS.length] + nameS[(i+10)%nameS.length] + nameS[(i+20)%nameS.length] + nameS[(i+30)%nameS.length];
    var age = parseInt(Math.random() * 100);
    var address = nameS[parseInt(Math.random()*nameS.length)] + nameS[parseInt(Math.random()*nameS.length)] + nameS[parseInt(Math.random()*nameS.length)] + 
            nameS[parseInt(Math.random()*nameS.length)] + nameS[parseInt(Math.random()*nameS.length)] + parseInt(Math.random()*1e5) + "号";
    for (var j = 1; j <= 2; j ++) {
        sql = `insert into my_test_table${j} (id,name,age,address) values (${id},'${name}',${age},'${address}');`;
        content += sql + "\r\n";
    }
}
fs.writeFileSync('./init.sql', content);

我们测试用的几个select语句如下：
sql1:

select * from my_test_table1 a left join my_test_table2 b on a.id=b.id

sql2:

select * from my_test_table1 a left join my_test_table2 b on a.id=b.id left join my_test_table1 c on a.id=b.id and b.id=c.id

sql3:

select * from my_test_table1 a left join my_test_table2 b on a.id=b.id left join my_test_table1 c on a.id=b.id and b.id=c.id left join my_test_table2 d on a.id=d.id and b.id=d.id and c.id=d.id

插入数据后，我们来进行一般的查询。

查询耗时：

• sql1: 8.488s,8.581s,8.626s
• sql2: 26.48s,26.30s,26.651s
• sql3: 52.645s,53.510s,53.57s

为两张表的id字段添加索引，然后再进行一次查询：

create index my_test_table1_idx_1 on my_test_table1 (id);
create index my_test_table2_idx_1 on my_test_table2 (id);

添加索引后的查询耗时：

• sql1: 51ms,50ms,40ms
• sql2: 80ms,100ms,140ms
• sql3: 203ms,166ms,100ms

可以看到，速度达到了飞速的提升！