mysql-索引2

一 索引分类1 主键索引也称为聚集索引或聚簇索引当一个表定义一个主键时会自动创建索引索引的值是主键列的值innoDB就使用它作为聚集索引这里推荐为每一个表定义一个主键如果没有逻辑上唯一且非空的列或列集可以作为主键则需要添加一个自增列2 普通索引是最基本的索引类型没有唯一性的限制。为了提升查询效率工作中通常为查询频繁且列的值重复度不高的列创建索引。创建索引之后会生成一颗索引树创建多少索引就会生成多少颗索引树索引树越多对增、删、改的效率越大因为生成的索引树也是会占用磁盘空间的所以要慎重考虑3 唯一索引当在一个表上定义一个唯一键 UNIQUE时会自动创建唯一索引。它与普通索引类似区别在于唯一索引的列不允许有重复值4 聚集索引它与主键索引是同义词如果没有为表定义主键InnoDB将使用第一个UNIQUE 和 NOT NOLL 的列作为聚集索引如果还没有合适的 UNIQUE 列InnoDB会为新插入的行生成一个行号并用6字节的 ROW_ID数据行中的隐藏列之一字段记录它是单调递增的使用它作为索引5 非聚集索引聚集索引以外的称为非聚集索引或二级索引二级索引中的每条记录都包括该行的主键列以及二级索引指定的列那非聚集索引的查询过程是怎样的呢1通过索引查到叶子节点中的索引记录2通过索引记录中的主键值去主键索引树中找相应的完整记录回表查询如果通过索引查询的列包含在索引中不需要回表查询这种现象就叫做索引覆盖值得注意的是创建索引的时候哪个列在前面使用的时候就得先使用哪个列。如果只使用后面的那个列索引就会失效但是就想单独使用它的话可以为它单独创建个索引。聊到这已经知道索引的情况了那该如何使用呢二 使用索引1 创建索引1.1 自动创建索引和上面一样。当为一张表添加主键约束外键约束唯一约束时会为对应的列自动创建一个索引。但是如果表不指定任何约束会生成那个ROW_ID1.2 手动创建索引1.2.1 主键索引1创建表时创建主键创建好之后想要查看索引信息show index(keys) from 表名;其中Key_name为索引名Column_name为索引列名Index_type为索引的数据类型B树Seq_in_index 索引中包含的列名2创建表时单独指定列3 修改表中的列为主键索引语法alter table 表名 [add/modify/drop] 要修改的内容创建t_pk3时只表明了数据类型并没有设置主键列和自增上面那个alter语句把id列设置为了主键列接下来再把id设为自增值得注意的是这个bigint类型不可以不写唯一索引和主键索引类似只是唯一索引关键字为unique在上面的表中就可以把name添加唯一索引这里就不展开了1.2.2 普通索引创建的时机创建表的时候数据少的时候明确的知道哪些列需要被频繁查询就可以创建了或者随着业务的不断发展数据增多了在版本迭代的过程中添加索引在创建主键的时候我们知道关键字是primary key而这里的关键字是index1创建表时指定索引列2修改表中的列为普通索引3单独创建索引常用语法create index 索引名 on 表名(列名)1.2.3 创建复合索引其实创建语法与创建普通索引没什么不同只不过指定多个列列与列之间用逗号隔开。2 删除索引2.1 主键索引第一步得先判断你要删除的索引有没有自增属性auto_increment。如果有先修改自增属性然后再删除主键删除主键的语法alter table 表名 drop primary key修改自增属性删除主键2.2 其他索引语法alter table 表名 drop index 索引名比如3 查看sql语句有没有使用索引可以查看执行计划通过explain这个关键字比如1不加条件查询所有代表全表扫描没有使用索引2使用主键查询type类型为常量possible_keys表示分析语句可能用到的索引key表示真实用到的索引3子查询使用索引这里解释一下type:4使用普通索引5使用复合索引要想得到这个结果得先把sn这个索引先删除这个是回表查询这个*包含了所有值而这个是索引覆盖用sn去查找name也是索引覆盖都在索引树里不用去主键了用name去查snusing index 知道了using where 是什么呢要是出现using where 得注意了说明性能不好得优化6值得注意的地方我们前面说过创建索引的时候哪个列在前面使用的时候就得先使用哪个列。前面是sn在前name在后。那下面的语句会不会使用索引对不对答案是会使用索引这里注意一下只要查询条件中使用了索引包含的所有列就会使用索引和顺序无关。OK啦到此结束