数据库-索引

最新推荐文章于 2023-04-05 13:16:35 发布

xx_01_2048769943

最新推荐文章于 2023-04-05 13:16:35 发布

阅读量89

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/m0_46289868/article/details/112944150

版权

数据库专栏收录该内容

26 篇文章 0 订阅

订阅专栏

定义

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。
在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。
当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。
通过使用快速路径访问方法快速定位数据,
减少了磁盘的I/O 与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

作用

（1）快速取数据；
（2）保证数据记录的唯一性；
（3）实现表与表之间的参照完整性；
（4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

优缺点

优点

1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

所有的设计原则

1．选择唯一性索引
2．为经常需要排序、分组和联合操作的字段建立索引
3．为常作为查询条件的字段建立索引
4．限制索引的数目
5．尽量使用数据量少的索引
6．尽量使用前缀来索引
7．删除不再使用或者很少使用的索引

索引类型

MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等

1.普通索引index :加速查找
2.唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
	注：只能创建在char,varchar或text类型的字段上。
5.空间索引spatial

索引的操作

创建索引三种方式：
1、创建表的时候创建索引
2、在已经存在的表上创建索引
3、使用ALTER TABLE语句来创建索引

索引的创建格式

1、建表时索引的创建格式

CREATE TABLE 表名 ( 属性名 数据类型 [完整性约束条件], 
属性名 数据类型 [完整性约束条件], 
…
属性名 数据类型 
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY 
[别名](属性名1 [(长度)] [ASC | DESC]) 
);

2、在已经存在的表上创建索引

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );

索引的创建

mysql> select *from student;
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |
| 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+-----------+------+-------+--------------+--------------------+
```shell
**创建普通索引**
mysql> create index index_student on student(id);

**创建唯一索引**
mysql> create unique  index index_student_1 on student(id);

**创建全文索引**
mysql> create fulltext index index_student_2 on student (address(100));

**创建多列索引**
mysql> create fulltext index index_student_3 on student (department(50),address(100));

使用alter创建索引

**创建普通索引**
mysql> alter table student add index index_student (id);

**创建唯一索引**
mysql> alter table student add unique index index_student_1 (id);

**创建全文索引**
mysql> alter table student add fulltext index index_student_2 (address);

查看索引

show create table 表名\G

mysql> show create table text2\G
*************************** 1. row ***************************
       Table: text2
Create Table: CREATE TABLE `text2` (
  `id` int(11) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `pass` varchar(50) DEFAULT NULL,
  KEY `index_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

方法一：
DROP INDEX 索引名 ON 表名 ; 
方法二：
ALTER TABLE 表名 DROP INDEX 索引名;

实例

创建表text1
mysql> create table text1(id int ,num int,pass varchar(50) );

创建表text2，带有索引
mysql> create table text2( id int , num int, pass varchar(50),index index_id (id));

创建表text3
mysql>  create table text3(id int ,num int,pass varchar(50) );

创建一个存储过程先将大量的数据插入
mysql> CREATE
    ->     PROCEDURE p2()
    ->     BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> WHILE(i<50000)DO
    -> INSERT INTO test.text1 VALUES(i,FLOOR(i+RAND()*i),MD5(i));
    -> SET i=i+1;
    -> END WHILE;
    ->     END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

调用存储过程插入数据
mysql> call p2();

将text1的数据插入text3中
mysql> insert into text3 select *from text1;
Query OK, 49999 rows affected (1.49 sec)
Records: 49999  Duplicates: 0  Warnings: 0

将text1的数据插入text2中，
mysql> insert into text2 select *from text1;
Query OK, 49999 rows affected (1.53 sec)
Records: 49999  Duplicates: 0  Warnings: 0

插入时间结果对比：有索引，插入过程比较慢

测试查询速度：
清空查询缓存
mysql> reset query cache;
mysql> explain select num,pass from text3 where id>=5000 and id<5050;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | text3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 49940 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

mysql> explain select num,pass from text2 where id>=5000 and id<5050;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | text2 | NULL       | range | index_id      | index_id | 5       | NULL |   50 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

EXPLAIN分析结果的含义:

table：	这是表的名字。 
type：连接操作的类型 
possible_keys：可能可以利用的索引的名字 
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。 
key_len：索引中被使用部分的长度，以字节计。 
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行 
rows：MySQL在找到正确的结果之前必须扫描的记录数。最理想的数字为1
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

MySQL使用索引的场景

1) 快速查找符合where条件的记录 
2) 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那 个索引，以便尽快淘汰不符合条件的记录。 
3) 如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作 为索引来加速查找。 
例如，若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均会 作为索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。 
4) 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。 
5)若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引 
6)对建立了索引的字段做sort或group操作时，MySQL会使用索引

利用索引的SQL语句

1) B-Tree可被用于sql中对列做比较的表达式，如=, >, >=, <, <=及between操作 
2) 若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引。 
比如，
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引，而
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'（以通配符开头）和SELECT * FROM tbl_name WHERE key_col LIKE other_col（like条件不是常量串）无法利用索引。 
对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找. 
3) 若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引。 
4) 对于联合索引，sql条件中的最左前缀匹配字段会用到索引。 
5) 若sql语句中的where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围

作业

writers表结构
字段名	数据类型	主键	外键	非空	唯一	自增
w_id	SMALLINT(11)	是	否	是	是	是
w_name	VARCHAR(255)	否	否	是	否	否
w_address	VARCHAR(255)	否	否	否	否	否
w_age	CHAR(2)	否	否	是	否	否
w_note	VARCHAR(255)	否	否	否	否	否

（1）在数据库里创建表writers,存储引擎为MyISAM,创建表的同时在w_id字段上添加名称为UniqIdx的唯一索引
create table writers( 
w_id smallint(11) not null unique primary key, 
w_name varchar(255), 
w_address varchar(255), 
w_age char(2), 
w_note varchar(255),
unique index UniqIdx (w_id)
)engine=MyISAM;

（2）使用alter table语句在w_name字段上建立nameIdx的普通索引
mysql> alter table writers add index nameIdx (w_name(20));
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

（3）使用CREATE INDEX 语句在w_address和w_age字段上面建立名称为MultiIdx的组合索引
mysql> create index MultiIdx on writers(w_address(255),w_age(2));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

（4）使用create index语句在w_note字段上建立名称为FTIdex的全文索引
mysql> create fulltext index FTIdex  on writers(w_note(255));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

（5）删除名为FTIdx的全文索引
mysql> drop index FTIdex on writers;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

xx_01_2048769943

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库-索引

定义索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O 与表独立存放，但不能独立存在，必须属于某个表由数据库自动维护，表被删除时，该表上的索引自动被删除。索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。作用（1）快速取数据；（2）保证数据
复制链接

扫一扫