从头开始学MySQL--------索引

9.1.1  索引是什么

        索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据库表里所有记录的引用指针。使用索引用于快速找出在某个或者多个列中有一特定值的行。对相关列使用索引是提高查询操作速度的最佳途径。

        如果有2万条记录，现在执行:SELECT * FROM table WHERE num = 10000；如果没有索引，那么需要遍历整个表，直到找到这一行为止。如果在num这列上建立索引，MySQL将不需要任何扫描，直接在索引里面找10000，就可以得知这一行的位置。

        原来，索引就是把这一列的数据单独抽取出来，下次查询的时候，不需要查询以前的表，只需要查询索引，然后在索引中找到了那条数据以后，返回那条数据在表中的位置。那么就可以精确的在表里定位到那条记录的位置，从而避免全表扫描。

索引的优点：

        （1）创建唯一索引，可以限制表的某列数据唯一，效果就像为那列数据加上了UNIQUE关键字一样。

        （2）加快查询速度，这是创建索引最核心的原因。从4秒钟的查询时间变成了几毫秒，简直不可思议。

        （3）在使用分组和排序子句进行数据查询的时候，显著减少查询中分组与排序的时间。

索引的缺点：

        占空间，难维护。因为索引是存储在物理磁盘上的，所以占磁盘。在数据库中数据修改的时候，索引也会关联变动，降低了数据的维护速度，即维护索引较为耗时。

        

9.1.2  索引分类

        普通索引：MySQL的基本索引，索引列可以插入空值与重复值。

        唯一索引：索引列的值必须唯一，但允许为空。效果就像为列加上了UNIQUE关键字。

                          主键是一种特殊的唯一索引，不允许为空值。

         单列索引：一个索引只包含一个列。

         组合索引：在多个字段组合上创建的索引，只有在查询条件中使用了这些字段的最左边字段时，索引才会被使用。

                           这个原则称为"最左前缀"。

9.1.3  索引设计原则

       索引数量并非越多越好，因为索引占磁盘空间。且表中数据更改时，索引也会更新。就像目录一样。

       对经常用于查询的字段设计索引。索引列尽可能少，避免添加不必要的字段。

       数据量小的表不要用索引。就像正文没一两页，还去查目录一样。

       不同值较多的列上建立索引。相反，如果列上值较少，比如“男、女”，加了索引只会降低数据更新速度。

       在频繁进行排序与分组的表上建立索引。如果排序列有多个，可以建立组合索引。

       当唯一性是某种数据的特征时，可以指定唯一索引。

       尽量使用短索引。对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。比如在CHAR(255)的列，如果在前10个或者30个字符，多数值是唯一的，则不需要对整个列进行索引。

9.2.1  建表时创建索引

DROP TABLE IF EXISTS t_student;

CREATE TABLE t_student (
  id INT (11) PRIMARY KEY AUTO_INCREMENT,     -- 特殊的唯一索引
  NAME VARCHAR (255),
  address VARCHAR (255),
  age INT (11), 
  idCard INT (11) UNIQUE,                     -- UNIQUE等关键字本事就是索引
  INDEX nameIdx (NAME(3)),                    -- 普通索引，前3个字就差不多能确定了
  UNIQUE INDEX idCardIndex (idCard),          -- 身份证，唯一索引
  INDEX mutiIdx (address(40), age)            -- 组合索引
);

SHOW INDEX FROM t_student;

SHOW CREATE TABLE t_student;

-- 结果

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `idCard` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idCard` (`idCard`),
  UNIQUE KEY `idCardIndex` (`idCard`),
  KEY `nameIdx` (`name`(3)),
  KEY `mutiIdx` (`address`(40),`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

         对于组合索引来说，并不是查询哪个字段都会使用索引，而是遵从“最左前缀”。例如，id、name、age组成的组合索引，索引行按照id name age的顺序存放，索引可以使用下面的组合：（id,name,age）、(id,name)、(id,age)、(id)

        （name,age）或者name等组合则不能使用索引查询。

  

INSERT INTO t_student VALUES(1,'大宇','苏州',22,320631);

  

EXPLAIN SELECT * FROM t_student;

        possible_keys和key的值为空，说明上述查询语句没有使用索引。 

  

        接下来使用多列索引mutiIdx。（address，age），这两个的组合顺序可以颠倒，因为颠倒了也会有顺序最左的address。

EXPLAIN SELECT * FROM t_student WHERE address='苏州' AND age = 22;

        根据上述结果来看，查询address与age使用了索引mutiIdx。

        另外一种创建索引的写法。

CREATE INDEX [IndexName] ON TABLE(COLUMN);
CREATE INDEX indexName ON t_teacher(id);

        

9.2.2  在已经存在的表上创建索引

DROP TABLE IF EXISTS t_teacher;

CREATE TABLE t_teacher (
  id INT (11) PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (255),
  classId INT (11),
  dept VARCHAR (255)
);

        需求：在name这列上添加普通索引，为dept列添加唯一索引。

ALTER TABLE t_teacher ADD INDEX teacName(name(3));     -- 为name列添加索引

ALTER TABLE t_teacher ADD UNIQUE INDEX uniqDept(dept); -- 同时限制了dept这列数据要唯一

       查看t_teacher表的索引。

SHOW INDEX FROM t_teacher;

EXPLAIN SELECT * FROM t_teacher WHERE NAME = '田老师';

        可以看到，执行SQL后，t_teacher表使用了teacName索引。而这个索引是name列对应的索引。

        possible_keys是指可能使用的索引， key是指实际使用的索引。

        如果要添加组合索引，只需要在括号里用逗号隔开各个列。

ALTER TABLE t_teacher ADD INDEX mutiIdx(classId,dept(5));

9.2.3  删除索引

SHOW INDEX FROM t_teacher;

        删除名为teacName的索引。

ALTER TABLE t_teacher DROP INDEX teacName;

SHOW INDEX FROM t_teacher;

        添加AUTO_INCREMENT约束字段的唯一索引不能被删除。注：因为主键是一种特殊的唯一索引，所以不允许被删除。

        删除表中的列的时候，如果删除的列是索引的组成部分，那么该列也会从索引中删除。

        如果组成索引的所有列都被删除，那么这个索引将会被删除。

 

                   

补充        2019年11月18日：今天发现一个非常奇怪的现象。下面的SQL语句死活不走索引。原因是：

        type字段类型为varchar，而SQL中是数字类型，所以不走索引。因此，大家也注意一下。需要把 2 修改为 字符串2："2"。 

SELECT
  t. YEAR AS NAME,
  t.industry_name AS type,
  t.rate AS
VALUE

FROM
  `t_employment_industry_rate` t
WHERE
  t.is_deleted = 0
AND t.school_id = 1018836520917407
AND t.type = 2