高性能MySql进化论(九):查询优化器常用的优化方式_MySQL
bitsCN.com
1 介绍
1.1 处理流程
当MYSQL 收到一条查询请求时，会首先通过关键字对SQL语句进行解析，生成一颗“解析树”，然后预处理器会校验“解析树”是否合法(主要校验数据列和表明是否存在，别名是否有歧义等)，当“解析树”被认为合法后，查询优化器会对这颗“解析树”进行优化，并确定它认为最完美的执行计划。
1.2 衡量标准
MYSQL查询优化器衡量某个执行计划是否完美的标准是“使用该执行计划时的成本”，该成本的最小单位是读取一个4K数据页的成本。
下面图中的数据说明，当使用id为条件查询时，查询的成本只有一个数据页，而使用mean(非索引)查询时，成本将近是22334个数据页
1.3 优化手段
下面列出了一些优化器常用的优化手段，但实际上远远不止这些
1. 重新定义关联表的顺序
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
AMD Ryzen 9900X, 9700X, 9600X processor Cinebench R23 running scores exposed, with an average increase of 10~15%
Jul 29, 2024 am 11:38 AM
According to news from this website on July 29, AMD Ryzen 9000 series processors are now available for reservation on JD.com. The first batch of four models will be launched and will be released on August 15. The evaluation data of these processors will be lifted on August 14, the day before they go on sale. However, some media or institutions have obtained samples in advance and started testing, so the running score data of R99900X, R79700X, and R59600X processors have been leaked. ▲Picture source: @9550pro Overall, the move from Zen4 to Zen5 is expected to bring about a 10% to 15% improvement in single-core performance and a 10% to 13% improvement in multi-core performance, but the TDP is somewhat lower than that of the Ryzen 7000 series. Reduced, which is also in line with AMD’s official IPC improvement data. Ryzen
Jingyue actual measurement: AMD R7 8700F, R5 8400F coreless graphics processor performance is similar to 8700G and 7500F
Apr 06, 2024 am 09:01 AM
According to news from this website on April 5, Jingyue officially confirmed that the AMDR78700F and R58400F coreless graphics processors exposed last month are models specially supplied to China, and released the first test video on the entire network, confirming that both have no core graphics and TDP The configurations are all 65W. In terms of specifications, AMD Ryzen78700F has 8 cores and 16 threads, a base frequency of 4.1GHz, an acceleration frequency of 5.05GHz, which is 0.10/0.05GHz lower than Ryzen78700G, and is equipped with a 16MB L3 cache. AMD Ryzen58400F adopts a 6-core 12-thread design with a frequency of 4.2~4.75GHz. Compared with the R57500F, the base frequency is increased by 0.1GHz and the acceleration frequency is reduced by 0.
144-core, 3D-stacked SRAM: Fujitsu details next-generation data center processor MONAKA
Jul 29, 2024 am 11:40 AM
According to news from this website on July 28, foreign media TechRader reported that Fujitsu introduced in detail the FUJITSU-MONAKA (hereinafter referred to as MONAKA) processor planned to be shipped in 2027. MONAKACPU is based on the "cloud native 3D many-core" architecture and adopts the Arm instruction set. It is oriented to the data center, edge and telecommunications fields. It is suitable for AI computing and can realize mainframe-level RAS1. Fujitsu said that MONAKA will achieve a leap in energy efficiency and performance: thanks to technologies such as ultra-low voltage (ULV) technology, the CPU can achieve 2 times the energy efficiency of competing products in 2027, and cooling does not require water cooling; in addition, the application performance of the processor It can also reach twice as much as your opponent. In terms of instructions, MONAKA is equipped with vector
AMD Ryzen 9 9950X overclocked to 6.6 GHz, CineBench R23 achieved a maximum score of 55296 points
Jul 17, 2024 pm 09:49 PM
According to news from this website on July 16, the AMDXOC team demonstrated the overclocking Ryzen 99950X processor to the invited media and guests at the Zen5 Technology Day. The processor was overclocked using liquid nitrogen (LN2) and scored more than 5.5 in CineBenchR23. million, and the power consumption is as high as 552W. The overclocking platform used by the XOC team is the ASUS X670EROG CorsshairGene motherboard, which is a motherboard specially designed for overclocking players and equipped with 2 DDR5DIMMs. After using liquid nitrogen, the operating temperature of the Ryzen 99950X processor dropped to minus 90 degrees Celsius, the power consumption was 552W, the CPU was overclocked at 6.4GHz, and the score in CineBenchR23 exceeded 55296
Multi-core exceeds 100,000, AMD EPYC 9755 processor CPU-Z running score exposed: 14% faster than EPYC 9654
Jul 25, 2024 am 10:46 AM
According to news from this website on July 25, the source HXL (@9550pro) tweeted yesterday (July 24), sharing information about the Zen5-based AMDEPYC9755 "Turin" CPU, which achieved excellent results in the CPU-Z benchmark test. score. AMDEPYC9755 "Turin" CPU information EPYC9755 is AMD's fifth-generation EPYC family product, equipped with 128 cores and 256 threads on Zen5 architecture. The base clock frequency of the EPYC9755 processor is 2.70GHz, and the acceleration clock frequency can reach 4.10GHz. Compared with the previous generation, the number of cores/threads has increased by 33%, and the clock frequency has increased by 11%. EPYC9755
Kirin 8000 processor competes with Snapdragon series: Who can be king?
Mar 25, 2024 am 09:03 AM
In the era of mobile Internet, smartphones have become an indispensable part of people's daily lives. The performance of smartphones often directly determines the quality of user experience. As the "brain" of a smartphone, the performance of the processor is particularly important. In the market, the Qualcomm Snapdragon series has always been a representative of strong performance, stability and reliability, and recently Huawei has also launched its own Kirin 8000 processor, which is said to have excellent performance. For ordinary users, how to choose a mobile phone with strong performance has become a key issue. Today we will
How to choose i5 i7 R5 R7? Attachment: Comparison and differences between i5 i7 R5 R7
Mar 09, 2024 am 10:34 AM
I believe everyone has had this dilemma when purchasing a notebook: How should I choose between AMD and Intel when the memory, hard drive, and graphics card are the same? How should I choose the processor models such as i5i7R5R7? Next, the author will answer them one by one... What is the difference between 01i5 and i7? Difference 1: The number of cores and threads is different. For example: Take the Lenovo Savior Y7000 2021 model as an example. It has two processor versions, the 11th generation i5-11400H and i7-11800H. The i5-11400H has 6 cores and 12 threads, and the i7 -1165G7 has 8 cores and 16 threads. The greater the number of CPU cores, the stronger the overall performance of the CPU. We can understand the CPU as a supermarket, the line
AMD Ryzen 7 9700X processor appears on Geekbench: single-core running score is 14% higher than R7 7700X
Jul 12, 2024 pm 01:59 PM
According to news from this website on July 9, an ASUS test machine equipped with AMD Ryzen 79700X processor appeared in the Geekbench database, equipped with ROG CROSSHAIRX670EHERO motherboard and 32GBDDR56000 memory. AMD Ryzen 79700X has 8 cores and 16 threads, 3.8GHz base frequency, 5.5GHz acceleration frequency, 40MB cache (note on this site: 32MBL3+8MBL2) and 65W TDP design, but there is also news that AMD has increased its TDP to 120W. As shown in the figure, the test platform ran single and multi-core scores of 3312 points and 16431 points on Geekbench6.3.0, which is better than R77700.
