2021-01-16 - mysql優(yōu)化

分頁優(yōu)化

表結(jié)構(gòu)如下

CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年齡',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時(shí)間',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='員工記錄表'

根據(jù)主鍵的查詢排序

select * from employees limit 10000,10;

sql查詢 會(huì)用到索引嗎黄鳍?


image.png
select * from employees where id > 10000 limit   10;

image.png
image.png

非主鍵的排序分頁查詢

sql查詢 會(huì)用到索引嗎推姻?

select * from employees  ORDER BY name limit 10
select * from employees  ORDER BY name limit 10,10;
select * from employees  ORDER BY name limit 100,10框沟;
select * from employees  ORDER BY name limit 1000,10藏古;
select name from employees  ORDER BY name limit 1000,10  ;
image.png

按照B+Tree的結(jié)構(gòu)增炭,應(yīng)該會(huì)走name字段索引,但是拧晕,操作的結(jié)果集太多隙姿,又要回表等等原因 , MySQL可能不選name 字段的索引 厂捞, key 字段對(duì)應(yīng)的值為 null 输玷,從而走了全表掃描 。靡馁。

 select * from employees a inner join (select id from employees order by name limit 10000,10) b on a.id = b.id;


image.png
image.png

索引優(yōu)化

select * from employees where name > 'a';
select * from employees where name > 'zzz';
image.png

SQL優(yōu)化

  1. 條件優(yōu)化
  2. 計(jì)算全表掃描成本
  3. 根據(jù)查詢條件饲嗽,找出所有可用的索引
  4. 計(jì)算各個(gè)索引的訪問成本
  5. 選擇成本最小的索引以及訪問方式

開啟查詢優(yōu)化器日志

為了能查看查詢優(yōu)化器優(yōu)化的細(xì)節(jié),我們需要開啟查詢優(yōu)化器日志奈嘿。

--開啟
set optimizer_trace="enabled=on";

--執(zhí)行sql
--查詢?nèi)罩拘畔?select * from information_schema.OPTIMIZER_TRACE;

--關(guān)閉
set optimizer_trace="enabled=off";

{
    "steps": [
        {
            "join_preparation": {
                "select#": 1,
                "steps": [
                    {
                        "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position` limit 0,200"
                    }
                ]
            }
        },
        {
            "join_optimization": {
                "select#": 1,
                "steps": [
                    {
                        "condition_processing": {
                            "condition": "WHERE",
                            "original_condition": "(`employees`.`name` > 'a')",
                            "steps": [
                                {
                                    "transformation": "equality_propagation",
                                    "resulting_condition": "(`employees`.`name` > 'a')"
                                },
                                {
                                    "transformation": "constant_propagation",
                                    "resulting_condition": "(`employees`.`name` > 'a')"
                                },
                                {
                                    "transformation": "trivial_condition_removal",
                                    "resulting_condition": "(`employees`.`name` > 'a')"
                                }
                            ]
                        }
                    },
                    {
                        "substitute_generated_columns": {}
                    },
                    {
                        "table_dependencies": [
                            {
                                "table": "`employees`",
                                "row_may_be_null": false,
                                "map_bit": 0,
                                "depends_on_map_bits": []
                            }
                        ]
                    },
                    {
                        "ref_optimizer_key_uses": []
                    },
                    {
                        "rows_estimation": [
                            {
                                "table": "`employees`",
                                "range_analysis": {
                                    "table_scan": {
                                        "rows": 100166,
                                        "cost": 10107
                                    },
                                    "potential_range_indexes": [
                                        {
                                            "index": "PRIMARY",
                                            "usable": false,
                                            "cause": "not_applicable"
                                        },
                                        {
                                            "index": "idx_name_age_position",
                                            "usable": true,
                                            "key_parts": [
                                                "name",
                                                "age",
                                                "position",
                                                "id"
                                            ]
                                        }
                                    ],
                                    "setup_range_conditions": [],
                                    "group_index_range": {
                                        "chosen": false,
                                        "cause": "not_group_by_or_distinct"
                                    },
                                    "skip_scan_range": {
                                        "potential_skip_scan_indexes": [
                                            {
                                                "index": "idx_name_age_position",
                                                "usable": false,
                                                "cause": "query_references_nonkey_column"
                                            }
                                        ]
                                    },
                                    "analyzing_range_alternatives": {
                                        "range_scan_alternatives": [
                                            {
                                                "index": "idx_name_age_position",
                                                "ranges": [
                                                    "a < name"
                                                ],
                                                "index_dives_for_eq_ranges": true,
                                                "rowid_ordered": false,
                                                "using_mrr": false,
                                                "index_only": false,
                                                "rows": 50083,
                                                "cost": 17529,
                                                "chosen": false,
                                                "cause": "cost"
                                            }
                                        ],
                                        "analyzing_roworder_intersect": {
                                            "usable": false,
                                            "cause": "too_few_roworder_scans"
                                        }
                                    }
                                }
                            }
                        ]
                    },
                    {
                        "considered_execution_plans": [
                            {
                                "plan_prefix": [],
                                "table": "`employees`",
                                "best_access_path": {
                                    "considered_access_paths": [
                                        {
                                            "rows_to_scan": 100166,
                                            "filtering_effect": [],
                                            "final_filtering_effect": 0.5,
                                            "access_type": "scan",
                                            "resulting_rows": 50083,
                                            "cost": 10105,
                                            "chosen": true
                                        }
                                    ]
                                },
                                "condition_filtering_pct": 100,
                                "rows_for_plan": 50083,
                                "cost_for_plan": 10105,
                                "chosen": true
                            }
                        ]
                    },
                    {
                        "attaching_conditions_to_tables": {
                            "original_condition": "(`employees`.`name` > 'a')",
                            "attached_conditions_computation": [
                                {
                                    "table": "`employees`",
                                    "rechecking_index_usage": {
                                        "recheck_reason": "low_limit",
                                        "limit": 200,
                                        "row_estimate": 50083
                                    }
                                }
                            ],
                            "attached_conditions_summary": [
                                {
                                    "table": "`employees`",
                                    "attached": "(`employees`.`name` > 'a')"
                                }
                            ]
                        }
                    },
                    {
                        "optimizing_distinct_group_by_order_by": {
                            "simplifying_order_by": {
                                "original_clause": "`employees`.`position`",
                                "items": [
                                    {
                                        "item": "`employees`.`position`"
                                    }
                                ],
                                "resulting_clause_is_simple": true,
                                "resulting_clause": "`employees`.`position`"
                            }
                        }
                    },
                    {
                        "reconsidering_access_paths_for_index_ordering": {
                            "clause": "ORDER BY",
                            "steps": [],
                            "index_order_summary": {
                                "table": "`employees`",
                                "index_provides_order": false,
                                "order_direction": "undefined",
                                "index": "unknown",
                                "plan_changed": false
                            }
                        }
                    },
                    {
                        "finalizing_table_conditions": [
                            {
                                "table": "`employees`",
                                "original_table_condition": "(`employees`.`name` > 'a')",
                                "final_table_condition   ": "(`employees`.`name` > 'a')"
                            }
                        ]
                    },
                    {
                        "refine_plan": [
                            {
                                "table": "`employees`"
                            }
                        ]
                    },
                    {
                        "considering_tmp_tables": [
                            {
                                "adding_sort_to_table": "employees"
                            }
                        ]
                    }
                ]
            }
        },
        {
            "join_execution": {
                "select#": 1,
                "steps": [
                    {
                        "sorting_table": "employees",
                        "filesort_information": [
                            {
                                "direction": "asc",
                                "expression": "`employees`.`position`"
                            }
                        ],
                        "filesort_priority_queue_optimization": {
                            "limit": 200,
                            "chosen": true
                        },
                        "filesort_execution": [],
                        "filesort_summary": {
                            "memory_available": 262144,
                            "key_size": 40,
                            "row_size": 186,
                            "max_rows_per_buffer": 201,
                            "num_rows_estimate": 100166,
                            "num_rows_found": 100000,
                            "num_initial_chunks_spilled_to_disk": 0,
                            "peak_memory_used": 38994,
                            "sort_algorithm": "std::stable_sort",
                            "unpacked_addon_fields": "using_priority_queue",
                            "sort_mode": "<fixed_sort_key, additional_fields>"
                        }
                    }
                ]
            }
        }
    ]
}
image.png
image.png
image.png
![image.png](https://upload-images.jianshu.io/upload_images/14736547-edd30ef7462b6052.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ![image.png](https://upload-images.jianshu.io/upload_images/14736547-e98800097d3dee19.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ![image.png](https://upload-images.jianshu.io/upload_images/14736547-dfbda166378ef541.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ![image.png](https://upload-images.jianshu.io/upload_images/14736547-ef47a7e20105ecf6.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ![image.png](https://upload-images.jianshu.io/upload_images/14736547-00c0e6ab78eb02b7.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

![image.png](https://upload-images.jianshu.io/upload_images/14736547-f03e07475865462b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ![image.png](https://upload-images.jianshu.io/upload_images/14736547-65068c4ea4456307.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

![image.png](https://upload-images.jianshu.io/upload_images/14736547-d4cadc4c0a989231.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ![image.png](https://upload-images.jianshu.io/upload_images/14736547-81b4bcdf8d712baa.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240) ![image.png](https://upload-images.jianshu.io/upload_images/14736547-0553404eb1cd9a3b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

image.png

image.png

image.png

image.png

image.png

image.png
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末貌虾,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子裙犹,更是在濱河造成了極大的恐慌尽狠,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,123評(píng)論 6 490
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件叶圃,死亡現(xiàn)場離奇詭異袄膏,居然都是意外死亡,警方通過查閱死者的電腦和手機(jī)掺冠,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,031評(píng)論 2 384
  • 文/潘曉璐 我一進(jìn)店門沉馆,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人德崭,你說我怎么就攤上這事斥黑。” “怎么了眉厨?”我有些...
    開封第一講書人閱讀 156,723評(píng)論 0 345
  • 文/不壞的土叔 我叫張陵锌奴,是天一觀的道長。 經(jīng)常有香客問我憾股,道長鹿蜀,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,357評(píng)論 1 283
  • 正文 為了忘掉前任服球,我火速辦了婚禮茴恰,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘斩熊。我一直安慰自己往枣,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,412評(píng)論 5 384
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著婉商,像睡著了一般。 火紅的嫁衣襯著肌膚如雪渣叛。 梳的紋絲不亂的頭發(fā)上丈秩,一...
    開封第一講書人閱讀 49,760評(píng)論 1 289
  • 那天,我揣著相機(jī)與錄音淳衙,去河邊找鬼蘑秽。 笑死,一個(gè)胖子當(dāng)著我的面吹牛箫攀,可吹牛的內(nèi)容都是我干的肠牲。 我是一名探鬼主播,決...
    沈念sama閱讀 38,904評(píng)論 3 405
  • 文/蒼蘭香墨 我猛地睜開眼靴跛,長吁一口氣:“原來是場噩夢啊……” “哼缀雳!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起梢睛,我...
    開封第一講書人閱讀 37,672評(píng)論 0 266
  • 序言:老撾萬榮一對(duì)情侶失蹤肥印,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后绝葡,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體深碱,經(jīng)...
    沈念sama閱讀 44,118評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,456評(píng)論 2 325
  • 正文 我和宋清朗相戀三年藏畅,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了敷硅。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,599評(píng)論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡愉阎,死狀恐怖绞蹦,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情榜旦,我是刑警寧澤坦辟,帶...
    沈念sama閱讀 34,264評(píng)論 4 328
  • 正文 年R本政府宣布,位于F島的核電站章办,受9級(jí)特大地震影響锉走,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜藕届,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,857評(píng)論 3 312
  • 文/蒙蒙 一挪蹭、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧休偶,春花似錦梁厉、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,731評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽八秃。三九已至,卻和暖如春肉盹,著一層夾襖步出監(jiān)牢的瞬間昔驱,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,956評(píng)論 1 264
  • 我被黑心中介騙來泰國打工上忍, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留骤肛,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 46,286評(píng)論 2 360
  • 正文 我出身青樓窍蓝,卻偏偏與公主長得像腋颠,于是被迫代替她去往敵國和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子吓笙,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,465評(píng)論 2 348

推薦閱讀更多精彩內(nèi)容