解锁 SQL 查询性能 深入理解关系数据库的内部机制与优化策略-李小燕.pdf

1、解锁SQL查询性能TheTradeDesk/Lisa LiLisa LiEver worked in IBM,Intel:storage,distributed systemJoined in TheTradeDesk in 2021,focus on Retail related business.Self introductionProgrammatic advertisingBuy SideSell SidePublisherPublisher(NY Times,Spotify,Coupang)Supply-Side PlatformSupply-Side Platform(Google

2、,Magnite)UserUserAdvertiserAdvertiser(BMW,Pizza Hut,P&G)Third-party Data Providers(Oracle,LiveRamp)Demand-SideDemand-Side PlatformPlatformAgencyAgency(WPP,Publicis)$First-party DataTheTradeDeskAgendaWhy investigate the topicUnderstanding Indexes and execution plansOptimizing with examplesItem-level

3、events for targeting and measurement Item-level events for targeting and measurementTables:Merchant MerchantProduct AdvertiserProductProductIdSourceProductIdMerchantIdProductName11911856931Shampoo11912MX986752ConditionerProblemUse SQL server to manage metadataData increased 10 x timesTimeout may hap

4、penUnderstand how query works and OptimizeProductCountBefore1,901,935After24,686,272IndexesWhat are indexes?Contain sorted data of the columns in the index definitionOn-disk structure associated with a table or viewContains keys built from one or more columns in a table or viewWhy use indexes?Speed

5、up the retrieval of dataReduce disk IOsImpactData updates and deletesB-Tree Data StructureSelf-balanced tree data structure that is a generalized form of the Binary Search Tree(BST).Each node contains at most a fixed number of keysAll leaf nodes are at the same level.Time complexity:O(logn),wherenis

6、 the number of keys stored in the treeFrom https:/www.geeksforgeeks.org/introduction-of-b-tree/IndexesClustered Index Sort data rows in the table itself Only one clustered index is allowed in a table A table without a clustered index is called HEAPNon-Clustered IndexA structure separate from the dat