当前位置: 首页 > 科技观察

MySQL性能优化,MySQL索引优化,orderby优化,详解优化

时间:2023-03-15 14:55:17 科技观察

前言今天我们就来聊一聊如何优化MySQL的性能,主要从索引优化入手。下篇文章讲MySQL慢查询日志。我们根据慢查询日志判断是哪条SQL语句有问题,然后进行优化。MySQL慢查询日志敬请期待。)NOTNULLDEFAULT""COMMENT'姓名',ageINTNOTNULLDEFAULT0COMMENT'年龄',posVARCHAR(20)NOTNULLDEFAULT""COMMENT'职位',add_timeTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'入职事件')CHARSETutf8COMMENT'员工记录表';//插入数据INSERTINT`Offtest`.`sta`(`name`,`age`,`pos`,`add_time`)VALUES('z3',22,'manager',now());INSERTINTO`test`.`staffs`(`name`,`age`,`pos`,`add_time`)VALUES('July',23,'dev',now());INSERTINTO`test`.`staffs`(`name`,`age`,`pos`,`add_time`)VALUES('2000',23,'dev',now());//创建复合索引(即一个索引包含多个字段)ALTERTABLEstaffsADDINDEXidx_staffs_nameAgePos(name,age,pos);优化一:索引引入全部使用建立的复合索引包含多个字段,查询时最好全部使用,并严格按照索引顺序,这样查询效率最高。(理想情况,具体情况具体分析)SQL用例优化2:最左前缀规则介绍如果创建复合索引,索引的顺序应该按照创建时的顺序,即从左到对,如:a->b->c(与B+树的数据结构有关)无效索引的例子a->c:a有效,c无效b->c:b和c都areinvalidc:cisinvalidSQLcase优化三:不对索引进行如下处理用法会导致索引失效计算,如:+、-、*、/、!=、<>、isnull、isnotnull,或函数,如:sum()、round()等手动/自动类型转换,例如:id="1",本来是一个数字,写成字符串SQL大小写优化4:不要放置范围查询右侧的索引例如复合索引:a->b->c,当wherea=""andb>10and3=""时,此时只能使用a和b,c不能使用索引,因为索引范围后无效(与B+树结构有关)SQL案例优化五:减少select*的使用,使用覆盖率索引为:select查询字段与where中使用的索引字段一致。SQL案例优化六:like模糊搜索失败like"%Zhangsan%"like"%Zhangsan"解决方案采用复合索引,即like字段为select的查询字段,如:selectnamefromtablewherenamelike"%Zhangsan"3%"Uselike"Zhang3%"SQL案例优化7:orderby优化当查询语句中使用orderby排序时,如果没有使用索引进行排序,文件中会发生filesort排序。这样的话,当数据量大或者并发量高的时候,就会出现性能问题,需要优化。文件排序的情况就是一个例子。按字段排序不是索引字段。orderby字段是一个索引字段,但是select中没有使用覆盖索引,如:select*fromstaffsorderbyageasc;orderby既有ASC升序也有DESC降序,如:selecta,bfromstaffsorderbyadesc,basc;orderby在对多个字段进行排序时,orderby不按照索引顺序进行,即不按照最左前缀规则,如:selecta,bfromstaffsorderbybasc,aasc;索引层面的解决方案是使用主键索引按照最左前缀规则进行排序,使用覆盖索引进行排序。对多个字段进行排序时,保持相同的排序方向。强制在SQL语句中使用索引,强制索引(索引名)在数据库中不排序,在代码级别排序按排序算法排序dual-waysortingMysql4.1使用了dual-waysorting,字面意思是扫描磁盘两次最终得到数据,读取行指针和ORDERBY列,对它们进行排序,然后扫描排序后的链表,根据链表中的值重新读取链表输出的数据。即从磁盘中读取排序字段,在缓冲区中排序,然后再从磁盘中读取其他字段。文件的磁盘IO是非常耗时的,所以在Mysql4.1之后,出现了第二种算法,就是单向排序。单向排序从磁盘中读取查询需要的所有列,在缓冲区中按照orderby列排序,然后扫描排序后的列表输出。效率更高,避免了数据的二次读取,把RandomIO变成了sequentialIO,但是占用空间更大,因为每一行都在内存中。当我们不可避免的要用到排序的时候,当索引层面无法优化的时候,我们该怎么办呢?只要有可能,让MySQL选择使用第二种单向算法进行排序。这样可以减少大量的随机IO操作,大大提高排序工作的效率。下面来看看单向排序优化需要注意的点单向排序优化要点增加max_length_for_sort_data在MySQL中,决定使用“双向排序”算法还是“单向排序”"算法由参数max_length_for_sort_data决定。当所有返回字段的最大长度小于该参数值时,MySQL将选择“单向排序”算法,否则,将选择“多向排序”算法。因此,如果MySQL有足够的内存存储需要返回的非排序字段,可以增大该参数的值,让MySQL选择使用“单向排序”算法。去除不必要的返回字段,避免select*当内存不是很充足时,不能简单地通过强行增大上述参数来强制MySQL使用“单向排序”算法,否则MySQL可能不得不将数据分成很多Segment,然后对它们进行排序,这可能会得不偿失。这时候需要去掉不需要的返回字段,让返回结果的长度适应max_length_for_sort_data参数的限制。增加sort_buffer_size参数以设置该值。如果值太小,而你一次返回的项太多,那么很可能要排序很多次,然后每次排序的结果在最后串联起来,这样比较慢,增加sort_buffer_size并不是让MySQL选择“单向排序”算法,而是让MySQL在排序过程中尽量减少需要排序的数据的切分,因为切分会导致MySQL不得不使用临时表来进行交换排序。但是sort_buffer_size并不是越大越好:Sort_Buffer_Size是一个连接级别的参数,当每个连接第一次需要使用这个buffer时,一次性分配设定的内存。Sort_Buffer_Size不是越大越好,因为是连接级别的参数,设置太大和高并发可能会耗尽系统内存资源。据说当Sort_Buffer_Size超过2M时,会使用mmap()代替malloc()进行内存分配,导致效率降低。优化八:groupby的原则是先排序后分组。优化方法参考orderby。where高于having,where可以写的条件不受having的限制。