索引学习
1.什么是索引
索引是帮助MySQL搞笑获取数据的数据结构,即排好序的快速查找数据结构。
2.索引的优劣势
优点:
- 提高数据检索的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
缺点:
- 索引存放在磁盘中,也要占用空间。
- 降低了更新表的速度,因为在MySQL数据更改的时候,索引文件也要发生调整。
3.索引类型
- 普通索引:基本的索引类型,值可以为空,没有唯一性限制。
- 唯一索引:索引列的值必须唯一,允许有空值。
- 主键索引:一种唯一性索引,必须指定为PRIMARY KEY,一个表只能有一个主键,不允许有空值。
- 组合索引:由多个字段创建的索引,专门用于组合搜索(最左匹配原则)。
- 全文索引:主要用来查找文本中的关键字,类型为FULLTEXT,可以在varchar、char、text类型上创建。(MyISAM支持,InnoDB在5.6之后支持)
4.索引相关命令
查看语句:
show index from mytable;
基本创建语句:
create [unique] index index_name on mytable(column_name(length));
eg:create index index_age on person(age);
alter mytable add [unique] index[index_name](column_name(length));
eg:alter perosn add index index_age(age);
删除语句:
drop index [index_name] on mytable;
-
为什么InnoDB表必须有主键?
MySQL创建索引时,会寻找是否有主键,没有的话,会选择唯一键作为主键,如果没有唯一键的话,会生成一个6位的row_id来作为主键。在多次的查找的时候会浪费大量的资源,所以在创建表的时候要创建主键,来减少资源的浪费。
-
为什么推荐使用整型的自增主键?
整型:在B+树的整个查找过程中,会有很多次的比较的过程,以前会用UUID作为主键,但是UUID在比较的时候会使用大量的资源,而且UUID所占空间还较大,所以应该结合业务逻辑来进行选择,一般选择的是整型的主键。
自增:数据库插入新的值的时候,还需要维护索引。B+树的叶子结点是已经排好序的,如果不是自增的,当向中间插入某个值的时候,如果插入的数据页满了,就需要单独申请一个新的数据页,然后移动部分数据过去(页分裂),此时性能会受到影响,数据页的空间使用率也会降低,给数据库极大地造成了负担。如果是自增的,那么每次插入的时候都会向后面插入,极大的减少了页分裂的概率,提高了效率。