MYSQL 索引类型、什么状况下用不上索引、什么状况下不推荐使用索引

2021年09月16日 阅读数:3
这篇文章主要向大家介绍MYSQL 索引类型、什么状况下用不上索引、什么状况下不推荐使用索引,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

索引类型mysql

在数据库表中,对字段创建索引能够大大提升查询速度。假如咱们建立了一个 mytable表sql

代码以下:数据库

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );函数

咱们随机向里面插入了10000条记录,其中有一条:5555, admin。大数据

在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,若是在username上已经创建了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描全部记录,即要查询10000条记录。索引

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表能够有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。ci

 

MySQL索引类型包括:字符串

1、普通索引数学

这是最基本的索引,它没有任何限制。它有如下几种建立方式:it

1.建立索引

代码以下:

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

若是是CHAR,VARCHAR类型,length能够小于字段实际长度;若是是BLOB和TEXT类型,必须指定 length,下同。

2.修改表结构

代码以下:

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;

2、惟一索引

它与前面的普通索引相似,不一样的就是:索引列的值必须惟一,但容许有空值。若是是组合索引,则列值的组合必须惟一。它有如下几种建立方式:

代码以下:

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))   );

3、主键索引

它是一种特殊的惟一索引,不容许有空值。通常是在建表的时候同时建立主键索引:

代码以下:

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID)   );

固然也能够用 ALTER 命令。记住:一个表只能有一个主键。

4、组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

代码以下:

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);

 

什么状况下有索引,但用不上?

索引并非时时都会生效的,好比如下几种状况,将致使索引失效:

若是条件中有or,即便其中有部分条件带索引也不会使用(这也是为何尽可能少用or的缘由),例子中user_id无索引


注意:要想使用or,又想让索引生效,只能将or条件中的每一个列都加上索引

2.对于多列索引,不是使用的第一部分,则不会使用索引

3.like查询是以%开头

 

4.存在索引列的数据类型隐形转换,则用不上索引,好比列类型是字符串,那必定要在条件中将数据使用引号引用起来,不然不使用索引

 

5. where 子句里对索引列上有数学运算,用不上索引

 

6. where 子句里对有索引列使用函数,用不上索引

 

7.若是mysql估计使用全表扫描要比使用索引快,则不使用索引

好比数据量极少的表

什么状况下不推荐使用索引?

1) 数据惟一性差(一个字段的取值只有几种时)的字段不要使用索引

好比性别,只有两种可能数据。意味着索引的二叉树级别少,可能是平级。这样的二叉树查找无异于全表扫描。

2) 频繁更新的字段不要使用索引

好比logincount登陆次数,频繁变化致使索引也频繁变化,增大数据库工做量,下降效率。

3) 字段不在where语句出现时不要添加索引,若是where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件,不建议使用索引

只有在where语句出现,mysql才会去使用索引

4) where 子句里对索引列使用不等于(<>),使用索引效果通常