今天给各位分享mysql的sql优化的知识，其中也会对mysql数据库优化及sql调优进行解释，如果能碰巧解决你现在面临的问题，别忘了关注本站，现在开始吧！
MySQL的索引是什么怎么优化
MySQL的索引类似于目录，可以提高数据检索的效率，降低数据库的IO成本。
也可以简单理解为：快速查找排好序的一种数据结构。
如果表有100万条记录，通过索引查找记录至少要比顺序扫描记录快1000倍。
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。
MySQL优化是每个程序员都必须掌握的学习内容，为了帮助大家进阶Java中高级、架构师，我准备了一套架构师学习教程还可加入大牛学习圈子，分享SQL优化、微服务架构、分布式JVM性能调优Java并发编程和网络编程电商项目实战redis等教程，各种大牛都是3-8年Java开发者，每天还有12年的架构师做讲解，助你进阶中高级Java程序员，增值涨薪！需要可关注本头条号，并且发送私信关键词：Java
MySQL如何优化大分页查询
很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。
我们先从一个常用但性能很差的查询来看一看。
SELECT*
FROMcity
ORDERBYidDESC
LIMIT0,15
这个查询耗时0.00sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。
CREATETABLEcity(
idint(10)unsignedNOTNULLAUTO_INCREMENT,
cityvarchar(128)NOTNULL,
PRIMARYKEY(id)
)ENGINE=InnoDB;
真正的问题在于offset(分页偏移量)很大的时候，像下面这样：
SELECT*
FROMcity
ORDERBYidDESC
LIMIT100000,15;
上面的查询在有2M行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。
对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。
SELECTCOUNT(*)
FROMcity;
然而，上面的SQL在采用InnoDB为存储引擎时需要耗费9.28sec。一个不正确的优化是采用SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句selectFOUND_ROWS();就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。
SELECTSQL_CALC_FOUND_ROWS*
FROMcity
ORDERBYidDESC
LIMIT100000,15;
这个语句耗时20.02sec，是上一个的两倍。事实证明使用SQL_CALC_FOUND_ROWS做分页是很糟糕的想法。
下面来看看到底如何优化。文章分为两部分，第一部分是如何获取记录的总数目，第二部分是获取真正的记录。
高效的计算行数
如果采用的引擎是MyISAM，可以直接执行COUNT(*)去获取行数即可。相似的，在堆表中也会将行数存储到表的元信息中。但如果引擎是InnoDB情况就会复杂一些，因为InnoDB不保存表的具体行数。
我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些操作导致缓存失效时，执行下面的语句：
SELECTCOUNT(*)
FROMcity
USEINDEX(PRIMARY);
获取记录
下面进入这篇文章最重要的部分，获取分页要展示的记录。上面已经说过了，大的偏移量会影响性能，所以我们要重写查询语句。为了演示，我们创建一个新的表“news”，按照时事性排序(最新发布的在最前面)，实现一个高性能的分页。为了简单，我们就假设最新发布的新闻的Id也是最大的。
CREATETABLEnews(
idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,
titleVARCHAR(128)NOTNULL
)ENGINE=InnoDB;
一个比较高效的方式是基于用户展示的最后一个新闻Id。查询下一页的语句如下，需要传入当前页面展示的最后一个Id。
SELECT*
FROMnewsWHEREid<$last_id
ORDERBYidDESC
LIMIT$perpage
查询上一页的语句类似，只不过需要传入当前页的第一个Id，并且要逆序。
SELECT*
FROMnewsWHEREid>$last_id
ORDERBYidASC
LIMIT$perpage
上面的查询方式适合实现简易的分页，即不显示具体的页数导航，只显示“上一页”和“下一页”，例如博客中页脚显示“上一页”，“下一页”的按钮。但如果要实现真正的页面导航还是很难的,下面看看另一种方式。
SELECTid
FROM(
SELECTid,((@cnt:=@cnt+1)+$perpage-1)%$perpagecnt
FROMnews
JOIN(SELECT@cnt:=0)T
WHEREid<$last_id
ORDERBYidDESC
LIMIT$perpage*$buttons
)C
WHEREcnt=0;
通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offsetId，这个问题就迎刃而解了。MarkCallaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。
如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。
SETp:=0;
UPDATEnewsSETpage=CEIL((p:=p+1)/$perpage)ORDERBYidDESC;
当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。
UPDATEpaginationT
JOIN(
SELECTid,CEIL((p:=p+1)/$perpage)page
FROMnews
ORDERBYid
)C
ONC.id=T.id
SETT.page=C.page;
现在想获取任意一页的元素就很简单了：
SELECT*
FROMnewsA
JOINpaginationBONA.id=B.ID
WHEREpage=$offset;
还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。
CREATETEMPORARYTABLE_tmp(KEYSORT(random))
SELECTid,FLOOR(RAND()*0x8000000)random
FROMcity;
ALTERTABLE_tmpADDOFFSETINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,DROPINDEXSORT,ORDERBYrandom;
接下来就可以向下面一样执行分页查询了。
SELECT*
FROM_tmp
WHEREOFFSET>=$offset
ORDERBYOFFSET
LIMIT$perpage;
简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。
MySQL实现批量插入以优化性能的教程
开启mysql事务，打开MyBatis的DEBUG日志查看执行的SQL，并打印DELETE返回的effectcount
mysql数据库cpu飙升800%，如何故障定位及优化
mysql中CPU占用过高的诊断思路，举个栗子~
mpstat-PALL1，查看cpu使用情况，主要消耗在sys即os系统调用上
perftop，cpu主要消耗在_spin_lock
生成perfreport查看详细情况
CPU主要消耗在mutex争用上，说明有锁热点。
采用pt-pmp跟踪mysqld执行情况，热点主要集中在mem_heap_alloc和mem_heap_free上。
Pstack提供更详细的API调用栈
#00x0000003e0caf80cfin__lll_unlock_wake_private()from/lib64/libc.so.6#10x0000003e0ca7cf6ain_L_unlock_5936()from/lib64/libc.so.6#20x0000003e0ca78bbcin_int_free()from/lib64/libc.so.6#30x000000000097dcb3inmem_area_free(void*,mem_pool_t*)()#40x000000000097d2d2inmem_heap_block_free(mem_block_info_t*,mem_block_info_t*)()#50x00000000009e6474inrow_vers_build_for_consistent_read(unsignedcharconst*,mtr_t*,dict_index_t*,unsignedlong**,read_view_t*,mem_block_info_t**,mem_block_info_t*,unsignedchar**)()#60x00000000009dce75inrow_search_for_mysql(unsignedchar*,unsignedlong,row_prebuilt_t*,unsignedlong,unsignedlong)()#70x0000000000939c95inha_innobase::index_read(unsignedchar*,unsignedcharconst*,unsignedint,ha_rkey_function)()Innodb在读取数据记录时的API路径为
row_search_for_mysql--》row_vers_build_for_consistent_read--》mem_heap_create_block_func--》mem_area_alloc--》malloc--》_L_unlock_10151--》__lll_unlock_wait_privaterow_vers_build_for_consistent_read会陷入一个死循环，跳出条件是该条记录不需要快照读或者已经从undo中找出对应的快照版本，每次循环都会调用mem_heap_alloc/free。
而该表的记录更改很频繁，导致其undohistorylist比较长，搜索快照版本的代价更大，就会频繁的申请和释放堆内存。
Linux原生的内存库函数为ptmalloc，malloc/free调用过多时很容易产生锁热点。
当多条SQL并发执行时，会最终触发os层面的spinlock，导致上述情形。
解决方案
将mysqld的内存库函数替换成tcmalloc，相比ptmalloc，tcmalloc可以更好的支持高并发调用。
修改my.cnf，添加如下参数并重启
[mysqld_safe]malloc-lib=tcmalloc上周五早上7点执行的操作，到现在超过72小时，期间该实例没有再出现cpu长期飙高的情形。
以下是修改前后cpu使用率对比
好了，文章到这里就结束啦，如果本次分享的mysql的sql优化和mysql数据库优化及sql调优问题对您有所帮助，还望关注下本站哦！
