2.btree索引和优化

最新推荐文章于 2023-09-16 20:39:12 发布

哥斯拉啊啊啊哦

最新推荐文章于 2023-09-16 20:39:12 发布

阅读量344

点赞数

分类专栏： mysql性能优化

本文链接：https://blog.csdn.net/weixin_43456598/article/details/108343689

版权

mysql性能优化专栏收录该内容

2 篇文章 0 订阅

订阅专栏

索引的作用是什么
大大减小服务器查找数据所需要的时间。
索引类似目录，可以快速定位到某个区域，找到响应的数据，减少服务器扫描的数据量，效率快。
而如果没有索引，只能一页一页翻找寻找需要的内容。在磁盘上表现就是要遍历扫描查找，查找的数据量大，效率慢。
另外在索引通常是按键值顺序存放的，而数据行的物理地址则是随机分布的。使用索引进行查找能让随机IO，变成顺序IO，而磁盘的顺序IO，性能肯定好于随机IO。

索引的类型主要有以下几种：
1. Btree索引
2. 自适应HASH索引
3. 全文索引
4. 空间索引

平时在使用mysql中，使用到的主要是 btree索引，后面主要介绍 btree索引

Btree索引，结构如下图

btree索引树的特点是每个叶子节点到跟节点的距离相等，且 btree索引是按键值顺序存储的

在索引的根节点中，存放了指向下层子节点的指针，这些指针定义了值的上限和下限。
存储引擎根据这些指针向下进行查找，通过比较节点页的值和要查找的值，找到合适的指针进入下层的子节点。

# b+树的查找过程

假如要查找的的数据项是28。
首先把 磁盘块1 加载到内存（发生一次IO），使用二分法查找确定在17，35之间，
锁定 磁盘块3 的指针 p2，p2 指针的磁盘地址将 磁盘块3 加载到内存（二次IO），
重复之前的流程，用二分查找法确定在26，30之间，锁定 磁盘块8 的指针p2，
并加载到内存中（三次IO），使用二分查找法找到29，结束查询，共 3次IO（等于btree的高度h）。

在实际场景中，3层的 btree索引 可以表示上百万的数据，如果没有索引，每个数据要发生一次IO，总共需要上百次IO，效率底下。
而如果使用了 btree索引，每次查找数据只需要 3次IO，性能提升巨大。

另外理解3个概念 "回表" ，"回表查询"，"索引覆盖查询" 后面会用到
一般来说，sql查询时，会先找到对应的 主键值，再根据 主键值 定位 行记录，
根据主键值定位到行记录这一步就叫做 "回表"，可以理解为这是一次 "回表查询"。
但也有 "非回表查询" 的场景。
因为 btree索引上会存储索引相关的数据，比如 table 中有10个索引，即10个索引的数据都会存在 该table 对应的 btree 上。
此时如果查询的数据都能在 btree 上找到，不需要 "回表" 这个过程，这种场景可以成为 "索引覆盖查询"

因为少了一个步骤，因此 "索引覆盖查询" 速度快于 "回表查询"

# btree索引在什么情况下可以用到？
1.全值匹配的查询
    order_id = "123456789"
2.匹配最左前缀的查询
    比如 a,b,c 组成了一个符合索引 abc，只有在查询a字段开头的场景下才能用到索引，如a,ab,ac,abc
3.匹配列前缀查询
    order_id like "12345"
4.匹配范围值的查询
    order_id > "123456" and order_id < "98765"
5.精确匹配左前列并范围匹配另一列
6.只访问索引的查询

# btree索引的限制
1.如果不是按照索引最左列开始查找，则无法使用索引
2.使用索引时不能跳过索引中的列
3.not in 和 <> 操作无法使用索引
4.如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

#a 使用索引优化sql

mysql中，使用 explain 命令，能获取 一条sql 的执行计划。
通过分析 sql的执行计划，能分析出 sql语句的优劣，并在此基础上做出优化。

创建如下表格，并插入1000条记录，用来辅助理解索引的优化：
CREATE TABLE `test_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint unsigned DEFAULT NULL,
  `referer_id` bigint unsigned DEFAULT NULL,
  `old_transaction_id` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

#####插入数据的sql语句如下：

INSERT INTO `test_table` (`id`, `order_id`, `old_transaction_id`) VALUES (235585611338122240, 235584330900997120, 1233), (235588033045365760, 235584330900997120, 1233), (235594734284866560, 235594076886434816, 11112), (235599674520667136, 235599448883889152, 1213321), (235607034702103552, 235606859833181184, 213332156), (235628728112153600, 235606859833181184, 213332156), (236119680908001280, 235630880528303104, 123221121), (310879767740057600, 310876520023360512, 27484067), (320915856693429248, 320569098415145984, 100013), (320918825035924480, 320569098415145984, 100013), (320946716637430784, 320569098415145984, 100015), (334229286678464512, 334228355626862592, 132221), (334282823227705344, 334228355626862592, 132221), (334781259165204480, 334228355626862592, 132221), (334783837202546688, 334228355626862592, 132221), (345476815684405248, 345476488964900864, 122567010), (345480543682463744, 345480285413999616, 122567020), (345591294598350848, 345536069321656320, 122567022), (345591468494194688, 345536069321656320, 122567022), (345596967109297152, 345596725567718400, 122567024), (345597227206476800, 345596725567718400, 122567024), (347006204112831488, 347005781578646528, 122667103), (347074946725348352, 249513978265372672, 27477058), (348142585820251136, 348139898261242880, 27487463), (348221338625803264, 348139898261242880, 27487463), (348221721448317952, 348135147591205888, 27487460), (348222214178374656, 348133425024107520, 27487457), (348372636566324224, 348372147141379072, 27487541), (348395443413091328, 348256309994357760, 27487499), (348398449739203584, 348397542813238272, 27487571), (349542185856697344, 349504919834756096, 110000105), (349544471198729216, 349504919834756096, 110000106), (349551768750818304, 349504919834756096, 110000107), (349553731836085248, 349504919834756096, 110000108), (349582513921886208, 349504919834756096, 110000114), (349596900820813824, 349504919834756096, 110000116), (349625697720895488, 349623851270834176, 27487748), (349629681688938496, 349628907051321344, 27487760), (349666531367487488, 349666072795842560, 27487775), (349756631191422976, 349628907051321344, 27487766), (349807591733364736, 349806967453157376, 27487853), (349809109031225344, 349808591152121856, 27487859), (350095998686627840, 350092067633924096, 27488006), (350948019467749376, 350940122801276928, 27488012), (350970624048400384, 350970453809989632, 122967004), (350993441078937600, 350992895785863168, 122967008), (351178882767947776, 351178558481139712, 122967010), (351219818118023168, 351215707867875328, 27488048), (351222930560026624, 351215707867875328, 27488048), (351282021307287552, 351245260799641600, 27488060), (351468808428357632, 351245260799641600, 27488060), (351501397583235072, 351245877110671360, 27488063), (351506836806993920, 351245877110671360, 27488063), (353139925190739968, 330871584745096192, 1230002), (353142486970958848, 330871584745096192, 1230003), (357075603708479488, 357075025330734080, 27488186), (357077794578007040, 357077141625538560, 27488195), (358153007428305920, 349545929189131264, 27487661), (358153016672551936, 349559625168749568, 27487679), (358153019491124224, 349781416105510912, 27487844), (358153021152068608, 349887472622143488, 27487949), (358153022058038272, 349892745298479104, 27487952), (358153024037749760, 350069339086424064, 27487997), (358167919386985472, 348131150285276160, 112321), (358167938311685120, 348133425024107520, 27487457), (358167942807979008, 348135147591205888, 27487460), (358167980556715008, 348255591174538240, 27487496), (358167984751019008, 348256309994357760, 27487499), (358167989012431872, 348372147141379072, 27487541), (358167993206735872, 348348007999112192, 27487562), (358167998105682944, 348397542813238272, 27487571), (358168002333541376, 349504919834756096, 110000101), (358168009128313856, 349504919834756096, 110000103), (358168015302329344, 349504919834756096, 110000104), (358168021258241024, 349522292390265856, 27487637), (358168028187231232, 349504919834756096, 110000109), (358168032918406144, 349504919834756096, 110000110), (358168037263705088, 349504919834756096, 110000111), (358168042414310400, 349504919834756096, 110000112), (358168046692500480, 349569429018806272, 27487685), (358168052900070400, 349621547926849536, 27487745), (358168060852470784, 349623851270834176, 27487748), (358168067143926784, 349623851270834176, 27487754), (358168073754149888, 349628907051321344, 27487760), (358168079676507136, 349628907051321344, 27487766), (358168086571942912, 349666072795842560, 27487775), (358168110412366848, 349800744917960704, 27487850), (358168116418610176, 349806967453157376, 27487853), (358168123515372544, 349808591152121856, 27487859), (358168129454507008, 349825914500057088, 27487865), (358168137356575744, 350029939069716480, 27487967), (358168143497036800, 350030848092833792, 27487970), (358168149301953536, 350040423151994880, 27487979), (358168155257865216, 350041113953863680, 27487982), (358168162203632640, 350048154260899840, 27487985), (358168168998405120, 350051955894159360, 27487991), (358168176581706752, 350100105161902080, 27488009), (358168187486897152, 350940122801276928, 27488012), (358168197922325504, 351214258819729408, 27488045), (358168204079563776, 330871584745096192, 1230003), (358168212585612288, 354337185710769152, 27488153), (358168218726073344, 357075025330734080, 27488186), (358168227081126912, 357075025330734080, 27488192), (358168244680426496, 357077141625538560, 27488195), (358168251223540736, 357373510324094976, 27488210), (358168257447887872, 357373898917970944, 27488213), (358168263470908416, 357374886106138624, 27488219), (358168269812696064, 357376396105909248, 27488222), (358226354430314496, 357373510324094976, 27488210), (358227842451933184, 357373898917970944, 27488213), (358253122260337664, 357376396105909248, 27488222), (358278294442903552, 1, 1240003), (358278966286518272, 1, 1240003), (358282478445691904, 1, 1240003), (358297634009547776, 1, 1240003), (358435554116797440, 1, 1240003), (358435691673191424, 1, 1240003), (358435910431310848, 1, 1240003), (358454656352945152, 358333496852251648, 27488243), (358454677190247424, 358336088680465408, 27488246), (358454683582366720, 358354803379700736, 27488255), (358454689907377152, 358357543703053312, 27488261), (358651691131766784, 358336088680465408, 27488246), (358651991695591424, 358333496852251648, 27488243), (358652355442411520, 358357543703053312, 27488261), (358652874529474560, 358354803379700736, 27488255), (358654925896452096, 358354803379700736, 27488255), (358925420134924288, 1, 1240003), (358926354122539008, 1, 1240003), (358927261769924608, 1, 1240003), (370032985031869440, 370024863903025152, 11067303), (370033502759979008, 370024863903025152, 11067303), (370046186620486656, 370045832017250304, 11067311), (370046490153878528, 370045832017250304, 11067311), (377607715184181248, 377600868318445568, 1161055), (377609456961191936, 377600868318445568, 1161055), (377609898722066432, 377600868318445568, 1161055), (379475941149114368, 379453720078843904, 1171814), (379477608502394880, 379453720078843904, 1171814), (406291697136337920, 406036079439808512, 27493148), (407735827678922752, 407731305514567680, 27493535), (407739030935996416, 407731305514567680, 27493535), (407753901740165120, 407731305514567680, 27493535), (407755384728619008, 407731305514567680, 27493535), (407782999539614720, 407731305514567680, 27493535), (407784003471442944, 407713301011662848, 27493526), (407789148020179968, 407713301011662848, 27493526), (407794061932528640, 407792338795004928, 27493613), (407838886274827264, 407837731582937088, 27493631), (407842148839360512, 407837731582937088, 27493631), (407858405559011328, 407837731582937088, 27493631), (407865961094349824, 407837731582937088, 27493631), (407875229684107264, 407837731582937088, 27493631), (407880770846229504, 407837731582937088, 27493631), (407888910799112192, 407837731582937088, 27493631), (407890731814913024, 407837731582937088, 27493631), (407896055846306816, 407837731582937088, 27493631), (407928830104208384, 407898073088427008, 27493709), (407931190390064128, 407837731582937088, 27493631), (408916641225671680, 408915433836545024, 27493754), (409229876143358976, 409022406791824384, 27493838), (409230909066216448, 409022406791824384, 27493838), (409232390913819648, 409022406791824384, 27493838), (410675085158941696, 410467093230683136, 27494141), (410675827450086400, 410675633371251712, 27494273), (410678574266221568, 410678361011028992, 27494282), (410813742809383936, 410813543194067968, 27494363), (410890849887486976, 410890602574545920, 27494489), (411783555333522432, 411782914242544640, 27494513), (411798470614090752, 411798261452538880, 27494534), (411928893990142976, 411928721721689088, 27494681), (411929332865336320, 411929180477883392, 27494690), (411929747732333568, 411929627943011328, 27494696), (412922823728693248, 412922564050944000, 2080025), (412970108969811968, 412486778532955136, 2080034), (413595768683791360, 413595081539355648, 27494915), (415150975737694208, 415150865695934464, 27495257), (415191039008080896, 415190930862146560, 27495290), (415204275929515008, 415204166894388224, 27495299), (415226621738386432, 415226337431684096, 27495311), (415242730482992128, 415150865695934464, 27495257), (415641898854645760, 415150865695934464, 27495257), (415644432902127616, 415150865695934464, 27495257), (415646656805994496, 415150865695934464, 27495257), (416159745125155840, 415204166894388224, 27495299), (416285730390705152, 416285598756668416, 27495605), (416287697485726720, 416287441247306752, 27495617), (416288556848284672, 416288432965321728, 27495629), (416299577180718080, 416299425145586688, 27495689), (416301725067019264, 416301585447027712, 27495722), (416400652894241792, 416288432965321728, 27495629), (416403443633128448, 416287441247306752, 27495617), (416471125824930816, 416470985064088576, 27495794), (416471901284631552, 416471747152348160, 27495803), (416479976695563264, 416479787398235136, 27495818), (416483240031847424, 416483078903464960, 27495830), (416483825288250368, 416483078903464960, 27495830), (416498271091983360, 416479787398235136, 27495818), (416505533294478336, 416505079806329856, 27495857), (416506036208305152, 416505079806329856, 27495857), (416507566609172480, 416507372882659328, 27495869), (416510016468584448, 416508852146571264, 27495878), (416517371046430720, 416516734602741760, 27495893), (416523152173073408, 416522641206183936, 27495911), (416531352188388352, 416530087454737408, 27495941), (416537194367321088, 416535907118646272, 27495950), (416547982821852160, 416547811627140096, 27495962), (417028252087877632, 417027384756142080, 12201), (417615701189101568, 417611966094280704, 27496082), (417617856625149952, 417611966094280704, 27496082), (417619867743586304, 417611966094280704, 27496082), (417624236312528896, 417623032345954304, 27496115), (417627059951536128, 417623032345954304, 27496115), (417640981467269120, 417640811379853312, 27496154), (417644935336986624, 417644379457488896, 27496169), (417758152117749760, 417757437005694976, 27496280), (417956310953265152, 417904946650843136, 27496475), (432728858467272704, 432727168246646784, 2230833), (433672622719404032, 433669101550863360, 27497153), (433788420389636096, 433787980658804736, 2230841), (434045140382812160, 434041780862747648, 2241512), (436712790992914432, 436708865610909696, 27497336), (436857939479791616, 437340657799921664, 2230855), (436864572301804544, 437344181736701952, 2230857), (436890438910117888, 437344181736701952, 2230857), (436892063934482432, 437344181736701952, 2230857), (436894160499279872, 437344181736701952, 2230857), (436979456486179840, 436976959147247616, 22667802), (437015872658245632, 437015365248124928, 2230861), (437019172736632832, 437018853415880704, 2230864), (437948195167832064, 437947435159946240, 27497590), (437952454265439232, 437951646106944512, 27497617), (437992309280244736, 437991475553272832, 27497673), (437994340078355456, 437993859075572736, 27497685), (437995662408188928, 437995246584890368, 27497693), (438150081179321344, 438149947716568064, 27497765), (438279092282885120, 438152762983744512, 27497771), (438361492807517184, 438331775945573376, 27497851), (438400927469175808, 438400845562807296, 27497885), (439621483103291392, 439621388832114688, 27497995), (439626512157673472, 439626373091330048, 27498000), (439643769453709312, 439626373091330048, 27498000), (439667175951730688, 439621388832114688, 27497995), (439688557406226432, 439681992347387904, 27498030), (439689028695001088, 439681992347387904, 27498030), (439702050733852672, 439681992347387904, 27498030), (439702661541955584, 439681992347387904, 27498030), (440804582411503616, 440804449703725056, 27498249), (440806529575192576, 440804449703725056, 27498249), (441015839085332480, 441014486254519296, 27498393), (441019361512162304, 441019234139538432, 27498405), (441022572688081920, 441022406778192896, 27498414), (441053741131532288, 441051630641644544, 27498459), (441056428388550656, 441055658213671936, 27498474), (441068271056225280, 441067286770518016, 27498498), (441070096853533696, 441069044351666176, 27498510), (447019273320170496, 447018420131629056, 27499143), (447022585159386112, 447022300483586048, 27499161), (447063433620259840, 447062733054052352, 27499197), (447064381499410432, 447064196916480000, 27499215), (449557425297589248, 449556038929122304, 27499545), (449557427730285568, 449557207193781248, 27499551), (449595203444048896, 449594456941822976, 27499590), (449605928346485760, 449605244658156544, 27499605), (449623714611364864, 449622843974517760, 27499626), (449630007828972544, 449629274027099136, 27499635), (449636005750801408, 449635124930184192, 27499644), (449638975049597952, 449638648078435328, 27499653), (449640329977562112, 449639902158554112, 27499662), (449683992782865408, 449682562508424192, 27499674), (450363650335571968, 450359375333163008, 30704241), (450367211886870528, 450366556602368000, 30704243), (450367406888452096, 450366556602368000, 30704243), (451672122188662784, 451671308476909568, 3082211), (451672365491849216, 451671308476909568, 3082211), (451672479023269888, 451671308476909568, 3082211), (451672564905838592, 451671308476909568, 3082211), (451672931823552512, 451671308476909568, 3082211), (451676576539510784, 451674810737526784, 30211318), (451679511814113280, 451674810737526784, 30211318), (451679961258952704, 451674810737526784, 30211318), (451681001597339648, 451674043163117568, 30211319), (451687695672969216, 451686323967789056, 3082111), (451688140302747648, 451686323967789056, 3082111), (451694949973360640, 451674392213061632, 3081003), (451696004538826752, 451674392213061632, 3081003), (451919456084426752, 451918030893481984, 3081801), (451946995246956544, 451937335999725568, 3081802), (451947457442480128, 451937335999725568, 3081802), (451947638636412928, 451937335999725568, 3081802), (451988573197959168, 451987993561923584, 2261922), (451989558842621952, 451987993561923584, 2261922), (451989714870730752, 451987993561923584, 2261922), (451990095696756736, 451987993561923584, 2261922), (451991687821000704, 451987993561923584, 2261922), (451992962855862272, 451987993561923584, 2261922), (451995672309137408, 451995200886145024, 2261923), (451995887896363008, 451995200886145024, 2261923), (451996097611563008, 451995200886145024, 2261923), (451996600911265792, 451995200886145024, 2261923), (452016442821312512, 452010811699757056, 3081808), (452016733067149312, 452010811699757056, 3081808), (452100869731647488, 452099570067832832, 3082100), (452101195025088512, 452099570067832832, 3082100), (452105226908860416, 452103796349206528, 2261930), (452111035617247232, 452103796349206528, 2261930), (452111493199036416, 452103796349206528, 2261930), (452112602072350720, 452103796349206528, 2261930), (452642129177511936, 452641537159890944, 309141857), (452646769235363840, 452646165339473920, 309142333), (452647163567048704, 452646165339473920, 309142333), (452653743607941120, 452651999649892352, 309142921), (452658232419190784, 452651999649892352, 309142921), (452832237382043648, 452832036491659264, 309172812), (452974508828000256, 452973468237955072, 309114842), (452975224007163904, 452973468237955072, 309114842), (452975416659935232, 452973468237955072, 309114842), (457192704992904192, 457192107673682944, 312173933), (458709648317383680, 458693005386221568, 313183033), (458738211712173056, 458734203836597248, 313191113), (458764285972087808, 458763848959165440, 313194056), (458764828748579840, 458763848959165440, 313194056), (458773552884122624, 458763848959165440, 313194056), (458774836290814976, 458763848959165440, 313194056), (458798180109158400, 458793195145298944, 27500490), (461130526414111744, 461130340623221760, 27500667), (461148780276777984, 461143731156845568, 315110508), (461173819952891904, 461171667134088192, 27500688), (462089579172724736, 462087860028178432, 315184258), (462545545047606272, 462545083623834624, 316101715), (463054632034238464, 463053852665446400, 316104224), (463072150316646400, 463071809202290688, 316110029), (467903266450080768, 466902465200030720, 27501090), (467903308208571392, 466952898702509056, 27501099), (467903320338498560, 466954994361336832, 27501102), (467903331747005440, 467002340168205312, 27501114), (467903345269441536, 467003837132080128, 27501117), (467903357852353536, 467005572147872768, 27501120), (469352717299516416, 468895977072264192, 27501369), (469352754125505536, 468935789372277760, 27501378), (469352766389650432, 468977678020543488, 27501390), (470069906927289344, 470069674042754048, 27501447), (470102368021677056, 470100631898588160, 27501489), (470103838678877184, 470103085146997760, 27501501), (470130834813256704, 470130641841718272, 321155249), (470131228993946624, 470130957052052480, 321155308), (470621339523219456, 470621202319147008, 321160009), (470621613813923840, 470621438861115392, 321160023), (470626753530822656, 470626610706382848, 321160531), (470627439869952000, 470627319644422144, 321160613), (470629487495610368, 470629233857658880, 321160807), (470659297554989056, 470659144630665216, 321163750), (470659507135971328, 470659396507009024, 321163805), (470802503290618880, 469845651383945216, 32167011), (470802528439665664, 469862688462243840, 32167042), (470802537012823040, 469904489097234432, 321120810), (470802578117002240, 469907518777231360, 32167025), (470802589911385088, 469926794439460864, 32167027), (471365482620946432, 471365343705597952, 322121923), (472251839887213568, 471362496192612352, 322121634), (472251865019483136, 471366779231307776, 27501669), (472251873995293696, 471369138963515392, 322122309), (472251880504853504, 471518953462597632, 322145159), (472251886796309504, 471562580683425792, 322153519), (473701453911852032, 472745979280393216, 32167034), (473701479362888704, 472747888695346176, 32167036), (473701491811582976, 472949237886256128, 32367032), (473701500166636544, 472950580633961472, 32367034), (473701506726528000, 472951662646952960, 32367036), (473701514846700544, 472953376020136960, 32367038), (473701522010571776, 472962169328141312, 32367002), (473701528654349312, 472964107113366528, 32367004), (473701535499453440, 472965420115067904, 32367006), (473701542143230976, 472967924886307840, 32367008), (477466389796389888, 477465990280545280, 326171950), (477487714510341120, 477487588580557824, 326174117), (477487985646928896, 477487824669541376, 326174132), (477496722415715328, 477496596888585216, 326175014), (477500817516037120, 477500611659596800, 326175333), (477501518384235520, 477501380391633920, 326175457), (477849265025056768, 477845857320828928, 326153636), (477849286130794496, 477845857320828928, 326153636), (477849328442933248, 477845857320828928, 326153636), (477856269344964608, 477845857320828928, 326153636), (477868789124300800, 477858470347931648, 326154936), (477878891105484800, 477877946330120192, 326160842), (478050155493360640, 477274916178134016, 27502044), (478059384958615552, 478057952905789440, 326190742), (478479471079359488, 478057952905789440, 326190742), (478602292698842112, 478562173325444096, 27502194), (478608953236816896, 478562173325444096, 27502194), (478860099486516224, 478860005500552192, 327162440), (478860698751894528, 478860549384340480, 327162512), (478947143357300736, 478944698866597888, 327094848), (478965975882924032, 478056938639196160, 326190648), (478973384114307072, 477570880411567104, 326190359), (478974414856126464, 478055368660877312, 326190510), (479022991154348032, 473510176217890816, 323155005), (479023926618357760, 477522487823860736, 326181549), (479034475796234240, 478056938639196160, 326190648), (479035008154075136, 478057952905789440, 326190742), (479101400681283584, 478057952905789440, 326190742), (479320461948223488, 479320130380103680, 327160145), (479320961473052672, 478055368660877312, 326190510), (479324315876065280, 479320812390711296, 327160226), (479338474185424896, 479338279452278784, 327161947), (479339124755529728, 479338953460154368, 327162027), (479360841603350528, 479360706345435136, 327164203), (479361344416514048, 479361213537452032, 327164234), (479499684776281088, 330871584745096192, 2343001), (479499722306913280, 478651524617110528, 27502203), (479499732591346688, 479447530971594752, 327180818), (479499733883192320, 479488234410213376, 327184845), (481372998062868480, 481372400458434560, 329100030), (481373615195981824, 481373511210797056, 329100137), (481866607010775040, 481866434306113536, 329101116), (481983004164489216, 481982919775092736, 329120700), (481984316126003200, 481984129932460032, 329120811), (481984579108864000, 481984129932460032, 329120811), (481988488183414784, 481984129932460032, 329120811), (481990436236296192, 481990328073584640, 329121421), (481990656470810624, 481990540775129088, 329121434), (489646561723189248, 489139824083634176, 403183645), (490068192337627136, 490068110062159872, 404095855), (490068353331791872, 490068263036815360, 404095904), (490094602561292288, 490094502602639360, 404102508), (490150085838014464, 490149612334647296, 404111953), (490156707805169664, 490156551240189952, 404112647), (490159524548741120, 490158829200246784, 404112902), (490169561904089088, 490169450184607744, 404113935), (490169984740263936, 490169869396903936, 404114000), (493020867379365888, 493020607097636864, 406105150), (493022699669456896, 493022342650300416, 406105334), (493024348718794752, 493023970543568896, 406105511), (493029046708373504, 493028838217910272, 406110008), (493029311234738176, 493029188693952512, 406110029), (497698172580169728, 497692515957245952, 27503763), (498178055953448960, 497692515957245952, 27503763), (498343884405900288, 497690410802513920, 27503760), (500300601930844160, 500300225852770304, 411112337), (500301935736293376, 500301751203694592, 411112507), (500303011004844032, 500302880092227584, 411112615), (500317483903780864, 500317295160100864, 411114034), (500318059848828928, 500317924305700864, 411114112), (500319028514949120, 500318732346755072, 411114200), (500319874623506432, 500319718159190016, 411114259), (500320697176852480, 500320466657904640, 411114343), (500321032536623104, 500320860469495808, 411114407), (501242971207796736, 500329392153593856, 27504189), (501243018804758528, 500502813504210944, 27504246), (501243032645961728, 500516579964913664, 27504249), (501243046000625664, 500518065889380352, 27504252), (501243058046666752, 500523574805694464, 27504255), (501243094906210304, 500526987593418752, 27504258), (501243109082957824, 500767523931719680, 27504393), (501243121481320448, 500768944391489536, 27504396), (503548138355722240, 503547734427469824, 413170943), (503994218541219840, 503994054577487872, 413163306), (503996121027182592, 503994054577487872, 413163306), (504000076943720448, 503999986027986944, 413163859), (504032872324136960, 504032785166499840, 413171134), (504033013034647552, 504032785166499840, 413171134), (504033090008514560, 504032785166499840, 413171134), (504035239052443648, 504035144865153024, 413171355), (504036719272656896, 504036642902769664, 413171524), (504036861493116928, 504036642902769664, 413171524), (504036935296090112, 504036642902769664, 413171524), (504038227577929728, 504038149094113280, 413171654), (504038364278685696, 504038149094113280, 413171654), (504038438383648768, 504038149094113280, 413171654), (504039949121617920, 504039886223835136, 413171837), (504040052284719104, 504039886223835136, 413171837), (504040113907433472, 504039886223835136, 413171837), (504046944448937984, 504046894234730496, 413172535), (504047012564434944, 504046894234730496, 413172535), (504047057678368768, 504046894234730496, 413172535), (504048764323889152, 504048669046079488, 413172721), (504048934612631552, 504048669046079488, 413172721), (504049029504565248, 504048669046079488, 413172721), (504051309964427264, 504051219753336832, 413172953), (504051616886816768, 504051219753336832, 413172953), (504057947601502208, 504057897873833984, 413173631), (504058025363898368, 504057897873833984, 413173631), (504058068749778944, 504057897873833984, 413173631), (508490670923614208, 508396396626313216, 416172626), (508490672232237056, 508485345248018432, 416185447), (508490673272424448, 508490191464300544, 416185935), (508490674245502976, 508514182681853952, 416192326), (508490675185027072, 508514848703774720, 416192406), (509940221194830848, 509409098458988544, 417101227), (509940222184686592, 509409440445759488, 417101248), (511305374050025472, 511305263773384704, 418173608), (511308411497873408, 511306620999827456, 418173728), (511312656670392320, 511312040023818240, 418174251), (511314769190322176, 511314447923412992, 418174515), (511389762439906304, 510839193870532608, 418095308), (511389763345875968, 511291346670059520, 418172218), (511389764268622848, 511319894965354496, 418175039), (511389773647086592, 511333468320301056, 418180408), (511389781364605952, 511335758427062272, 418180625), (511860530248452096, 511860301658884096, 419104731), (511861473513867264, 511860927096719360, 419104808), (511863247285028864, 511863100618606592, 419105018), (511897123738912768, 511896865084573696, 27504933), (512025334653159424, 512013499921106944, 27505029), (512604629222031360, 512604394307452928, 419150642), (512604787783499776, 512604394307452928, 419150642), (512605172736720896, 512605013755822080, 419150719), (512839390272194560, 512253629512582144, 419171815), (512839391798921216, 512253950863377408, 419171834), (512839392805554176, 512257121253884928, 419172143), (512839393812187136, 512303287907681280, 419180735), (512839395389245440, 512378580580205568, 419192222), (512839396899194880, 512396057104089088, 419113942), (512839398459475968, 512415794257232896, 419195921), (512839400187529216, 512431114019701760, 27505209), (512839401764587520, 512524882920079360, 419134742), (512839403391977472, 512529676757893120, 419135228), (512839404952258560, 512565145923223552, 419142742), (512839406613202944, 512587705910034432, 419145007), (512839408240592896, 512588734017830912, 419145108), (512839409700210688, 512590917505384448, 419145318), (512839411243714560, 512595477871460352, 419145750), (512839413256980480, 512597042061639680, 419145923), (512839414901147648, 512605013755822080, 419150719), (512839417971378176, 512611182285160448, 419151326), (512839419447773184, 512665860540727296, 419160745), (512839421041608704, 512666073393266688, 419160758), (512839422048241664, 512666788287217664, 419160841), (513528373943763968, 513528264438875136, 420142411), (513549017922176000, 513548799919031296, 420144453), (514289199499740160, 513415773725688832, 420123244), (514289237399471104, 513527962264437760, 420142353), (514289257062368256, 513528264438875136, 420142411), (514289279057298432, 513528587266065408, 420142430), (514289297579344896, 513547495373046784, 420144335), (514289316856366080, 513548799919031296, 420144453), (514289337693668352, 513686561531921408, 42067805), (514289339639825408, 513691756798182400, 420170654), (514289341250438144, 513691989129069568, 420170708), (514289342995268608, 513692425319908352, 42067806), (514289345125975040, 513699565132613632, 420171439), (514289346786919424, 513715146049226752, 420173008), (514289367104128000, 513821253971214336, 420111532), (514289369167725568, 513823898630881280, 420111810), (514289370795115520, 513835060395245568, 420112914), (514289372388951040, 513839572560379904, 420113344), (514289374033118208, 513844943718973440, 420113904), (514289396665582592, 513850055686356992, 420114409), (514289417855206400, 513894214208061440, 420122801), (518180621651017728, 518180423780532224, 423112559), (518182554335969280, 518181990990610432, 423112732), (518186335350030336, 518186058878287872, 423113135), (518188939090067456, 518188801332346880, 423113418), (518637892373873664, 518188662081454080, 423113410), (519443977758344192, 519435275080860672, 424161234), (519925833209479168, 519918654289084416, 424161246), (520087415264675840, 519112557831686144, 27506085), (520087417345050624, 519113558877833216, 27506088), (520087419039549440, 519119256705599488, 27506103), (520087420767602688, 519120069578491904, 27506106), (520087422428547072, 519123406231209984, 27506112), (520087424072714240, 519125334621194240, 27506118), (520087425733658624, 519128688118237184, 27506121), (520087427444934656, 519129459182307328, 27506124), (520087429105879040, 519130563056011264, 27506127), (520087430733268992, 519130907408369664, 27506130), (520087432427767808, 519131218374068224, 27506136), (520087435095345152, 519132261833017344, 27506139), (520087436773066752, 519132932552558592, 27506142), (520087438534674432, 519235923485950976, 424125432), (520087440212396032, 519496875196318720, 424171346), (520087459791407104, 519982166151528448, 424171551), (520473670313870336, 520473575421936640, 425092402), (520474235622163456, 520474070366585856, 425092432), (520475123069780992, 520475016886780928, 425092528), (520559622407357440, 520559448662508544, 425104920), (520928379726234624, 521411317089697792, 425165535), (520950611013795840, 520950346437099520, 425091739), (520951065357582336, 520950900269776896, 425091812), (521067369833955328, 521067026823774208, 425111334), (521071423930761216, 521070867682164736, 425111723), (521405977069617152, 521405765676695552, 425165004), (521406508236275712, 521406358734503936, 425165040), (521407740287909888, 521407587615244288, 425165153), (521432030408343552, 520948718359315456, 425171603), (521432121844170752, 520948718359315456, 425171603), (521434296943116288, 521434154336780288, 425171816), (521438220194414592, 521437995362942976, 425172205), (521443243947196416, 521442995644399616, 425172703), (521443503155183616, 521443360548847616, 425172725), (521445395558039552, 521445245401956352, 425172917), (521449220645847040, 521449006736343040, 425173302), (521453589281898496, 521453485263159296, 425173729), (521473015251206144, 521472708026826752, 425175634), (521475711031050240, 521475512355258368, 425175921), (521477890324299776, 521477728692600832, 425180132), (521479544524242944, 521479175408713728, 425180300), (521494091561697280, 521493237618180096, 425181658), (521497015729782784, 521496858040729600, 425182034), (521497961931210752, 521497776559751168, 425182129), (521498257210212352, 521498112926154752, 425182149), (521498526669078528, 521498385555914752, 425182205), (521537713833281536, 520473302053979136, 425092346), (521537723329185792, 520949970577129472, 425091717), (521537734100158464, 520951502672494592, 425091848), (521537742153222144, 521098970978582528, 425114518), (521537749837187072, 521099145478406144, 425114528), (521537758477453312, 521445102007091200, 425172909), (521537766832506880, 521448689663737856, 425173243), (521537774432585728, 521453283114483712, 425173717), (521537782821193728, 521455261081141248, 425173914), (521537791746672640, 521472092403662848, 425175536), (521537799933954048, 521494591505956864, 425181819), (521537808892987392, 521498776448270336, 425182228), (522100429107005440, 522099579659782144, 27506910), (522104681275623424, 522103782234949632, 27506925), (522986409137570816, 521968508397978624, 426100907), (522988582659458048, 522527111777388544, 426192402), (522988584337179648, 522851674620952576, 426164626), (522988585729688576, 522852088128995328, 426164652), (522988588732810240, 522996729910394880, 426191033), (524435814587860992, 523677528716511232, 27507063), (525885430005008384, 524867998675142656, 428100930), (525885433662441472, 525124633087739904, 34567899), (525885459616794624, 525126915661531136, 42867801), (525885466629670912, 525127908587505664, 42867802), (525885473239894016, 525132774215026688, 42867805), (525885480403765248, 525135905179076608, 42867807), (525885488121284608, 525135992269604864, 42867808), (525885495201269760, 525137010462066688, 42867809), (525885502046373888, 525138583359621120, 27507273), (525885504630065152, 525140613318871040, 42867811), (525885511676495872, 525142571253532672, 42867813), (525885518303496192, 525142797091637248, 42867814), (525885524813056000, 525149594733021184, 27507300), (525885527547742208, 525151693327861760, 27507306), (525885530131433472, 525156430676789248, 27507315), (525885532396357632, 525160365739247616, 27507324), (525885534963271680, 525160954200098816, 27507327), (525885537446299648, 525162430863542272, 27507330), (525885539878995968, 525163648168003584, 27507333), (525885542328469504, 525164701324183552, 27507336), (525885544744388608, 525167124407158784, 27507345), (525885547160307712, 525167812843440128, 27507348), (525885549559449600, 525202939820673024, 27507351), (525885552076032000, 525213086915790848, 27507363), (525885554357733376, 525309925358864384, 428172831), (525887753481652224, 525310785962936320, 428172922), (525889950374201344, 525335095914039296, 428175331), (525895585270630400, 525766122155278336, 428170142), (525897786424592384, 525792993433616384, 428172823), (525899985061971968, 525873602067693568, 428184828), (528784470216574976, 525359597259622400, 428181752), (528784472045291520, 525400465886709760, 428185827), (528784473655904256, 525400649580447744, 428185839), (528784475300071424, 525401682469750784, 428185940), (528784503066363904, 525874921528623104, 428184947), (528784505079629824, 525877634337341440, 428185228), (528784506790905856, 525878741600043008, 428185334), (530796624276915200, 530796083480134656, 502121831), (530798368788612096, 530798285657506816, 502122043), (530800220406055936, 530800101858247680, 502122231), (530804945641833472, 530804843988681728, 502122714), (530805339738637312, 530805215184585728, 502122736), (530811443122045952, 530810641372447744, 502123259), (530814911392023552, 530812754462802944, 502123505), (530817195006002176, 530817079192880128, 502123923), (530817396231930880, 530817302162080768, 502123936), (530817565077832704, 530817477668537344, 502123947), (530818478379140096, 530818360569529344, 502124039), (530818753005388800, 530818670075610112, 502124058), (530818929317151744, 530818822563726336, 502124107), (530819223488857088, 530819131717485568, 502124125), (531683514957989888, 530706061418595328, 502104906), (531683528010663936, 530706167987471360, 502104912), (531683537204578304, 530706226724504576, 502104916), (531683546599819264, 530707657066054656, 502105041), (531683555424634880, 530707931105100800, 502105057), (531683564870207488, 530708027389543424, 502105103), (531683574349334528, 530708507117257728, 502105132), (531683586277935104, 530724163363570688, 502110705), (531683594616211456, 530726260465239040, 502110910), (531683603592022016, 530726471673611264, 502110922), (531683615805835264, 530760877599654912, 502114333), (531683625184299008, 530762205549855744, 502114452), (531683637196785664, 530762398789829632, 502114504), (531683646004824064, 530762541077398528, 502114512), (531683657681766400, 530778306425947136, 13177016), (531683666624022528, 530795290437911552, 502121744), (531683678804281344, 530798213632918528, 502122038), (531683688014972928, 530802336633750528, 502122444), (531683699641583616, 530804421085398016, 502122648), (531683708902606848, 530805150860739584, 502122732), (531683717442209792, 530816953867076608, 502123915), (531683729052043264, 530818205044737024, 502124030), (531683741467183104, 530988912982656000, 502153005), (531683750409439232, 530989247134467072, 502153025), (531683763009128448, 530991593579119616, 502153245), (531683775256496128, 530992079900279808, 502153314), (531683787822630912, 531001567936021504, 502154239), (531683796647446528, 531001685208761344, 502154246), (531683838070392832, 531174947075163136, 502183453), (532369186270249984, 532363438026949632, 50367303), (532412603691993088, 532257496635573248, 27508233), (532418519590012928, 532257496635573248, 27508233), (532447784406648832, 532930752069763072, 503153907), (532602486024865792, 532602372661217280, 503181255), (532614838015264768, 532232400822895616, 50367805), (532858911443648512, 532857291402117120, 503142608), (533133004160140288, 532120178561942528, 503101354), (533133033184723968, 532120514290811904, 503101413), (533133045482423296, 532121466381042688, 503101510), (533133059088745472, 532123116822889472, 503101649), (533133068618204160, 532133434240304128, 503102704), (533133079053632512, 532149602292368384, 27507864), (533133081251447808, 532151607035462656, 27507873), (533133083382154240, 532155774361699328, 503104915), (533133093129716736, 532156637650125824, 503105007), (533133102491403264, 532156795876049920, 27507900), (533133104773104640, 532159512442864640, 27507909), (533133106887033856, 532161373321331712, 27507918), (533133108967408640, 532162284038947840, 503105543), (533133121130890240, 532162452817740800, 503105553), (533133130693903360, 532162520245371904, 503105557), (533133141917860864, 532168699143029760, 27507930), (533133143914349568, 532174801352033280, 27507951), (533133146145719296, 532182403326706688, 27507990), (533133148309980160, 532185232787407872, 27508002), (533133150524572672, 532186066665374720, 27508008), (533133152353289216, 532197278476174336, 27508065), (533133154702099456, 532228324697605120, 503120119), (533133167402451968, 532286344655832064, 27508245), (533133169600267264, 532288470798861312, 27508251), (533133171814859776, 532363438026949632, 50367303), (533133180505457664, 532602372661217280, 503181255), (533133191276430336, 532603499603593216, 503181402), (533133201275651072, 532623628823463936, 503183402), (533133225401287680, 532856415664996352, 503142516), (533133238655288320, 532930752069763072, 503153907), (533805819947419648, 533804762060064768, 504140655), (533868484845668352, 533867738913867776, 27508374), (533875878245992448, 533875308340742144, 27508380), (533955588208494592, 533955114201810944, 27508395), (533964082882054144, 533963282776623104, 27508404), (533972031692113920, 533971217342826496, 27508410), (533974600199997440, 533973688911955968, 27508416), (533981312831227904, 533980294169004032, 27508422), (533986311971833856, 533985093274863616, 27508431), (533996131374368768, 533985093274863616, 27508431), (534128972716345344, 534128298909796352, 27508554), (534132798626236416, 534132240683141120, 27508566), (534582692759503872, 533804762060064768, 504140655), (534582714905428992, 533852095854446592, 504145424), (534582726699811840, 533852526945011712, 504145450), (540380782443661312, 532232400822895616, 50367806), (540380792442882048, 539688149962490880, 27508878), (540874375352584192, 540873048828446720, 27508920), (541157700520675328, 541156910028589056, 27509055), (541158514685413376, 541156910028589056, 27509055), (541167336397244416, 541166312450197504, 27509067), (541176529690855424, 541174896714090496, 27509076), (541177879770204160, 541177161470478336, 27509085), (541184471873914880, 541183037086402560, 27509091), (541188245522778112, 541187616427509760, 27509100), (541190631427442688, 541189938209654784, 27509106), (541194592326813696, 541193824802736128, 27509115), (541197433397348352, 541196686945454080, 27509124), (541208346506594304, 541207650872887296, 27509154), (541241865018574848, 541241693421209600, 50367106), (541250901881423872, 541221381833000960, 27509169), (541276428163909632, 541276166338676736, 509174933), (541276981946255360, 541276574410901504, 50367109), (541279147717068800, 541278993886775296, 50367111), (541285052978333696, 541221381833000960, 27509169), (541307932101347328, 541305521920051200, 27509226), (541316762856097792, 541316566948546560, 50367112), (541323664952428544, 541323109593024512, 27509262), (541323701778417664, 541323425893877760, 50367113), (541327819058219008, 541327209357412352, 27509268), (541329386301851648, 541329064045086720, 50961113), (541339766113732608, 541338869740636160, 27509310), (541342316418336768, 541342081000441856, 50961114), (541345748164315136, 541345261440501760, 27509322), (541348042834479104, 541346884501932032, 27509328), (541361833102051328, 541361063178831872, 27509361), (541830400176064512, 540827815893829632, 27508905), (541830410309502976, 540839223595403264, 27508908), (541830421835450368, 540871346997987328, 27508914), (541830430693820416, 540872324774135808, 27508917), (541830442723084288, 540873048828446720, 27508920), (541830451631785984, 541092450035337216, 509144703), (541830463610718208, 541106781670115328, 509150117), (541830476579506176, 541113190516295680, 509150739), (541830485806974976, 541136024592484352, 27509019), (541830495789418496, 541326072952686592, 50361113), (541830505302099968, 541329380547266560, 27509280), (541830514881890304, 541348730465454080, 27509337), (542243068988917760, 542241982714513408, 27509400), (542243324807907328, 542241982714513408, 27509400), (542259069285599232, 542258453846984704, 27509436), (542262491099465728, 542261802176645120, 27509448), (542264163955673088, 542262959619999744, 27509454), (542409774956839936, 542409050281772032, 27509571), (542410108773106688, 542409050281772032, 27509571), (542525008040199168, 542524434863391744, 27509610), (542539068286731264, 542537672573355008, 27509640), (542546901082342400, 542544941134414848, 27509652), (542549621843461120, 542549143357260800, 27509661), (542558065380066304, 542557381758845952, 27509679), (542564117794292736, 542563556780966912, 27509700), (542566337906508800, 542565110569272320, 27509706), (542857667182329856, 542857442350858240, 510120024), (542895674572668928, 542895544549244928, 510123815), (543279922429330432, 542285622065268736, 27509475), (543279956436747264, 542286714882787328, 27509478), (544729431344711680, 543824400013625344, 511120058), (544729457064183808, 544328783843295232, 511122202), (544729466140657664, 544330786975776768, 511122402), (544729477582718976, 544331333913018368, 511122435), (548437993971550208, 548437669164648448, 27510195), (548712613693917184, 548712404817577984, 27510555), (548713508187965440, 548713409873479680, 27510564), (549078076890318848, 548350900456949760, 514145740), (549080266249897984, 548351157114800128, 514145755), (549082466229454848, 548369405172549632, 27510051), (549082468477601792, 548437669164648448, 27510195), (549082497787398144, 548488971525588992, 27510225), (549082505421031424, 548490291707938816, 27510234), (549082512501016576, 548762317538494464, 27510693), (550527601106520064, 549509417150744576, 27510702), (550527621574723584, 549576625352640512, 27510747), (550527636992985088, 549608265319745536, 27510816), (550527671671490560, 549645159357253632, 27510831), (550527684690610176, 549654774815950848, 27510834), (550527697659398144, 549677671454377984, 27510840), (550527710594631680, 549757460856997888, 27510867), (550527726197442560, 549807474627086336, 27510882), (550527742756554752, 549841550662731776, 27510900), (550527755339466752, 549879330939897856, 27510957), (550527772502558720, 549879570535319552, 27510960), (550527789145556992, 550059353990793216, 27511044), (550527797416724480, 550059548858157056, 27511047), (551275553336887296, 551275150734034944, 27511227), (551278774562359296, 551277914025396224, 27511251), (551279894491858944, 551279404999804928, 27511263), (551281596959852544, 551280888609015808, 27511275), (551977198991475712, 550934246982715392, 516094325), (551977227814732800, 550953445419419648, 516100304), (551977239105799168, 550954541927925760, 516100409), (551977247309857792, 550967944658585600, 516101728), (551977261285278720, 551746771194216448, 516151109), (551977271435494400, 551748455307935744, 516151249), (551977282189689856, 552080761256148992, 516204256), (551977284102292480, 552083693477298176, 516204551), (551977285444469760, 552084881253859328, 516204702), (551977286786647040, 552090184397619200, 516205218), (551977288061715456, 552114399842664448, 516211622), (552425350525650944, 552425218975500288, 517102508), (552426726207031296, 552426644199999488, 517102633), (552427164327249920, 552427091145033728, 517102700), (552437052617033728, 552436095392974848, 27511425), (552438327098247168, 552437859433351168, 27511437), (552439715010217984, 552439253217347584, 27511452), (552465503604475904, 552465083251329024, 27511491), (552465817086756864, 552465083251329024, 27511491), (552467121683073024, 552466743994386432, 27511509), (552467591411566592, 552466743994386432, 27511509), (552472311664510976, 552471886395639808, 27511527), (552481022764156928, 552471886395639808, 27511527), (552488877386926080, 552487997807822848, 27511548), (552496540447442944, 552496039312001024, 27511578), (552511636804503552, 552509001724626944, 27511623), (552530441496725504, 552530093956696064, 27511689), (552531679470716928, 552530885824514048, 27511701), (552531682977155072, 552530885824514048, 27511701), (552532550677989376, 552532115678332928, 27511719), (552533916427257856, 552532932091217920, 27511731), (552534369495975936, 552532932091217920, 27511731), (552538513552214016, 552537283916498944, 27511749), (552684336332179456, 552683859808913408, 27511887), (552695791362278400, 552694864605643776, 27511914), (552778745971246080, 552777403659748352, 27511971), (552780490717824000, 552780012013519872, 27511983), (552781965351224320, 552781468527526912, 27511995), (552785046721823744, 552784574174757888, 27512007), (552785433772196864, 552784574174757888, 27512007), (552790762601874432, 552790297017353216, 27512037), (552793615718450176, 552792801721484288, 27512046), (552794877063103488, 552794285297140736, 27512055), (552797403174962176, 552796723966149632, 27512064), (552797778061853696, 552796723966149632, 27512064), (553426717671195648, 552421544245102592, 517102129), (553428903507559424, 552528362682222592, 27511668), (553431102430151680, 552545678832732160, 27511767), (553433305681265664, 552553926377900032, 27511806), (553435502926136320, 552575382222571520, 27511827), (553437700489774080, 552650834479974400, 517140839), (553439899429143552, 552800343214294016, 27512079), (553442100633437184, 553153203466665984, 517142755), (553444298079634432, 553179909288099840, 517145448), (553446496599573504, 553205557020852224, 517152020), (553448694783968256, 553236356784979968, 517155055), (554667344526345216, 554667168332022784, 518233139), (554876280340776960, 553878797858932736, 518102908), (554876283041908736, 553880794616072192, 34561892), (554876283964655616, 553897287089162240, 34561893), (554876284837070848, 553898585394022400, 34561894), (554876285743040512, 553901071727101952, 34561895), (554876286665787392, 553938317935151104, 34561897), (554876287554979840, 554141847207839744, 34561898), (554876288628721664, 554154389451868160, 34561810), (554876289585022976, 554157678004307968, 34561811), (554876290490992640, 554173498902218752, 34561812), (554876291346630656, 554256181049198592, 34561814), (554876292185491456, 554341084499575808, 34561821), (554876293057906688, 554369968355968000, 34561831), (554876294114871296, 554383345987912704, 34561832), (554876294970509312, 554398876438660096, 34561841), (554876295910033408, 554398876438660096, 34561842), (554876296782448640, 554398876438660096, 34561844), (554876297705195520, 554410963432932352, 34561851), (554876298728605696, 554410963432932352, 34561852), (554876299634575360, 554410963432932352, 34561853), (554876300523767808, 554410963432932352, 34561854), (554876301563955200, 554410963432932352, 34561861), (554876302587365376, 554417840866755584, 27512274), (554876330504652800, 554410963432932352, 34561871), (554876331410622464, 554410963432932352, 34561872), (554876332316592128, 554410963432932352, 34561873), (554876333189007360, 554410963432932352, 34561874), (558474116764566528, 558473901814875136, 521143358), (558474379210556416, 558474234993607680, 521143418), (558474725878170624, 558474532973741056, 521143436), (558534928736748544, 559017781761146880, 521153414), (558535364491379712, 558535243527652352, 521153454), (558546700772344832, 558545962440623104, 27512517), (558547871989793792, 558547272120435712, 27512529), (558557730785231872, 558557324524946432, 27512568), (558559094588343296, 558558610163008512, 27512577), (558560289159676928, 558559729186538496, 27512589), (558562089052963840, 558561409860928512, 27512601), (558562551030383616, 558561409860928512, 27512601), (558568295649805312, 558567884507350016, 27512625), (558572627157877760, 558572142061454336, 27512637), (558574442855302144, 558573843875136512, 27512649), (558576513281197056, 558575872844530688, 27512676), (558576836460708864, 558575872844530688, 27512676), (558584851440108544, 558582088920499200, 27512721), (558586309782832128, 558586142698537984, 521162528), (558723617974682624, 558723199181816832, 27512832), (558724551358317568, 558723199181816832, 27512832), (559224994924626944, 558442889131099136, 521140229), (559225003464229888, 558472970243179520, 521143302), (559225012020610048, 558497944018387968, 521145751), (559225020207891456, 558499697086465024, 521145935), (559636520839971840, 559632473504384000, 27512880), (559713189277762560, 554410963432932352, 34561871), (559714507581064192, 559714167456564224, 34561881), (559922944977961984, 559767552910197760, 52261001), (560227554594686976, 560225682290935808, 522193412), (560674602590700544, 559714167456564224, 34561881), (560674603496670208, 559767552910197760, 52261001), (560674604352308224, 559817052190180352, 27512913), (560674624333972480, 559928818530288640, 522143918), (560674626515010560, 560049882618955776, 522163934), (561107547122075648, 561107417316755456, 523101008), (561155840472549376, 561155622335186944, 523105801), (561378996118389760, 561377825806910464, 523143845), (561379333994742784, 561379262540579840, 523144011), (561481664560137216, 561481556313539584, 523162148), (561621769077687296, 561621546041377792, 52361027), (561864434377589760, 561566324925432832, 27513249), (561868165412324352, 561566324925432832, 27513249), (561893002000237568, 561615969730987008, 27513288), (561898955831542784, 561897833083470848, 27513384), (562124072817821696, 561107417316755456, 523101008), (562124073623128064, 561155413257521152, 523105748), (562124074545874944, 561155488234899456, 523105753), (562124075418290176, 561160289622460416, 27513009), (562124076273928192, 561226767109489664, 52361001), (562124077146343424, 561373258125968384, 523143413), (562126281068546048, 561377723449115648, 523143839), (562128480007915520, 561379092704822272, 523144001), (562130681061214208, 561393808386851840, 27513105), (562130702267615232, 561413438484546560, 27513117), (562130709834139648, 561444157080503296, 27513150), (562130710891104256, 561478359096263680, 27513165), (562130711797073920, 561480319732057088, 27513168), (562130712635934720, 561481054792221696, 27513171), (562130713609013248, 561481465381028864, 523162143), (562130714498205696, 561483222274311168, 27513174), (562130715353843712, 561487594483909632, 523162748), (562130716276590592, 561492832968279040, 27513180), (562130717165783040, 561498008907187200, 52361014), (562130718021421056, 561524555932271616, 27513195), (562130718944167936, 561525206905029632, 27513201), (562130719866914816, 561526863738995712, 27513210), (562130720823216128, 561532192115688448, 27513216), (562130721712408576, 561564979795362816, 27513240);

执行以下sql，得出执行计划
explain select * from `test_table` where `old_transaction_id` > 1;

执行计划如下：
id	select_type	table	    partitions	type	possible_keys	key	    key_len	 ref	rows	filtered	Extra
1	SIMPLE	    test_table	NULL	    ALL	    NULL	        NULL	NULL	 NULL	1000	33.33       Using where

id：表示查询执行的顺序，id相同时由上到下执行，id不同时由大到小执行，若为null，则表示为两个其他查询通过 union 得来的结果

select_type：有4种值
simple:  不包含子查询或union操作的查询
primary:  查询中如果包含任何子查询，那么最外层的查询则被标记为primary
subquery:  select列表中的子查询
dependent subquery:  依赖外部结果的子查询
简单理解：若sql只有1层子查询，为primary，若有多层子查询，最外层为primary，最底层为dependent subquery，中间层为subquery

table：输出数据所在的表，3情况
1.如果表格起了别名，就显示别名
2.<union M,N>由id为M，N查询union产生的结果集
3.<derived N> / <subquery N>由ID为N的查询产生的结果

partitions：只有在查询分区表时才有意义
对于分区表，显示查询的分区ID
对于非分区表，显示NULL

possob;e_keys：可能使用到的索引

keys：实际使用到的索引

key_len：实际使用索引的最大长度。比如1个联合索引总长度为100，但key_len可能不到100，那就说明查询
可能没有使用联合索引的所有列，这个值是由表中定义的长度来计算的，并不是实际存储的长度。
另外由于mysql innodb 最小单位是页（16K），索引越小，一页内能容纳的索引越多，所需IO越少，检索越快。
因此在设计数据库时要用满足数据的最小长度，有利于提高性能

ref：哪些列或常量被用于通过索引来进行数据过

rows：根据统计信息预估扫描的行数（预估值）

filtered：表示返回结果的行数占需读取行数的百分比（预估值）

Extra：不适合在其他页所显示的一些信息，常见值如下图

执行计划字段如下：
1. id	
2. select_type	
3. table	
4. partitions	
5. type	
6. possible_keys	
7. key	
8. key_len	
9. ref	
10. rows	
11. filtered	
12. Extra
上面 执行计划 的这些字段中，"rows" 是核心指标，绝大部分 "rows" 小的语句执行快(有例外)

示例1
explain select `id` from `test_table` where `id` = 358168218726073344;
执行计划如下：
id	select_type	table	    partitions	type	possible_keys	key	    key_len	 ref    rows filtered	Extra
1	SIMPLE	    test_table	NULL	    const	PRIMARY	        PRIMARY	8	     const	1    100.00     Using index

extra为'Using index'，说明查询的内容能在 btree中 查询到，不需要 "回表"，这是一次 "索引覆盖查询"

示例2
explain select `old_transaction_id` from `test_table` where `old_transaction_id` = 27488186;
执行计划如下
1	SIMPLE	test_table	NULL	ALL	NULL	NULL	NULL	NULL	1000	10.00	Using where

因为 "old_transaction_id" 没有索引，只能遍历所有行记录，一条一条查找，最终 "rows" 为1000。
只要查询1条数据，却要遍历table所有行记录，如果行记录更多，比如10000000，那时rows将会更大，更加耗时。
对比下加上索引的场景，将 "old_transaction_id" 加上索引，指向下面sql
alter table `test_table` add index `old_transaction_id`(`old_transaction_id`);
再次查询执行计划:
explain select `old_transaction_id` from `test_table` where `old_transaction_id` = 27488186;
执行计划如下:
1	SIMPLE	test_table	NULL	ref	old_transaction_id	old_transaction_id	9	const	2	100.00	Using index

"rows" 变为2，"extra" 变为 Using where

添加索引后，执行计划优化非常大，速度提高很多
因此用好索引，能极大提升sql的性能
另外要注意 extra 出现using temporary的情况，因为使用到了外部临时表，通常来说性能不太好

##总结

index: 索引，可存在相同的数据
unique index：唯一索引，不可存在相同的数据
primary index：主键索引，唯一，不可存在相同的数据

索引匹配最左前缀的查询，因此区分度最高的应放在联合索引的最前，这样能筛选的数据越高。如果用abc组成的索引，查询ac，那只能用到a的索引，无法跳过联合索引中间的字段
使用 or 运算符来关联多个查询条件，可能无法用到索引，但 in 查询是可以用到索引的。所以在优化sql时，有一种方法就是用in代替or，但如果in()表中数据太多，mysql优化器认为效率不好也可能用全局扫描的方法
索引并不是越多越好，增加索引会增加写操作的成本。太多的索引也会增加查询优化器的选择时间，因此要根据实际的需求去增加索引
在某些情况下，比如订单id 这种，因为经常用到，这时即使已经有关于订单ID 的联合索引，还是可能单独增加一个订单ID 的索引，以此提高性能
写sql语句时，应尽量往 “索引覆盖查询” 和 “回表查询” 这方面优化，避免那种需要遍历table所有行记录的场景

哥斯拉啊啊啊哦

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
2.btree索引和优化

索引的作用是什么大大减小服务器查找数据所需要的时间。索引类似目录，可以快速定位到某个区域，找到响应的数据，减少服务器扫描的数据量，效率快。而如果没有索引，只能一页一页翻找寻找需要的内容。在磁盘上表现就是要遍历扫描查找，查找的数据量大，效率慢。另外在索引通常是按键值顺序存放的，而数据行的物理地址则是随机分布的。使用索引进行查找能让随机IO，变成顺序IO，而磁盘的顺序IO，性能肯定好于随机IO。索引的类型主要有以下几种：1. Btree索引2. 自适应HASH索引3. 全文索引4. 空间索.
复制链接

扫一扫

专栏目录