mySQL—索引
文章目录
- 什么是索引？
- 为什么要使用索引？
- 索引的优缺点
- 索引的优点
- 索引的缺点
- 索引这么多优点，为什么不对表中的每一个列创建一个索引呢？
- 使用索引的注意事项？
- 哪些操作会引起索引失效?
- 索引使用原则：
- Mysql索引主要使用的数据结构
- 哈希索引
- BTree索引
- B 树和 B+树区别
- Hash 索引和 B+树索引优劣分析
- Hash 索引定位快
- Hash 冲突问题
- MyISAM和InnoDB实现BTree索引方式的区别
- MyISAM
- InnoDB
- 覆盖索引介绍
- 什么是覆盖索引
- 覆盖索引使用实例
- 选择索引和编写利用这些索引的查询的3个原则
- 最左前缀原则
- 注意避免冗余索引
- 索引类型
- 主键索引(Primary Key)
- 二级索引(辅助索引)
- Mysql如何为表字段添加索引？
————————————————————————————————
什么是索引？
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
索引的作用就相当于目录的作用。 打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。
为什么要使用索引？
1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2、可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
3、帮助服务器避免排序和临时表。
4、将随机IO变为顺序IO
5、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
索引的优缺点
索引的优点
- 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
- 毕竟大部分系统的读请求总是大于写请求的。
- 另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
索引的缺点
- 创建索引和维护索引需要耗费许多时间
- 当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 占用物理存储空间
- 索引需要使用物理文件存储，也会耗费一定空间。
索引这么多优点，为什么不对表中的每一个列创建一个索引呢？
1、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
使用索引的注意事项？
- 在经常需要搜索的列上，可以加快搜索的速度；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
- 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
- 在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；
- 避免 where 子句中对字段施加函数，这会造成无法命中索引。
- 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
- 将某一列设置为default null，where 是可以走索引，另外索引列是否设置 null 是不影响性能的。 但是，还是不建议列上允许为空。最好限制not null，因为null需要更多的存储空间并且null值无法参与某些运算。
- 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用
- 在使用 limit offset 查询缓慢时，可以借助索引来提高性能
哪些操作会引起索引失效?
1）隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误。
- 由于表的字段 S_id 定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
- 错误的例子：select * from test where S_id=111;
- 正确的例子：select * from test where S_id=‘111’;
2） 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
- 错误的例子：select * from test where id-1=9;
- 正确的例子：select * from test where id=10;
3）使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引。
- 错误的例子：select * from test where round(id)=10;
- 说明，此时id的索引已经不起作用了
4）以下使用会使索引失效，应避免使用；
- 使用 <> 、not in 、not exist、!=
- like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
- 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。
- 字符型字段为数字时在where条件里不添加引号.
- 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
5） 不要将空的变量值直接与比较运算符（符号）比较。
如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。
6）不要在 SQL 代码中使用双引号。
因为字符常量使用单引号。
如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。
索引使用原则：
在根据执行计划对SQL进行分析之后，逐步的去优化每个子查询的索引及成本使用情况，执行计划我的理解就是用来对索引进行逐步的优化的一个过程，让每个查询都尽量使用索引，在索引过程中，索引会经常性失效。
Mysql索引主要使用的数据结构
哈希索引
对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
BTree索引
B 树和 B+树区别
- B 树的所有节点既存放 键(key) 也存放 数据(data);而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
- B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
- B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
Hash 索引和 B+树索引优劣分析
Hash 索引定位快
Hash 索引指的就是 Hash 表，最大的优点就是能够在很短的时间内，根据 Hash 函数定位到数据所在的位置，这是 B+树所不能比的。
Hash 冲突问题
知道 HashMap 或 HashTable 的同学，相信都知道它们最大的缺点就是 Hash 冲突了。不过对于数据库来说这还不算最大的缺点。
Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点。
试想一种情况:
SELECT * FROM student WHERE id < 500;
B+树是有序的，在这种范围查询中，优势非常大，直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的，难不成还要把 1 - 499 的数据，每个都进行一次 hash 计算来定位吗?这就是 Hash 最大的缺点了。
MyISAM和InnoDB实现BTree索引方式的区别
MyISAM
B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB
其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
覆盖索引介绍
什么是覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢；覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！
覆盖索引使用实例
现在我创建了索引(username,age)，我们执行下面的 sql 语句
select username , age from user where username = 'Java' and age = 22
在查询数据的时候：要查询出的列在叶子节点都存在！所以，就不用回表。
选择索引和编写利用这些索引的查询的3个原则
- 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。
- 按顺序访问范围数据是很快的，这有两个原因。第一，顺序 I/O 不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。
- 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。
最左前缀原则
MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。
如User表的name和city加联合索引就是(name,city)，
而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。
如下：
select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。
由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。
注意避免冗余索引
冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，
那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的
在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
MySQL 5.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引
索引类型
主键索引(Primary Key)
- 数据表的主键列使用的就是主键索引。
- 一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
- 在 mysql 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
二级索引(辅助索引)
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。
也就是说，通过二级索引，可以定位主键的位置。
唯一索引，普通索引，前缀索引等索引属于二级索引。
- 唯一索引(Unique Key)
- 唯一索引也是一种约束。
- 唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。
- 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
- 普通索引(Index)
- 普通索引的唯一作用就是为了快速查询数据
- 一张表允许创建多个普通索引，并允许数据重复和 NULL。
- 前缀索引(Prefix)
- 前缀索引只适用于字符串类型的数据。
- 前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
- 全文索引(Full Text)
- 全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。
- Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。
Mysql如何为表字段添加索引？
- 添加PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
- 添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
- 添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
4.添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
5.添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )