mysql主键查询性能_MySQL查询性能优化（精）
MySQL查询性能优化
MySQL查询性能的优化涉及多个⽅⾯，其中包括库表结构、建⽴合理的索引、设计合理的查询。库表结构包括如何设计表之间的关联、表字段的数据类型等。这需要依据具体的场景进⾏设计。如下我们从数据库的索引和查询语句的设计两个⾓度介绍如何提⾼MySQL查询性能。
数据库索引
索引是存储引擎中⽤于快速到记录的⼀种数据结构。索引有多种分类⽅式，按照存储⽅式可以分为：聚簇索引和⾮聚簇索引；按照数据的唯⼀性可以分为：唯⼀索引和⾮唯⼀索引；按照列个数可以分为：单列索引和多列索引等。索引也有多种类型：B-Tree索引、Hash索引、空间数据索引(R-Tree)、全⽂索引等。
B-Tree索引mysql语句多表查询
在利⽤B-Tree索引进⾏查询的过程中，有⼏点注意事项，我们以表A进⾏说明。其中表A的定义如下：
create table A(id int auto_increment primary key, name varchar(10), age tinyint, sex enum('男','⼥'), birth datatime,
key(name,age,sex)); id为主键，并在name,age,sex列上建⽴了索引。
全值匹配：指和索引中的所有列进⾏匹配，例如查name='Jone' and age=13 and sex='男'的⼈；
匹配最左前缀：指⽤索引的第⼀列name，如where name='Jone'，该查询只使⽤了索引的第⼀列
匹配列前缀：匹配索引列值的开头，如where name like 'J%'，查名字以J开头的⼈；
匹配范围值：例如查年龄在10-30之间的Jone，where name='Jone' and age between 10 and 30;
只访问索引的查询：如果在select中选择的字段都是索引中的字段，那么就不需要访问数据⾏，从⽽提⾼查询速度。
如果不是按照索引的最左列进⾏查，则⽆法使⽤索引，如当仅查表A中年龄为15岁的⼈时则⽆法使⽤索引；
不能跳过索引中的列，如查表A中名字为Jone且为男性的⼈，则索引只能使⽤name列，⽆法使⽤sex列；
查询中索引的某列是范围查询，则该列后的查询条件将不能使⽤索引。
Hash索引与B-Tree的区别：
Hash索引指包含哈希值(根据key中的列计算)和⾏指针，⽽B-Tree存储的是列值。所以Hash不能使⽤索引来避免读取数据⾏；
Hash索引数据不是按照索引值顺序存储的，所以⽆法⽤于排序；
Hash索引不⽀持部分索引列匹配查，因为Hash值是根据索引中的全部列计算出来的；
Hash索引只⽀持等值⽐较查询，包括=、in()、<=>。不⽀持范围查询。
索引的优点
索引不仅仅可以让服务器快速定位到表的指定位置，⽽且还有以下优点：
B-Tree索引按照列的顺序存储数据，所以可以⽤来做Order by和group by操作，避免排序和临时表ins特效安卓教程
B-Tree索引中存储索引列的值，所以当select的值在索引中时，可以避免访问数据⾏
索引可以有效减少服务器扫描的数据量。
⾼性能的索引策略
正确地创建和使⽤索引是实现⾼性能查询的基础。前⾯已经介绍了各种类型的索引以及对应的优缺点。⾼效地选择和使⽤索引有很多种⽅式，其中有些是针对特殊案例的优化，有些则是针对特定⾏为的优化。
独⽴的列：指索引不能是表达式的⼀部分，也不能是函数的参数。如：select * from A where id+1=5; 则⽆法使⽤主键索引。
前缀索引和索引选择性：有时需要索引很长的字符串，索引会占⽤很⼤的空间，通常可以索引开始的部分字符来节约索引空间，提⾼索引效率，但也会降低索引的选择性。索引的选择性=不重复索引值/数据表的记录总数。索引的选择性越⾼查询效率越⾼。
多列索引：⾸先需要说明在多列上创建索引不等同于给这些列的每⼀列单独建⽴索引。当执⾏查询的时候，MySQL只能使⽤⼀个索引。如果你有三个单列的索引，MySQL会试图选择⼀个限制最严格的索引。即使是限制最严格的单列索引，它的限制能⼒也肯定远远低于这三个列上的多列索引。⽐如我们想查询表A中id为3或者名字⾸字母为A的⼈，sql语句的两种写法对⽐，其中第⼆种写法⽐第⼀种减少对表的扫描次数：
嵌入式开发培训 杭州多列索引中索引列的顺序也⼗分重要，在设计索引的顺序时也需要考虑如何更好地满⾜排序和分组的需要(B-Tree)。在⼀个多列的B-Tree索引中，索引列的顺序意味着索引⾸先按照最左列进⾏排序，其次是第⼆列等等。确定索引列的顺序有⼀个经验法则：将选择性最⾼的列放到索引最前列。当然如果需要考虑对表的排序的情况就需要另当考虑了。
聚簇索引：不是⼀种单独的索引类型，⽽是⼀种数据存储⽅式，具体的细节依赖于其实现⽅式，InnoDB的聚簇索引实际上在同⼀个结构中保存了B-Tree索引和数据⾏，⼀个表只能有⼀个聚簇索引。聚簇索引的优(1-3)缺(4-7)点如下：
可以把相关数据保存在⼀起。例如实现电⼦邮箱时，可以根据⽤户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能够获取某个⽤户的全部邮件。如果没有聚簇索引，则每封邮件都可能导致⼀次磁盘I/O；
数据访问更快。聚簇索引将索引和数据保存在同⼀个B-Tree中，因此聚簇索引中获取数据通常⽐在⾮聚簇索引中查要快；
使⽤覆盖索引扫描的查询可以直接使⽤页节点中的主键值；element ui是谁开发的
B-Tree索引插⼊速度严重依赖于插⼊顺序。按照聚簇索引列中值的顺序插⼊数据到InnoDB表中速度最快的；
更新聚簇索引列的值代价很⾼，因为会强制InnoDB将每个被更新列所在的⾏移动到新的位置；
插⼊新的⾏可能⾯临“页分裂”的问题。页分裂问题是聚簇索引要求必须将这⼀⾏插⼊到某个已满的页中时，存储引擎会将该页分裂成两个页⾯来容纳该⾏，也就是⼀次页分裂操作，导致表占⽤更多的磁盘空间；
聚簇索引可能导致全表扫描变慢，尤其是⾏⽐较稀疏，或者由于页分裂导致数据存储不连续的时候。
如上是盗取的⼀个向InnoDB表中插⼊数据的时间和索引⼤⼩的图，其中userinfo表和userinfo_uuid表唯⼀的区别是userinfo表以id为主键，⽽userinfo_uuid表以uuid为主键，⽽插⼊100万和300万数据的顺序是按照id列的顺序插⼊的，由上图可知，当插⼊300万数据⾏时，userinfo_uuid表由于不是按照主键(uuid)的顺序插⼊的数据，会导致⼤量的页分裂，从⽽插⼊需要更多的时间、索引占⽤更⼤的空间。
覆盖索引：⼤家都会根据where的条件建⽴合适的索引，这只是索引优化的⼀个⽅⾯。优秀的索引还应该考虑整个查询。MySQL可以使⽤索引直接获取列的数据，这样就不需要读取数据⾏了。如果索引包含(覆盖)所有需要查询的字段值，我们就称之为覆盖索引。当查询是⼀个索引覆盖查询时，Extra列可以看到Using index的信息。
当然覆盖查询还是有很多陷阱可能导致⽆法实现优化的。MySQL查询优化器会在执⾏查询前判断是否有⼀个索引能够进⾏覆盖，覆盖where条件中的字段和select的字段。如果不能覆盖，则还是需要扫描数据⾏。
因为InnoDB表中⾮聚簇索引中存储主键值，所以我们先根据条件获取主键值，然后再根据主键值进⾏查询，这种⽅式叫做延迟关联。
使⽤索引扫描来做排序。如果EXPLAIN出来的type列值为index，说明MySQL使⽤了索引扫描来做排序。扫描索引本⾝是很快的，但是如果索引不能覆盖查询所需的全部列，那就不得不每扫描⼀条索引记录就回表查询⼀次对应的⾏。这基本都是随机I/O，因此按索引顺序读取的速度通常要⽐顺序地全表扫描慢，尤其是I/O密集型的⼯作负载时。因此MySQL设计索引时应尽可能的满⾜排序和查。只有索引列顺序和order by⼦句的顺序完全⼀致，并且所有列的排序⽅向都⼀致时，MySQL才能使⽤索引来对结果做排序。如果查询关联多张表，则只有order by⼦句引⽤的字段全部为第⼀个表时，才能使⽤索引排序。
如上是分别使⽤主键id排序和name排序的查询，可以看出使⽤id排序的查询使⽤了索引排序，⽽name排序的查询使⽤的是filesort。
总结
总的来说编写查询语句时，应尽可能选择合适的索引以避免单⾏查，尽可能的使⽤原⽣顺序从⽽避免额外的排序操作，并尽可能使⽤索引覆盖查询。我们通过响应时间来对查询进⾏分析，出消耗时间最长的查询或者给服务器带来压⼒最⼤的查询，然后检查查询的schema、SQL和索引结构，判断是否有查询扫描了太多的⾏，是否做了很多额外的排序或者使⽤了临时表，是否使⽤了随机I/O访问数据，或者太多回表查询哪些不在索引中的列的操作。
查询设计
在发现查询效率不⾼时，⾸先就需要考虑查询语句的设计是否合理。如下将会介绍⼀些查询优化技巧，然后在介绍⼀些MySQL优化器内部的机制，并展⽰MySQL是如何执⾏查询的。最后探索查询优化的模式，以帮助MySQL更有效地执⾏查询。
优化数据访问
查询性能低下的最基本原因是访问的数据太多了。因此⼤部分的性能低下查询都可以通过减少访问的数据量进⾏优化。减少数据访问量通常意味着访问了太多的⾏，但有时也可能是访问了太多的列。在查询时如果仅需要查询结果集中的前某些⾏，则最简单的⽅式是在查询语句的最后加上limit。在进⾏多表关联查询时应尽量避免使⽤select *，因为它返回表的所有列，但是这些列可能并不都是必须的。除了请求了不需要的数据，还需要查看MySQL是否在扫描额外的记录，其中可以通过扫描⾏数和返回⾏数进⾏衡量。如果发现查询中需要扫描⼤量的数据但是只返回少数的⾏，通常可以：
使⽤索引覆盖扫描，把所有需要的列都放⼊索引，这样存储引擎⽆须回表获取对应⾏就可以返回结果；
改变库表结构；
求c语言程序设计答案重写这个复杂的查询，让MySQL优化器能够以更优的⽅式执⾏这个查询。
linux操作系统的版本主要有
重构查询⽅式
设计查询的时候⼀个需要考虑的重要问题是，是否需要将⼀个复杂的查询分成多个简单的查询。在传统的实现中总是强调数据库层完成尽可能多的⼯作，这样的逻辑在于以前总是认为⽹络通信、查询解析和优化是⼀件代价很⾼的事情。但是这样的想法对于MySQL并不适
⽤，MySQL从设计上连接和断开连接都很轻量级，在返回⼀个⼩的查询结果⽅⾯很⾼效。
分解关联查询：很多⾼性能的应⽤都会对关联查询进⾏分解，简单地说就是对每个表进⾏⼀次单表查询，然后将结果在应⽤程序中进⾏关联。如下图所⽰：
查询计算机1班学⽣的所有成绩，我们可以将上过程分解为三个⼦步骤，如下：
那么这么分解的好处⼜在哪⾥呢？⾸先是让缓存的效率更⾼。许多应⽤程序可以⽅便的缓存单表查询对应的结果对象。如已经缓存了计算机1班对应的id为1，tb_student表中1班的学⽣有1号和5号，从⽽可以从成绩表中查询1号和5号学⽣的成绩；其次查询分解后，执⾏单个查询可以减少锁竞争；再次查询本⾝效率也会有所提升。如上使⽤in()代替关联查询，可以让MySQL按照ID顺序进⾏查询，这可能⽐随机的关联更加⾼效；最后分解关联查询可以减少冗余记录的查询，在应⽤层做关联查询时，意味着对于某条记录应⽤只需要查询⼀次，⽽在数据库中做关联查询，则可能需要重复地访问⼀部分数据。
查询执⾏的基础
当希望MySQL能够以较⾼的性能运⾏查询时，最好的办法就是弄清楚MySQL是如何优化和执⾏查询的。如下图展⽰了向MySQL发送⼀个请求时MySQL具体的操作过程：
⾸先服务器接收到⼀条客户端请求，先检查查询缓存，如果命中缓存，则⽴刻返回缓存中的数据，否则进⼊下⼀阶段；
服务器进⾏SQL解析、预处理，再由优化器⽣成对应的执⾏计划；
MySQL根据优化器⽣成的执⾏计划，调⽤存储引擎的API执⾏查询；
将结果返回给客户端。
第⼀步是MySQL客户端/服务器通信，⼆者之间通信协议是“半双⼯”的，也就是说在某⼀时刻只能有⼀⽅在发送数据。在任何⼀个时刻MySQL连接都有⼀个状态，该状态表⽰MySQL当前的⼯作，通过SHOW FULL PROCESSLIST命令查询状态。其中状态有Sleep、Query、Locked、Analyzing and statistics、Coping to tmp table、Sorting result、Sending data。
第⼆步是查寻缓存。在解析⼀个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。通常是通过⼀个对⼤⼩写敏感的Hash查实现。如果命中，那么在返回结果前MySQL会检查⼀次⽤户权限，该过程⽆须解析查询SQL语句。如果未命中，则解析SQL语句。
第三步是查询优化处理。包括解析SQL、预处理、优化SQL执⾏计划，其中出现任何错误都会终⽌查
询。⾸先，MySQL通过关键字将SQL 语句进⾏解析，并⽣成⼀棵对应的“解析树”。查询优化器负责将解析树转化成执⾏计划，优化器的作⽤就是到查询的较优执⾏计划。MySQL使⽤基于成本的优化器，它将尝试预测⼀个查询使⽤某种执⾏计划时的成本(SHOW STATUS LIKE 'Last_query_cost')，并选择成本最⼩的⼀个。查询优化器是⼀个⾮常复杂的部件，它使⽤了很多优化策略来⽣成⼀个最优的执⾏计划。优化策略分为：静态优化和动态优化。静态优化可以直接对解析树进⾏分析，并完成优化。例如，优化器可以通过简单的代数变换将where条件转换成另⼀种等价形式，静态优化不依赖于特别的数值，如where中带⼊的常数。静态优化在第⼀次完成后就⼀直有效，即使使⽤不同的参数重复执⾏也不会发⽣变化，可以认为是⼀种“编译时优化”。动态优化是上下⽂相关的，如where条件中取值、索引条⽬对应的数据⾏数等，是⼀种“运⾏时优化”。如下是MySQL能够处理的优化类型：
重新定义关联表的顺序：数据表的关联并不总是按照查询中指定的顺序进⾏。
将外连接转化为内连接：并不是OUTER JOIN语句都必须以外连接的⽅式执⾏。如where条件、库表结构都可能会让外连接等价于⼀个内连
接；

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