mysql千万级数据量根据索引优化查询速度

灰太狼 2022-09-25 04:20 216阅读 0赞

**（一）索引的作用**

索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描，数据量少还可以，一旦数据量超过百万甚至千万，一条查询sql执行往往需要几十秒甚至更多，5秒以上就已经让人难以忍受了。

能在软件上解决的，就不在硬件上解决，毕竟硬件提升代码昂贵，性价比太低。代价小且行之有效的解决方法就是合理的加索引。索引使用得当，能使查询速度提升上千倍，效果惊人。

**（二）mysql的索引类型：**

mysql的索引有5种：主键索引、普通索引、唯一索引、全文索引、聚合索引（多列索引）。

唯一索引和全文索引用的很少，我们主要关注主键索引、普通索引和聚合索引。

1）主键索引：主键索引是加在主键上的索引，设置主键（primary key）的时候，mysql会自动创建主键索引；

2）普通索引：创建在非主键列上的索引；

3）聚合索引：创建在多列上的索引。

**（三）索引的语法：**

查看某张表的索引：show index from 表名；

创建普通索引：alter table 表名 add index  索引名 (加索引的列)

创建聚合索引：alter table 表名 add index  索引名 (加索引的列1,加索引的列2)

删除某张表的索引：`drop index 索引名 on 表名;`

**（四）性能测试**

测试环境：博主工作用台式机

处理器为Intel Core i5-4460 3.2GHz;

内存8G;

64位windows。

1：创建一张测试表

DROP TABLE IF EXISTS `test_user`;
    CREATE TABLE `test_user` (
      `id` bigint(20)  PRIMARY key not null AUTO_INCREMENT,
      `username` varchar(11) DEFAULT NULL,
      `gender` varchar(2) DEFAULT NULL,
      `password` varchar(100) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

存储引擎使用MyISAM是因为此引擎没有事务，插入速度极快，方便我们快速插入千万条测试数据，等我们插完数据，再把存储类型修改为InnoDB。

2：使用存储过程插入1千万条数据

create procedure myproc() 
    begin 
    declare num int; 
    set num=1; 
    while num <= 10000000 do 
    insert into test_user(username,gender,password) values(num,'保密',PASSWORD(num)); 
    set num=num+1;
    end while;
     end

call myproc();

由于使用的MyISAM引擎，插入1千万条数据，仅耗时246秒，若是InnoDB引擎，就要花费数小时了。

然后将存储引擎修改回InnDB。使用如下命令：  alter table test\_user engine=InnoDB;此命令执行时间大约耗时5分钟，耐心等待。

tips：这里是测试，生产环境中不要随意修改存储引擎，还有alter table 操作，会锁整张表，慎用。其次：myisam引擎没有事务，且只是将数据写到内存中，然后定期将数据刷出到磁盘上，因此突然断电的情况下，会导致数据丢失。而InnDB引擎，是将数据写入日志中，然后定期刷出到磁盘上，所以不怕突然断电等情况。因此在实际生产中能用InnDB则用。

3：sql测试

select id,username,gender,password from test\_user where id=999999

耗时：0.114s。

因为我们建表的时候，将id设成了主键，所以执行此sql的时候，走了主键索引，查询速度才会如此之快。

我们再执行select id,username,gender,password from test\_user where username='9000000'  
耗时：4.613s。

我们给username列加上普通索引。

ALTER TABLE \`test\_user\` ADD INDEX index\_name(username) ;

此过程大约耗时 54.028s,建索引的过程会全表扫描，逐条建索引，当然慢了。

再来执行：selectid,username,gender,password from test\_user where username='9000000'  
耗时：0.043s。

再用username和password来联合查询

select id,username,gender,password  from test\_user where username='9000000' and \`password\`='\*3A70E147E88D99888804E4D472410EFD9CD890AE'

此时虽然我们队username加了索引，但是password列未加索引，索引执行password筛选的时候，还是会全表扫描，因此此时