mysql 索引暴力测试_mysql前缀索引测试

当要索引的列内容很多时 索引则会很大且变慢

可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率

举例

地区表

area

gdp

code

chinaShanghai

100

aaa

chinaDalian

200

bbb

usaNewYork

300

ccc

chinaFuxin

400

ddd

chinaBeijing

500

eee

发现 area 字段很多都是以 china 开头的

那么如果以前1-5位字符做前缀索引就会出现大量索引值重复的情况

索引值重复性越低 查询效率也就越高

测试

// 创建一个测试表

CREATE TABLE `x_test` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`x_name` varchar(255) NOT NULL,

`x_time` int(10) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4145025 DEFAULT CHARSET=utf8mb4

// 添加200万条测试数据 200万 慎重

INSERT INTO x_test(x_name,x_time) SELECT CONCAT(rand()*3300102,x_name),x_time FROM x_test WHERE id < 30000;

测试数据

四种情况跑一下速度

在无任何索引的情况下随便查询一条

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:2.253s

添加前缀索引 ( 以第一位字符创建前缀索引 )

alter table x_test add index(x_name(1))

再次查询相同sql语句

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:3.291s

当使用第一位字符创建前缀索引后 貌似查询的时间更长了

因为只第一位字符而言索引值的重读性太大了

200万条数据全以数字开头那么平均20万条的数据都是相同的索引值

重新建立前缀索引 这次以前4位字符来创建

alter table x_test add index(x_name(4));

再次查询相同sql语句

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:0.703s

这次以前4位创建索引 大大减少了索引值的重复性 查询速度从3秒提升到0.7秒

200万条数据都以数字开头 而0-9排列组合7位则可达到千万种组合,也就是以前7位来做索引则不会出现重复索引值的情况了

alter table x_test add index(x_name(7));

再次查询相同sql语句

SELECT * FROM x_test WHERE x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

查询时间:0.014s ( 首次执行无缓存状态下 )