细说mysql索引

本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方

本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方

首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname=’css’,如果要拿这条数据的话需要些的sql是
SELECT * FROM award WHERE nickname = ‘css’

首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname=’css’,如果要拿这条数据的话需要些的sql是
SELECT * FROM award WHERE nickname = ‘css’

一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname=’css’的数据,是不是感觉性能提升了好多咧….

一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname=’css’的数据,是不是感觉性能提升了好多咧….

mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

单列索引:一个索引只包含一个列,一个表可以有多个单列索引.

单列索引:一个索引只包含一个列,一个表可以有多个单列索引.

组合索引:一个组合索引包含两个或两个以上的列,

组合索引:一个组合索引包含两个或两个以上的列,

本文使用的案例的表

本文使用的案例的表

CREATE TABLE `award` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
   `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
   `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
   `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
   `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
   `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
   `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
   `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

图片 1

(一)索引的创建

CREATE TABLE `award` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
   `aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
   `nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
   `is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
   `award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
   `account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
   `password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
   `message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
   `created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
   `updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';

1.单列索引

图片 2

1-1)  
 普通索引,这个是最基本的索引,

(一)索引的创建

其sql格式是
CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER
TABLE TableName ADD INDEX IndexName(`字段名`(length))

1.单列索引

第一种方式
:

1-1)    普通索引,这个是最基本的索引,

  CREATE INDEX account_Index ON `award`(`account`);

其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length))
或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

第二种方式: 

第一种方式 :

ALTER TABLE award ADD INDEX account_Index(`account`)
  CREATE INDEX account_Index ON `award`(`account`);

 

第二种方式: 

 

ALTER TABLE award ADD INDEX account_Index(`account`)

 

 

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

 

1-2)  
 唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

 

其sql格式是 CREATE
UNIQUE INDEX IndexName ON `TableName`(`字段名`(length));
或者 ALTER TABLE TableName ADD UNIQUE (column_list)  

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

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

1-2)  
 唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

1-3)  
 主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

其sql格式是 CREATE UNIQUE INDEX IndexName ON
`TableName`(`字段名`(length));
或者 ALTER TABLE TableName ADD UNIQUE (column_list)  

主键索引建立的规则是
int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为
int 而且是 AUTO_INCREMENT自增类型的

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

 

1-3)  
 主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

2.组合索引

主键索引建立的规则是
int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为
int 而且是 AUTO_INCREMENT自增类型的

一个表中含有多个单列索引不代表是组合索引,通俗一点讲
组合索引是:包含多个字段但是只有索引名称

 

其sql格式是 CREATE INDEX IndexName On
`TableName`(`字段名`(length),`字段名`(length),…);

2.组合索引

 CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);

一个表中含有多个单列索引不代表是组合索引,通俗一点讲
组合索引是:包含多个字段但是只有索引名称

 

其sql格式是 CREATE INDEX IndexName On
`TableName`(`字段名`(length),`字段名`(length),…);

图片 3

 CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);

如果你建立了
组合索引(nickname_account_createdTime_Index)
那么他实际包含的是3个索引 (nickname)
(nickname,account)(nickname,account,created_time)

 

在使用查询的时候遵循mysql组合索引的”最左前缀”,下面我们来分析一下
什么是最左前缀:及索引where时的条件要按照建立索引的时候字段的排序方式

图片 3

1、不按索引最左列开始查询(多列索引) 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where
`c2` = `aaa` and `c3`=`sss` 不能使用索引

如果你建立了 组合索引(nickname_account_createdTime_Index)
那么他实际包含的是3个索引 (nickname)
(nickname,account)(nickname,account,created_time)

2、查询中某个列有范围查询,则其右边的所有列都无法使用查询(多列查询)

在使用查询的时候遵循mysql组合索引的”最左前缀”,下面我们来分析一下
什么是最左前缀:及索引where时的条件要按照建立索引的时候字段的排序方式

Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

1、不按索引最左列开始查询(多列索引) 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where
`c2` = `aaa` and `c3`=`sss` 不能使用索引

发表评论

电子邮件地址不会被公开。 必填项已用*标注