Mysql入门（四）索引的分类、建立、删除与优化

最新推荐文章于 2024-09-12 01:15:37 发布

渐行渐远-渐无声

最新推荐文章于 2024-09-12 01:15:37 发布

阅读量121

点赞数 1

分类专栏：个人成长文章标签： mysql

本文链接：https://blog.csdn.net/qq_39830532/article/details/96140075

版权

个人成长专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Mysql索引

索引是一种将数据库中单列或者多列的值进行排序的结构。应用索引能够大幅度提高查询的速度，降低服务器的负载。mysql中应用索引可以提高检索且可以提高系统的整体性能，但创建与维护索引需要耗费时间，并且耗费的时间与数据量的大小成正比，另外，索引需要占用物理空间，给数据的维护造成很多麻烦。
不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引，总索引长度至少为256个字节。有些索引支持更多的索引数和更大的索引长度。索引共有两种存储类型，包括B树（BTREE）索引和哈希（HASH）索引。B树为系统默认索引方法。

索引的分类

普通索引。即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或者唯一。
唯一索引。使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某一条记录，主键是一种特殊索引。
全文索引。使用FULLTEXT参数可以设置索引为全文索引，全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用它可以提高查询速度。在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序后，可以执行大小写敏感的全文索引。
单列索引。单列索引只对应一个字段的索引，可以包括上述的三种索引方式，应用该索引的条件只需要保证该索引值对应一个字段即可。
多列索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。想要应用该索引，用户必须使用这些字段的第一个字段。
空间索引。使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中只有MyISAM存储引擎支持空间引擎，且索引的字段不能为空。

索引的建立

在创建数据库时创建
这种方式比较直接，且方便、易用。其基本语法结构如下：
create table table_name(
属性名数据类型[约束条件],
属性名数据类型[约束条件],
属性名数据类型[约束条件]
…
索引名数据类型
[UNIQUE | FULLTEXT | SPATIAL ] INDEX [ 别名](属性名1[(长度)] [ASC | DESC])
)
其中UNIQUE可选项表明索引为唯一索引，FULLTEXT表明全文索引，SPATIAL表明空间索引。INDEX用于指定字段名。
例：create table address(id int(11) auto_increment primary key not null,name varchar(50),
address varchar(200),UNIQUE INDEX address(id ASC));
在已建立的数据表中创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON
table_name(属性[(length)][ASC | DESC]);
index_name 为索引名称
table_name 为表名
可选参数，指定索引类型。UNIQUE（唯一索引），FULLTEXT（全文索引），SPATIAL（空间索引）
属性参数，指定索引对应的字段名，该字段必须已经存在与用户操作的表中，不存在会报错
lengh为可选参数，指定索引长度。
ASC与DESC指定数据表的排序顺序。
例如：create UNIQUE INDEX index1_id ON index(cid);
修改数据表结构添加索引
ALTER TABLE table_name ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name(
属性名 [(length)] [ASC | DESC];
例如：alter table 表名 add unique index 索引名称(字段名);

索引的删除

DROP INDEX index_name ON table_name;

建索引的几大规则

mysql的使用规范

最左前缀匹配原则，数据库会一直向右匹配直到遇到范围查询就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的；
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序；
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，所以最好别对这些字段创建索引；
索引列不能参与计算，保持列“干净”，因为B+树中存的都是数据表中的字段值，但进行检索的时候，需要把所有元素都应用函数才能比较，显然成本太大；
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可；
索引要建立在查询频繁的字段上。这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

索引优化

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；例子：select id from t where num is null
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描；（可以使用union来代替or）
in 和 not in 也要慎用，因为in会使系统无法使用索引，而只能直接搜索表中的数据，对于连续的数值，能用 between 就不要用 in；
尽量避免在索引过的字符数据中，使用非打头字母搜索，这也使得引擎无法利用索引；比如：SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
应尽量避免在 where 子句中对字段进行表达式操作或者函数操作，都会导致引擎放弃使用索引而进行全表扫描；比如：SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’