从头开始学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