Mysql学习笔记

索引

• 索引是一种数据结构
• 并不是走索引就一定比全表扫描快

索引失效

• 查询语句中存在类型转化或者运算

create table t_test(
	a int primary key,
	b int,
	c int,
	d int,
	e varchar(20)
)engine innodb;
create index idx_b_c_d on t_test(b,c,d);
create index idx_e on t_test(e);

select * from t_test where a + 1 = 2; # 不可以走索引
select * from t_test where e = '1';  # 可以走索引
select * from t_test where e = 1;  # 不可以走索引

因为在MySQL在进行字符串与数字类型的数据进行比较时，会先将字符串转化成数字类型，然后进行比较；如果字符串是个数的话转化成对应的数值，否则都转换为0；

• 不满足最左匹配原则

使用覆盖索引

当需要查询的字段在索引中时，可以通过查询索引来获取所要查找的内容，因为索引相对与数据文件来说占用空间小，可以通过索引查询来进行数据查询

存储引擎类型

- innoDB
- MyISAM
- MEMORY

innoDB和MyISAM的区别

• innoDB支持事务，MyISAM不支持事务
• innoDB中锁的最小粒度是行锁，MyISAM锁的最小粒度是表锁
• innoDB支持外键，MyISAM不支持
• innoDB不存储表的记录数，每次查询进行全表扫描，MyISAM存储有表中记录的条数
• innoDB的主键索引是聚集索引，MyISAM的主键索引是非聚集索引；innoDB的数据和主键索引放在同一个文件中，主键索引的叶子节点是数据，其他索引的叶子节点存储的是数据的主键。MyISAM的数据和索引是分离开来的，索引的叶子几点存储的是数据的地址。

查询innoDB的死锁问题

show engine innoDB status;

索引下推（index condition pushdown）

• 索引下推的概念：在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
• 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
• 索引下推相关的参数

　　set optimizer_switch='index_condition_pushdown=off';