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

建表注意

1、建议字段定义为NOT NULL

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

搜索引擎

MyISAM
InnoDB
区别

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

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

sql优化

任何地方都不要使用 select * from t ,用具体的字段列表代替“*”
尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
尽量避免大事务操作,提高系统并发能力。

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

索引

  • 索引的创建

    普通索引
    CREATE INDEX IndexName ON TableName(字段名(length))
    ALTER TABLE TableName ADD INDEX IndexName(字段名(length))
    组合索引
    CREATE INDEX IndexName On TableName(字段名(length),字段名(length),…);
    全文索引
    ALTER TABLE tablename ADD FULLTEXT(column1, column2)

  • 索引的优点

    1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
    2.建立索引可以大大提高检索的数据,以及减少表的检索行数
    3.在表连接的连接条件 可以加速表与表直接的相连
    4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
    5.建立索引,在查询中使用索引 可以提高性能

  • 索引的缺点

    1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
    2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
    3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,
    (建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快)。

  • 需要注意的地方

    1.在经常需要搜索的列上,可以加快索引的速度
    2.在表与表的而连接条件上加上索引,可以加快连接查询的速度
    3.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间,
    (单独order by 用不了索引,索引考虑加where 或加limit)
    4.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
    5.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname like ‘%ABC%’
    那么这个索引讲不会起到作用.而nickname like ‘ABC%’ 那么将可以用到索引
    6.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,
    复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ‘ ‘字符串
    7.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符
    (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.
    8.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
    9.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

  • 什么情况不需要索引

    1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
    2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中
    ,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
    3.定义为text和image和bit数据类型的列不应该增加索引,
    4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

 

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

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

本文使用的案例的表

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.单列索引

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

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

第一种方式
:

  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)  

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

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

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

 

2.组合索引

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

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

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

 

菲律宾太阳 1

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

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

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

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

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

发表评论

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