Mysql基础知识,索引

1.为什么用索引

       关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。所以我们需要使用索引来进行sql的优化.

1.1 使用索引与未使用索引的对比

对比一下使用了索引和非使用索引(在2000w的大表中测试)的区别:

未使用索引:

使用索引:

就结果来说,相差了整整的540倍.

1.2 索引的基本原理

         在MySQL里常用的索引数据结构有B+树索引和哈希索引两种:

   B+树索引

    B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。所以它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量.

    

 

  哈希索引

   哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。但是只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

   

 

1.3 什么时候用

  什么时候【要】创建索引

•   （1）表经常进行 SELECT 操作
•   （2）表很大(记录超多)，记录内容分布范围很广
•   （3）列名经常在 WHERE 子句或连接条件中出现

  什么时候【不要】创建索引

•   （1）表经常进行 INSERT/UPDATE/DELETE 操作
•   （2）表很小(记录超少)
•   （3）列名不经常作为连接条件或出现在 WHERE 子句中

2.索引的分类

   MySQL目前主要有以下几种索引类型：

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索

2.1 普通索引

    是最基本的索引，它没有任何限制。我们可以在Navicat for MySQL中简单的创建一个普通索引:

2.2 唯一索引

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

2.3 主键索引

    是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

2.4 组合索引

    指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则,我们可以使用Navicat for MySQL创建组合索引.

最左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如member2表的names,Nation和Gender加联合索引就是(names,Nation,Gender)而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

在查询的时候,我们使用以下sql语句,会有不同的效果:

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDERBY子句也遵循此规则。 

•      SELECT * FROM `member2` WHERE member2.` names` = "王杰" AND member2.Gender="M"   命中索引
•      SELECT * FROM `member2` WHERE member2.` names` = "王杰" AND member2.Nation="汉"    命中索引
•      SELECT * FROM `member2` WHERE member2.` names` = "王杰"    命中索引
•      SELECT * FROM `member2` WHERE member2.` Nation ` = "王杰" AND member2.Gender="M"   未命中索引                                  

2.5 全文索引

    主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。强烈注意：MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效.

跟普通索引稍有不同

     使用全文索引的格式：  MATCH (columnName) AGAINST ('string')

           SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

     当查询多列数据时：建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。

           SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')

之后我们还是利用Navicat for MySQL来创建全文索引:

我们可以注意的是,此时只需要声明是全文索引就可,索引方法为空;

我们可以对比一下全文索引和普通索引:

普通索引

全文索引

这里就奇怪了,为什么我们全文索引要比单个的普通索引耗时还要长呢??其实我们需要相对的来看,我们这里只是搜索了一个Address(地址)字段,字段长度也就不过100,如果我们这里面查询的是文章详情等大型的字段,那么我们全文索引的优势就显现出来了.

最后,需要我们在Mysql.ini中配置:

    # 不使用停止词 
    ft_stopword_file = '' 
    # 最少单词 为1个字的英文 
    ft_min_word_len = 1 

这样就可以实现了我们中文的模糊搜索;

 

3.定位及优化SQL语句的性能

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划。
我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。
而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

使用关键字EXPLAIN查看sql的执行情况:

 

图片描述

执行计划包含的信息
id
有一组数字组成。表示一个查询中各个子查询的执行顺序;

• id相同执行顺序由上至下。

• id不同，id值越大优先级越高，越先被执行。

• id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type
每个子查询的查询类型，一些常见的查询类型。

id	select_type	description
1	SIMPLE	不包含任何子查询或union等查询
2	PRIMARY	包含子查询最外层查询就显示为 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查询
4	DERIVED	from字句中包含的查询
5	UNION	出现在union后的查询语句中
6	UNION RESULT	从UNION中获取结果集，例如上文的第三个例子

table
查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id

type(非常重要，可以看到有没有走索引)
访问类型

• ALL 扫描全表数据

• index 遍历索引

• range 索引范围查找

• index_subquery 在子查询中使用 ref

• unique_subquery 在子查询中使用 eq_ref

• ref_or_null 对Null进行索引的优化的 ref

• fulltext 使用全文索引

• ref 使用非唯一索引查找数据

• eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

possible_keys
可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。
key
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

TIPS:查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中
key_length
索引长度 
ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows
返回估算的结果集数目，并不是一个准确的值。
extra
extra的信息非常丰富，常见的有：
1.Using index 使用覆盖索引
2.Using where 使用了用where子句来过滤结果集
3.Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4.Using temporary 使用了临时表