【技术累积】【点】【sql】【17】了解索引

先上结论

  • 数据库数据以平衡树进行聚合索引——主键的作用;
  • 数据每行都存在叶子节点;
  • 单独字段的索引,单独存在,且将该字段值取出;
  • 单独字段的索引,查到对应的主键id,再通过聚合索引查到数据;
  • 多字段索引,若只需要查多字段中的数据,可不通过聚合索引;
  • 树结构导致查询速度加快,查询的时间复杂度由O(n)变为O(log2n),大数据量情况效果明显;
  • 树结构导致写入等操作变慢;
  • 索引单独存取,所以索引越多,占用磁盘空间越多

具体

创建/修改/删除索引

  • 创建表的时候一起创建
CREATE TBALE table_name (
...
PRIMARY KEY (`id`),
UNIQUE KEY uniq_dfasd (property_code)
)
  • 单独创建
CREATE INDEX index_name ON table_name(column_name)
CREATE UNIQUE INDEX index_name ON table_name(column_name)
/*CREATE INDEX可对表增加普通索引或UNIQUE索引。
  • ALTER添加索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
  • 删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

索引类型

  • 主键:PRIMARY,每张表一个,是唯一的聚合索引;本质上是一个特殊的UNIQUE键;
  • 唯一键:UNIQUE,限制了列中不能重复,否则写入有问题;
  • 普通键:INDEX

查看&分析索引

SHOW keys FROM table_name
SHOW index FROM table_name

得图类似如下

大部分一眼就能读懂,说说其中难懂的几个

  • Collation:排序方式,A为升序
  • Cardinality:基数,就是有多少值是不重复的。这个是个估计值,且值越大越好;
  • Null:列中数据是否有为null的;很重要,若有,意味着这个索引就废了(索引不能有null)
  • Seq_in_index:索引中的顺序;主要针对联合索引的,有顺序排列的不同;

适宜使用索引的场景

  • 主键;
  • 唯一约束;
  • 直接条件查询的字段;
select * from zl_yhjbqk where qc_bh=’7001’
  • 查询中与其它表关联的字段
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
/*如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)
  • 查询中排序的字段
  • 查询中统计或分组统计的字段
select max(hbs_bh) from zl_yhjbqk
select qc_bh,count(*) from zl_yhjbqk group by qc_bh

不适宜使用索引的情况

  • 数据少;
  • 数据重复严重;
  • 有null值;
  • 频繁更新/插入;

其他注意事项

  • 对于长字段,使用短索引
alter table test add index `a`(`a`(3));
  • mysql在一个查询中只使用一个索引,注意使用哪个或者用联合索引;以及是否排序也用了索引

mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

  • 注意like

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。


参考文章

posted @ 2018-09-30 10:47  韧还  阅读(159)  评论(0编辑  收藏  举报