MYSQL索引

MySQL索引

数据库中提供了存储共享数据的功能，但是更重要的是快速高效的获取指定的数据。
在没有索引的情况下，如果要寻找特定行，数据库可能要遍历整个数据库，使用索引后，数据库可以根据索引找出这一行，极大提高查询效率。

什么是索引

MySQL官方对索引的定义为：索引Index是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。
数据库查询是数据库的最主要功能之一，希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

MySQL目前主要有普通索引、唯一索引、主键索引、组合索引和全文索引共5种索引类型。

普通索引和唯一索引、主键索引

普通索引和唯一索引是根据索引的功能来划分。
普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

DROP INDEX index_name ON table

唯一索引的索引列的值必须唯一，但允许空值。如果是组合索引，则列的值得纸盒必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

CREATE UNIQUE INDEX indexName ON table(column(length))
 ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

主键索引

一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引

单列索引和组合索引

单列索引和组合索引的划分是根据创建索引时所引用的列的数量来划分。
单列索引是只一个索引只包含单个列，一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建索引，但只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。
ALTER TABLE table ADD INDEX name_city_age (name,city,age);
组合索引可以起到多个索引的作用，但是使用时并不是随便哪个字段都可以使用索引的，而是遵从“最左前缀”：利用索引中最左边的列集列匹配行，这样的列集称为最左前缀。例如由id、name、age这3个字段字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：（id, name , age）、（id , name）、（id）。如果不构成索引的最左边的前缀，MySQL不能使用局部索引，如（age）、（name, age）都不能使用索引进行查询。

全文索引

全文索引类型为FULLTEXT，在定义索引的列上至此值需得全文查找，允许在这些索引列上插入空值和重复值
like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
create fulltext index cft on test(content,tag);
在具体的应用中，一般不会使用数据库管理系统的全文索引，而是引入ElasticSearch这种NoSQL数据库实现

空间索引

空间索引可以用作地理数据存储，通过R树来实现。空间索引会从所有维度来索引数据，使得空间搜索变得高效。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。
space geometry not null,
spatial index index6_sp(space)
创建了一个名为index6_sp的空间索引
索引的优点
可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性

建立索引可以大大提高检索的数据,以及减少表的检索行数
在表连接的连接条件 可以加速表与表直接的相连
在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
建立索引,在查询中使用索引 可以提高性能，但是会影响修改的性能

索引的缺点

在创建索引和维护索引 会耗费时间,随着数据量的增加而增加，所以一般建议一个表上创建的索引数不要超过6个
索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)

注意事项

• 1、索引不会包含有null值的列，在数据库设计时不要让索引字段的默认值为null。
• 2、使用短索
• 3、索引列排序
• 4、like语句操作， like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
• 5、不要在列上进行运算
• 6、不使用not in和<>操作