MYSQL索引使用及原理笔记

索引

索引有很多中类型：普通索引、唯一索引、主键索引、组合索引、全文索引

索引的创建方式

EXPLAIN查看sql执行详情，包括索引使用情况
直接创建索引

CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length))

[UNIQUE|FULLLTEXT]：表示可选择的索引类型，唯一索引还是全文索引，不加话就是普通索引。
table_name：表的名称，表示为哪个表添加索引。
column_name(length)：column_name是表的列名，length表示为这一列的前length行记录添加索引。

创建表的时候创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    [UNIQUE|FULLLTEXT] INDEX index_name (title(length))
)

使用SHOW INDEX命令查看表中索引信息

SHOW INDEX FROM table_index\G;

image

字段	解释
Table	索引所在的表
Non_unique	非唯一索引，如果是0，代表唯一的，也就是说如果该列索引中不包括重复的值则为0 否则为1
Key_name	索引的名字，如果是主键的话则为PRIMARY
Seq_in_index	索引中该列的位置，从1开始,如果是组合索引那么按照字段在建立索引时的顺序排列
Collation	列是以什么方式存储在索引中的。可以是A或者NULL，B 树索引总是A，排序的，
Sub_part	是否列的部分被索引，如果只是前100行索引，就显示100，如果是整列，就显示NULL
Packed	关键字是否被压缩，如果没有，为NULL
Index_type	索引的类型，对于InnoDB只支持B 树索引，所以都是显示BTREE

Cardinality关键字解析

在上面介绍了那么多个关键字的意思，但是Cardinality这个关键字非常的关键，优化器会根据这个值来判断是否使用这个索引。在B 树索引中，只有高选择性的字段才是有意义的，高选择性就是这个字段的取值范围很广，比如姓名字段，会有很多的名字，可选择性就高了。

一般来说，判断是否需要使用索引，就可以通过Cardinality关键字来判断，如果非常接近1，说明有必要使用，如果非常小，那么就要考虑是否使用索引了。

B树索引的使用

联合索引

联合索引是指对表上的多个列进行索引，这一部分我们将通过几个例子来讲解联合索引的相关知识点。
什么时候联合索引才会发挥作用呢
带着这个问题，我们了解一下联合索引的最左匹配原则。

最左匹配原则：这个原则的意思就是创建组合索引，以最左边的为准，只要查询条件中带有最左边的列，那么查询就会使用到索引。==联合索引的使用有一个好处，就是索引的下一个字段是会自动排序的==

为什么需要使用联合索引：

减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。将被查询的字段，建立到联合索引里去。将被查询的字段，建立到联合索引里去。在一棵索引树上就能获取到所有列数据不需要回表
效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

索引失效情况

以%开头的 like 查询不能利用 B-Tree 索引，执行计划中 key 的值为 null 表示没有使用索引
数据类型出现隐式转换的时候也不会使用索引，例如，where 'age' 10=30
对索引列进行函数运算，原因同上
正则表达式不会使用索引
字符串和数据比较不会使用索引
复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则 leftmost，是不会使用复合索引的
如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引
用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
使用负向查询（not ，not in， not like ，<> ,!= ,!> ,!< ）不会使用索引