MySQL索引学习

索引学习

1.什么是索引

索引是帮助MySQL搞笑获取数据的数据结构，即排好序的快速查找数据结构。

2.索引的优劣势

优点：

• 提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

缺点：

• 索引存放在磁盘中，也要占用空间。
• 降低了更新表的速度，因为在MySQL数据更改的时候，索引文件也要发生调整。

3.索引类型

1. 普通索引：基本的索引类型，值可以为空，没有唯一性限制。
2. 唯一索引：索引列的值必须唯一，允许有空值。
3. 主键索引：一种唯一性索引，必须指定为PRIMARY KEY，一个表只能有一个主键，不允许有空值。
4. 组合索引：由多个字段创建的索引，专门用于组合搜索(最左匹配原则)。
5. 全文索引：主要用来查找文本中的关键字，类型为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+树的叶子结点是已经排好序的，如果不是自增的，当向中间插入某个值的时候，如果插入的数据页满了，就需要单独申请一个新的数据页，然后移动部分数据过去(页分裂)，此时性能会受到影响，数据页的空间使用率也会降低，给数据库极大地造成了负担。如果是自增的，那么每次插入的时候都会向后面插入，极大的减少了页分裂的概率，提高了效率。

• InnoDB与MyISAM之间的区别?

• MySQL索引数据结构的选择？