网站编辑建设,用ssh做的网站,建设工程企业资质工作网站,怎么发帖子做推广给你100万条数据的一张表#xff0c;你将如何查询优化#xff1f;1.两种查询引擎查询速度#xff08;myIsam 引擎 #xff09;InnoDB 中不保存表的具体行数#xff0c;也就是说#xff0c;执行select count(*) from table时#xff0c;InnoDB要扫描一遍整个表来计算有多…给你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秒钟。这是一个我们无法接受的数值改进方案 1select * 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 news
where 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 news
where 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 news
where 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 typeinnodb。应用场景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 辅助索引使用主键作为指针 而不是使用地址值作为指针的好处是减少了当出现行移动或者数据页分裂时辅助索引的维护工作使用主键值当作指针会让辅助索引占用更多的空间换来的好处是InnoDB在移动行时无须更新辅助索引中的这个指针。也就是说行的位置实现中通过16K的Page来定位后面会涉及会随着数据库里数据的修改而发生变化前面的B树节点分裂以及Page的分裂使用聚簇索引就可以保证不管这个主键B树的节点如何变化辅助索引树都不受影响。所以在百万级数据及更大数据情况下mysql innoDB 的索引表现更加优秀 5、MySQL性能优化的一些经验a.为查询优化你的查询大多数的MySQL服务器都开启了查询缓存。这是提高性能最有效的方法之一而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候这些查询结果会被放到一个缓存中这样后续的相同的查询就不用操作表而直接访问缓存结果了。这里最主要的问题是对于程序员来说这个事情是很容易被忽略的。因为我们某些查询语句会让MySQL不使用缓存。请看下面的示例// 查询缓存不开启$r mysql_query(SELECT username FROM user WHERE signup_date CURDATE());// 开启查询缓存$today date(Y-m-d);$r mysql_query(SELECT username FROM user WHERE signup_date $today);上面两条SQL语句的差别就是 CURDATE() MySQL的查询缓存对这个函数不起作用。所以像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存因为这些函数的返回是会不定的易变的。所以你所需要的就是用一个变量来代替MySQL的函数从而开启缓存。 b.学会使用EXPLAIN使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。select id, title, cate from news where cate 1发现查询缓慢然后在cate字段上增加索引则会加快查询 c.当只要一行数据时使用LIMIT 1当你查询表的有些时候只需要一条数据请使用 limit 1。 d.正确的使用索引索引并不一定就是给主键或是唯一的字段。如果在你的表中有某个字段你总要会经常用来做搜索、拍下、条件那么请为其建立索引吧。 e.不要ORDER BY RAND()效率很低的一种随机查询。 f.避免SELECT *从数据库里读出越多的数据那么查询就会变得越慢。并且如果你的数据库服务器和WEB服务器是两台独立的服务器的话这还会增加网络传输的负载。必须应该养成一个需要什么就取什么的好的习惯。 g.使用 ENUM 而不是 VARCHARENUM 类型是非常快和紧凑的。在实际上其保存的是 TINYINT但其外表上显示为字符串。这样一来用这个字段来做一些选项列表变得相当的完美。如果你有一个字段比如“性别”“国家”“民族”“状态”或“部门”你知道这些字段的取值是有限而且固定的那么你应该使用 ENUM 而不是 VARCHAR。 h.使用 NOT NULL除非你有一个很特别的原因去使用 NULL 值你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议请往下看。首先问问你自己“Empty”和“NULL”有多大的区别如果是INT那就是0和NULL如果你觉得它们之间没有什么区别那么你就不要使用NULL。你知道吗在 Oracle 里NULL 和 Empty 的字符串是一样的)不要以为 NULL 不需要空间其需要额外的空间并且在你进行比较的时候你的程序会更复杂。 当然这里并不是说你就不能使用NULL了现实情况是很复杂的依然会有些情况下你需要使用NULL值。 下面摘自MySQL自己的文档“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”i.IP地址存成 UNSIGNED INT很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放只需要4个字节并且你可以有定长的字段。而且这会为你带来查询上的优势尤其是当你需要使用这样的WHERE条件IP between ip1 and ip2。我们必需要使用UNSIGNED INT因为 IP地址会使用整个32位的无符号整形 j.固定长度的表会更快如果表中的所有字段都是“固定长度”的整个表会被认为是 “static” 或 “fixed-length”。 例如表中没有如下类型的字段 VARCHARTEXTBLOB。只要你包括了其中一个这些字段那么这个表就不是“固定长度静态表”了这样MySQL 引擎会用另一种方法来处理。固定长度的表会提高性能因为MySQL搜寻得会更快一些因为这些固定的长度是很容易计算下一个数据的偏移量的所以读取的自然也会很快。而如果字段不是定长的那么每一次要找下一条的话需要程序找到主键。并且固定长度的表也更容易被缓存和重建。不过唯一的副作用是固定长度的字段会浪费一些空间因为定长的字段无论你用不用他都是要分配那么多的空间。 k.垂直分割“垂直分割”是一种把数据库中的表按列变成几张表的方法这样可以降低表的复杂度和字段的数目从而达到优化的目的。需要注意的是这些被分出去的字段所形成的表你不会经常性地去Join他们不然的话这样的性能会比不分割时还要差而且会是极数级的下降。 l.拆分大的 DELETE 或 INSERT 语句如果在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询你需要非常小心要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的表一锁住了别的操作都进不来了。Apache 会有很多的子进程或线程。所以其工作起来相当有效率而我们的服务器也不希望有太多的子进程线程和数据库链接这是极大的占服务器资源的事情尤其是内存。如果你把你的表锁上一段时间比如30秒钟那么对于一个有很高访问量的站点来说这30秒所积累的访问进程/线程数据库链接打开的文件数可能不仅仅会让你泊WEB服务Crash还可能会让你的整台服务器马上掛了。 m.越小的列会越快对于大多数的数据库引擎来说硬盘操作可能是最重大的瓶颈。所以把你的数据变得紧凑会对这种情况非常有帮助因为这减少了对硬盘的访问。 n.选择正确的存储引擎在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB每个引擎都有利有弊。MyISAM 适合于一些需要大量查询的应用但其对于有大量写操作并不是很好。甚至你只是需要update一个字段整个表都会被锁起来而别的进程就算是读进程都无法操作直到读操作完成。另外MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。InnoDB 的趋势会是一个非常复杂的存储引擎对于一些小的应用它会比 MyISAM 还慢。他是它支持“行锁” 于是在写操作比较多的时候会更优秀。并且他还支持更多的高级应用比如事务。转载文章地址https://www.cnblogs.com/llzhang123/p/9239682.html
