WordPress站群 管理,网站建设的前景,建立网站的英语,网站建设html5模板1、前言
MySQL中数据表设计合理的索引对提高性能很有帮助。使用索引可以快速地定位表中的某条记录#xff0c;从而提高数据库查询的速度#xff0c;提高数据库的性能。 大多数情况下都#xff08;默认#xff09;采用B 树来构建索引。只是空间列类型的索引使R- 树#xf…1、前言
MySQL中数据表设计合理的索引对提高性能很有帮助。使用索引可以快速地定位表中的某条记录从而提高数据库查询的速度提高数据库的性能。 大多数情况下都默认采用B 树来构建索引。只是空间列类型的索引使R- 树并且MEMORY 表还支持hash 索引。其实用不用索引最终都是优化器说了算。 执行优化器是基于cost开销(CostBaseOptimizer) 它不是基于规则 Rule-BasedOptimizer也不是基于语义。另外 SQL 语句是否使用索引跟数据库版本、数据量、数据选择度都有关系。但是还是有一些明显的规则可以去判别是否索引失效比如最左匹配原则、数据类型转换、反向判断等。
1.1、索引类型
MySQL支持多种类型的索引结构包括Btree、Hash、R-tree和Full-text等。 Btree索引MySQL中最常用的一种索引结构。Btree是一种平衡树每个节点的度数在一个范围之内并且每个叶子节点都在同一层上。Btree索引可以在极短的时间内进行查找、排序和插入等操作适合于较小的索引和范围查询。 Hash索引一种使用哈希表实现的索引结构。哈希索引在存储数据时将key和值映射到一个哈希表中的位置查询时可以直接通过计算key的哈希值来快速访问数据。哈希索引适合等值查询即查询操作中通过“”来查询数据。 R-tree索引一种用于存储和查询基于位置的数据的索引结构。R-tree索引可以在地理信息系统GIS中使用用于查询特定区域内的位置数据。 Full-text索引一种用于对文本列进行全文搜索的索引结构。Full-text索引可以在文本中查找关键字和短语并返回匹配的行。Full-text索引适合于对大量文本数据进行搜索和过滤的场景。
1.2 分析工具EXPLAIN
例子
explain select * from person where age 9idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_age_IDXperson_age_IDX5const1100.0
字段含义id执行select查询语句的序号它是sql执行的顺序的标识sql按照id从大到小执行id相同的为一组从上到下执行select_type查询的类型也就是对应的是简单查询还是复杂查询table表名partitions当前查询匹配记录的分区。对于未分区的表返回nulltype连接类型有如下几种取值性能从好到坏排序systemconsteq_refrefindex_mergerangeindexALLpossible_keys展示当前查询可以使用哪些索引这一列的数据是在优化过程的早期创建的因此有些索引可能对于后续优化过程是没用的key表示MySQL实际选择的索引key_len索引使用的字节数。由于存储格式当字段允许为NULL时key_len比不允许为空时大1字节rowsMySQL估算会扫描的行数数值越小越好ref表示将哪个字段或常量和key列所使用的字段进行比较filtered表示符合查询条件的数据百分比最大100。用rows × filtered可获得和下一张表连接的行数。例如rows 1000filtered 50%则和下一张表连接的行数是500Extra展示有关本次查询的附加信息
1.2、优化器跟踪(Optimizer Trace)
这个优化器跟踪的目的是生成可被人类和程序读取的输出以帮助理解MySQL优化器所采取的决策和操作。 开启操作语法
SET SESSION OPTIMIZER_TRACEenabledon; # enable tracingstatement to trace; # like SELECT, EXPLAIN SELECT, UPDATE, DELETE...SELECT * FROM information_schema.OPTIMIZER_TRACE;[ repeat last two steps at will ]SET SESSION OPTIMIZER_TRACEenabledoff; # disable tracing下面举个例子 该例子用的数据下面person表中的可以参考2、数据准备。
explain select * from person where age not in (9); idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALLperson_age_IDX7100.0Using where
优化选择过程
SET optimizer_traceenabledon;
select * from person where age not in (9);
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_traceenabledoff; 结果 从分解过程中可以看到best_access_path中使用了scancost为2.4。
join_preparation完成SQL的准备工作。在这个阶段SQL语句会被格式化输出通配符*会被具体字段代替但不会进行等价改写动作。传入的SQL语句是select * from person where age not in (9)的结果。在完成了语句的补充、格式化后准备阶段结束并进入下一阶段。
join_optimization完成SQL语句的逻辑与物理优化的过程这其中的优化步骤比较多。在展开具体内容之前先解释下”select #”的问题。在输出中经常会看到有”select#:N”的字样它表示当前跟踪的结构体是属于第几个SELECT。如果语句中使用多个SELECT语句拼接如UNION或者有嵌套子查询中有SELECT会产生多个序号。
considered_execution_plans 对比实际的不同路径的成本。如果是多表关联且有存在执行顺序(如left/right join或straight_join来强制指定顺序)则在plan_prefix部分会有前置条件否则就按照所有可能性评估。
{steps: [{join_preparation: {select#: 1,steps: [{expanded_query: /* select#1 */ select person.id AS id,person.age AS age,person.code AS code,person.name AS name,person.weight AS weight,person.height AS height,person.deleted AS deleted from person where (person.age 9) limit 0,200}]}},{join_optimization: {select#: 1,steps: [{condition_processing: {condition: WHERE,original_condition: (person.age 9),steps: [{transformation: equality_propagation,resulting_condition: (person.age 9)},{transformation: constant_propagation,resulting_condition: (person.age 9)},{transformation: trivial_condition_removal,resulting_condition: (person.age 9)}]}},{substitute_generated_columns: {}},{table_dependencies: [{table: person,row_may_be_null: false,map_bit: 0,depends_on_map_bits: []}]},{ref_optimizer_key_uses: []},{rows_estimation: [{table: person,range_analysis: {table_scan: {rows: 7,cost: 4.5},potential_range_indexes: [{index: PRIMARY,usable: false,cause: not_applicable},{index: person_age_IDX,usable: true,key_parts: [age,id]},{index: person_code_IDX,usable: false,cause: not_applicable},{index: person_height_IDX,usable: false,cause: not_applicable}],setup_range_conditions: [],group_index_range: {chosen: false,cause: not_group_by_or_distinct},analyzing_range_alternatives: {range_scan_alternatives: [{index: person_age_IDX,ranges: [NULL age 9,9 age],index_dives_for_eq_ranges: true,rowid_ordered: false,using_mrr: false,index_only: false,rows: 7,cost: 10.41,chosen: false,cause: cost}],analyzing_roworder_intersect: {usable: false,cause: too_few_roworder_scans}}}}]},{considered_execution_plans: [{plan_prefix: [],table: person,best_access_path: {considered_access_paths: [{rows_to_scan: 7,access_type: scan,resulting_rows: 7,cost: 2.4,chosen: true}]},condition_filtering_pct: 100,rows_for_plan: 7,cost_for_plan: 2.4,chosen: true}]},{attaching_conditions_to_tables: {original_condition: (person.age 9),attached_conditions_computation: [],attached_conditions_summary: [{table: person,attached: (person.age 9)}]}},{refine_plan: [{table: person}]}]}},{join_execution: {select#: 1,steps: []}}]
}2、数据准备
version()5.7.28-log
创建数据库
CREATE DATABASE demo /*!40100 DEFAULT CHARACTER SET latin1 */;创建表age和height为普通索引code、name、weight为组合索引。
-- demo.person definitionCREATE TABLE person (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键id,age int(11) DEFAULT NULL COMMENT 年龄,code varchar(100) DEFAULT NULL COMMENT 编码,name varchar(50) DEFAULT NULL COMMENT 名字,weight int(3) DEFAULT NULL COMMENT 体重,height int(3) DEFAULT NULL COMMENT 身高,deleted tinyint(1) NOT NULL DEFAULT 0 COMMENT 是否删除 0 1,PRIMARY KEY (id),KEY person_age_IDX (age) USING BTREE,KEY person_code_IDX (code,name,weight) USING BTREE,KEY person_height_IDX (height) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT0 DEFAULT CHARSETutf8mb4;插入数据
INSERT INTO demo.person (age, code, name, weight, height, deleted) VALUES(12, 4545345345342, 奥特曼, 15, 89, 0);
INSERT INTO demo.person (age, code, name, weight, height, deleted) VALUES(52, 552421231332, 卫斯理, 21, 45, 0);
INSERT INTO demo.person (age, code, name, weight, height, deleted) VALUES(87, 85124561321, 福尔摩斯, 50, 180, 0);
INSERT INTO demo.person (age, code, name, weight, height, deleted) VALUES(56, 86454212, 爱因斯坦, 56, 190, 0);
INSERT INTO demo.person (age, code, name, weight, height, deleted) VALUES(45, 86454212, 爱威立雅, 108, 172, 0);
INSERT INTO demo.person (age, code, name, weight, height, deleted) VALUES(10, 8754534, 爱尔兰, 78, 169, 0);
INSERT INTO demo.person (age, code, name, weight, height, deleted) VALUES(9, 867564233, 小兰, 98, 150, 0);3、举例说明
1、普通索引
explain select * from personidselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALL7100.0
索引有效
explain select * from person where age 9idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_age_IDXperson_age_IDX5const1100.0
发生值类型转换9依然有效。有些博文中说这个不行万事没有绝对。
explain select * from person where age 9idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_age_IDXperson_age_IDX5const1100.0
索引失效
explain select * from person where age 9idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALLperson_age_IDX785.71Using where
索引有效in
explain select * from person where age in(9)idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_age_IDXperson_age_IDX5const1100.0
in索引不一定有效如下面的情形in的范围比较大时索引失效。
explain select * from person where age in (9,10,28,56,41,50,92,20,12,51,54,2,45,2)idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALLperson_age_IDX7100.0Using where
索引失效not in
explain select * from person where age not in (9)idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALLperson_age_IDX7100.0Using where
2、组合索引联合摄影
该组合索引是code,name,weight
最左匹配原则 MySQL 建立多列索引联合索引有最左匹配的原则即最左优先如果有一个 2 列的索引 (a, b)则已经对 (a)、(a, b) 上建立了索引如果有一个 3 列索引 (a, b, c)则已经对 (a)、(a, b)、(a, b, c) 上建立了索引。 只有code 索引有效
explain select * from person where code 11222 idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX403const1100.0
name 索引失效
explain select * from person where name 小兰idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALL714.29Using where
顺序为code,name时有效
explain select * from person where code 111 and name 小兰idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX606const,const1100.0
顺序为name,code时也有效优化器会优化为code,name。
explain select * from person where name 111 and code 小兰idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX606const,const1100.0
优化器分析过程
{steps: [{join_preparation: {select#: 1,steps: [{expanded_query: /* select#1 */ select person.id AS id,person.age AS age,person.code AS code,person.name AS name,person.weight AS weight,person.height AS height,person.deleted AS deleted from person where ((person.name 111) and (person.code 小兰)) limit 0,200}]}},{join_optimization: {select#: 1,steps: [{condition_processing: {condition: WHERE,original_condition: ((person.name 111) and (person.code 小兰)),steps: [{transformation: equality_propagation,resulting_condition: ((person.name 111) and (person.code 小兰))},{transformation: constant_propagation,resulting_condition: ((person.name 111) and (person.code 小兰))},{transformation: trivial_condition_removal,resulting_condition: ((person.name 111) and (person.code 小兰))}]}},{substitute_generated_columns: {}},{table_dependencies: [{table: person,row_may_be_null: false,map_bit: 0,depends_on_map_bits: []}]},{ref_optimizer_key_uses: [{table: person,field: code,equals: 小兰,null_rejecting: false},{table: person,field: name,equals: 111,null_rejecting: false}]},{rows_estimation: [{table: person,range_analysis: {table_scan: {rows: 7,cost: 4.5},potential_range_indexes: [{index: PRIMARY,usable: false,cause: not_applicable},{index: person_age_IDX,usable: false,cause: not_applicable},{index: person_code_IDX,usable: true,key_parts: [code,name,weight,id]},{index: person_height_IDX,usable: false,cause: not_applicable}],setup_range_conditions: [],group_index_range: {chosen: false,cause: not_group_by_or_distinct},analyzing_range_alternatives: {range_scan_alternatives: [{index: person_code_IDX,ranges: [小兰 code 小兰 AND 111 name 111],index_dives_for_eq_ranges: true,rowid_ordered: false,using_mrr: false,index_only: false,rows: 1,cost: 2.21,chosen: true}],analyzing_roworder_intersect: {usable: false,cause: too_few_roworder_scans}},chosen_range_access_summary: {range_access_plan: {type: range_scan,index: person_code_IDX,rows: 1,ranges: [小兰 code 小兰 AND 111 name 111]},rows_for_plan: 1,cost_for_plan: 2.21,chosen: true}}}]},{considered_execution_plans: [{plan_prefix: [],table: person,best_access_path: {considered_access_paths: [{access_type: ref,index: person_code_IDX,rows: 1,cost: 1.2,chosen: true},{access_type: range,range_details: {used_index: person_code_IDX},chosen: false,cause: heuristic_index_cheaper}]},condition_filtering_pct: 100,rows_for_plan: 1,cost_for_plan: 1.2,chosen: true}]},{attaching_conditions_to_tables: {original_condition: ((person.name 111) and (person.code 小兰)),attached_conditions_computation: [],attached_conditions_summary: [{table: person,attached: null}]}},{refine_plan: [{table: person}]}]}},{join_execution: {select#: 1,steps: []}}]
}code,name,weight顺序索引有效
explain select * from person where code 111 and name 小兰 and weight 52idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX611const,const,const1100.0
order by 索引有效
explain select * from person where code 111 and name 小兰 and weight 52 order by code,name,weightidselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX611const,const,const1100.0
explain select * from person where code 111 and name 小兰 and weight 52 order by name,weightidselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX611const,const,const1100.0
explain select * from person where code 111 and name 小兰 and weight 52 order by weight,nameidselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX611const,const,const1100.0
explain select * from person where code 111 and name 小兰 and weight 52 order by weight,ageidselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX611const,const,const1100.0Using index condition; Using filesort
code,weight,name顺序索引有效优化器会优化为code,name,weight
explain select * from person where code 111 and weight 52 and name 小兰idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX611const,const,const1100.0
code,weight索引有效索引有效值为 code
explain select * from person where code 111 and weight 52idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX403const114.29Using index condition
code,weight,name 索引有效索引有效值为 code
explain select * from person where code 111 and weight 52 and name 小兰idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrangeperson_code_IDXperson_code_IDX6111100.0Using index condition
length(code)包含函数索引失效但是这个不一定要看数据版本数据量等
explain select * from person where length(code)10idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALL7100.0Using where
索引有效
explain select count(1) from person where code 86454212idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX403const2100.0Using index
索引有效
explain select sum(age) from person where code 86454212idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrefperson_code_IDXperson_code_IDX403const2100.0
索引有效
explain select * from person where code like 86454212idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrangeperson_code_IDXperson_code_IDX4032100.0Using index condition
索引无效
explain select * from person where code like %86454212idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALL714.29Using where
索引有效
explain select * from person where code like 86454212%idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonrangeperson_code_IDXperson_code_IDX4032100.0Using index condition
or索引无效
explain select * from person where code 111 or name 小兰idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALLperson_code_IDX726.53Using where
组合索引和普通索引一起索引无效
explain select * from person where code 111 or age 9idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonALLperson_age_IDX,person_code_IDX726.53Using where
普通索引和普通索引一起索引无效
explain select * from person where age 9 or height 90idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEpersonindex_mergeperson_age_IDX,person_height_IDXperson_age_IDX,person_height_IDX5,52100.0Using union(person_age_IDX,person_height_IDX); Using where
3、总结
最佳左匹配法则重点计算、函数、类型转换(自动或手动)导致索引失效范围条件右边的列索引失效不等于(! 或者)导致索引失效is null可以使用索引is not null无法使用索引like以通配符%开头索引失效重点OR 前后只要存在非索引的列都会导致索引失效
