MySQL之详解索引

可以通过插入数据时不断select查询观察细节

-- 删除表中所有数据
delete  from t1;
-- 删除指定id的数据
delete from t1 where a in(1,4,8);

存储过程慢慢生成B+树(数据都在叶子节点)

通过explain查看一些sql语句的性能进行简单的查询

-- 通过primary索引查询
EXPLAIN SELECT * FROM t1 WHERE a > 1;
-- 全表查询all
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c = 1 AND d = 1 ;
-- 直接主键索引查询，性能高
EXPLAIN SELECT * FROM t1 WHERE a = 8;
-- 通过查看索引，我们发现只有主键查询
SHOW INDEX FROM t1;

所以我们创建其他索引，达到快速查询的要求。

-- 创建bcd组合索引
CREATE INDEX idex_t1_bcd ON t1(b,c,d);

创建组合索引后，执行如下语句：了解key_len

-- 通过组合索引index_t1_bcd查询，但是由于是*所以需要回表查询using where
-- key_len是真正使用的索引大小，此处为15
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c = 1 AND d = 1 ;
-- key_len此处为5
EXPLAIN SELECT * FROM t1 WHERE  b = 1;
-- key_len此处为10,e不在index_t1_bcd组合索引的列中，不计算
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c = 1 AND e = 1 ;

练习完以上案例后，看如下图MySQL 索引，就会明白索引中原来有这么多原理和关键字

最左前缀原则

要运用组合索引时，必须从遵循最左原则(就是最左边的列必须出现在where后的第一个)

-- 全表查询all
EXPLAIN SELECT * FROM t1 WHERE   c = 1 AND d = 1 ;
-- 虽然使用的是index_t1_bcd索引但是由于c > 1是范围，所以key_len在此处是10
EXPLAIN SELECT * FROM t1 WHERE  b = 1 AND c > 1 AND d = 1 ;

了解了以上语句执行后效果，还有除此之外运用组合索引的特殊情况：

-- possible_keys显示此处可能使用index_t1_bcd索引
-- 但是由于b > 1是范围，type是all，系统自动选择全表查询
EXPLAIN SELECT * FROM t1 WHERE   b > 1;
-- 通过组合索引index_t1_bcd查询，key_len此处为5
-- 有人就说了b > 6不是范围吗？怎么能用组合？难道不遵循最左原则？
-- 这是因为b > 6后面只有一个记录，所以系统会选择最优的查询路径
EXPLAIN SELECT * FROM t1 WHERE   b > 6;

索引覆盖

索引覆盖的使用能够减少树的搜索次数，避免了回表

-- 通过组合索引index_t1_bcd查询，key_len此处为5
-- 有人又有疑问了，b > 1不是范围吗？怎么能有组合索引？
-- 这是因为查询的只有b所以通过组合查询比较快。
EXPLAIN SELECT b FROM t1 WHERE   b > 1;
-- 在组合索引中就有id的存储，所以不用回表查询，这就是索引覆盖
EXPLAIN SELECT a,b FROM t1 WHERE   b > 1;

索引下推

MySQL5.6出现,主要用于非主键索引尤其联合索引(组合索引)，减少回表查询的次数

-- 在没有索引下推之前，在组合索引中有几条b = 1的记录(忽略d)，就会回表查询(d)多少次
-- 对于此sql语句b = 3有两条((4,3,1,1,'d')，(3,3,2,2,'c'))查询过程就需要回表两次
-- 而又索引下推优化后，在组合索引中不会忽略d这个字段，进而判断符合条件的，从而只需要回表查询一次。
-- 索引下推在Extra中会显示Using index condition
-- 虽说需要回表查询，但是经过索引下推后，满足b = 3 and d = 1的记录只有1条
-- 所以row在此案例中应该显示1
EXPLAIN SELECT * FROM t1 WHERE  b = 3 AND d = 1 ;
-- 没有d字段，所以需要将满足b = 3的记录全部回表查询，row显示2
EXPLAIN SELECT * FROM t1 WHERE  b = 3 AND e = 1 ;

以上都是int类型的索引，下面给varchar类型创建索引

CREATE INDEX idx_t1_e ON t1(e);

执行如下sql语句:数据类型转换

-- 返回结果为1
SELECT 1 = 1;
-- 返回结果为0
SELECT 1 = 0;
-- 返回结果为1
SELECT 1 = '1';
EXPLAIN SELECT * FROM t1 WHERE a = 1;
EXPLAIN SELECT * FROM t1 WHERE a = '1';
EXPLAIN SELECT * FROM t1 WHERE e = 1;
EXPLAIN SELECT * FROM t1 WHERE e = '1';

7.数据库文件存储

-- 查看存储引擎
SHOW ENGINES;

7.1物理文件(顺序IO)

MySQL通过日志记录数据库操作信息和错误信息。

-- 查看当前数据库中的日志使用信息
SHOW VARIABLES LIKE 'log_%';

默认开启。记录了运行中遇到的所有严重的错误以及MySQL每次启动和关闭的详细信息

错误日志名字；hostname.err

查看日志的文件路径：log_error

log_warings：警告信息是否开启

7.2数据文件(随机IO)

show variables like '%datadir%';

ibd为InnoDB存储，.MYD,.MYI为MyISAM存储

InnoDB数据文件

MyISAM数据文件；

-- 查看总内存大小
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

二叉树，红黑树，B-tree,B+tree

据信息，主要包括表结构的定义信息

-- 查看总内存大小
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

关键字:

（编辑：佛山站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!