给你100万条数据的一张表,你将如何查询优化? | 您所在的位置:网站首页 › 条件分页查询怎么比较快 › 给你100万条数据的一张表,你将如何查询优化? |
1.两种查询引擎查询速度(myIsam 引擎 ) InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行。 MyISAM只要简单的读出保存好的行数即可。 注意的是,当count(*)语句包含 where条件时,两种表的操作有些不同,InnoDB类型的表用count(*)或者count(主键),加上where col 条件。其中col列是表的主键之外的其他具有唯一约束索引的列。这样查询时速度会很快。就是可以避免全表扫描。 总结: mysql 在300万条数据(myisam引擎)情况下使用 count(*) 进行数据总数查询包含条件(正确设置索引)运行时间正常。对于经常进行读取的数据我们建议使用myIsam引擎。
2.百万数据下mysql分页问题 在开发过程中我们经常会使用分页,核心技术是使用limit进行数据的读取,在使用limit进行分页的测试过程中,得到以下数据: select * from news order by id desc limit 0,10耗时0.003秒select * from news order by id desc limit 10000,10耗时0.058秒select * from news order by id desc limit 100000,10 耗时0.575秒select * from news order by id desc limit 1000000,10耗时7.28秒我们惊讶的发现mysql在数据量大的情况下分页起点越大查询速度越慢,100万条起的查询速度已经需要7秒钟。这是一个我们无法接受的数值! 改进方案 1 select * from news where id > (select id from news order by id desc limit 1000000, 1)order by id desc limit 0,10查询时间 0.365秒,提升效率是非常明显的!!原理是什么呢??? 我们使用条件对id进行了筛选,在子查询 (select id from news order by id desc limit 1000000, 1) 中我们只查询了id这一个字段比起select * 或 select 多个字段 节省了大量的查询开销!
改进方案2 适合id连续的系统,速度极快! select * from news where id between 1000000 and 1000010 order by id desc不适合带有条件的、id不连续的查询。速度非常快! 3. 百万数据下mysql条件查询、分页查询的注意事项 接上一节,我们加上查询条件: select id from news where cate = 1order by id desc limit 500000 ,10查询时间 20 秒 好恐怖的速度!!利用第一节知识进行优化: select * from newswhere cate = 1 and id > (select id from news where cate = 1 order by id desc limit 500000,1 ) order by id desc limit 0,10查询时间 15 秒 优化效果不明显,条件带来的影响还是很大!在这样的情况下无论我们怎么去优化sql语句就无法解决运行效率问题。那么换个思路:建立一个索引表,只记录文章的id、分类信息,我们将文章内容这个大字段分割出去。 表 news2 [ 文章表 引擎 myisam 字符集 utf-8 ] ------------------------------------------------- id int 11 主键自动增加 cate int 11 索引 在写入数据时将2张表同步,查询是则可以使用news2 来进行条件查询: select * from newswhere cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) order by id desc limit 0,10注意条件 id > 后面使用了news2 这张表! 运行时间 1.23秒,我们可以看到运行时间缩减了近20倍!!数据在10万左右是查询时间可以保持在0.5秒左右,是一个逐步接近我们能够容忍的值! 但是1秒对于服务器来说依然是一个不能接受的值!!还有什么可以优化的办法吗??我们尝试了一个伟大的变化: 将 news2 的存储引擎改变为innodb,执行结果是惊人的! select * from newswhere cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) order by id desc limit 0,10只需要 0.2秒,非常棒的速度。
4.mysql存储引擎 myIsam和innodb的区别 MySQL有多种存储引擎,MyISAM和InnoDB是其中常用的两种。这里介绍关于这两种引擎的一些基本概念(非深入介绍)。 MyISAM存储引擎,基于传统的ISAM类型,支持全文搜索,但不是事务安全的,而且不支持外键。每张MyISAM表存放在三个文件中:frm 文件存放表格定义;数据文件是MYD (MYData);索引文件是MYI (MYIndex)。 InnoDB是事务型引擎,支持回滚、崩溃恢复能力、多版本并发控制、ACID事务,支持行级锁定(InnoDB表的行锁不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,如like操作时的SQL语句),以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中,表空间可以包含数个文件。 核心区别 MyISAM是非事务安全型的,而InnoDB是事务安全型的。 MyISAM锁的粒度是表级,而InnoDB支持行级锁定。 MyISAM支持全文类型索引,而InnoDB不支持全文索引。 MyISAM相对简单,所以在效率上要优于InnoDB,小型应用可以考虑使用MyISAM。 MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。 InnoDB表比MyISAM表更安全,可以在保证数据不会丢失的情况下,切换非事务表到事务表(alter table tablename type=innodb)。 应用场景 MyISAM管理非事务表。它提供高速存储和检索,以及全文搜索能力。如果应用中需要执行大量的SELECT查询,那么MyISAM是更好的选择。 InnoDB用于事务处理应用程序,具有众多特性,包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作,则应该使用InnoDB,这样可以提高多用户并发操作的性能。 Mysql的存储引擎和索引 数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们非常容易想象出一个只有单关键字组成的表如何使用B+树进行索引,只要将关键字存储到树的节点即可。当数据库一条记录里包含多个字段时,一棵B+树就只能存储主键,如果检索的是非主键字段,则主键索引失去作用,又变成顺序查找了。这时应该在第二个要检索的列上建立第二套索引。 这个索引由独立的B+树来组织。有两种常见的方法可以解决多个B+树访问同一套表数据的问题,一种叫做聚簇索引(clustered index ),一种叫做非聚簇索引(secondary index)。这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式。对于聚簇索引存储来说,行数据和主键B+树存储在一起,辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说,主键B+树在叶子节点存储指向真正数据行的指针,而非主键。 InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。 MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。 为了更形象说明这两种索引的区别,我们假想一个表如下图存储了4行数据。其中Id作为主索引,Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。
我们重点关注聚簇索引,看上去聚簇索引的效率明显要低于非聚簇索引,因为每次使用辅助索引检索都要经过两次B+树查找,这不是多此一举吗?聚簇索引的优势在哪? 1 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。 2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用 |
CopyRight 2018-2019 实验室设备网 版权所有 |