Mysql 学习笔记（三）

索引（index）

（1） 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集 合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

（2）优点：

大大加快数据的检索速度;创建唯一性索引，保证数据库表中每一行数据的唯一性;加速表和表之间的连接;在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

（3）缺点

索引需要占物理空间，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的 维护速度。

（4）索引的分类

➢ 普通索引：这是最基本的索引，它没有任何限制

➢ 唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允 许有空值。如果是组合索引，则列值的组合必须唯一

➢ 主键索引（通过主键约束间接创建）：它是一种特殊的唯一索引，不允许有空值。 一般是在建表的时候同时创建主键索引

➢ 组合索引：在表中的多个列上创建的索引。组合索引中列的顺序是任意的，可以 是相邻的列，也可以是不相邻的列。

（1）索引的创建

➢ 普通（唯一）索引的创建：

（1）CREATE [UNIQUE] INDEX index_name ON tbl_name(index_col_name,…)

注： UNIQUE：唯一 ，index_name：索引名， tbl_name： 增加索引的表名， index_col_name： 对哪列进行索引，多列时用逗号隔开。

index_col_name: col_name[(length)][ASC|DESC]

注： 对于字符类型的列，可以编制“前缀索引”，Length 表示按照列的指定长度的字符串 索引

（2） alter table table_name add index index_name (column_list) ;

alter table table_name add unique (column_list) ;

alter table table_name add primary key (column_list) ;

其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

➢ 创建组合索引：

CREATE INDEX index_name ON tbl_name(index_col_name1,index_col_name2,..)

基于（列 A，列 B）两列创建索引：

可应用索引的情况：A;AB 两列结合;

不可用索引的情况：B

Create index index_name on table_name(列 A，列 B)；

索引--1 索引--2

（2）查看索引

show index from tab_name;

注：tab_name:表名

（3）删除索引

drop index index_name from tab_name;

注： index_name ：索引名， tab_name：表名

索引--3

（4）使用索引

强制使用索引

select * from tab_name force index(index_name) where [查询的条件]；

禁止使用索引

select * from tab_name ignore index(index_name) where [查询的条件];

索引--4

（5） EXPLAIN 解释命令 查看索引是否生效

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了：

例：explain select * from sc where cno='c001';

下面给出图示，可以看出其中的差别，可以发现type和rows的变化

索引--5 未使用索引 索引--6 使用索引

（6）EXPLAIN 列的解释

➢ table：显示这一行的数据是关于哪张表的

➢ type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

➢ possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

➢ key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

➢ key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

➢ ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

➢ rows：MYSQL认为必须检查的用来返回请求数据的行数

➢ Extra：关于MYSQL如何解析查询的额外信息。但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢extra列返回的描述的意义

➢ Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

➢ Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

➢ Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

➢ Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

➢ Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

➢ Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接

➢ type 类型的解释（按照效率高低的顺序排序）

➢ system                表只有一行

➢ const                   表最多只有一行匹配，通用用于主键或者唯一索引比较时

➢ eq_ref                  每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种， 特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引

➢ ref                         如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键

➢ fulltext                     全文搜索

➢ ref_or_null               与ref类似，但包括NULL

➢ index_merge           表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。 这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）

➢ unique_subquery       在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。 PS：所以不一定in子句中使用子查询就是低效的

➢ index_subquery           同上，但把形如”select non_unique_key_column“的子查询替换

➢ range                            这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

➢ index                             这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

➢  ALL                                这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

附，例子的表和数据

（后续的视图和触发器将使用相同的表和数据进行操作）

create table student( sno varchar(10) primary key, sname varchar(20), sage int(2), ssex varchar(5) );

create table sc( sno varchar(10), cno varchar(10), score double, constraint pk_sc primary key (sno,cno) );

insert into student values ('s001','张三',23,'男');

insert into student values ('s002','李四',23,'男');

insert into student values ('s003','吴鹏',25,'男');

insert into student values ('s004','琴沁',20,'女');

insert into student values ('s005','王丽',20,'女');

insert into student values ('s006','李波',21,'男');

insert into student values ('s007','刘玉',21,'男');

insert into student values ('s008','萧蓉',21,'女');

insert into student values ('s009','陈萧晓',23,'女');

insert into student values ('s010','陈美',22,'女');

insert into sc values ('s001','c001',78.9);

insert into sc values ('s002','c001',80.9);

insert into sc values ('s003','c001',81.9);

insert into sc values ('s004','c001',60.9);

insert into sc values ('s001','c002',82.9);

insert into sc values ('s002','c002',72.9);

insert into sc values ('s003','c002',81.9);

insert into sc values ('s001','c003',59.0);