MySql高级进阶
1.MySql引擎
1.概述
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控 制访问权限并快速处理事务,从而满足企业内大多数 需要处理大量数据的应用程 序的要求。
2.mysql数据库常用的引擎主要有MyIsam和InnoDB
MyIsam | InnoDB | |
---|---|---|
存储限制 | 256TB | 64TB |
支持事务 | No | Yes |
支持全文索引 | Yes | Yes |
支持数索引 | Yes | Yes |
支持哈希索引 | No | No |
支持数据缓存 | No | Yes |
支持外键 | No | Yes |
mysql默认的引擎时InnoDB
可通过语句修改引擎
ALTER TABLE 表名 ENGINE = INNODB
InnoDB是一种事务型存储引擎,有行级锁定和外键约束。
Innodb 引擎提供了对数据库 ACID 事务的支持,并且实现了 SQL 标准的四种隔 离级别,该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库 系统.MySQL 运行时 Innodb 会在内存中建立缓冲池,用于缓冲数据和索引。该引擎支持 FULLTEXT 类型的索引(全文检索),而且它没有保存表的行数,当 SELECT COUNT(*) FROM TABLE 时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发 较高时,使用 Innodb 引擎会提升效率。但是使用行级锁也不是绝对的,如果在 执行一个 SQL 语句时 MySQL 不能确定要扫描的范围,InnoDB 表同样会锁全 表。
MyIsam极度强调快速读取操作.
如果读操作远远超过写操作,比较适合
2.索引
1.概述
当数据库中数据量非常大时,总是扫描整张表对于数据库压力非常大,在高并发,与数据量非常大时,数据库需要在磁盘读数据
需要创建一个像书本一样目录的东西————索引。(磁盘读数据很慢)
索引由数据库中的一列或多列组成,为了特高数据库的压力,优化查询效率而产生
客观的讲,索引也是一张“表”,保存了主键与索引字段,并指向实体表的记录。
2.优点:
1.索引提高了数据库的性能,减少了数据库缓慢的io操作
2.如果用索引对数据进行排序,排序速率很快,降低cpu消耗
3.缺点
1.占储存空间
2.当数据库进行新增,修改删除时,先对表进行更改,再对相应的索引进行操作
4.索引的分类
2.41.主键索引(存储的是所有字段的值)
设主键后数据库自动创建索引
2.4.2.唯一索引(索引的列的值必须唯一)
CREATE UNIQUE INDEX 索引名 ON 表名(列名)
DROP INDEX 索引名 ON 表名;
2.4.3.单值索引(一个索引只包含一个列)
CREATE INDEX 索引名 ON 表名(列名)
DROP INDEX 索引名 ON 表名;
2.4.4.复合索引
符合索引指的是,一个索引包含多个列,减少开销(当表的行数远大于列数)
CREATE INDEX 索引名 ON 表名(列名)
DROP INDEX 索引名 ON 表名;
5.索引创建的规则
2.5.1创建原则
1.主键创建索引
2.频繁作为查询条件的列进行创建索引
3.外键创建索引
4.需要排序的列创建索引
2.5.2不创建原则
1.表记录少(可以但没必要,影响写操作)
2.很少作为查询条件
3.表中一列中重复的值过于多(例如:男女)
3.索引数据结构
mysql的索引底层的储存采用的b+树,高度很低,利于查找
Hash表:无序,不连续
红黑树:如果数据过大,高度会变的很高,查询会很慢
4.聚簇索引和非聚簇索引
!!!主键索引存储***所有字段的值***
!!!非主键索引存储***主键的值***(除了主键索引,其他为非主键索引)
区别:是否进行回表查询
聚簇索引:
1.select * from user where id=1
id为主键,根据主键的索引去命中id=1,查找对应的索引叶子节点存储了 id=1的所有字段的值。
2.select no from user where no=1(no为编号,非主键索引)
根据no索引查找值,查到的是主键的值,这时候拥有id、no字段的值,需要查询的字段已经查询完成,不用回表查询。
非聚簇索引
1.select * from user where no =1
根据no索引查找值,查到的是主键的值,这时候拥有id、no字段的值,再通过主键去通过主键索引查找其他字段的值。