mysql 索引原理

索引

数据库索引，是数据库管理系统中一个排序的数据结构（存储在磁盘中），以协助快速查询，更新数库表中的数据。

索引存储结构 

平衡二叉树 （基于磁盘）

• 左右树深度差绝对值不能超过 1 
• 左子树节点 < 父节点
• 右子树节点 > 父节点

多路平衡查找树 BTREE

• 频繁修改的数据字段 不建议添加索引   索引存储节点更新耗时长

加强版多路平衡查找树   B+TREE

• 扫库，扫表能力更强
• 磁盘读写能力更强 （降低io，节省磁盘空间） 
• 排序能力更强  （叶子节点本身为有序结构）
• 效率更加稳定

索引类型

PRIMARY  主键索引（不允许重读，并且值不能为空）

UNIQUE  唯一索引（不允许重复，但可以为空）

FULLTEXT 全文索引    需要like   "%aaaa%"  时使用   语法应为  MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier:
  {
       IN NATURAL LANGUAGE MODE
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

natural language search

• 全文搜索默认是大小写不敏感的
• 当不指定模式修饰符时，使用的是自然语言修饰符
• MATCH中的列必须和全文索引中的列匹配
• 当在WHERE子句中使用MATCH时，会返回相关性大于0的记录，并按照相关性的得分降序排列

SELECT COUNT(*) FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);

boolean search

boolean search可以通过一些符号来增加或减少某些词在搜索中所占的权重

-- 匹配既有管理又有数据库的记录
SELECT * FROM articles WHERE MATCH (title,body) 
    AGAINST ('+数据库 +管理' IN BOOLEAN MODE);

-- 匹配有数据库，但是没有管理的记录
SELECT * FROM articles WHERE MATCH (title,body)
   AGAINST ('+数据库 -管理' IN BOOLEAN MODE);

存储引擎 == 表类型

常用 InnoDB(5.5之后默认)，MyISAM

• 查询数据文件存储位置

聚集索引：决定了数据物理存放顺序的索引，（B+TREE 叶子节点上的存放顺序）

一张表不可能没有索引存在3个自动创建的隐藏字段

• 存在主键索引 则主键索引为聚集索引
• 不存在则查找第一个没有空值的唯一索引
• 都不存在则使用隐藏字段rowid作为聚集索引

辅助索引

除了主键索引其它都为辅助索引

InnoDB 引擎  辅助索引查询（先扫描自己的B+TREE,再扫描主键索引B+TREE）

 

索引使用

使用explain关键字分析sql

• 不用在重复值比较多的列建立索引  

联合索引

• 最常用的查询字段放在前边
• where 条件顺序不影响索引的使用 
• 联合索引的第一个字段  可以单独查询 联合索引依旧可以使用，其他字段查询不使用索引

创建索引
ALTER TABLE `u_user` ADD INDEX comidx_name_phone (NAME,phone)

使用索引
EXPLAIN SELECT * FROM `u_user` WHERE NAME = "21" AND phone = "18719445972"
使用索引
EXPLAIN SELECT * FROM `u_user` WHERE phone = "18719445972" AND NAME = "21"
使用索引
EXPLAIN SELECT * FROM `u_user` WHERE NAME = "21"
不使用索引
EXPLAIN SELECT * FROM `u_user` WHERE phone = "18719445972"

覆盖索引

查询的字段为索引字段则不会再次扫描扫描主键索引的B+TREE

出现 Using index则为覆盖索引
不是覆盖索引
EXPLAIN SELECT * FROM `u_user` WHERE NAME = "21" AND phone = "18719445972"
是覆盖索引
EXPLAIN SELECT name,phone FROM `u_user` WHERE phone = "18719445972" AND NAME = "21"
是覆盖索引
EXPLAIN SELECT phone FROM `u_user` WHERE NAME = "21"
是覆盖索引 不使用索引查询 但是查询字段为索引字段，则也不扫描主键索引树
EXPLAIN SELECT phone FROM `u_user` WHERE phone = "18719445972"

• 因为覆盖索引提高数据查询效率 则不要使用select * from .....   查询数据

存在索引但不适用的情况

• 在索引列使用函数或者表达式、计算
• 字符串不加引号，出现隐式转换
• like 条件前面带%  即 "%aaa"   但是  "aaa%" 可以使用索引
• 负向查询： NOT LIKE、!=  （<>）、NOT  IN
• 使不使用索引由optimizer（优化器）决定