数据量小的时候,所有数据都能在内存中,全表扫描也不慢
但是数据量变大,查询频率变高,内存不能缓存所有数据时,就需要索引了
索引在引擎层
B-tree
一般是这个
B+树结构存储
平衡查找树,每个叶子到root距离相同
,所有数据都按顺序存放在同层叶节点,叶之间指针连接,具体实现各引擎不同
叶子MyISAM通过物理位置引用行,Innodb通过主键
从根节点开始搜索
适合范围查找
什么样情况适合:
- 全值匹配 =
- 最左前缀查询 where的第一个
- 列前缀 order_sn like '9876%'
- 范围 > <
- 精确匹配左前列,范围匹配另外一列
- 只访问索引查询
- order by group by
用不到的情况
- 命中大多数数据 还不如全表扫描 不用索引了
- 不是索引最左列 如 "订单号 订单日期"联合索引,订单号是最左列, 'where 订单日期' 这样就用不到索引
- 不能跳过索引中的的列 [日期 下单人姓名 电话]联合索引 where [日期 电话] 不行
- not in 和!=不行
- 某列范围查询 右边的列就无法使用索引
Hash索引
特点
innodb的Hash索引 会自动根据访问的频率和模式,betree索引的使用情况来自动地为某些热点页建立哈希索引 又称自适应hash索引
- 基于hash表 必须精确==匹配 模糊和范围不行
- 对索引中的列,会合起来算一个hash码存储,只能全值匹配,只存索引让数据紧凑,查找非常快
限制
- 二次查找:先找到对应的行 对行的数据进行读取(betree可能直接有存)
但是经常被访问到的行,一般在内存中,访问速度很快,这点对性能的影响不明显 - 顺序打乱 无法范围,排序,部分查找
- 如果 hash冲突太多 影响查找和维护,所以不适合用在选择性差的列 比如性别,而是适合身份证号这种
- 不能避免表扫描, 就是要回表
索引的作用
- 大大减少存储引擎需要扫描的数据量
innodb以页(默认16k)为单位读取,索引的大小比一行原本的大小小得多,需要读取的页数变少 - 帮助排序,避免使用临时表
- 数据行的物理地址通常是随机分布,索引是顺序,变随机io为顺序io,根据充分发挥io性能
索引性能损耗
- 写入,需要索引信息维护
innodb有插入缓存把多次插入变成一次,为了写入速度改删除所有索引,除了主键递增 - 索引 增加 查询优化器 选择时间
索引优化策略
-
索引列不能表达或函数
改写 - betree索引的键 innodb最大限制767字节(大概255个字) 注意字符串类型 用前缀索引 要注意选择性
CREATE INDEX index_name ON table(col_name(n)); - 联合索引的顺序
经常使用的列(除非是状态这种选择性差的)→选择性高的→宽带小的列
覆盖索引
包含了查询全部值的索引
Extra 情况
（1）using index condition ,先在二级索引上使用索引查找到主键ID，然后在主键索引上通过主键ID进行查找。这里之所以需要去主键索引上查，是因为select 需要的数据，二级索引不能完全提供
explain SELECT * from user_man where manname = 'zzk';
(2)using where ,直接在主键索引上过滤数据，必带where子句，而且用不上索引
explain select * from user_man where manage in (9,10);
（3）using index，直接在主键索引上完成查询和所有数据的获取,一般是只获取主键ID
explain select manid from user_man
(4)Using where; Using index,直接在二级索引上获取全部数据(覆盖索引)
explain select manname,manid from user_man where manname = 'yjz'
Mysql explain-Extra(using where,using index)使用详解
除此之外都是二级索引（就是我们自己建的）：
mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。
以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键 时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作 为聚簇索引。mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。
以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键 时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作 为聚簇索引。
不该使用的情况:
- 有的存储引擎不支持覆盖索引
- 太多列
排序
- 通过排序操作
- 很快 btree按照索引顺序扫描数据 explain没有 Extra: Using filesort就是
条件苛刻Order By的字段
1.与索引顺序完全一致,
2.所有升序降序一致
3.全部在关联表第一张表
因为rentail_date是联合索引的第一列 这么一用后面的索引就死去效用了
normal：表示普通索引
unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique
full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
总结，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。
cuidiwhere 的CSDN 博客
不支持hash的引擎时 betree模拟hash索引优化查询
ALTER TABLE film add title_md5 varchar(32);update film set title_md5=md5(title);create index idx_md5 on film(title_md5);
必须先md5列把可能的数据返回到内存中,再筛选
锁
innodb行锁是说更新时 只锁定需要的行,过滤掉了不需要的行时才会有效,如果不能过滤掉行,就会锁定所有行,再在内存中用where条件过滤,索引可以减少被锁定的行数,因为加快了处理速度 锁的释放速度也会变快
drop index idx_actor_last_name on actor;
如果索引不删 不会这样
该删的索引
查看可删的索引
pt-duplicate-key-checker h=127.0.0.1
没被使用的索引
可以看到索引使用次数
要定期维护表和索引的碎片,更新索引统计信息
analyze table table_name更新索引统计信息MyISAM会锁全表,innodb只是估值
optimize table table_name维护碎片 都会锁全表
