mysql索引对查询效率的影响

最新推荐文章于 2024-10-24 21:19:50 发布

原创最新推荐文章于 2024-10-24 21:19:50 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

Java基础专栏收录该内容

71 篇文章

订阅专栏

本文探讨了在一张学生表中插入2000万条数据后，无索引与建立索引（如生日索引）对查询性能的影响。通过实测，展示了未索引情况下查询效率低下，而添加索引后显著提升查询速度，特别是对于模糊匹配和排序操作。

文章目录

数据准备

创建一张表

create table student
(
  s_id    varchar(64)            not null
    primary key,
  s_name  varchar(20) default '' not null,
  s_birth varchar(20) default '' not null,
  s_sex   varchar(10) default '' not null
) DEFAULT CHARSET utf8;

创建随机生成姓名的函数

-- 创建随机生成姓名的函数
create function createName()
  RETURNS varchar(3)

BEGIN
  DECLARE LN VARCHAR(1000);
  DECLARE MN VARCHAR(4000);
  DECLARE FN VARCHAR(4000);
  DECLARE LN_N INT;
  DECLARE MN_N INT;
  DECLARE FN_N INT;
  SET LN = '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹段';
  SET MN = '听见雨滴落在青青草地听见远方下课钟声响起可是没有听见声音认真呼唤姓名爱上时候还不懂感情离别了才觉得刻骨铭心为什么没有发现遇见了是生命最好事情也许当时忙着微笑和哭泣忙着追逐天空中流星人理所当然忘记是谁风里雨里一直默默守护在原地原来是最想留住幸运原来和爱情曾经靠得那么近那为对抗世界决定那陪淋雨一幕幕都是一尘不染真心与相遇好幸运可已失去为泪流满面权利但愿在看不到天际张开了双翼遇见注定她会有多幸运青春是段跌跌撞撞旅行拥有着后知后觉美丽来不及感谢是给勇气让能做回自己也许当时忙着微笑和哭泣忙着追逐天空中流星人理所当然忘记是谁风里雨里一直默默守护在原地原来是最想留住幸运原来和爱情曾经靠得那么近那为对抗世界决定那陪淋雨一幕幕都是一尘不染真心与相遇好幸运可已失去为泪流满面权利但愿在看不到天际张开了双翼遇见注定她会有多幸运';
  SET FN = '如果骄傲没被现实大海冷冷拍下又怎会懂得要多努力才走得到远方如果梦想不曾坠落悬崖千钧一发又怎会晓得执着人拥有隐形翅膀把眼泪种在心上会开出勇敢花可以在疲惫时光闭上眼睛闻到一种芬芳就像好好睡了一夜直到天亮又能边走着边哼着歌用轻快步伐沮丧时总会明显感到孤独重量多渴望懂得人给些温暖借个肩膀高兴一路上我们默契那么长穿过风又绕了弯心还连着像往常一样最初梦想紧握在手上最想要去地方怎么能在半路就返航最初梦想绝对会到达实现了真渴望才能够算到过了天堂如果骄傲没被现实大海冷冷拍下又怎会懂得要多努力才走得到远方如果梦想不曾坠落悬崖千钧一发又怎会晓得执着人拥有隐形翅膀把眼泪种在心上会开出勇敢花可以在疲惫时光闭上眼睛闻到一种芬芳就像好好睡了一夜直到天亮又能边走着边哼着歌用轻快步伐沮丧时总会明显感到孤独重量多渴望懂得人给些温暖借个肩膀高兴一路上我们默契那么长穿过风又绕了弯心还连着像往常一样最初梦想紧握在手上最想要去地方怎么能在半路就返航最初梦想绝对会到达实现了真渴望才能够算到过了天堂最初梦想绝对会到达实现了真渴望才能够算到过了天堂';
  SET LN_N = CHAR_LENGTH(LN);
  SET MN_N = CHAR_LENGTH(MN);
  SET FN_N = CHAR_LENGTH(FN);
  RETURN CONCAT(substring(LN,CEIL(RAND()*LN_N),1),substring(MN,CEIL(RAND()*LN_N),1),substring(FN,CEIL(RAND()*FN_N),1));

END;

创建新增n条数据的存储过程

create procedure insertStudent(in sn int)
begin
  declare i int;
  set i = 1;
  while i <= sn DO
  insert into
    student
  (s_id, s_name, s_birth, s_sex)
  values(
          CONCAT(replace(replace(replace(convert(now(),char),'-',''),' ',''),':',''),LPAD(i,8,'0')),
          createName(),
           concat('199',convert(ceil(rand()*9),char(1)),'-',
           LPAD(convert(ceil(rand()*12),char(2)),2,'0'),
           '-',
           LPAD(convert(ceil(rand()*28),char(2)),2,'0')),
          if(rand()*2>1,'男','女')
);
set i=i+1;
end while;
end;

表里插入两千万条数据

call insertStudent(20000000);

没索引

统计数据个数

test> SELECT count(*) FROM student
[2021-12-16 14:29:15] 1 row retrieved starting from 1 in 13 s 860 ms (execution: 13 s 829 ms, fetching: 31 ms)

主键查询

test> SELECT * FROM student WHERE s_id = '2021110516302300031092'
[2021-12-16 14:40:06] 1 row retrieved starting from 1 in 36 ms (execution: 5 ms, fetching: 31 ms)

没建索引查模糊匹配存在的生日

test> SELECT * FROM student WHERE s_birth like '1990%'
[2021-12-16 14:41:03] 0 rows retrieved in 15 s 836 ms (execution: 15 s 811 ms, fetching: 25 ms)

没建索引查模糊匹配不存在的生日

test> SELECT * FROM student WHERE s_birth like '2000%'
[2021-12-16 14:56:43] 0 rows retrieved in 14 s 663 ms (execution: 14 s 640 ms, fetching: 23 ms)

没建索引按生日排序

test> SELECT * FROM student ORDER BY s_birth
[2021-12-16 15:09:38] 500 rows retrieved starting from 1 in 21 s 663 ms (execution: 21 s 627 ms, fetching: 36 ms)

有索引

建另外一张一模一样的表

create table student_02 as SELECT * from student;

建生日索引

create index student_02_s_birth on student_02(s_birth);

建索引查模糊匹配存在的生日

test> SELECT * FROM student_02 WHERE s_birth like '1998%'
[2021-12-16 15:07:41] 500 rows retrieved starting from 1 in 52 ms (execution: 17 ms, fetching: 35 ms)

建索引查模糊匹配不存在的生日

test> SELECT * FROM student_02 WHERE s_birth like '2000%'
[2021-12-16 15:08:26] 0 rows retrieved in 35 ms (execution: 6 ms, fetching: 29 ms)

建索引按生日排序

test> SELECT * FROM student_02 ORDER BY s_birth
[2021-12-16 15:10:55] 500 rows retrieved starting from 1 in 143 ms (execution: 65 ms, fetching: 78 ms)