MySQL索引优缺点
索引优点:
索引缺点:
聚簇索引和非聚簇索引
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储 在一起。
图中,左侧的索引就是聚簇索引,因为数据行在磁盘的排列和索引排序保持一致的
聚簇索引的好处:按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多 个数据块中提取数据,所以节省了大量的 io 操作。
聚簇索引的限制:对于mysql 数据库目前只有 innodb 数据引擎支持聚簇索引,而 Myisam 并不支持聚簇索引。 由于数据物理存储排序方式只能有一种,所以每个 Mysql 的表只能有一个聚簇索引。一般情况下就是 该表的主键。
为了充分利用聚簇索引的聚簇的特性,所以 innodb 表的主键列尽量选用有序的顺序 id,而不建议用 无序的 id,比如 uuid 这种。
在MySQL数据库中使用索引,虽然能够提高数据的查询性能,但是创建的索引并不是越多越好,而需要遵循一定的设计原则。
1.尽量使用小的数据类型的列创建索引
数据类型越小,所占用的存储空间越小,不仅能够节省系统的存储空间,而且处理效率也会更高。例如,同样是整数类型,能够使用TINYINT类型时,就尽量不要使用INT类型。
2.尽量使用简单的数据类型的列创建索引
处理简单的数据类型比复杂的数据类型,系统开销小,因为数据类型越复杂,执行数据的比较操作时采取的比较操作也就越复杂。例如,INT类型与VARCHAR类型,INT类型的数据互相比较时,使用比较运算符直接进行比较即可;VARCHAR类型的数据互相比较时,需要将每个字符转化成对应的ANSI码,再进行比较。
3.尽量不要在NULL值字段上创建索引
在NULL值字段上创建索引,会使索引、索引的统计信息和比较运算更加复杂。因此在创建数据表时,尽量不要使字段的默认值为NULL,将字段设置为NOT NULL,并赋予默认值。
在MySQL的实际应用中,有一些使用场景适合在数据表中创建索引,总结如下:
1.必须为数据表中的主键和外键添加索引。
2.数据表中的数据达到一定量级时,应当为数据表适当添加索引。
3.与其他表进行关联的字段,并且经常进行关联查询时,应当为连接字段创建索引。
4.作为WHERE子句的条件判断字段,并且经常用来进行相等比较操作的字段,应当添加索引。
5.作为ORDER BY语句的字段,并且经常用来执行排序操作的字段,应当添加索引。
6.作为搜索一定范围内的字段,并且经常用来执行查询操作,应当添加索引。
索引有很多种类:
创建唯一索引
索引列的值必须唯一,但允许有空值。如果创建的唯一索引中包含多个字段,也就是复合索引,则索引中包含的多个字段的值的组合必须唯一。
创建主键索引
设定为主键后数据库会自动建立索引,innodb为聚簇索引。主键索引是特殊类型的唯一索引,与唯一索引不同的是,主键索引不仅具有唯一性,而且不能为空,而唯一索引中的列的数据可能为空。
单列索引表示在创建的索引中,只包含数据表中的单个字段或列。MySQL中,支持在一张数据表中创建多个单列索引。
创建复合索引
即一个索引包含多个列,MySQL中,同样支持在一张数据表中创建多个组合索引。在使用组合索引查询数据时,MySQL支持最左匹配原则。
1.使用CREATE TABLE语句创建普通索引
2.使用CREATE INDEX语句创建普通索引
创建全文索引时,对列的数据类型有一定的限制,只能为定义为CHAR、VARCHAR和TEXT数据类型的列创建全文索引,全文索引不支持对列的局部进行索引。
MySQL 支持在GEOMETRY数据类型的字段上创建空间索引。例如,创建名称为t8的数据表,并为t8数据表中的t_location字段创建空间索引。
CREATE TABLE t8 (id INT NOT NULL,t_location GEOMETRY NOT NULL,
SPATIAL INDEX geo_index(t_location));