1、普通索引
CREATE INDEX indexName ON mytable(username(length));
创建表的时候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
CREATE INDEX idx_name ON table_name (column_name);
在创建索引时需要注意以下几点:
- 索引会占用额外的存储空间,因此需要权衡空间占用和查询效率之间的关系。
- 索引的选择应该根据实际查询需求来确定,不是所有字段都需要创建索引。
- 索引的维护会对数据库的写操作产生一定的性能影响,因此需要根据实际情况来决定是否创建索引。
建立索引时需要根据具体情况选择合适的索引策略,以下是一些常用的索引策略:
-
单列索引:对单个字段创建索引,适用于经常用于查询条件的字段。例如,对用户表的用户ID字段创建单列索引。
-
复合索引:对多个字段创建联合索引,适用于经常一起作为查询条件的字段。例如,对订单表的用户ID和订单日期字段创建复合索引。
-
唯一索引:对字段创建唯一索引,确保字段的数值唯一性。例如,对用户表的邮箱字段创建唯一索引。
-
聚簇索引:将数据存储在索引中,适用于经常需要按照某个字段排序或分组的查询。例如,对订单表的订单ID字段创建聚簇索引。
-
覆盖索引:包含了查询所需的所有字段,避免了回表操作,提高查询效率。例如,对订单表的订单ID和订单金额字段创建覆盖索引。
-
索引前缀:对字段的前缀创建索引,适用于字段值较长的情况。例如,对用户表的手机号码字段的前几位数字创建索引。
-
索引优化器:根据查询的实际情况选择最优的索引使用方式,避免不必要的全表扫描。
在选择索引策略时,需要考虑查询频率、数据量、数据类型以及查询需求等因素,以提高查询效率并减少数据库的性能消耗。同时,定期对索引进行优化和维护也是非常重要的,可以通过数据库性能分析工具来监控索引的使用情况,及时调整索引策略以提高数据库性能。
删除索引的语法:
DROP INDEX [indexName] ON mytable;
2、唯一索引
它与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
创建索引:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构:
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定:
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
有四种方式来添加数据表的索引:
1.ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
2.ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
3.ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。4.ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
例如:
创建索引:ALTER TABLE testalter_tbl ADD INDEX (c);
删除索引:ALTER TABLE testalter_tbl DROP INDEX (c);
显示索引信息
SHOW INDEX FROM table_name\G