mysql-索引篇

索引分类

1.主键索引

PRIMARY KEY (id)

2.唯一索引

ALTER TABLE table_name ADD UNIQUE (column)
CREATE UNIQUE INDEX IndexName ON table_name(column)

3.普通索引

ALTER TABLE table_name ADD INDEX index_name (column)
CREATE INDEX IndexName ON table_name(column)

4.组合索引

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)
CREATE INDEX nickname_account_createdTime_Index ON table_name(nickname, account, created_time)

5.全文索引

ALTER TABLE table_name ADD FULLTEXT (column)

6.查询索引

SHOW INDEX FROM baginfo

7.删除索引

alter table table_name drop index index_name
DORP INDEX IndexName ON TableName

聚集索引、辅助索引

1.聚集索引，又称为聚簇索引、主键索引（(clustered index)），辅助索引又称为普通索引、二级索引(secondary index)
2.聚集索引可简单认为是主键索引，一个表只会有一个主键，也就是只会有一个聚集索引。聚集索引中叶子节点存储的是一行数据记录。如果没有指定主键，mysql会选取第一个非空的唯一列作为主键索引，如果没有该列，mysql会自动生成一个隐藏的列作为主键。
3.辅助索引是除主键索引外的其他索引，该索引中叶子节点保存的不是一条完整的数据记录，而是只包含主键id、索引字段和值。此时，如果select查询的字段包含非索引列，那么查询过程需要进行一次回表操作。即先根据辅助索引查找到叶子节点，从叶子节点中拿到主键id，再根据主键id进行一次聚集索引，进而获取到值。

覆盖索引

索引包含了查询正在查找的所有数据（列），在EXPLAIN的Extra列可以看到“Using index”的信息

B+树特点

1.有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）
2.所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序连接
3.非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字
4.查找时，如果在非叶子节点上的关键字等于给定值，并不终止，而是继续沿着指针直到叶子节点位置。因此在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径

myisam和innodb区别

1.myisam索引和数据分开。叶子节点中data域保存的是数据的地址。而inodb索引和数据是在一个文件中，在聚集索引下，叶子节点中data域存储的是整行数据
2.myisam有三个文件：frm(表结构)、myd(数据文件)、myi(索引文件)；innodb有两个文件：frm(表结构)、idb(数据和索引文件)
3.InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，MyISAM可以没有
4.MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁

explain字段解释

1.type

null>system>const>eq_ref>ref>re_or_null>index_merge>range>index>all

2.extra

1.using index ：使用覆盖索引的时候就会出现
2.using where：在查找使用索引的情况下，需要回表去查询所需的数据
3.using index condition：查找使用了索引，但是需要回表查询数据
4.using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
5.null：
6.Using filesort：where中有order by情况下会出现