0. 文章内容
凡是创建的普通的索引默认是会跟主键做一个联合索引
idx_col(col) => idx_col(col, id)
1. 索引(索引是什么，怎么创建(σﾟ∀ﾟ)σ..自个百度)
1.1 索引的类型
1.1.1 根据创建分类
1.1.1.1 主键索引(聚集索引 & 特殊索引)
1.1.1.2 二级索引(非聚集索引)
1.1.2 根据使用分类
1.1.2.1 覆盖索引
1.1.2.2 辅助索引(二级索引+回表 30%(需要获取的数据) )
1.1.1 根据字段数量分类
1.1.3.1 单索引(唯一索引 -- 几乎为唯一值的字段 90%以上是惟一的)
1.1.3.2 联合索引(复合索引 - 推荐创建联合索引)
1.2 索引结构(BTree)
1.2.1 BTree (根据平衡二叉树进行演变 索引维护成本 会随索引的长度增加)
主键:数据类型最好自增,字符串主键
1.3 最左匹配原则(联合索引)
1.3.1 规则是什么?
1.3.2 演示
1.3.3 覆盖索引就是任选(･ω<)☆
1.4 关于SQL语句优化的点思路
1.4.1 过滤性SQL
1.4.1.1 单SQL
1.4.1.2 join
1.4.2 本质上是全表扫描的SQL
1.4.2.1 单SQL
1.4.2.2 join
1.5 索引的疑问与注意
1.5.1 索引的复用性(=゜ω゜)ノ
1.5.2 索引建立限制(数量，字段数量)
2. 优化器执行流程
3. 学员案例实践
4. max与min的一个优化技巧(利用索引排序)
5. 索引的总结
3. 查询优化器的操作
mysql> select * from information_schema.OPTIMIZER_TRACE \G;
*************************** 1. row ***************************
QUERY: explain select * from details where equipmentMD5 = "4" and type = 4 and age = 0
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `details`.`id` AS `id`,`details`.`equipmentMD5` AS `equipmentMD5`,`details`.`type` AS `type`,`details`.`age` AS `age` from `details` where ((`details`.`equipmentMD5` = '4') and (`details`.`type` = 4) and (`details`.`age` = 0))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`details`.`equipmentMD5` = '4') and (`details`.`type` = 4) and (`details`.`age` = 0))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`details`.`equipmentMD5` = '4') and multiple equal(4, `details`.`type`) and multiple equal(0, `details`.`age`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`details`.`equipmentMD5` = '4') and multiple equal(4, `details`.`type`) and multiple equal(0, `details`.`age`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`details`.`equipmentMD5` = '4') and multiple equal(4, `details`.`type`) and multiple equal(0, `details`.`age`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`details`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`details`",
"field": "type",
"equals": "4",
"null_rejecting": false
},
{
"table": "`details`",
"field": "age",
"equals": "0",
"null_rejecting": false
},
{
"table": "`details`",
"field": "type",
"equals": "4",
"null_rejecting": false
},
{
"table": "`details`",
"field": "equipmentMD5",
"equals": "'4'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`details`",
"range_analysis": {
"table_scan": {
"rows": 100042,
"cost": 20300
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_type",
"usable": true,
"key_parts": [
"type",
"id"
]
},
{
"index": "idx_age_type_equipmentMD5",
"usable": true,
"key_parts": [
"age",
"type",
"equipmentMD5",
"id"
]
}
],
"best_covering_index_scan": {
"index": "idx_age_type_equipmentMD5",
"cost": 21276,
"chosen": false,
"cause": "cost"
},
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_type",
"ranges": [
"4 <= type <= 4"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 50021,
"cost": 60026,
"chosen": false,
"cause": "cost"
},
{
"index": "idx_age_type_equipmentMD5",
"ranges": [
"0 <= age <= 0 AND 4 <= type <= 4 AND 4 <= equipmentMD5 <= 4"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": true,
"rows": 1,
"cost": 1.21,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"intersecting_indexes": [
{
"index": "idx_age_type_equipmentMD5",
"index_scan_cost": 1,
"cumulated_index_scan_cost": 1,
"disk_sweep_cost": 0,
"cumulated_total_cost": 1,
"usable": true,
"matching_rows_now": 1,
"isect_covering_with_this_index": true,
"chosen": true
}
],
"clustered_pk": {
"clustered_pk_added_to_intersect": false,
"cause": "no_clustered_pk_index"
},
"chosen": false,
"cause": "too_few_indexes_to_merge"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_age_type_equipmentMD5",
"rows": 1,
"ranges": [
"0 <= age <= 0 AND 4 <= type <= 4 AND 4 <= equipmentMD5 <= 4"
]
},
"rows_for_plan": 1,
"cost_for_plan": 1.21,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`details`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_type",
"rows": 50021,
"cost": 10871,
"chosen": true
},
{
"access_type": "ref",
"index": "idx_age_type_equipmentMD5",
"rows": 1,
"cost": 1.2,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "idx_age_type_equipmentMD5"
},
"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": "((`details`.`age` = 0) and (`details`.`type` = 4) and (`details`.`equipmentMD5` = '4'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`details`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"table": "`details`"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
3… join
4. max与min的一个优化技巧(利用索引排序)
这里以salary为例
select * from details limit 0,5;
+----+--------------------+------+-----+
| id | equipmentMD5 | type | age |
+----+--------------------+------+-----+
| 1 | wQU5bJjXuJsJF1CVEE | 1 | 101 |
| 2 | pp5nK4nsU2RJxY5FHb | 1 | 98 |
| 3 | fI7au1b9Mm9zKK2lQX | 1 | 6 |
| 4 | IKvi21q3wX0x6kRXGj | 1 | 37 |
| 5 | JI3ybbKuyzNIm9HYjN | 1 | 3 |
+----+--------------------+------+-----+
5 rows in set (0.00 sec)
如上的数据是最初始的状态，也就是没有进行操作的时候
show index from details;
+---------+------------+--------------+--------------+-------------+-----------+-------------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type |
+---------+------------+--------------+--------------+-------------+-----------+-------------+------------+
| details | 0 | PRIMARY | 1 | id | A | 997442 | BTREE |
| details | 1 | idx_age_type | 1 | age | A | 121 | BTREE |
| details | 1 | idx_age_type | 2 | type | A | 545 | BTREE |
| details | 1 | idx_type | 1 | type | A | 3 | BTREE |
+---------+------------+--------------+--------------+-------------+-----------+-------------+------------+
4 rows in set (0.00 sec)
现在我们想要知道age最低和最大的用户
select id,max(age) from details;
+----+----------+
| id | max(age) |
+----+----------+
| 55 | 119 |
+----+----------+
1 row in set (0.18 sec)
select * from details where id = 55;
+----+--------------------+------+-----+
| id | equipmentMD5 | type | age |
+----+--------------------+------+-----+
| 55 | zwWzo4qtjdEffbMguZ | 1 | 0 |
+----+--------------------+------+-----+
1 row in set (0.00 sec)
select id,age from details where age = (select max(age) from details) limit 0, 1;
+----+-----+
| id | age |
+----+-----+
| 67 | 119 |
+----+-----+
1 row in set (0.00 sec)
-- 数据量太多....
select id,age from details order by age desc limit 0, 1;
+--------+-----+
| id | age |
+--------+-----+
| 999901 | 119 |
+--------+-----+
1 row in set (0.00 sec)
可以看到我们就需要使用第三条SQL的方式，不过我们也可以通过order by排序；不过我们也可以通过使用索引快速获取所需要的数据；比如这里我们使用age索引获取最大和最小
最小
select id,age from details use index(idx_age_type) limit 0,1;
+----+-----+
| id | age |
+----+-----+
| 55 | 0 |
+----+-----+
1 row in set (0.00 sec)
最大
select count(*) from details;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.13 sec)
select id,age from details use index(idx_age_type) limit 999999,1;
+--------+-----+
| id | age |
+--------+-----+
| 999901 | 119 |
+--------+-----+
1 row in set (0.16 sec)
这个策略的话可以了解一下，在一些情况下是会让MySQL扫描尽可能少的记录数。不过这种方式实际上并不是SQL的本意了，但是它也会返回我们所需要的数据信息，之所以可以这么做是因为与income索引本质在建立的时候就会对于数据的结果进行了排序
5. 索引总结
在联合索引中：SQL索引联合索引的话通常来说最好是不要超过5个，因为超过了五个在过滤上就没有太大的效果，但是如果说有些时候需要定义10个字段的关联能起到很好地效果的话还是可以去这么做的。如果在目前的SQL中我们不想建立5个索引的话，那么我们可以尝试去优化子查询来提高MySQL的查询效率
在使用索引的同时，还应该了解MySQL中索引存在的限制，以便在索引应用中尽可能地避开限制所带来的问题。下面列出了目前MySQL中与索引使用相关的 限制 。
- blob和text类型的列只能创建前缀索引
- MySQL 5.7 对于普通索引不支持函数操作，若要使用可以定义函数索引
- 使用不等于（！=或者 <>）的时候MySQL无法使用索引；
- 使用like操作的时候如果 %在前面无法使用索引
- hash索引不能做范围查询
如下内容出自上期优化课程中的笔记
10.4、什么情况使用索引
当使用不以通配符开始的like
Select * from table1 where username like 'pack%';
一个索引字段的前缀使用了order by或者group by;
Select * from table2 order by field1;
对索引字段使用了>,=,<,>=,<=,IS NULL和BETWEEN
Select * from table3 where total_fee IS NULL
查找某个字段的最大值和最小值
Select min(score),max(score) from table4 where class_id = 1;
查询的列是后面字段的部分时间
Select time1 from table5 where time2 = '2017-2';
10.5、什么情况不使用索引
以通配符开始的like查询
Select * from table1 where username like '%pack%';
搜索某列，而在另一个列的索引做order by会优先选择与where上的条件
explain select * from customers where mobile = "0" order by city;
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+----------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+----------+---------------------------------------+
| 1 | SIMPLE | customers | ref | idx_mobile | idx_mobile | 33 | const | 245076 | 100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------+---------------+------------+---------+-------+--------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
10.6、误区
不是在where条件常用的列都加上索引
Where course = 'php' and number > 50;
在多列建立索引后，查询哪个列，都将发挥作用？
满足左前缀要求。
Index(field1,field2,field3)
Where field1 = 1 生效
Where field1 = 1 and field2 =2 生效
Where field1 = 1 and field2 =2 and field3 = 3; 生效
Where field2 = 2 Where field3 =3 不生效
Where field1 = 1 and field3 = 3; field1生效，field3不生效
Where field1 = 1 and field2 >2 and field3 = 3; field1和field2生效，field3不生效
Where field1 = 1 and field2 like 'pack%' and field3 = 3; field1和field2生效，field3不生效
explain select * from customers1 where gender > 0 ;
+----+-------------+------------+------------+------+--------------------------------------------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | rows | filtered | Extra |
+----+-------------+------------+------------+------+--------------------------------------------+------+--------+----------+-------------+
| 1 | SIMPLE | customers1 | NULL | ALL | idx_gender_city_name_monthsalary_yearbonus | NULL | 577859 | 50.00 | Using where |
+----+-------------+------------+------------+------+--------------------------------------------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
最左前缀：
选择一个能够淘汰最多纪录的索引；
字段是有序的；
10.7、SQL的优化
Limit分页优化
Select * from table1 order by id asc limit 2200,10;
Select * from table1 where id>=2200 order by id limit 10;
Count(*) 改为辅助索引
Select count(*) from table2;
Select count(*) from table2 where course_id >=0;
聚集索引：主键、没有主键则定位第一个唯一索引（所有行的值非空）、如果上2个条件都不满足则自动产生一个6字节的id聚集索引。
辅助索引：包含键值的书签，会存储书签数据。
Not in子查询优化
select * from customers where id in (select id from salary ); 时间: 9.841s
select * from customers where exists (select id from salary where customers.id = salary.id );时间: 3.780s
select * from customers left join salary on customers.id = salary.id 时间: 2.072s
Or条件优化
Select * from table1 where a = '123' or b = '456';
Select * from table1 where a = '123' union all Select * from table1 b = '456';
扫一扫
1386
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
