MySQL高级篇（一）——索引

引言

            将MySQL索引比作书本的章节目录，这个类比合理吗？

概述

            对于引言中的疑问？特给出如下定义：索引是帮助MySQL快速查找和排序的一种数据结构。将索引比作章节目录不是很合理，众所周知，章节目录基本都是有序的，而索引并不算是顺序的数据结构，就比如MySQL常用都是B树索引，类似数据结构的排序二叉树。

内容

             一 分类

             1 按照类型：B树（聚集索引、次要索引、覆盖索引、符合索引、前缀索引）、哈希索引（hashindex）

             2 表现形式：

                （1）单值：一个索引值包含单个列，一个表可以有多个单列索引，一张表建立索引最多不超过5个；

                （2）唯一：索引列的值必须唯一，但允许有空值。主键创建后一定包含一个唯一索引，唯一索引并不一定就是主键。

                （3）复合：在表的多个列上建立的索引。

        二 结构：BTree索引、Hash索引、full-text全文索引、R-Tree索引

             三 优、劣势

             1 优势

                （1）提高数据检索的效率，降低数据库的IO成本

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

             2 劣势

                （1）索引占用一定的空间

                （2）提高了查询，降低了更新表的速度

                （3）当MySQL有大量数据，索引优化耗费大量时间

        四 使用

             1 检索原理（以B树为例）

                

                 如上图，是一颗b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

             2 基本语法

                （1）查看：SHOW INDEX FROM table_name\G

                （2）创建：CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));

                （3）删除：DROP INDEX [indexName] ON mytable;

                （4）使用（ALTER命令）：

                         ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);添加一个主键，索引值必须是唯一的，且不能为NULL。

                         ALTER TABLE tbl_name ADD UNIQUE index_name(column_list);创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

                         ALTER TABLE tbl_name ADD INDEX index_name(column_list);添加普通索引，索引值可出现多次。

                         ALTER TABLE tbl_name ADD FULL TEXT index_name(column_list);索引为FULLTEXT，用于全文索引。

             3 场景

                （1）需要创建索引

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

                       2）频繁作为查询条件的字段

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

                       4）高并发下倾向创建组合索引

                       5）查询中排序的字段

                       6）查询中统计或分组字段

                （2）不要创建索引

                       1）频繁更新的字段

                       2）where条件里用不到的字段

                       3）表记录太少

                       4）经常增删改的表

                       5）数据重复且分布平均的表字段

             4 优化

                （1）问题（索引失效）

                      1）违反最佳左前缀法则

                      2）在索引列上做如下任何操作（计算、函数、（自动or手动）类型转化）

                      3）使用不等于的时候无法使用索引

                      4）is null，is not null也无法使用索引

                      5）like以通配符开头（'%abc....'），索引失效，变为全表扫描操作

                      6）字符串不加单引号

                      7）用or来连接

                      8）存储引擎不能使用索引中范围条件右边的列

                 （2）优化建议

                      1）单值索引：尽量选择针对当前query过滤性更好的索引

                      2）组合索引：当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好；尽量选择可以能够包含当前query中的where子句中更多字段的索引

                      3）尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

                 （3）面试题：like '%字符串%'时索引失效问题？覆盖索引

总结

             一般MySQL数据库的数据量达到了百万级别，才有可能用到索引，百万级以下的数据量，MySQL本身的优化机制就可以满足我们增删改的操作。所以索引不一定必须使用，这只是一种数据库量大之后，推出的一种性能优化方式。