【linux】循序渐进学运维-MySQL-索引

mac2024-10-11 50

文章目录

索引的概念索引概念1.索引的优点：2. 索引的缺点：索引的类型：1 普通索引2. 唯一索引3. 主键索引4. 复合索引5. 全文索引 (FULLTEXT INDEX) 索引设计原则面试题

索引的概念

索引概念

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1.索引的优点：

为了加快搜索速度，减少查询时间。索引大大减少了服务器需要扫描的数据量。

2. 索引的缺点：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。 3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

因为索引非常占内存，所以索引也需要谨慎添加，那些字段需要索引。

索引的类型：

1，普通索引：最基本的索引，没有任何限制，是我们经常使用到的索引。 2，唯一索引：与普通索引类似，不同的是，唯一索引的列值必须唯一，但允许为空值。

主键索引是特殊的唯一索引，不允许有空值。

3，全文索引：全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表，作用于CHAR，VARCHAR、TEXT数据类型的列。 4、组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

1 普通索引

最基本的索引，不具备唯一性，就是加快查询速度

创建普通索引：方法一：创建表时添加索引 create table 表名（列定义 index 索引名称（字段） index 索引名称（字段））注：可以使用key，也可以使用index 。index 索引名称（字段），索引名称，可以加也可以不加，不加使用字段名作为索引名。创建test01表

mysql> create table test01( id int primary key not null auto_increment, a1 char(10), a2 char(10), a3 char(10), a4 char(10), a5 char(10)); Query OK, 0 rows affected (0.01 sec)

插入数据

mysql> insert into test01(a1,a2,a3,a4,a5) values('wg01','wg02','wg03','wg04','wg05'); Query OK, 1 row affected (0.00 sec) mysql> insert into test01(a1,a2,a3,a4,a5) values('zmedu01','zmedu02','zmedu03','zmedu04','zmedu05'); Query OK, 1 row affected (0.00 sec) mysql> insert into test01(a1,a2,a3,a4,a5) values('xinsz01','xinsz02','xinsz03','xinsz04','xinsz05'); Query OK, 1 row affected (0.00 sec) mysql> insert into test01(a1,a2,a3,a4,a5) values('zr01','zr02','zr03','zr04','zr05'); Query OK, 1 row affected (0.00 sec) mysql> select * from test01; +----+---------+---------+---------+---------+---------+ | id | a1 | a2 | a3 | a4 | a5 | +----+---------+---------+---------+---------+---------+ | 1 | wg01 | wg02 | wg03 | wg04 | wg05 | | 2 | zmedu01 | zmedu02 | zmedu03 | zmedu04 | zmedu05 | | 3 | xinsz01 | xinsz02 | xinsz03 | xinsz04 | xinsz05 | | 4 | zr01 | zr02 | zr03 | zr04 | zr05 | +----+---------+---------+---------+---------+---------+ 4 rows in set (0.00 sec)

建索引：方法一：当创建完表后，使用alter添加索引

mysql> alter table test01 add index idx(a1); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc test01; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | a1 | char(10) | YES | MUL | NULL | | | a2 | char(10) | YES | | NULL | | | a3 | char(10) | YES | | NULL | | | a4 | char(10) | YES | | NULL | | | a5 | char(10) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)

注：如果Key是MUL, 就是一般性索引，该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL。就是表示是一个普通索引。我们先删除索引

方法二：在创建表的时候直接创建索引

mysql> create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd) ); 注意：index和 key 是相同的 mysql> create table demo1( id int(4), name varchar(20), pwd varchar(20), key(pwd) );

mysql> alter table demo drop key pwd; #注意此处的pwd指的是索引的名称,而不是表中pwd的那个字段再用alter添加 mysql> alter table demo add key(pwd);

2. 唯一索引

与普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，用来约束内容，字段值只能出现一次，应该加唯一索引。唯一性允许有NULL值<允许为空>。

创建唯一索引：方法一：创建表时，加唯一索引 create table 表名( 列定义： unique key 索引名（字段）; ) 注意：唯一常用在值不能重复的字段上，比如说用户名，电话号码，身份证号。

方法二：修改表时，加唯一索引 alter table 表名 add unique 索引名 (字段); mysql> alter table order drop key uName; mysql> alter table order add unique(uName);

3. 主键索引

查询数据库，按主键查询是最快的，每个表只能有一个主键列，可以有多个普通索引列。主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一，不允许为空

mysql> show create table test2; mysql> show index from test2 \G

方法二：创建表后添加<不推荐>,如果生产的数据无法保证唯一，创建主键报错再添加先删除测试

删除遇到这种情况是auto_increment的原因 mysql> alter table demo5 change id id int(4) not null; #先取消自增长 mysql> alter table demo5 drop primary key; 再删除主键 mysql> alter table demo5 change id id int(4) not null primary key auto_increment; 总结：主键索引，唯一性索引区别： 1）主键索引不能有NULL，唯一性索引可以有空值 2）主键索引创建后一定包含一个唯一性索引，而唯一性索引不一定就是主键。 3）一个表最多有一个主键，但可以创建多个唯一索引 4）主键更适合哪些不容易更改的唯一表示，如自动递增，身份证号等。

4. 复合索引

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引

5. 全文索引 (FULLTEXT INDEX)

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。 MySQL在数据量较大的情况下，高并发连接的情况下。 select 语句 where bName like ‘%网%’ 使用% _ 通配符，不通过索引，直接全表扫描。 ABSUWU LIKE ‘%U_U’ 数据库压力大。 MySQL的解决方案：全文索引：3.2开始支持全文索引。无法正确支持中文。从MySQL 5.7.6开始 MySQL内置了ngram全文检索插件，用来支持中文分词

查看表当前默认的存储引擎： mysql> show create table 表名;

全文索引只能用在 varchar text

创建全文索引：方法一：创建表时创建 create table 表名（列定义， fulltext key 索引名（字段）; ）

方法二：修改表时添加 alter table 表名 add fulltext 索引名（字段）; ALTER TABLE books ADD FULLTEXT [索引名] (author )

注意：MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效。一般交给第三方软件进行全文索引。 http://sphinxsearch.com/

索引设计原则

不要过度使用索引，索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响inset，update，delete等性能。不要在区分度低的字段上建索引，比如性别字段，建索引完全起不到优化效果当唯一性是某个字段本身的特征时候，制定唯一索引能提高查询速度

面试题

mysql索引中最重要的数据结构是？ B+ tree说一下B+ tree 原理？B+ tree 与红黑树的比较什么是最左前缀原则？可以使用多少列创建索引？任何标准表最多可以创建16个索引列。 6. 简单描述下索引，主键，唯一索引，联合索引的区别，对数据库性能上有什么影响（从读写两个方面）？ 7. 主键，外键，和索引的区别？ 8. mysql为什么用自增列作为主键？

最新回复(0)