MySQL 前缀索引

MySQL 前缀索引

当要索引的列字符很多时 索引则会很大且变慢
( 可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率 )

原则： 降低重复的索引值

例如现在有一个地区表

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万条测试数据 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语句