mysql100万条数据的⼀张表，如何查询优化？
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 = 1
order 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秒，⾮常棒的速度。
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 辅助索引使⽤主键作为"指针" ⽽不是使⽤地址值作为指针的好处是，减少了当出现⾏移动或者数据页分裂时辅助索引的维护⼯作，使⽤主键值当作指针会让辅助索引占⽤更多的空间，换来的好处是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 ⽽不是 VARCHAR
ENUM 类型是⾮常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显⽰为字符串。这样⼀来，⽤这个字段来做⼀些选项列表变得相当的完美。
如果你有⼀个字段，⽐如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限⽽且固定的，那么，你应该使⽤ ENUM ⽽不是 VARCHAR。
h.使⽤ NOT NULL
除⾮你有⼀个很特别的原因去使⽤ NULL 值，你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议，请往下看。
⾸先，问问你⾃⼰“Empty”和“NULL”有多⼤的区别（如果是INT，那就是0和NULL）？如果你觉得它们之间没有什么区别，那么你就不要使⽤NULL。（你知道吗？在 Oracle ⾥，NULL 和 Empty 的字符串是⼀样的！)
不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进⾏⽐较的时候，你的程序会更复杂。 当然，这⾥并不是说你就不能使⽤NULL了，现实情况是很复杂的，依然会有些情况下，你需要使⽤NULL值。
下⾯摘⾃MySQL⾃⼰的⽂档sql容易学吗
“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”。 例如，表中没有如下类型的字段：VARCHAR，TEXT，BLOB。只要你包括了其中⼀个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会⽤另⼀种⽅法来处理。
固定长度的表会提⾼性能，因为MySQL搜寻得会更快⼀些，因为这些固定的长度是很容易计算下⼀个数据的偏移量的，所以读取的⾃然也会很快。⽽如果字段不是定长的，那么，每⼀次要下⼀条的话，需要程序到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯⼀的副作⽤是，固定长度的字段会浪费⼀些空间，因为定长的字段⽆论你⽤不⽤，他都是要分配那么多的空间。
k.垂直分割
“垂直分割”是⼀种把数据库中的表按列变成⼏张表的⽅法，这样可以降低表的复杂度和字段的数⽬，从⽽达到优化的⽬的。需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会⽐不分割时还要差，⽽且，会是极数级的下降。
l.拆分⼤的 DELETE 或 INSERT 语句

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。