索引
- 是一个排序列表，存储着索引值和这个值所对应的物理地址
- 无需对整个表进行扫描，通过物理地址就可以找到所需数据
- 是表中一列或若干列值排序的方法
- 需要额外的磁盘空间
索引的作用
- 加速数据检索：索引最直接的作用是显著提高数据库查询的速度。通过在数据库表的列上创建索引，可以使得数据库系统以更快的速度定位到表中的特定记录。这是因为索引通常是以B树（或其变种，如B+树）等高效的数据结构实现的，这些结构能够迅速缩小查找范围，减少磁盘I/O操作。
- 优化排序和分组：除了加速数据检索外，索引还可以帮助数据库优化排序（ORDER BY）和分组（GROUP BY）操作。如果排序或分组的列被索引了，数据库可以利用索引的顺序性来减少排序所需的计算量，从而加快查询速度。
- 保证数据的唯一性：唯一索引能够确保表中每一行数据的指定列或列组合的值都是唯一的。这对于保证数据的完整性和避免重复记录非常有用。
- 加快表连接（JOIN）操作：在执行表连接查询时，如果连接条件中的列被索引了，那么数据库可以利用这些索引来减少需要比较的行数，从而加快连接操作的速度。
- 实现全文搜索：虽然全文索引与传统索引（如B树索引）在实现方式上有所不同，但它们同样能够显著提高在大型文本字段中搜索特定关键词的速度。全文索引通常支持复杂的搜索查询，如模糊匹配、通配符搜索等。
- 作为外键约束的基础：在某些数据库中，外键约束的实现依赖于索引。这是因为外键约束需要快速验证一个表中的值是否存在于另一个表的对应列中，而索引能够加速这种验证过程。
创建索引的原则依据（什么情况下创建）
- 表的主键、外键必须有索引
- 记录超过200行的表应该有索引
- 经常与其他表进行连接的表，在连接字段上应该建立索引
- 唯一型太差的字段不适合建立索引
- 更新太频繁的字段不适合创建索引
- 经常出现在where子句中的字段，特别是大表的字段，应该建立索引
- 索引应该建立在选择性高的字段上
- 索引应该建立在小字段上，对于大的文本字段甚至超长字段，不要建立索引
索引的类型
普通索引
- 最基本的索引类型，没有唯一型之类的限制
- 创建普通索引的方式
- create index 索引标识 on 表名(列)
- alter table 表名 add index 索引标识 (列)
- create table 表 (id int, name varchar(255),age int,index 索引标识(id)); //创建表时创建
示例
#为students表name列添加普通索引示例1
create index students_index on students(name);
#为students表name列添加普通索引示例2
alter table students add index students_index (name);
#创建表时创建name列普通索引
create table students (id int, name varchar(255),age int,index students_index(name));唯一索引
- 与普通索引基本相同
- 与普通索引的区别在于索引列的所有值只能出现一次，即必须唯一
- 创建唯一索引的方式
- create unique index 索引标识 on 表名(列)
- alter table 表名 add unique 索引标识 (列)
- create table 表 (id int, name varchar(255),age int,unique 索引标识(id));
示例
#为students表id列添加唯一索引示例1
create unique index students_index on students(id);
#为students表id列添加唯一索引示例2
alter table students add unique students_index (id);
#创建表时创建name列普通索引
create table students (id int, name varchar(255),age int,unique students_index(id));主键索引
- 是一种特殊的唯一索引 ，指定为“PRIMARY KEY”
- 一个表只能由一个主键索引
- 创建主键索引的方式
- CREATE TABLE 表名 (id int(11) ,name char(255) ,PRIMARY KEY (id));
- CREATE TABLE 表名 (id int(11) PRIMARY KEY, name char(255) );
- ALTER TABLE 表 ADD PRIMARY KEY (字段);
示例
#创建表时添加字段示例1
create table students (id int primary key,name varchar(255));
#创建表时添加字段示例2
create table students (id int, name varchar(255), primary key (id));
#已存在表创建主键索引
alter table students add primary key (id);组合索引（单列索引与多列索引）
- 可以是单列上创建的索引，也可以是在多列上创建的索引，注意列的顺序很重要，因为它会影响索引的效率和适用性。
- 创建组合索引的方式
- CREATE INDEX 索引（标识）名称 ON 表名(列, 列,...);
示例
#为students表的name列和age列添加组合索引
create index students_a1 on students(name,age);
全文索引
- 全文索引（Full-Text Index）是一种特殊类型的索引，它允许你对文本内容进行快速的搜索。全文索引特别适用于对大量文本数据（如文章、博客帖子等）进行搜索的场景。5.6版本之后才支持全文索引
- 创建全文索引的方式
- 创建表时添加索引 CREATE TABLE 表(id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,name VARCHAR(200),addres TEXT,FULLTEXT(name,addres)) ENGINE=InnoDB;
- 现有表添加索引 ALTER TABLE 表 ADD FULLTEXT(列, 列);
- 创建全文索引后可以使用SELECT * FROM 表 WHEREMATCH(索引列, 索引列) AGAINST('MySQL 全文索引'INNATURALLANGUAGE MODE);来查询
示例
#创建表时添加全文索引
create table students(id int,
name varchar(255),
addres text,
fulltext(name,addres)
);
#现有表添加全文索引
alter table students add fulltext (name,addres);查看索引
- show index from 表名;
- show keys from 表名;
示例
show index from students;
show keys from students;删除索引
- alter table 表名 drop index 索引标识;
- drop index 索引标识（名）on 表名
示例
#删除students表内一个名为students_index的索引
alter table students drop index students_index;
#删除students表内一个名为students_index的索引
drop index students_index on students；事务
四个特性
- 原子性
- 事务是一个完整的操作，事务的各元素时不可分的
- 事务中的所有元素必须作为一个整体提交或回滚
- 如果事务中的任何元素失败，则整个事务将失败
- 一致性
- 隔离性
- 持久性
事务的控制方法
事务处理命令控制事务
- begin：开始一个事务
- commit：提交事务
- rollback：回滚事务
使用set命令进行控制
- set autocommit=0：禁止自动提交
- set autocommit=1：开启自动提交
示例
#MySQL默认事务是自动提交的所以测试事务可以先使用set关闭事务的自动提交
#示例提交事务
#关闭自动提交
set autocommit=0
#开启事务
begin
insert into students values (1,张三,22);
------多条sql语句------
#提交事务
commit
#提交事务之后insert插入语句便会执行将数据存储到students表内
#示例回滚事务
#关闭自动提交
set autocommit=0
#开启事务
begin
insert into students values (1,张三,22);
------多条sql语句------
#回滚事务
rollback
#回滚事务之后insert插入语句便不会执行存储引擎
MyISAM存储引擎
- MyISAM不支持事务，也不支持外键
- 访问速度快
- 对事务完整性没有要求
- MyISAM在磁盘上存储成三个文件
- .frm文件存储表定义
- 数据文件的扩展名为MYD（MYData）
- 索引文件的扩展名是MYI（MYIndex）
- 表级锁定形式，数据在更新是锁定整个表
- 数据库在读写过程中相互阻塞
- 也会在数据写入的过程阻塞用户数据的读取
- 也会在数据读取的过程中阻塞用户数据的写入
- 数据单独写入或读取，速度较快且占用资源较少
- MyISAM 表还支持 3 种不同的存储格式：
- 静态(固定长度)表
- 动态表
- 压缩表
MySQL存储引擎
- 支持4个事务隔离级别
- 行级锁定，但是全表扫描仍然会是表级锁定
- 读写阻塞与事务隔离级别相关
- 能非常高效的缓存索引和数据
- 表与主键以簇的方式存储
- 支持分区、表空间，类似oracle数据库
- 支持外键约束，5.5前不支持全文索引，5.5后支持全文索引
- 对硬件资源要求还是比较高的场合
设置存储引擎的方法
创建表时设置存储引擎
CREATE TABLE 表(字段) ENGINE=InnoDB;
修改现有表的存储引擎
ALTER TABLE 表 ENGINE=InnoDB;
通过修改MySQL的配置文件更改MySQL的默认存储引擎
示例
#创建表时设置存储引擎为InnoDB
create table students (id int,name varchar(255)) engine=InnoDB;
#修改现有表的存储引擎为MyISAM
alter table students engine=MyISAM
#修改MySQL的配置文件设置默认存储引擎为InnoDB
vi /etc/my.conf //my.conf通常为unix系统的配置文件，而windows的配置文件通常为my.ini
#内容如下
[mysqld] //mysql段
default-storage-engine=InnoDB
1930
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
