mysql数据库4---数据库索引

mysql数据库3—数据库索引

1.索引的概念

1.概念

索引是对数据库表中一列或多列的值进行排序的一种结构，能够快速查询到数据。类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据

2.优缺点

优点：

1.提高查询速度

2.唯一索引还能保住数据的唯一性

3.减少数据库需要扫描的数据量

缺点：

1.占用一定的磁盘空间

2.会降低插入、删除、更新表的速度，因为在执行这些操作时，还要操作索引。

2.索引的种类

1.添加索引

（1）普通索引（Normal）

最基本的索引，它没有任何限制

语法：ALTER TABLE 表名字 ADD INDEX 索引名字 ( 字段名字 )

有以下几种创建方式
-- 1.直接创建索引
CREATE INDEX 索引名字 ON 表名字(字段名字)
-- 2.修改表结构的方式添加索引
ALTER TABLE 表名字 ADD INDEX 索引名字 (字段名字)
-- 3.创建表的时候同时创建索引
CREATE TABLE `表名字` (
    `id`  int(11)  auto_increment  primary  key  not  null,
    `title` VARCHAR(25) NOT NULL,
    `time` DATE,
    INDEX 索引名字(字段名字)
);
-- 4.删除索引
DROP INDEX 索引名字 ON 表名

2.主键索引

特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

语法：ALTER TABLE 表名字 ADD PRIMARY KEY ( 字段名字 )

-- 1.修改表结构创建
ALTER TABLE `表名字` ADD PRIMARY KEY ( 字段名字 )
-- 2.建表时创建
CREATE TABLE `表名字` (
    `id`  int(11) auto_increment NOT NULL,
    `title` VARCHAR(25) NOT NULL,
    `time` DATE,
  PRIMARY KEY (字段名字)
);

3.唯一索引（Unique）

索引列的值必须唯一，但允许有空值。

语法：ALTER TABLE 表名字 ADD UNIQUE (字段名字)

有以下几种创建方式
-- 1.创建唯一索引
CREATE UNIQUE INDEX 索引名字 ON 表名字(字段名字)
-- 2.修改表结构的方式添加索引
ALTER TABLE 表名字 ADD UNIQUE 索引名字 (字段名字)
-- 3.创建表的时候同时创建索引
CREATE TABLE `表名字` (
    `id`  int(11)  auto_increment  primary  key  not  null,
    `title` VARCHAR(25) NOT NULL,
    `time` DATE,
    UNIQUE INDEX 索引名字(字段名字)
);

4.全文索引（FullText）

-- 1.修改表结构的方式
ALTER TABLE `表名字` ADD FULLTEXT (`字段名字`)

5.联合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左匹配原则

-- 1.修改表结构的方式
ALTER TABLE `表名字` ADD INDEX 索引名字 ( `字段名字1`, `字段名字2`, `字段名字3`)

3.最左匹配原则

1.什么是最左匹配原则

顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例如：

1.如果建立(a,b)顺序的索引，where b = 2 是匹配不到(a,b)索引的；但是如果查询条件是where a = 1 and b = 2或者where a=1(又或者是where b = 2 and a = 1)就可以，因为优化器会自动调整a,b的顺序。

2.如果建立(a,b,c)顺序的索引，查询where b = 2 and a = 1是有用到索引的，但where c = 2 and a = 1 是没有的，因为少了b的左连接。

2.如果建立(a,b,c,d)顺序的索引，where a = 1 and b = 2 and c > 3 and d = 4 ，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

2.违背最左原则导致索引失效的情况

下面以联合索引建立(a,b,c)顺序来进行讲解

1、查询条件中，缺失优先级最高的索引 “a”
  当 where b = 65 and c = ‘小林’ 这种没有以 a 为条件来检索时，就会去全表扫描了（即不走索引）。

2、查询条件中，缺失优先级居中的索引 “b”
  当 where a =20 and c =“路飞” 这样的数据来检索时；从左开始匹配，由于下一个字段 b 的缺失，所以c不走索引了，但a还是有走索引的，这样的查询语句比直接全表扫描好多了

3.遇到范围查询(>、<、between、like)就会停止匹配,比如 select * from LOL where a = 2 and b > 1000 and c=‘七龙珠’;因为b使用了’>’，所以后面c就无法使用索引了。可以考虑改变索引顺序为（a,c,b）,这样三个字段都可以使用到索引

4.查询优化器

1、如果建的索引顺序是 (a, b)。而查询的语句是 where b = 1 AND a = ‘七龙珠’; 为什么还能利用到索引？是因为 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，内部帮你调整为where a = ‘七龙珠’ AND b = 1 ; 所以 MySQL 不存在 where 子句的顺序问题而造成索引失效。

总结：

1、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

5.建立索引的时机

在WHERE和JOIN中出现的列需要建立索引，但也不完全如此：

• MySQL只对<，<=，=，>，>=，BETWEEN，IN，AND使用索引。
• 某些时候的LIKE也会使用索引，比如而like “aaa%”可以使用索引。
• 在LIKE以通配符%和_开头作查询时，MySQL不会使用索引。

6.explain查看索引，进行sql优化

1.简介

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。通过explain我们可以获得以下信息：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

2.怎么使用？

语法：explain + SQL 
例子：explain select * from User;

3.参数介绍

（1）id

表示SQL执行的顺序的标识，规则如下：
1.id相同，执行顺序由上至下 
2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行sql

（2）select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询，规则如下：
1.SIMPLE(简单SELECT,不使用UNION或子查询等)

2.PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

3.UNION(UNION中的第二个或后面的SELECT语句)

4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

5. UNION RESULT(UNION的结果)

6. SUBQUERY(子查询中的第一个SELECT)

7.DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

8.DERIVED(派生表的SELECT, FROM子句的子查询)

9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

（3）table

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

（4）type

表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL, index,  range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

（5）possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出， 但不一定被查询实际使用

（6）key

实际使用的索引，如果为NULL，则没有使用索引。

（7）key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

（8）ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

（9）rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

（10）Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

(如果出现以上的两种的红色的Using temporary和Using filesort说明效率低)

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

 (复合索引再使用时，尽量的考虑查询时，常用的排序方向和字段组合顺序)

7.注意事项

使用索引时，有以下一些技巧和注意事项：

1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

2.查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

3.like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

4.不要在列上进行运算
这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

5.不使用not in和<>操作