关于mysql:mysql索引优化实践

sql 执行计划计算

mysql 底层会对 sql 进行查问优化，根据各个计划所产生的 cost 成本计算最优执行计划，sql 的最终执行计划是否走了索引，抑或为什么没有走索引的起因能够用 trace 工具来剖析。
开启 trace：

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启 trace
mysql> select * from user where name > 'a' order by age;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

查看 trace 字段：

6 {
7 "steps": [
8 {
9 "join_preparation": { ‐‐第一阶段：SQL 筹备阶段，格式化 sql
10 "select#": 1,
11 "steps": [
12 {
13 "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`empl
oyees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from
`employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
14 }
15 ] /* steps */
16 } /* join_preparation */
17 },
18 {
19 "join_optimization": { ‐‐第二阶段：SQL 优化阶段
20 "select#": 1,
21 "steps": [
22 {
23 "condition_processing": { ‐‐条件解决
24 "condition": "WHERE",
25 "original_condition": "(`employees`.`name` >'a')",
26 "steps": [
27 {
28 "transformation": "equality_propagation",
29 "resulting_condition": "(`employees`.`name` >'a')"
30 },
31 {
32 "transformation": "constant_propagation",
33 "resulting_condition": "(`employees`.`name` >'a')"
34 },
35 {
36 "transformation": "trivial_condition_removal",
37 "resulting_condition": "(`employees`.`name` >'a')"
38 }
39 ] /* steps */
40 } /* condition_processing */
41 },
42 {
43 "substitute_generated_columns": {44} /* substitute_generated_columns */
45 },
46 {
47 "table_dependencies": [ ‐‐表依赖详情
48 {
49 "table": "`employees`",
50 "row_may_be_null": false,
51 "map_bit": 0,
52 "depends_on_map_bits": [53] /* depends_on_map_bits */
54 }
55 ] /* table_dependencies */
56 },
57 {
58 "ref_optimizer_key_uses": [59] /* ref_optimizer_key_uses */
60 },
61 {
62 "rows_estimation": [ ‐‐预估表的拜访老本
63 {
64 "table": "`employees`",
65 "range_analysis": {
66 "table_scan": { ‐‐全表扫描状况
67 "rows": 10123, ‐‐扫描行数
68 "cost": 2054.7 ‐‐查问老本
69 } /* table_scan */,
70 "potential_range_indexes": [ ‐‐查问可能应用的索引
71 {
72 "index": "PRIMARY", ‐‐主键索引
73 "usable": false,
74 "cause": "not_applicable"
75 },
76 {
77 "index": "idx_name_age_position", ‐‐辅助索引
78 "usable": true,
79 "key_parts": [
80 "name",
81 "age",
82 "position",
83 "id"
84 ] /* key_parts */
85 }
86 ] /* potential_range_indexes */,
87 "setup_range_conditions": [88] /* setup_range_conditions */,
89 "group_index_range": {
90 "chosen": false,
91 "cause": "not_group_by_or_distinct"
92 } /* group_index_range */,
93 "analyzing_range_alternatives": { ‐‐剖析各个索引应用老本
94 "range_scan_alternatives": [
95 {
96 "index": "idx_name_age_position",
97 "ranges": [
98 "a < name" ‐‐索引应用范畴
99 ] /* ranges */,
100 "index_dives_for_eq_ranges": true,
101 "rowid_ordered": false, ‐‐应用该索引获取的记录是否依照主键排序
102 "using_mrr": false,
103 "index_only": false, ‐‐是否应用笼罩索引
104 "rows": 5061, ‐‐索引扫描行数
105 "cost": 6074.2, ‐‐索引应用老本
106 "chosen": false, ‐‐是否抉择该索引
107 "cause": "cost"
108 }
109 ] /* range_scan_alternatives */,
110 "analyzing_roworder_intersect": {
111 "usable": false,
112 "cause": "too_few_roworder_scans"
113 } /* analyzing_roworder_intersect */
114 } /* analyzing_range_alternatives */
115 } /* range_analysis */
116 }
117 ] /* rows_estimation */
118 },
119 {
120 "considered_execution_plans": [
121 {
122 "plan_prefix": [123] /* plan_prefix */,
124 "table": "`employees`",
125 "best_access_path": { ‐‐最优拜访门路
126 "considered_access_paths": [ ‐‐最终抉择的拜访门路
127 {
128 "rows_to_scan": 10123,
129 "access_type": "scan", ‐‐拜访类型：为 scan，全表扫描
130 "resulting_rows": 10123,
131 "cost": 2052.6,
132 "chosen": true, ‐‐确定抉择
133 "use_tmp_table": true
134 }
135 ] /* considered_access_paths */
136 } /* best_access_path */,
137 "condition_filtering_pct": 100,
138 "rows_for_plan": 10123,
139 "cost_for_plan": 2052.6,
140 "sort_cost": 10123,
141 "new_cost_for_plan": 12176,
142 "chosen": true
143 }
144 ] /* considered_execution_plans */
145 },
146 {
147 "attaching_conditions_to_tables": {148 "original_condition": "(`employees`.`name` >'a')",
149 "attached_conditions_computation": [150] /* attached_conditions_computation */,
151 "attached_conditions_summary": [
152 {
153 "table": "`employees`",
154 "attached": "(`employees`.`name` >'a')"
155 }
156 ] /* attached_conditions_summary */
157 } /* attaching_conditions_to_tables */
158 },
159 {
160 "clause_processing": {
161 "clause": "ORDER BY",
162 "original_clause": "`employees`.`position`",
163 "items": [
164 {
165 "item": "`employees`.`position`"
166 }
167 ] /* items */,
168 "resulting_clause_is_simple": true,
169 "resulting_clause": "`employees`.`position`"
170 } /* clause_processing */
171 },
172 {
173 "reconsidering_access_paths_for_index_ordering": {
174 "clause": "ORDER BY",
175 "steps": [176] /* steps */,
177 "index_order_summary": {
178 "table": "`employees`",
179 "index_provides_order": false,
180 "order_direction": "undefined",
181 "index": "unknown",
182 "plan_changed": false
183 } /* index_order_summary */
184 } /* reconsidering_access_paths_for_index_ordering */
185 },
186 {
187 "refine_plan": [
188 {
189 "table": "`employees`"
190 }
191 ] /* refine_plan */
192 }
193 ] /* steps */
194 } /* join_optimization */
195 },
196 {
197 "join_execution": { ‐‐第三阶段：SQL 执行阶段
198 "select#": 1,
199 "steps": [200] /* steps */
201 } /* join_execution */
202 }
203 ] /* steps */
204 }
205
206 论断：全表扫描的老本低于索引扫描，所以 mysql 最终抉择全表扫描
207
208 mysql> select * from employees where name > 'zzz' order by position;
209 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
210
211 查看 trace 字段可知索引扫描的老本低于全表扫描，所以 mysql 最终抉择索引扫描
212

（↑↑↑援用↑↑↑）

mysql> set session optimizer_trace="enabled=off"; ‐‐敞开 trace

索引优化

外围 1：索引排序分为 Using index 和 Using filesort，其中 Using filesort 较慢，如果呈现则须要思考应用 Using index 进行优化。
Using filesort 的呈现起因是因为内存大小有余，无奈寄存须要排序的值，所以采纳了内部文件排序，如果能放得下则会采取 Using index。

filesort 的单路排序和双路排序
单路排序：单路排序是一次性取出所有的数据在内存中排序
双路排序：双路排序是只取出须要排序的字段和索引 ID，排序后再依据 id 回表取出所有的数据
有点相似于汇集索引和辅助索引的查问形式区别
MySQL 通过比拟零碎变量 max_length_for_sort_data(默认 1024 字节) 的大小和须要查问的字段总大小来
判断应用哪种排序模式。如果 字段的总长度小于 max_length_for_sort_data，那么应用 单路排序模式；如果 字段的总长度大于 max_length_for_sort_data，那么应用 双路排序模式。简略说就是看放不放得下，放得下就间接都取出来，放不下就只取一部分

外围 2：所有的优化准则都是基于 B +tree 构建的索引树，辅助索引的检索优先于汇集索引，组合索引的最左前缀准则意味着组合起因里每一个索引字段都是后面那个索引字段的细分，必须依序执行，否则无奈胜利索引。

外围 3：范畴查问应该放在查问的最初一位，一般来说范畴查问后的查问条件无奈匹配到对应的索引，如果有多个范畴查问能够思考应用冗余的常量字段来代替范畴查问。

外围 4：如果非必要，在查问时尽量应用笼罩索引代替 select * from 的形式来查找，能够大大晋升查问效率。

索引设计准则
1、代码后行，索引后上
2、联结索引尽量笼罩条件
3、不要在小基数字段上建设索引
4、长字符串咱们能够采纳前缀索引
5、where 与 order by 抵触时优先 where
6、基于慢 sql 查问做优化

Order by 与 Group by 优化
应用级联索引能够使 order by 和 group by 的执行速度大幅度放慢，但不能违反最左前缀法令，条件和 orderby 或者 groupby 必须以索引建设的程序进行查问。
惟一的例外是

select * from user where name='a' and age=12 order by height，age;

user 表中存在一个 name，height，age 的索引，以上查问是能够走索引的，因为 age 的值 12 是常量，在执行的时候会被优化，所以最终走的索引仍旧是 name，height，age。

分页查问优化

limit 分页的执行原理：

select * from user limit 100,10

以上查问 mysql 会先查问出表中的前 100 条数据，而后舍弃掉，再查问 10 条，所以一共是查问了 110 条数据，在数据量过大时效率会比较慢，能够采纳以下优化形式。

select * from user u inner join (select id from user order by uid limit 90000,5) ud
on u.id = ud.id;

这种查问形式只查 ID，而后应用 ID 的主键索引进行回表检索，会大幅度缩小查问的数据量，在大数据查问时候无效的防止了 filesort 形式的排序。

Join 关联查问优化
join 的查问形式分两种
1. 嵌套循环链接（Nested-Loop Join(NLJ)）

1. 从驱动表中读取一行数据（如果驱动表表有查问过滤条件的，会从过滤后果里取出一行数据）；2. 从第 1 步的数据中，取出关联字段，到被驱动表中查找；3. 取出被驱动表中满足条件的行，跟驱动表中获取到的后果合并，作为后果返回给客户端；4. 反复下面 3 步。

2. 基于块的嵌套循环链接（Block Nested-Loop Join(BNL)）

1. 把驱动表的所有数据放入到 join_buffer 中
2. 把被驱动表中每一行取出来，跟 join_buffer 中的数据做比照
3. 返回满足 join 条件的数据
ps：如果 join_buffer 中放不下则分批把驱动表中的数据放入进去

mysql 优化时 inner join 会主动选取较小的表作为驱动表，但并非 100% 抉择正确
left join 左侧为驱动表
right join 右侧为驱动表

优化重点：
关联字段加索引 ，让 mysql 做 join 操作时尽量抉择 NLJ 算法
小表驱动大表，写多表连贯 sql 时如果明确晓得哪张表是小表能够用 straight_join 写法固定连贯驱动形式，省去
mysql 优化器本人判断的工夫

in 和 exsits 优化
优化重点：小表驱动大表

count(*)查问优化
count（*）是最快的查问总数的函数，执行效率如下：

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) // 字段有索引，count(字段)统计走二级索引，二
级索引存储数据比主键索引少，所以 count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) // 字段没有索引 count(字段)统计走不了索引，count(主键 id)还能够走主键索引，所以 count(主键 id)>count(字段)