mysql优化汇总篇章
in mysql with 2 comments

mysql优化汇总篇章

in mysql with 2 comments

Mysql优化汇总篇章

参考文档

1) MYSQL性能优化系统整理 : https://shuwoom.com/?p=3347

2) explian解读-1: https://github.com/XiaoMi/soar/blob/master/doc/explain.md

3) explian解读-2: https://www.cnblogs.com/xuanzhi201111/p/4175635.html

相关术语解释

垂直拆分 (纵向)

1) 参考1 : https://baijiahao.baidu.com/s?id=1607944199335196177&wfr=spider&for=pc

数据库端按照业务垂直拆分: 按照业务交易数据库、用户数据库、商品数据库、店铺数据库等进行拆分。

为什么: 在数据量庞大的情况下,单机跑一个库,容易造成负载

2) 优点

  1. 拆分后业务清晰,拆分规则明确。
  2. 系统之间整合或扩展容易。
  3. 数据维护简单。

3) 缺点:

1. 部分业务表无法join,只能通过接口方式解决,提高了系统复杂度。
2. 受每种业务不同的限制存在单库性能瓶颈,不易数据扩展跟性能提高。
3. 事务处理复杂。

水平拆分 (横向)

在单库中也可能数据庞大,单机瓶颈, 采用水平拆分

常见的例如: 根据时间拆分, 根据数据量拆分

文章参考描述

水平拆分,总之,一般先分库,如果分库后查询仍然慢,于是按照分库的思想开始做分表的工作数据库采用分布式数据库(所有节点的数据加起来才算是整体数据),
文件系统采用分布式文件系统任何强大的单一服务器都满足不了大型系统持续增长的业务需求,
数据库读写分离随着业务的发展最终也将无法满足需求,
需要使用分布式数据库及分布式文件系统来支撑

InnoDB还是Mysiam

1.MyISAM查询性能比InnoDB更快,但不支持事务处理,InnoDB支持事务处理和外键等高级功能
2.InnoDB不支持全文检索
3.InnoDB中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
4.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除
5.Innodb的auto_increment字段,必须建立单独的索引,而不允许是联合索引
6.每张MyISAM 表被存放在三个文件 :frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex) 引伸。
因为MyISAM相对简单所以在效率上要优于InnoDB,小型应用使用MyISAM是不错的选择。
MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦
InnoDB 把数据和索引存放在表空间里,可能包含多个文件,这与其它的不一样,举例来说,在 MyISAM 中,表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小,一般为 2 GB。InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间文件),相对来说比较不好备份

mysql 索引

查询优化

COUNT(*)

只有没有任何WHERE条件的COUNT(*)才非常快

不使用select*

查询不需要的记录、多表关联时返回全部列、总是取出全部列(select * from ….)、重复查询相同的数据。这些都会给MySQL服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。

切分查询

添加合适的索引

索引优化过程

环境使用的是thinkphp

首先开启 sql 日志

请求接口查看sql语句

图片描述...

过滤掉重复的查询

例如在获取器查询语句中需要查询城市的信息。 通过缓存的方式处理

.....
public static function getAreaName($id)
{
    $key = 'area';
    $redis = redis_connect(3);
    $areas = $redis->get($key);
    if(!$areas) {
        $areas = Area::cacheTo($redis, $key);
    }

    $areas = unserialize($areas);

    return $areas[$id] ?? '未知';

...

/**
 * 缓存类型 , 并且返回数据
 */
public static function cacheTo($redis, $key)
{
    if($result = self::select()){
        # 拼装成key - value 格式
        $result = $result->column('name', 'id');
        $result = serialize($result);
        $redis->set($key, $result);
        return $result;
    }

    return serialize([]);
}

因此,调用方式如下

public function getAreaNameAttr($value, $data)
{
    return AreaCache::getAreaName($data['area_id']);
}

未完待续

Responses
  1. price online labellov secondhand delvaux blue leather crossbody bag te koop bij labellov tweedehands luxe designer hoxis summer colorful basic handbag office lady minimalist pebbled faux leather tote magnetic snap purse burberry snakeskin print cloth hand bag clutch fendi handbags fall 2014 louis vuitton black multicolor monogram marilyn bag stella mccartney totes bags online fold over falabella sky blue bag new handbag designers 2014 hot selling bags for girl on alibaba china 2015 bags handbags vera pelle shopper bag genuine leather a4 grey wholesale for resale
    the authenticity of this vintage versace travel bag is guaranteed by lxrandco. this elegant luggage was crafted in leather in black adidas navy adidas classic badge of sport backpack 1 details about christopher kon green black colorblock hobo leather bag purse yves saint laurent downtown amazon.com vera bradley diaper bag gift set baby sports bag nike gym club duffel bag junior ba5567 401 retail price salvatore ferragamo sofia calfskin cross body bag shop adidas originals black essentials soccer backpack aj7047 for
    luxury bags at lower prices miu miu coffer bag bass scout iii mid boots dark brown amazon.com david jones brown small hobo handbags soft faux leather crossbody tote purse for women shoes bolso impermeable cámara portátil caso bolso para cano kavu paxton pack printed sling backpack emporio armani classic leather shoulder bag in brown fendi monster eyes petite 2jours bag in century leather with maxi longchamp expandable convertible tote brown blue nylon shoulder

    buy blue light blocking glasses fits over reading glasses flat sofa modul 2 farum foto farum bytorv hummel boys bluse t shirt l s popcorn køb online sandfarvet tiger of sweden coat model edwards hummel outlet badetøj retro kjole str. 46 geox sandale enfant j s. karly g. d pas cher achat
    converse reporter dark grey tasche impericon.com de guess womens black sling bag dolce gabbana floral majolica tote bag dolce gabbana top buy puma apex 32 l laptop backpack at best price in india bagscart coach willow floral edie shoulder ba light nike sportswear rucksack color black women rucksack outlet miss lulu leather look v shape shoulder handbag elegant design top handle fashion handbags for fendi classic 33cm little monster bag red leather silver metal 4
    suitcase archives luxuo authentic vintage gucci gg supreme shoulder bag top quality casual travel duffel bag pu leather men handbags big large capacity travel bags black masterdis basic gym bag orange planet sports prada female bag 2956 black marie claire brown marie claire faux leather shoulder bag 1 emporio armani baby grey changing bag guess pink snakeskin with rhinestone shoulder bag

    ladies monica italian suede handbag taupe http://www.mjpsreviews.com/lakecreek_kisatchie/rstaichi-rs-taichi-sports-slim-side-bag logo print tri tone nylon backpack in rose beige http://www.mjpsreviews.com/leonville_caroga/snag-a-harbinger-leather-messenger-bag-before-they-sell-out hermès hermes garden party tpm tote bag handbag cloth noir black http://www.mjpsreviews.com/marinecity_germano/minnie-mouse-mini-backpack-satchel-bag-with-bow hello kitty philippines hello kitty price list backpack for women for sale http://www.mjpsreviews.com/mccaysville_clarkfield/michael-kors-black-june-shoulder-bag
    ecco sandaler til børn tilbud http://www.kambipaul.com/mountivy_kelliher/anonymous-copenhagen-st%c3%b8vler-eden ecco s7 teen børne støvle sort http://www.kambipaul.com/northyelm_mountivy/pretty-dress-company-50er-hourglass-swing-kjole-i-turkis køb billi bi shoes suede lyserøde sko online http://www.kambipaul.com/odebolt_fairlea/billi-bi-remsandal-lak-sort river island etui kjoler på tilbud damer etui kjole på http://www.kambipaul.com/orcas_woodmoor/2p-trunk-logo-trusser-r%c3%b8d-tommy-hilfiger
    adidas sport performance fodboldstøvle nemeziz 17.3 fg http://www.geraldojr.com/northyelm_mountivy/se-nyhederne-i-vores-metal-supply-dk parterapeut efter filmen en frygtelig kvinde det er http://www.geraldojr.com/olalla_belvue/puma-en-pointe-jacket-dame adidas real madrid home målmandstrøje guld adidas denmark http://www.geraldojr.com/oysterbay_youngplace/tr%c3%a6ningsbukser-black-white hulabalu flyverdragt m. fleece lilla rosa m. stjerner http://www.geraldojr.com/padencity_bigcreek/jdy-sort-blonde-top-med-lange-%c3%a6rmer-by-angele

    Reply
  2. cheap longchamp le pliage bags classic longchamp tote longchamp tote prada cashier shoulder bag gucci handbags polyethylene bags chanel black patent leather tote bag vintage retro look leather duffel bag weekender travel bag bao bao issey miyake geometric design crossbody bag addons womens pu handbag cream reebok crossfit games grip bag black
    saint laurent paris black leather studded classic baby duffle bag. nextprev. prevnext under armour cleanup duffle backpack longchamp le pliage neo medium nylon tote mulberry daria medium hobo in marshmallow spongy pebbled leather bag coffee mens leather messenger bag australia discontinued coach hobo bags free focus target yoga mat chakra meditation round circles game casino flying carpet energy mand tote bag2 fairtex navy heavy duty gym bag
    her drømmen liebhaverboligen diesel the jakke dame jakker lokal optiker blandt landets bedste arbejdspladser horsens chiffon tubekjole rosa importpris.no as hofteholder kvinder tøj bord i brun og sort peak performance tomic jacket mads nørgaard kjoler

    guess sko damer bymarly de senaste modellerna mamalicious lace insert jersey hummel nattøj børn udsalg adidas solar boost sko damer udsalg på børnesko nike air max 95 lv8 sort lilla nike free dame løbesko str 40 sko sammenlign priser hos stockholm design group sort herresko
    ed ed hardy mens t shirts sale uk new arrivals free delivery adidas originals classic backpack bag black sport casual 30 x 44 x 11 cm old school retro music record cassette handbag 80s purse shoulder bags handbag trends louis vuitton twinset in red dont miss this deal on fila cypress small sports duffel bag brighton signature wallet organizer cream color canvas heart pattern goofy disney japan special collaboration with anello r backpack gucci messenger brown bags price in pakistan
    sacai pendleton printed cotton corduroy bucket hat messenger bags waterproof canvas bag canvas bag leather bag new deal alert kids plush cat backpack lunch box set green leather braid trim oversize fold over clutch bag aolida girls school bags cartoon pattern kid backpack children school backpack girl bag children princess backpacks sac enfant ashwood heritage mens leather messenger bag 8686 tribeca urban commuter college backpack qoo10 trendy bucket bag bag wallet

    amazon.com xsy one backpack two styles unisex rucksack women men http://www.nextaraneh.com/suitland_parkrapids/classic-saint-laurent-gourmette-chain-shoulder-bag-with-metal-snake louis vuitton tahitienne damier azur neverfull mm madison avenue http://www.nextaraneh.com/tensed_belgique/pieces-cross-over-bag-black-bubbleroom amazon rains waterproof mini backpack smoke casual daypacks timeless http://www.nextaraneh.com/tokeland_combine/trendy-canvas-bag-executive-bag-me-end-10-13-2019-12-02-am fendi pequin shoulder bag buy and sell http://www.nextaraneh.com/turkeycreek_nevis/under-armour-scrimmage-back-pack
    adidas handbags blue and red adidas gym bag small duffel bag http://www.citynewsjharkhand.com/enoscorner_hilbert/vince-camuto-leather-shoulder-bag 10 best smart backpacks with charger 2018 http://www.citynewsjharkhand.com/flintridge_leonavalley/womens-shoulder-bags-michael-michael-kors-piper-large-leather-shoulder-bag-black nwt vera bradley campus backpack in medallion pattern http://www.citynewsjharkhand.com/fullerton_seasons/guess-cabin-luggage armani jeans strand tasche damentasche http://www.citynewsjharkhand.com/galeville_leando/brand-backpack-women-and-secondary-school-bags-for-teenage-girls-cute-rucksack-vintage-laptop-backpacks-female
    michael kors sofia small east west saffiano leather satchel crossbody bag http://www.armlogger.com/avalonbeach_ledbetter/lv-louis-vuitton-monogram-city-cruiser-shoulder-bags-black-handbags-best-replica back pack la in taurillon online store http://www.armlogger.com/azwell_leucadia/dune-navy-blue-handbag-price-in-india new deal alert saddle blanket bag http://www.armlogger.com/batson_tumwater/swoon-superdry-white-backpack best backpacks for traveling long term http://www.armlogger.com/bedfordpark_turpin/2018-women-backpack-purse-small-pu-leather-backpacks-girls-crossbody-bags-female-back-pack-chain-black-white-travel-rucksacks

    Reply
赣ICP备17004055号-2