mysql索引

什么是索引： 索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。【相当于书的目录

数据库使用 select 查询的时候
1.先遍历表

2.把当前的行给带入到条件中,看条件是否成立

3.条件成立, 这样的行就保留.不成立就 跳过

索引 ：

属于是针对 査询操作 引入的 优化手段,可以通过索引来加快査询的速度,避免针对表进行遍历

索引是能提高查询速度的,但是也有代价:

1.占用更多的空间.生成索引,是需要一系列的数据结构,以及一系列的额外的数据,来存储到硬盘空间中的.2.但是可能会降低插入修改删除的速度,

注意，主键 unique 外键 都会自动生成索引

mysql> create table student (id int unique, name varchar(20));------unique-------
Query OK, 0 rows affected (0.02 sec)

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | id       |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

索引的相关操作

1.查看索引:

show index from 表名;

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2.创建索引

创建索引操作,也是一个危险操作!!
创建索引的时候,需要针对现有的数据,进行大规模的重新整理
如果当前表是一个空表,或者数据不多,创建索引都没啥问题,如果这个表本来就很大,创建索引,也很容易就把数据库服务器给卡住

create index 索引名字 on 表名（列名）;

如果是自动创建索引（主键/外键 , unique 不能删除的）

mysql> create table student (id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> show index from student;
Empty set (0.00 sec)

——————————————如上没有添加索引——————————

——————下表添加了索引——————————————
mysql> create table student (id int primary key , name varchar(20));-----一个索引是  根据一个列来指定的
Query OK, 0 rows affected (0.02 sec)

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


————————————————————————手动添加——————————————————
create index idx_student_name on student(name);


mysql> show index from student;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | id               |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | idx_student_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3.删除索引

drop index 索引名 on 表名；

mysql> drop index  idx_student_name on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | id       |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

4.B+树

B+树的优点(相对于 B 树以及哈希,红黑树)
1)N 又搜索树,树的高度有限的.降低 10 的次数,
2)非常擅长范围查询
3)所有查询最终都是要落到叶子节点.查询和查询之间的时间开
不会出现这次特别快,下次特别慢的情况
4)由于叶子节点是全集,会把行数据只存储在叶子节点上非叶子节点只是存储一个用来排序的 key(比如存个 id )
数据库里是按行组织数据的.创建索引的时候,是针对这一列进行创建~~

mysq| 索引实现,也是有一些变数的.不是只有 B+ 树这一种情况.
mysq! 内部有一个模块,存储引擎存储引擎模块是提供了很多版本的实现的,Innodb 当前最常用的 mysq| 存储引擎 => B+ 树.