mysql基础--索引

mysql基础--索引

索引是最物美价廉的提升数据库性能的方法，不用修改程序，不用调sql，查询速度就可以提高千百倍

-- 创建索引示例(在emp表的empno列上创建索引empno_index，注意索引本身占用额外空间)
create index empno_index on emp (empno)

索引原理就是使用空间换时间，要注意，对单列创建索引只会优化本列的where条件速度，并不会对所有列进行索引优化

原理：形成一个索引的数据结构，比如二叉树等等，代价是需要额外占用空间与维护成本（删除、添加、修改，也就是dml语句效率的影响）

使用索引判断：具体项目中，select【90%】多还是update，delete，insert【10%】操作多？虽然有不利的但是利大于弊就应该使用

• 类型

  • 主键索引，主键自动的为主索引（类型primary key）

  • 唯一索引（unique）

  • 普通索引（index）

  • 全文索引（fulltext）适用于MyISAM

    一般开发中不使用自带的全文索引，而是使用搜索框架：Solr 和 ElasticSearch

  create table t1(
  	id int primary key, -- 主键，同时也是一个索引，称为主键索引
      name varchar(32),
  )
  create table t1(
  	id int unique, -- 唯一约束，同时也是一个索引，称为unique索引
      name varchar(32),
  )
  

• 使用

  • 查询索引

    • show indexes from 表：查询表的索引表
    • show index from 表：查询表的索引表
    • show keys from 表：查询表的索引表
    • desc 表：key中mul就是索引，简略查看

  • 添加索引

    • create [unique] index index_name on tbl_name (col_name[(length)] [asc | desc], ...)
    • alter table tbl_name add index [index_name] (index_col_name,...)

    -- 添加唯一索引(用在知道本字段不会重复，优先用唯一索引，速度快)
    create unique index id_index on tbl (id)
    -- 添加普通索引(用在知道本字段会重复，但又想提高查询速度，就使用)
    create index id_index on tbl (id) -- 方式1
    alter table tbl add index index_name (id) --方式2
    -- 如果建表设置了主键，就有了主键索引，如果没有设置主键，可以事后添加主键，也就加了主键索引
    alter table tbl add primary key (id)
    

  • 删除索引

    -- 删除tbl表中索引名称为id_index的索引
    drop index id_index on tbl
    -- 删除主键索引，就是删除主键
    alter table tbl drop primary key
    

• 应用

  哪些列上适合使用索引

  • 较频繁的作为查询条件字段应该创建索引
  • 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
  • 更新非常频繁的字段不适合创建索引
  • 不会出现在where子句中字段不该创建索引