mysql索引篇_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本身的优化机制就可以满足我们增删改的操作。所以索引不一定必须使用，这只是一种数据库量大之后，推出的一种性能优化方式。