MySQL_MySQL索引基础(重要)

引入:

        对于什么是索引，我们先有一个这样的认识。我们在大学里都去过图书馆，每一本图书管里面的书都是按照一定的分类来进行存放的，如按照学科类型，或者是按照图书名称等。也就是说每一本图书都会有唯一的标识来标识图书的存放位置，那么当我们需要进行查找的时候就只需要按照分类的规则就可以快速的找到我们需要的图书的。类似于这样，数据库在存放数据的时候也可以进行类似的操作。按照我们自己的查找需求建立相关的分类规则，这样就可以快速的查找到我们需要的数据。这一个建立分类的过程就是建立索引。但是值得说的一点就是，如果说你的数据量比较少(一般小于100000或者说更少)，这样建立索引与否对于查询效率是没有多大的影响的。

索引简介

1.什么是索引?

   MySQL官方给出了对索引的定义是:索引(index)是帮助MySQL高效获取数据的数据结构。我们从这一个定义中获取索引的本质:索引是一种数据结构。我们可以理解为索引就是排序好的快速查找的数据结构。

2.使用索引的优缺点

   优点

          (1).在数据量较大的表中进行查找到时候可以较大的提高查询的效率。

          (2).数据库表中的任意字段都可以作为索引。也就是说一张数据库表中的任意字段都可以作为索引。但是建立索引还是需要根据我们实际的需求来创建。

   缺点

          (1).创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

          (2).由于索引实质也是一张数据库表，也是需要占用空间。如果建立了数量较为多的索引，那么空间占用会较大。

          (3).当数据库表的内容发生变化的时候，索引也需要进行相应的改变。(数据库表内容更新的时候，索引也是需要对应更新的)。

3.索引的是使用选择

    通过上述的索引的优缺点，我们可以大致的归结索引的选用原则:

     1.对数据量比较少的数据库表，没有比较建立数据库索引；对于数据量较大的数据库表可以考虑建立索引。

     2.对于增、删、改较多的数据库表不建议建立过多的索引，因为当数据改变的时候，索引也需要更新；对于查询操作比较多的数据库表可以考虑建立索引，这样可以快速的查询出结果。

     3.对于值内容少的列上不建议考虑建立索引，如性别，国籍等。

4.索引的分类

     对于索引，我们这里讲述三个较为常见常用的索引:单列索引(包括:普通索引、唯一索引、主键索引)，组合索引，全文索引

     (1).单列索引

           所谓的单列索引，就是针对数据库表单列所建立的索引。一张数据库表可以建立多个单列索引。

            A:普通索引

                 就是一般的索引，可以对数据库中的基本类型都可以建立，对数据值也没有什么限制。

             B:唯一索引

                 要建立唯一索引，要求对建立的数据库表列的值必须是唯一的，也包括null值。

             C:主键索引

                  一种比较特殊的所有，要求建立的数据库列的值是唯一的，同时不能够是null值，可以理解为是对数据库的表的主键建立的索引。

        (2).组合索引

             所谓的组合索引，就是可以针对多个数据库表中的列建立缩影。但是值得注意的是组合索引遵循一个"最左前缀原则",就是说你再查询的时候最左的优先。这一个和btree的数据结构有关，它是按照从左到右的顺序来建立搜索树的。

       (3).全文索引

             所谓的全文索引，就是在一定的字符串范围内进行对关键字的查找。

             要建立全文索引，要求

             ①、建立的字段类型是和字符串相关的，如char,varchar,text等。

             ②、建立全文索引的表的存储引擎是MyISAM

5.索引的操作(创建以及删除)

        (1).创建索引

创建索引格式:

格式1:在创建表的时候创建索引

      create table 表名[字段名 数据类型]  [unique|fulltest...] [index|key] [索引名称] (字段名[length])

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



格式2:在已有表的基础上创建索引
       
       (1).修改表结构的时候创建
    
       alter table 表名 add[unique|fulltest...] [index|key] [索引名称] (索引字段名[length])]
       
       (2).直接在表上面创建

      create index 索引名称 ON 表名(字段名称(length)); 

       下面我们就使用创建索引的格式来创建一个索引。

        方式1:在创建表的时候创建索引

         如:在创建Student表的时候我们在字段st_name上建立索引

        方式2:在已有的表上创建索引。

        已有数据库表如下:user表，我们给username创建一个索引

创建索引指令以及结果如下: 

     (2).查看所创建的索引

查看已建好的索引语法:

show index from 表名;

如查看在user表中username上的索引:

      (3).删除索引

删除索引的语法

drop index [indexName] ON  表名称; 

    如:删除我们之前user中在username上建好的索引:

6.索引的基本使用测试

     (1).创建测试数据库表

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bookname` varchar(50) DEFAULT NULL,
  `bookauthor` varchar(50) DEFAULT NULL,
  `booktype` varchar(50) DEFAULT NULL,
  `bookprice` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

     (2).导入较大量的数据(62834),使用流程控制语句直接插入数据

DELIMITER $
CREATE PROCEDURE test_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO book(bookname,bookauthor,booktype,bookprice) 
                    VALUES(CONCAT('测试书名',i),CONCAT('张三',i),'科普读物',i);
		SET i=i+1;
	END WHILE;
	
END $

CALL test_while(62834)$

     (3).未建索引查询测试,查找出作者为"张三50000"的记录

      A:开启查看SQL语句执行时间的功能

 B:执行查询语句，并查看执行sql的花费时间

  (4).建立索引查询测试

A:在bookauthor上创建索引

B.查看查询语句是否在查询的时候使用了索引

C:进行数据的查询以及耗时显示