索引 （Index）
比如我们要在字典中找某一字，如何才能快速找到呢？那就是通过字典的目录。
对数据库来说，索引的作用就是给‘数据’加目录。创建索引的目的就是为了提高查询速度
索引算法
- btree(平衡树)索引 log2N
- hash(哈希)索引 1
优缺点
- 好处:加快了查询速度(select )
- 坏处:降低了增,删,改的速度(update/delete/insert),增大了表的文件大小(索引文件甚至可能比数据文件还大)
索引类型
- 普通索引(index)：仅仅是加快了查询速度
- 唯一索引(unique)：行上的值不能重复
- 主键索引(primary key)：不能重复
- 全文索引(fulltext):仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
- 组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。
聚集索引与非聚集索引的区别：
1. 聚集索引：
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。
缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序
2. 非聚集索引：
索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列，当我们使用非聚集索引查询数据时，需要拿到叶子上的主键再去表中查到想要查找的数据。这个过程就是我们所说的回表。
区别：
聚集索引在叶子节点存储的是表中的数据。 非聚集索引在叶子节点存储的是主键和索引列。 1. 聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。 聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。 聚集索引:物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。 非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。 2. 优势与缺点 聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。
索引语法
- 查看某张表上的所有索引
show index from tableName [\G,如果是在cmd窗口，可以换行];
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Table |
Non_unique |
Key_name |
Seq_in_index |
Column_name |
Collation |
Cardinality |
Sub_part |
Packed |
Null |
Index_type |
Comment |
Index_comment |
emp |
0 |
PRIMARY |
1 |
empno |
A |
11 |
NULL |
NULL |
BTREE |
|||
emp |
1 |
fk_emp_dept |
1 |
deptno |
A |
5 |
NULL |
NULL |
YES |
BTREE |
||
---- |
---- |
----------- |
---- |
------ |
---- |
---- |
---- |
---- |
---- |
----- |
---- |
---- |
- 建立索引
alter table 表名 add index/unique/fulltext 索引名 （列名) ; ---索引名可不写,不写默认使用列名
alter table emp add index ename_index (ename);
alter table 表名 add primary key(列名) --不要加索引名，因为主键只有一个
- 删除非主键索引
alter table 表名 drop index 索引名；
alter table emp drop index ename_index;
- 删除主键索引：
alter table 表名 drop primary key; - 全文索引与停止词
全文索引的用法：match(全文索引名) against('keyword');
关于全文索引
关于全文索引的停止词：
全文索引不针对非常频繁的词做索引
如：this,is,you,my等等
全文索引在mysql的默认情况下，对于中文意义不大。
因为英文有空格，标点符号来拆成单词，进而对单词进行索引；
而对于中文，没有空格来隔开单词，mysql无法识别每个中文词。
可以使用sphinx插件来进行全文索引的中文索引。
组合索引
(5)复合索引
代码如下:
CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) );
name索引是一个对last_name和first_name的索引。索引可以用于为last_name，或者为last_name和first_name在已知范围内指定值的查询。因此，name索引用于下面的查询：
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
但是不能用于SELECT * FROM test WHERE first_name='Michael';这是因为MySQL组合索引为“最左前缀”的结果,简单的理解就是只从最左面的开始组合。
建立索引的策略
- 主键列和唯一性列 √
. 不经常发生改变的列 √
. 满足以上2个条件,经常作为查询条件的列 √
. 重复值太多的列 ×
. null值太多的列 ×
B+Tree 的优势
B+树与B树的不同在于：
（1）所有值存储在叶子节点，非叶子节点不存储真正的data，而是作为索引
（2）为所有叶子节点增加了一个链指针 （可用作区间查询效率高）
索引是以索引文件的形式存在于硬盘中的，磁盘IO的消耗远远大于内存IO的消耗，若要根据索引的数据结构找数据时要尽量减少磁盘IO的次数。 磁盘并不是每次严格按需读取，而是每次都会预读。磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中。 （预读的原因： (1)当一个数据被用到时，其附近的数据也通常会马上被使用 (2)程序运行期间所需要的数据通常比较集中 ） 预读可以提高I/O效率.预读的长度一般为页(page)的整倍数 MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K 为什么mysql的索引使用B+树而不是B树呢？？ （1）B+树更适合硬盘存储,由于非叶子节点不存储data，作为索引开销，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。 （2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。
