MySQL索引
- 1.索引分几类：
- 1.1.按照索引的存储来划分：
- 1.2.按照使用来分：
- 2.几个常见的索引名词：
- 2.1.回表：
- 2.2.索引下推（5.6版本之后用）：
- 2.3.谓词下推：
1.索引分几类：
1.1.按照索引的存储来划分：
聚簇索引：innodb，数据文件和索引文件放在同一文件中，因此要指定一个key值（此处key值不是主键，而是索引）；关于key值翻译过来是主键的争议：其实key值翻译成主键是不太对的，因为我们建表时不一定有主键，这时innodb会从其他列中选择一个唯一键来创建聚簇索引，如果连唯一键也没有，就会选择6字节的rowid（用户不可见）来创建聚簇索引。数据要跟某个索引关联在一起才能查找。
非聚簇索引：myisam，数据文件和索引文件不放在同一文件中
1.2.按照使用来分：
主键索引：建表时设置主键
唯一索引：建表时设置唯一键
mysql默认创建主键索引和唯一索引：
create table test(id int primary key,name varchar(10) unique);普通索引（辅助）：回表：通过普通索引去树中查找返回主键值，根据主键去索引树查找数据，建立普通索引，Mysql会再为这个索引创建一颗B+树，叶节点包含的不再是数据，而是对应的。
例子：
建表时：id(primary key),name(普通),age,sex,address 使用：select id,age from test where name='zhangsan';
覆盖索引：
例子：
id(primary key),name(普通),age,sex,address
select id,age from test where name='zhangsan';
select id,name from test where name='zhangsan';组合索引：最左匹配：条件中至少包含索引中最左边字段
例子：
name,age（组合索引）
select * from test where name=？and age=?;
select * from test where name=?;
select * from test where age=?;
select * from test where age=? and name=?;（这个虽然顺序调了，但在mysql的组织架构中的server层的优化器会把age=? and name=?顺序调换回来）
除了3都符合
全文索引:本质和常用的模糊匹配使用 like + % 相同。
建索引语法：
select * from fulltext_test
where match(content,tag) against('xxx xxx');例子：
建表：
create table Test (
id int ,
content text not null,
primary key(id),
fulltext key content_index(content)
) engine=MyISAM default charset=utf8;插数据：
insert into Test (content) values ('x'),('l'),('h');
insert into Test (content) values ('xx'),('ll'),('hh');
insert into Test (content) values ('xxx'),('lll'),('hhh');
insert into Test (content) values ('xxxx'),('llll'),('hhhh');测试：
select * from Test where match(content) against('x');只有一条结果
只有执行select * from Test where match(content) against('xxxx');才会搜到xxxx这一条，原因如下：在MySQL 的全文索引中，有最小搜索长度和最大搜索长度两个变量，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。也就是说，只有这个词语的长度在以上两个变量的区间内，才能对一个词语使用全文索引搜索。
2.几个常见的索引名词：
2.1.回表：
首先要明确mysql数据表中一定会有索引（上面图中有解释），回表指的是通过我们自定义创建的二级索引（辅助索引）得到最初创建的索引（可能是主键，唯一字段或者rowid），再根据这个索引去拿到对应记录，例如：select * from test where name=ma;，先去我们为name字段创建索引得到的B+树得到对应主键，再根据主键得到对应的记录。
缺点：查询效率可能会更慢，比如说查询一颗B+树需要三次IO，两颗6次IO，这时耗费的时间也许比不建索引更多。
注意：例子中select id from test where name=ma;只需查询一颗B+树。
2.2.索引下推（5.6版本之后用）：
select * from table where name=? and age=?
数据存储磁盘
mysql有自己的服务
mysql服务要跟磁盘发生交互.- 没有索引下推:
1、先从存储引擎中拉取数据（根据name筛选的数据）再mysql server根据age进行数据的筛选 - 有索引下推：
会再拉取数据的时候直接根据name，age来获取数据，不需要server做任何的数据筛选
例子：
- 没用的时候：
比如要匹配（2,3），就只匹配到2，返回了（2,3）、（2,4）、（2,6）到服务层（内存），返回的数据量多IO次数增加，性能下降，同时造成并发带来的内存问题。 - 用的时候：
比如要匹配（2,3），先匹配到2，再匹配到3，返回（2,3），所有工作都在磁盘，解决并发带来的内存问题。
2.3.谓词下推：
引入：
select tl.name,t2.name from tl join t2 on t1.id=t2.id
1.先做表连接，然后查询需要的字段
2.先把需要的所有字段都拿出来，然后在做关联-谓词下推
索引下推唯一的缺点是需要在磁盘上多做数据筛选，原来的筛选是放在内存中的，现在放到了磁盘查找数据的环节，这样做看起来成本比较高（匹配一个再匹配一个），但是别忘了，数据的排序的，所有的数据是聚集存放所以性能不会有影响，而且整体的io量会大大减少，反而会提升性能。
