mysql索引学习


来学习一下mysql的索引吧, 经常都听到索引这个词,它到底是什么呢?

索引的概念

在mysql的官方中, 对索引的定义为: 索引是帮组mysql 高效获取数据的数据结构.
可以得到索引的本质: 索引是数据结构.
也可以简单理解为 排好序的快速查找数据结构(解决where 后面查找快, 和 order by 后面排序快).
在数据之外, 数据库还维护者 满足特定查找算法的数据结构. 这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据 结构上实现高级查找算法.
这种数据结构, 就是索引.
我们平常所说的索引, 如果没有特别指明, 都是指 B树 索引. 当然, 除了B树这种类型的索引之外, 还有哈希索引.

索引的优势和劣势

优势: 类似大学图书馆建 书目索引, 提高检索的效率, 降低 数据库的IO 成本;
通过索引对数据进行排序, 降低数据排序的成本, 降低了cpu 的消耗.
劣势: 实际上索引也是一张表, 该表保存了主键与索引字段, 并指向 实体表的记录, 所以索引列也是需要占用空间的;
索引大大提升了查询速度, 同时却会降低更新表的速度;
索引只是提高效率的一个因素, 如果你的mysql 有大数据量的表, 就需要 花时间建立最优秀的索引, 或优化查询.

索引分类

单值索引(即一个索引包含单个列, 一个表可以有多个单列索引);
唯一索引(索引列的值必须唯一, 但允许有空值);
复合索引(即一个索引包含多个列)

索引命令

索引的基本语法
创建索引, 可以是建表的时候创建索引, 也可以修改表结构的时候创建索引

-- 创建索引
alter table author add index  index_birth(birthday );
-- 查看索引
show index from author;
-- 删除索引
drop index  index_birth on author;

有四种方式来添加数据表的索引

  1. 添加主键, 这意味着索引值必须是唯一的, 而且不能是空;
  2. 添加唯一约束, 创建索引的值必须是唯一的(除了null 外, null 可能会出现多次);
  3. 添加普通索引, 索引值可出现多次;
  4. 添加索引为 fulltext, 用于全文索引.
    -- 该语句添加一个主键,这意味着索引值必须是唯一的, 而且不能为NULL 
    ALTER  table tbl_name add PRIMARY key(column_list); 
    
    -- 这条语句创建索引的值必须是唯一的(除了NULL 外, NULL 可能会出现多次)
    ALTER  table tbl_name  add UNIQUE idex_name  (column_list); 
    
    --  添加普通索引, 索引值可出现多次
    ALTER  table tbl_name  add index  idex_name  (column_list); 
    
    -- 该语句指定了索引为FULLTEXT ,用于全文索引
    ALTER TABLE tbl_name ADD FULLTEXT idex_name(COLUMN_list); 

索引的检索原理

B树索引的原理,按块存储,按照左边低,右边高的顺序排序.
mysql索引原理图

哪些情况适合建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段, 外键关系建立索引
  4. 频繁更新的字段不适合创建索引-因为每次更新不单单是更新了记录还会更新索引
  5. where 条件里用不到字段不创建索引
  6. 单键/组合索引的选择问题, who ? 在高并发下倾向创建组合 索引
  7. 查询中排序的字段, 排序字段若通过索引去访问将大大提高排序速度
  8. 查询中统计或者分组字段

    哪些情况不适合建索引

    一.表记录太少
    二.经常增删改的表(数据重复且分布平均的表字段, 因此应该只为最经常查询和最经常排序的数据列建立索引)
    三.如果某个数据列包含许多重复的内容, 为它建立索引就没有太大的实际效果

文章作者: 陌上人如玉
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 陌上人如玉 !
  目录