mysql 优化以及索引

目前mysql 主流的存储引擎为：innerDB 和 MyISAM（MEMORY/HEAP 不常用）

而索引的分类主要分为BTREE 和HASH 索引 ，innerDB MyISAM 默认为BTREE ，　MEMORY/HEAP存储引擎：支持HASH和BTREE索引，

我们谈一下，为什么要注重Sql 优化以及选择存储引擎的重要性：

第一：我们写一句简单的查询语句 select * from user where username = 'xxx' ,对于这样的一条简单的sql语句,假设表里面有1W条数据，我们在没有索引的情况下就要从第一条开始读完整的一张表，而索引用于快速找出在某个列中有一特定值的行。显然使用索引对于数据量比较大的查询来说是相当乐观的。

第二：当我们查询的时候select 后面使用* 的话这样的情况 ，我们就得读所有的列 ，这样也会浪费大量的时间，所以在查询的时候也尽量不要使用*。

第三：有时候我们会使用in('xxx','xxx','xxx') 或者or，在mysql中in里面如果是字符串的话，会自动转化成int类型的，而且根据测试，or的效率为O(n)，而in的效率为O(logn)，测试结果是如果in和or所在列有索引或者主键的话，or和in没啥差别，执行计划和执行时间都几乎一样。如果in和or所在列没有索引的话，性能差别就很大了（补充一点当我们使用子查询的时候in的范围是不明确的，可能需要子查询来获得，这也是我们要使用in的原因之一，第三点感觉没有什么实质性的建议）。

第四：选择索引也是一本技术，比如多表查询，作为表连接的列应该尽量使用索引，where 后面跟多列查询时根据表结构情况建立组合索引（下面会讲组合索引的具体用法），组合索引套用网上的话是相当于通讯录中一个人的姓和名，如果我们只有姓去检索的话，同样会查出很多，如果姓名同时查的话就能精确的查找到。

第五：索引也不是我们盲目的使用，索引列最好是保持not null,索引也需要索引空间，暂用I/O，而且建立索引过程中会大量的使用数据库，降低数据库的性能，所以当我们的表数量小的时候不要建索引，对于使用数据库较多的业务环境下，建索引尽量选择业务不平凡的时间点。

第六：选择一个比较适合自己的存储引擎比较重要， MyISAM 仅仅缓存索引，而 InnoDB 缓存数据和索引，MyISAM 不支持事务，可能对于查询特别多的业务，而数据的更新操作特别特别少的情况下可以考虑，一般情况我们选择InnoDB，也是目前我们使用的默认引擎。

第七：BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，select * from user where username like 'jw%',所以我们使用like 通配符的时候 尽量不要这样：select * from user where username like '%jw'（不使用索引）。我们也谈谈hash:Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引

但是：

1. Hash索引仅仅能满足“=”,“IN”,“<=>”查询，不能使用范围查询。
2. 联合索引中，Hash索引不能利用部分索引键查询。
对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。
3. Hash索引无法避免数据的排序操作
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。
4. Hash索引任何时候都不能避免表扫描
Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。
5. Hash索引遇到大量Hash值相等的情况后性能并不一定会比BTree高
对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下

下面谈一下索引：

1、索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

　　　　　　　　　　1.1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

　　　　　　　　　　　　　1.1.1、普通索引：

　　　　　　　　　　　　　　　　　　MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

　　　　　　　　　　　　　1.1.2、唯一索引：

　　　　　　　　　　　　　　　　　　索引列中的值必须是唯一的，但是允许为空值，

　　　　　　　　　　　　　1.1.3、主键索引：

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

1.2、组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说　

1.3、全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大煞笔，二货 ..." 通过大煞笔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思

1.4、空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL（了解一下就好）

重点：执行计划

EXPLAIN SELECT * FROM book WHERE year_publication = 1990\G;

解释：虽然表中没数据，但是有EXPLAIN关键字，用来查看索引是否正在被使用，并且输出其使用的索引的信息。

　　　　　　　　　　　　　　　　　　　　　　　

　id:　SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出现。在次语句中，select就只有一个，所以是1.

select_type:所使用的SELECT查询类型，SIMPLE表示为简单的SELECT，不实用UNION或子查询，就为简单的SELECT。也就是说在该SELECT查询时会使用索引。其他取值，PRIMARY：最外面的SELECT.在拥有子查询时，就会出现两个以上的SELECT。UNION：union(两张表连接)中的第二个或后面的select语句  SUBQUERY：在子查询中，第二SELECT。

table：数据表的名字。他们按被读取的先后顺序排列，这里因为只查询一张表，所以只显示book

type：指定本数据表和其他数据表之间的关联关系，该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。(注意，个人这里不是很理解，百度了很多资料，全是大白话，等以后用到了这类信息时，在回过头来补充，这里不懂对后面的影响不大。)可能的取值有 system、const、eq_ref、index和All

possible_keys：MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引，year_publication

key：实际选用的索引

key_len：显示了mysql使用索引的长度(也就是使用的索引个数)，当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。这里就使用了1个索引，所以为1，

ref:给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的是1990，就是常量。

rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。

extra：提供了与关联操作有关的信息，没有则什么都不写。　　　

上面的一大堆东西能看懂多少看多少，我们最主要的是看possible_keys和key 这两个属性，上面显示了key为year_publication。说明使用了索引。

创建索引的方法：

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

格式：ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

有了上面的基础，这里就不用过多陈述了。

命令一：SHOW INDEX FROM 表名\G　查看一张表中所创建的索引　SHOW INDEX FROM song\G;

挑重点讲，我们需要了解的就5个，用红颜色标记了的，如果想深入了解，可以去查查该方面的资料，我个人觉得，这些等以后实际工作中遇到了在做详细的了解把。

Table:创建索引的表;

Non_unique：表示索引非唯一，1代表 非唯一索引， 0代表 唯一索引，意思就是该索引是不是唯一索引;

Key_name：索引名称

Seq_in_index 表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序(这个只需要知道单列索引该值就为1，组合索引为别的)

Column_name：表示定义索引的列字段

Sub_part：表示索引的长度

Null：表示该字段是否能为空值

Index_type：表示索引类型

 

建立组合索引：

　这个时候我们可以看到使用了索引，

而

　这个时候根据最左前缀原则应该是不会使用索引的，但是为什么是能看到索引呢？

创建组合索引 有最左前缀原则，但是 a,c 的情况还是使用了索引 只是只使用的a的索引，当作普通索引来使用了

最后是删除索引：

ALTER TABLE song DROP INDEX index_cname;

index_cname 索引也成功删除了。