mysql千万级数据量根据索引优化查询速度的实现
作者:李秀才 发布时间:2024-01-21 19:16:31
(一)索引的作用
索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询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筛选的时候,还是会全表扫描,因此此时
查询速度立马降了下来。
耗时:4.492s。
当我们的sql有多个列的筛选条件的时候,就需要对查询的多个列都加索引组成聚合索引:
加上聚合索引:ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
再来执行:
耗时:0.001s。
开篇也说过软件层面的优化一是合理加索引;二是优化执行慢的sql。此二者相辅相成,缺一不可,如果加了索引,还是查询很慢,这时候就要考虑是sql的问题了,优化sql。
Tips:
1:加了索引,依然全表扫描的可能情况有:
索引列为字符串,而没带引号;
索引列没出现在where条件后面;
索引列出现的位置没在前面。
2:关联查询不走索引的可能情况有:
关联的多张表的字符集不一样;
关联的字段的字符集不一样;
存储引擎不一样;
字段的长度不一样。
来源:https://liboyi.blog.csdn.net/article/details/52192551
猜你喜欢
- 以前写过一个刷校内网的人气的工具,Java的(以后再也不行Java程序了),里面用到了验证码识别,那段代码不是我自己写的:-) 校内的验证是
- 例如torch.nn.ReLU(inplace=True)inplace=True表示进行原地操作,对上一层传递下来的tensor直接进行修
- 简介看到了一个能够轻松实现获取系统运行的进程和系统利用率(包括CPU、内存、磁盘、网络等)信息的模块–psutil模块。这次利用psutil
- 长话短说:本人下载 matplotlib 花了大概三个半小时屡屡碰壁,险些暴走。为了不让新来的小伙伴走我的弯路,特意创作本片文章指明方向。1
- 当下,C#与Python都是比较热门的计算机编程语言,他们各有优缺点,如果能让他们互相配合工作,那是多么美好的事情,今天我来讲解一下如何利用
- 方法一.Image { max-width:600px;height:
- 我就废话不多说了,直接上代码吧!#coding=utf-8'''openCV中最核心的的类是Mat,他是matrix
- python创建一个类很简单只需要定义它就可以了.class Cat: pass就像这样就可以了,通过创建子类我们可以继承他的父
- python-redis-lock官方文档不错的博文可参考问题背景在使用celery执行我们的异步任务时,为了提高效率,celery可以开启
- 注意: 在搭建网络的时候用carpool2D的时候,让高度和宽度方向不同池化时,用如下:nn.MaxPool2d(kernel_size=2
- ipython简介ipython他是一个非常流行的python解释器,相比于原生的python解释器,有太多优点和长处,因此几乎是pytho
- 忙活了三个多小时,连学带做,总算是搞出来了一个具有基本功能的串口通信PC机的GUI界面,Tkinter在python中确实很好用,而且代码量
- 本文实例讲述了Python使用Pandas库常见操作。分享给大家供大家参考,具体如下:1、概述Pandas 是Python的核心数据分析支持
- 背景本文总结pytest的测试用例参数化。说明软件测试中,输入相应值,检查期望值,是常见测试方法。在自动化测试中,一个测试用例对应一个测试点
- 1 概述1.1 无监督学习 在一个典型的监督学习中,我们有一个有标签的训练集,我们的目标是找到能够区
- 前言在日常工作中,可能需要结合网上现在的一些API或者公司提供的数据接口来得到相应的数据或者实现对应的功能。因此API的调用和数据接口的访问
- 如下所示:a = [99,1,2,1,3,4]# 集合存储重复数据b=set()for i in a: if a.count(i
- 本次做一个最简单的贪食蛇雏形游戏,就是一个小蛇在画面上移动,我们可以控制蛇的移动方向,但是不能吃东西,蛇不会长大。但是基础的有了,完整版的贪
- 要使用request对象的ServerVariables属性,通过它来获得环境变量的值。使用的语法为:Request.ServerVaria
- 前言在想题材之际,打开私信,有许多萌新&小伙伴询问我之前写的一篇《python爬取天气预报数据,并实现数据可视化》中的bug怎么解决