MySQL索引

1.索引篇

1.什么是索引

1、MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质为：索引是数据结构。

2、索引的目的在于提高查找效率，可以类比字典。（排序+查找两大功能）

3、索引可以简单理解为：排好序的快速查找数据结构

4、一般索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

5、平常所说的索引，如果没有特别指明，都是指B树结构组织（多路搜索树，并不一定是二叉树）的索引。默认都是使用B+树索引，此外还有哈希索引hash index。

2.索引优势和劣势

1、优势：

• 类似大学图书馆建立书目索引，提高数据检索的效率，减少数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

2、劣势：降低了更新表的速度，如对表进行INSERT,UPDATE,DELETE

3.索引分类

1、单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

2、唯一索引：索引列的值必须唯一，但允许有空值

3、复合索引：即一个索引包含多个列

4、基本语法：

• 创建：CREATE [UNIQUE] INDEX indexName ON mytable(columnname); //创建索引，且为单值索引，唯一索引

ALTER TABLE mytable ADD [UNIQUE] INDEX indexName (columnname);

• 删除：DROP INDEX indexName ON mytable;
• 查看：SHOW INDEX FROM mytable;

4.索引结构

1、mysql索引结构：

• BTree索引

• • 检索原理：
  • 
  • 一颗B+树，浅蓝色表示一个磁盘块，每个磁盘块包含几个数据项（深蓝色），和指针（黄色）
  • 真实的数据存放在叶子节点中，例如3，5，9，10......99
  • 非叶子节点不存储真实数据，只存储指引搜索方向的数据项，例如17，35并不真实存在于数据表中。

• Hash索引
• full-text全文索引
• R-Tree索引

5.哪些情况适合建索引

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引

3、查询中与其他表关联的字段，外键关系建立索引

4、频繁更新的字段不适合建索引---因为每次更新不仅更新了记录而且还会更新索引

5、在高并发下倾向建立组合索引

6、查询中排序的字段

7、查询中统计或分组字段

6.哪些情况不适合建索引

1、表记录太少

2、经常增删改的表（上述有提到过，更新表以后，mysql不仅要保存数据，还要保存一下索引文件）

3、数据重复且分布平均的表字段

公式：索引的选择性是指索引列中不同值的数目与表中记录数的比，其值越接近于1，这个索引的效率就越高。

7.索引优化

1、最佳左前缀法则：如果是复合索引，则查询从索引的最左前列开始并且不跳过索引中间的列。（带头大哥不能死，中间兄弟不能断）即类比1栋2层5房间。

2、不在索引列上做任何操作（计算，函数，类型转换），否则会导致索引失效而转向全表扫描

3、存储引擎不能使用索引中范围条件右边的列（范围条件之后的列全失效）

4、尽量使用覆盖索引（索引列和查询一致），减少select *

5、mysql在使用不等于（!=或<>）的时候无法使用索引会导致全表扫描

6、is null,is not null也无法使用索引

7、like以通配符开头（'%abc'）mysql索引会失效变成全表扫描的操作

9、字符串不加单引号索引会失效

10、少用or，否则索引会失效