数据库索引知识

索引

原文链接：https://blog.csdn.net/weixin_42181824/article/details/82261988

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的，在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记录。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

1) 创建表时创建普通索引
CREATE TABLE table_name （
属性名 数据类型，
属性名 数据类型，
… ….
属性名 数据类型，
INDEX | KEY 【索引名】（属性名1 【（长度）】【ASC | DESC】）
);

示例：
CREATE TABLE t_dept（
deptno INT,
dname VARCHAR(20),
loc VARCHAR(40),
INDEX index_deptno(deptno)
);

普通索引:

CREATE INDEX indexName ON mytable(username(length));

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

修改表结构：

ALTER mytable ADD INDEX [indexName] ON (username(length))

 

创建表的时候直接指定：
CREATE TABLE mytable( 
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL, 
INDEX [indexName] (username(length)) 
);  
删除索引：

DROP INDEX [indexName] ON mytable;

 

CREATE TABLE mytable4(  
stuid INT NOT NULL,  
username VARCHAR(16) NOT NULL,
city VARCHAR(50) NOT NULL,
age INT NOT NULL,
stuname VARCHAR(16)  NOT NULL,
PRIMARY KEY(stuid),
INDEX index_age(age)
);

 

唯一索引

创建索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构：

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

 

创建表的时候直接指定：
CREATE TABLE mytable( 
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL, 
UNIQUE [indexName] (username(length)) 
);  

主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
CREATE TABLE mytable( 
ID INT NOT NULL,  
username VARCHAR(16) NOT NULL, 
PRIMARY KEY(ID) 
); 

组合索引

CREATE TABLE mytable(  
    ID INT NOT NULL,  
    username VARCHAR(16) NOT NULL, 
    city VARCHAR(50) NOT NULL, 
    age INT NOT NULL
    );

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。相当于分别建立了下面三组组合索引：usernname,city,age    usernname,city   usernname 
下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州"

SELECT * FROM mytable WHREE username="admin"

建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引

只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引：

SELECT * FROM mytable WHERE username like'admin%' 会使用

SELECT * FROM mytable WHEREt Name like'%admin' 不会使用索引

对已经存在的表创建索引

CREATE INDEX 索引名
ON 表名 （属性名 【（长度）】 【ASC | DESC】）
案例：
CREATE INDEX index_deptno
ON t_dept （detpno）；

 

sql语句优化

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

下面的查询也将导致全表扫描： select id from t where name like '%abc%'

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描：select id from t where num/2=100

 in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3)

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致