mysql索引学习

一、索引创建的三个方法：

        1、建表时增加索引create table

                create table forIndex (

                         ID INT AUTO_INCREMENT ,

                         NAME VARCHAR(30) NOT NULL,

                         AGE TINYINT,

                         IDCARD VARCHAR(18) UNIQUE,

                         GENDER TINYINT,

                         PRIMARY KEY PRIMARY_KEY (ID),

                         INDEX INDEX_NAME (NAME)

               )

        2、alter table add index

               ALTER TABLE forIndex ADD PRIMARY KEY(ID);新增主键

              ALTER TABLE forIndex ADD INDEX INDEX_NAME(NAME);新增普通索引

               ALTER TABLE forIndex ADD UNIQUE INDEX UNIQUE_INDEX_IDCARD(IDCARD);新增唯一索引

       3、create index

                这种方法无法指定主键

              CREATE INDEX INDEX_NAME ON forIndex (NAME);新增普通索引

               CREATE UNIQUE INDEX UNIQUE_INDEX_IDCARD ON forIndex (IDCARD);增加唯一索引

补充：bigint 从-2^63(-9223372036854775808)~2^63-1(9223372036854775807)的整型数据，8个字节（建表时的length只是用于显示的长度）；

      int从-2^31(-2,147,483,648)~2^31-1(2,147,483,647)的整型数据，4个字节；

      smallint从-2^15(-32,768)~2^15-1(32,767)的整型数据，2个字节；

      tinyint从0~255,1个字节；

二、索引分类：

        1、普通索引：最基本的索引类型，无唯一性限制创建方式如一

        2、唯一索引：基本索引加上值必须唯一的限制创建方式如一

        3、主键       ：是一种唯一索引用PRIMARY KEY指定方式如一

        4、全文索引：可在text或者varchar类型的字段上创建方式如下：

               新增一个varchar类型字段description

                ※ ALTER TABLE forIndex ADD DESCRIPTIONVARCHAR(255) ；此处如果不加(数字)无法成功 

                ※可以在创建表的时候直接创建全文索引但是这样效率不高

                ※使用ALTER table forIndex ENGINE=myisam(5.6版本以下innodb存储引擎不支持全文索引)

                    ALTER TABLE forIndex ADD FULLTEXT INDEXfulltext_description(description)

三、索引使用

      1、单列索引：单列索引顾名思义就是单个字段做单独索引

        例如：Name字段加了索引，直接使用

        SELECT *FROM forIndex where Name='n'

        

      2、多列索引：顾名思义多列组成一个索引

        ALTER TABLEforIndex ADD INDEX INDEX_MULTI_NAME_FIRSTNAME(FIRSTNAME,NAME);

        ALTER TABLEforindex DROP INDEX INDEX_NAME;删除INDEX_NAME索引

        select *from forindex where FIRSTNAME='赵' AND`NAME`='C';   使用索引INDEX_MULTI_NAME_FIRSTNAME  

     

        select *from forindex where FIRSTNAME='赵' ANDage=3;  使用索引INDEX_MULTI_NAME_FIRSTNAME

        select *from forindex where FIRSTNAME='赵';  使用索引INDEX_MULTI_NAME_FIRSTNAME

     

        select *from forindex where NAME='C';  不使用索引INDEX_MULTI_NAME_FIRSTNAME

      3、可以考虑使用索引的主要有两种类型的列：在WHERE子句中出现的列，在join子句中出现的列;

四、索引的缺点

      虽然索引能提高查询效率，但是会降低更新的效率，并且索引也需要占用磁盘空间