MySQL中的前缀索引

骑猪看日落 2022-11-13 15:27 198阅读 0赞

** 前缀索引**：当给某一个列字段添加索引时，如果该列字段的字符串值很长时，那么我们创建的索引则会很大且很慢，这个时候如果可以只索引列开始的部分字符串，那么就可以节约索引空间，从而提高索引效率，此时这种类型的索引就叫“**前缀索引**”（**索引值重复性越低，查询效率就越高**）

假设我们创建了一张表名为test的表，其数据量为500万以上

-- 1.创建一个测试表
    CREATE TABLE test (
      id INT(11) NOT NULL AUTO_INCREMENT,
      num VARCHAR(255) NOT NULL,
      PRIMARY KEY (id)
    )
    
    -- 2.先插入10条数据
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+10);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+20);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+30);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+40);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+50);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+60);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+70);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+80);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+90);
    INSERT INTO test(num) VALUES (FLOOR(RAND()*100000000)+100);
    
    -- 3.假设该表的数据量需要达到500万条数据以上，则执行下述sql脚本19次即可，合计5242880条数据
    INSERT INTO test(num) SELECT num+(FLOOR(RAND()*1000000)) FROM test;

![20210327184831296.png][]

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

SELECT * FROM test WHERE num = '59465183';

查询时间：1.992s

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

ALTER TABLE test ADD INDEX(num(1));

再次查询相同的sql语句

SELECT * FROM test WHERE num = '59465183';

查询时间：51.954s

当使用第一位字符创建前缀索引后，貌似查询的时间更长了，因为只第一位字符而言索引值的重读性太大了

3.重新建立前缀索引，这次以前2位字符来创建

DROP INDEX num ON test;
    ALTER TABLE test ADD INDEX(num(2));