MySQL-索引篇

介绍：帮助MySQL高效获取数据的数据结构，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据。

优点：1.提高数据查询效率，降低数据库的IO成本

           2.通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：1.索引列占空间

           2.降低更新表的速度

索引结构：

B+树索引：常见索引类型，大部分都支持B+树索引

Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才能有效，不支持范围查询

R-树（空间索引）：MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-text（全文引擎）：一种通过建立倒排序索引，快速匹配文档的方式 

索引分类

在Innodb中，索引的存储形式：

• 集聚索引：将数据存储与索引放在一起，索引架构的叶子结点保留了行数据，必须有且只有一个

• 二级索引：将数据和索引分开存储，索引结构的叶子结点关联的是对应主键，可以存在多个

聚集索引选取规则：

1. 如果存在主键，主键索引就是聚集索引

2. 如果没有主键，就使用第一个唯一索引作为集聚索引

3. 如果没有主键和没有合适的唯一索引，则Innodb会自动生成主键rowid作为隐藏的集聚索引

   二级索引查询流程：

 二级索引查询流程：

 索引语法

1.创建索引

CREATE [UNIQUE |  FULLTEXT] INDEX   index_name   NO    table_name(index_col_name,...);

 2.查找索引

SHOW  INDEX  FROM  table_name;

3.删除索引

DROP  INDEX  index_name  ON  table_name； 

索引使用 

1.最左前缀法则：如果索引了多列，要遵循最左前缀法则。查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某列，索引将失效。

2.范围查询：联合索引中，出现范围查询，范围查询右侧的列索引失效

索引失效方式 

1.索引列运算：不要在索引列上进行运算操作，否则索引失效

2.字符串不加引号：字符串类型字段使用时，不叫引号，索引失效

3.模糊查询：如果进行头部模糊，则索引失效，如果尾部模糊，索引可以正常使用

4.or连接的条件：用or分割的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及索引的不会被使用

5.数据发布影响：如果MySQL评估使用索引比全表慢，则不适用索引

6.SQL提示：是优化数据库的一个重要手段，就是在sql语句上加入一些人为的提示来达到优化操作的目的

7.前缀索引：当字符类型为字符串，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询速率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

 索引设计规则

1. 针对数据量较大，且查询比较频繁的表建立索引

2. 针对常作为条件 排序 分组操作的字段建立索引

3. 对区分度高的列建立索引，且尽量建立唯一索引，区分度越高，使用索引的效率越高

4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引

5. 尽量使用联合索引，减少单列索引，查询时联合索引很多时候可以覆盖索引，节约存储空间，避免回表，提高查询效率

6. 要控制索引的数量，索引并不是越多越好，索引越多维护索引结构的代价越大，会影响增删改的效率

7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约。当优化器知到每列是否包含NULL值时，它可以更好地确定那个索引最有效地用于查询