数据库索引——Mysql

数据库索引

一：索引定义：索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。

 

二：索引的分类：1：单列索引（普通索引、唯一索引、主键索引）。2：组合索引。3：全文索引。4：空间索引

（1）：普通索引——最基本的索引，没有任何限制。（关键字：index）

（2）：唯一索引——与普通索引类似，但是索引值必须唯一，允许有空值。（关键字unique）

（3）：主键索引——它是一种特殊的唯一索引，不允许有空值。一般会在建表的时候同时创建主键索引，如果设置了主键其实就是创建了主键索引。（关键字primary）

（4）：组合索引——为了进一步提高效率，就考虑建立组合索引。就是将多列建到一个索引里

（5）：全文索引——希望通过关键字的匹配来查询过滤，那么就需要相似度的查询，全文索引就是为这种场景设计的。（关键字fulltext）

             有人可能会用like +%就可以了，对于文本比较少时是合适的，如果对大量的文本数据检索，全文检索比like+%快N倍，但是全文索引可能会存在精度的问题。

三：索引的设计规则：

1：索引并非越多越好。

2：避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。

3：数据量小的表最好不要使用索引。

4：在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。

5：当唯一性是某种数据本身的特征时，指定唯一索引。

6：在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引

四：创建索引：

1：创建表时创建索引。语法：CREATE  TABLE   table_name [col_name data_type]  [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]    [index_name] (col_name [length])   [ASC | DESC]

2：在已经存在的表上创建索引。①：使用alter table 语句。②：使用create index 创建索引，

      create index index_name on talbe_name（column_name（length））；     create + 索引类型+索引名 + on + 表名（列名（列长度））；

      alter table_name add index[index_name] on （column_name（length））； 

注意：1：如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

           2：如果表中建立多个单列索引，查询时会远远低于组合索引。虽然有多个索引，但是MySQL只能用到它认为最有效率的单列索引

           3：mysql的全文索引，有最小搜索长度和最大搜索长度，这个可以通过命里show variable like  '%ft%';查看，只有在这个区间，全文索引不失效，同时表里只是要有4条以上的记录

例如：

    create index index_name on talbe_name(column_name(length));            -- 创建普通索引

    create unique unique_name on table_name(column_name(length))；      -- 创建唯一索引

    create primary primary_name on table_name(key_name(length));            -- 主键索引

    create index  index_col1_col2_col3_name on table_name(col1_name(length),col2_name,col3_name);         -- 组合索引，这里的length长度，只要大于实际存储值的长度就可以

    create fulltext index index_name on table_name（col1,col2）;                -- 全文索引，可以单列也可以多列

和like+%不同，全文索引有自己的语法格式，使用match和against关键字，如：select * from table_name where match(col1,col2) against('xxxx  yyyy');

五：删除索引

1：使用alter table 删除索引。语法： alter table table_name drop index index_name

2：使用drop index语句删除索引。语法：drop index index_name on talbe_name

六：索引不生效的情况——MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。

1：以通配符%和_开头作查询时，MySQL不会使用索引。select * from table_name where name like '%admin'   ——这样的不生效

2：范围字段会结束索引对范围后面索引字段的使用。select * from table_name where age > 20 and name = 'admin'   ——age索引生效，后面的索引不生效

3：！=   不走索引。 select * from talbe_name where age != 20 and name = 'admin'     ——这种情况，索引不生效

4：用索引计算的，索引无效。select * from table_name where age = 10+8 （走索引） select * from table_name where age + 8 = 18   （不够索引）

5：对索引列用函数了，不走索引。select * from student where  concat('name','哈') ='王哈哈';（不走） select * from student where name = concat('王哈','哈');（走索引）

索引的优点：

1：建立主键索引和唯一索引，可以保证数据库表中每一行数据的唯一性

2：建立索引可以大大提高检索的数据，以及减少表的检索行数

3：建立索引，在查询中使用索引可以提高性能

4：在表连接的连接条件上加索引，可以加速表与表的关联

5：在分组和排序进行数据检索，可以减少查询时间

索引的缺点：

1：创建索引和维护索引，会耗费时间，随着数据的增加而增加

2：建立索引会占用磁盘（物理）空间。每一个索引都会占用一定的物理空间。如果一张表建立太多的索引，索引文件会膨胀的很快。

3：当对表数据进行增、删、改 的时候，索引要动态维护，这样就会降低数据的维护速度。

七：索引的数据结构——平衡树。通常采用的数据结构有：Hash、二叉搜索树、红黑树、B树、B+树。