mysql索引语法_MySql中索引的语法

接下来这些东西是我们日常都总用的到的。

首先创建一张表的时候加索引：

create table testTable(

id int not null,

username varchar(16) not null,

index [indexName] (username(length))

);

创建表之后加索引：

alter table test_table add [unique] index index_name(column_name);

还有就是

create index index_name ON test_table(column_name)

需要注意的是：

添加索引会消耗我们的磁盘空间，所以我们要考虑磁盘空间是否充足。

创建索引的时候还得对表进行加锁。

根据索引查询

select * from test_table where column_1 = column_2 (1建立索引)

还有模糊查询啥的，自己百度吧。

删除索引

drop index test_table on tablename

还有就是

alter table table_name drop index index_name

查询语句使用索引

explain select * from table_name WHERE column_1='123'

借鉴

索引的有缺点

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

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作(删除，添加，修改)的效率，因为在修改数据表的同时还需要修改索引表；

索引的分类

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

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

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

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、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

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.数据有序的。

2.去中间数据做对比。

什么是二叉树：

基于二分法的策略提高数据的查找速度的二叉树的数据结构。

平衡二叉树

平衡二叉树是在二叉树的基础上，平衡二叉树为了避免因为二叉树结构的两边节点高度极度不一致，而造成树结构的检索效率低下，从而增加了平衡算法的树结构(Treap、红黑树)

1、所有节点最多拥有两个子节点；

2、左边的子节点要小于当前节点、右边子节点大于当前节点；

3、树的左右两边的层级数相差不会大于1;

B树

和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树。

B树的查找、插入、删除

磁盘读取数据的过程

B+树

B+树的非叶子节点不保存关键字记录的指针。

B+树父节点关键字记录的指针都保存在叶子节点。

每个叶子节点的关键字从小到大构成有序链表。

B*树

总结几种树的差别

1、相同思想和策略

从二叉树、平衡二叉树、B树、B+树、B*树总体来看它们的贯彻的思想是相同的，都是采用二分法和数据平衡策略来提升查找数据的速度；

2、不同的方式的磁盘空间利用

不同点是他们一个一个在演变的过程中通过IO从磁盘读取数据的原理进行一步步的演变，每一次演变都是为了让节点的空间更合理的运用起来，从而使树的层级减少达到快速查找数据的目的；

MySQL的索引实际就是采用B+tree

索引建立的原则

1、离散性

2、查询频率高、更新频率低

3、索引字段能小则小

4、大字段不适合建索引