mysql> create table test( --创建测试表
-> id int(10) not null AUTO_INCREMENT,
-> a int(10) default null,
-> b int(10) default null,
-> c int(10) default null,
-> d int(10) default null,
-> primary key(id), --主键索引
-> key idx_a(a), --辅助索引
-> key idx_b_c(b,c) --联合索引
-> )engine=InnoDB charset=utf8mb4;
Query OK, 0 rows affected, 5 warnings (0.09 sec)
mysql> drop procedure if exists insert_test_data;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> delimiter | --创建存储过程，插入十万个数据
mysql> create procedure insert_test_data()
-> begin
-> declare i int;
-> set i=1;
-> while(i<=100000) do
-> insert into test(a,b,c,d)values(i,i,i,i);
-> set i=i+1;
-> end while;
-> end |
Query OK, 0 rows affected (0.11 sec)
mysql> delimiter ;
mysql> call insert_test_data(); --执行存储过程
Query OK, 1 row affected (11 min 44.13 sec)
数据检索时在条件字段添加索引
聚合函数对聚合字段添加索引
对排序字段添加索引
为了防止回表添加索引
关联查询在关联字段添加索引
可以看出使用索引后，对查询速度优化提升是巨大的，本文将从底层到实践搞懂MySQL索引。
从二叉树到B+树
二叉树：
二叉树（Binary Tree）是指至多只有两个子节点的树形数据结构，没有父节点的节点为根节点，没有子节点的节点称为叶子节点。
二叉搜索树就是任何节点的左子节点小于当前节点键值，右子节点大于当前节点键值。
如下图的二叉搜索树，我们最多只需要 ⌈ l o g ( n ) ⌉ ⌈log(n)⌉ ⌈log(n)⌉即三次即可匹配到数据，而线性查找的话最坏情况需要 n n n次才可匹配到。
用explain分析索引使用
explain可以看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，语法：explain select... from ... [where...]。
用前面概述那节的test表做测试：
mysql> explain select * from test where a=88888;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ref | idx_a | idx_a | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
mysql> explain select b,c from test where b=88888;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | idx_b_c | idx_b_c | 5 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where a=(select a from test where a=88888);
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | test | NULL | ref | idx_a | idx_a | 5 | const | 1 | 100.00 | Using where |
| 2 | SUBQUERY | test | NULL | ref | idx_a | idx_a | 5 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
重点看这三列即可：select_type、type、extra。
select_type值 说明 SIMPLE 简单查询（不使用关联查询或子查询） PRIMARY 包含关联查询或子查询 UNION 联合查询中第二个及后面的查询 DEPENDENT UNION 依赖外部的关联查询中第二个及以后的查询 UNION RESULT 联合查询结果 SUBQUERY 子查询中的第一个查询 DEPENDENT SUBQUERY 依赖外部查询的子查询中的第一个查询 DERIVED 用到派生表的查询 MATERIALIZED 被物化的子查询 UNCACHEABLE SUBQUERY 子查询结果不能被缓存，必须重新评估外层查询的每一行type（显示这一行的数据是关于哪张表的）
type的值 说明 system 查询对象只有一会数据 ，最好的情况 const 基于注解或唯一索引查询，最多返回一条结果 eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描 ref 基于普通索引的等值查询或表间等值连接 fulltest 全文检索 ref_or_null 表连接类型是ref，但扫描的索引中可能包含NULL值 index_merge 利用多个索引 unique_subquery 子查询使用唯一索引 index_subquery 子查询使用普通索引 range 利用索引进行范围查询 index 全索引扫描extra（解决查询的详细信息）
extra的值 说明 Using filesort 用的外部排序而不是索引排序 Using temporary 需创建一个临时表来存储结构，通常发生在对没有索引的列进行group by时 Using index 使用覆盖索引 Using where 使用where来处理结果 Impossible where 对where子句判断结果总是false而不能选择任何数据 Using join buffer 关联查询中，被驱动表的关联字段没有索引 Using index condition 先条件过滤索引再查数据 Select tables optimized away 使用聚合函数来访问存在索引的某个字段 总结
本篇文章就到这里了，希望能给你带来帮助，也希望您能够多多关注脚本之家的更多内容!
数据库
发布于：2021-08-03 19:58
|
阅读数：410
|
评论：0
QQ好友和群
QQ空间
