Excel-mysql索引和B-Tree

一、MySQL中索引的语法

创建索引

在创建表的时候添加索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);

在创建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
-- 或者
CREATE INDEX index_name ON my_table(column_name);

注意：

1、索引需要占用磁盘空间

2、创建索引时需要对表加锁

删除索引

DROP INDEX my_index ON tablename；(无法删除主键)
-- 或者
ALTER TABLE table_name DROP INDEX index_name;(删除主键先去掉唯一约束)

查看表中的索引

SHOW INDEX FROM tablename;

二、索引的优缺点

优势：可以快速检索，减少I/O次数,加快分组和排序；

劣势： 1.占用存储空间

​ 2.成本随着数据量增大而增大

​ 3.降低数据表的修改操作（删除，添加，修改）的效率

三、索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；
secondary key

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；
-- 5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。--- 5.6版本之后InnoDB存储引擎开始支持全文索引
-- 在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；
--表示使用col1的前4个字符和col2的前3个字符作为索引

四、索引的实现原理

1、哈希索引：

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能

2、B+Tree索引

正常情况下，如果不指定索引的类型，那么一般是指B+Tree索引（或者B+Tree索引）。

存储引擎以不同的方式使用B+Tree索引。性能也各有不同，但是InnoDB按照原数据格式进行存储。

B+Tree 索引能够加快数据的读取速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，相反是从索引的根节点开始进行搜索，通过相应的指针移动，最终存储引擎要么找到了对应的值，要么该记录不存在。树的深度与表的大小直接相关。

B+Tree索引是按照顺序组织存储的，所以适合范围查找数据

B+Tree索引使用与全键值、键值范围或者键前缀查找，其中键前缀进适用于根据最左前缀的查找。

B-Tree

B+Tree

为什么使用B+树而不是B树

1.磁盘读写代价更低

2.随机I/O的次数更少

3.查询速度更稳定

3,聚簇索引和非聚簇索引

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

InnoDB——聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。

聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

、索引的使用策略

B+Tree索引是按照顺序组织存储的，所以适合范围查找数据

B+Tree索引使用与全键值、键值范围或者键前缀查找，其中键前缀进适用于根据最左前缀的查找。

– varchar char区别
varchar不能超长度存储,但依然是变长的
你可以限制长度为10,存储不能超过10 ,但是可以少于10 ,而存储的长度就是你字符的实际长度
char 定长存储,不能超长存储数据,存储数据不管是否符合长度要求,都占指定长度空间

索引的优势就是提高了数据检索速度,不要浪费索引
有索引但是用不上情况:
1.like 模糊查询, 当不符合最左前缀原则时,有索引也不用上
select from users where email = ‘zl@%’ ok
select from users where email = ‘%l@’ NO
以下语句为什么没有用到索引?
select * from users where phone = 13701383017
2.phone是一个字符串类型的列,在查询时给定的参数是int类型