目录
一、MySQL 架构
连接层:
客户端链接请求,对用户进行认证。
服务层:
可以接收sql,调用存储过程,优化sql,缓存数据
引擎层:
负责实际与文件层进行交互操作的,可以由不同的引擎选择。
物理文件层:
存储表数据以及各种日志文件。
二、引擎
一、概述
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控 制访问权限并快速处理事务,从而满足企业内大多数需要处理大量数据的应用程 序的要求。
引擎是指mysql在数据操作时,所使用的技术(事务,索引,锁,外键),mysql中提供了多种引擎技术,不同引擎特征不同。
查看支持的引擎
SHOW ENGINES;
查看表引擎
SHOW TABLE STATUS LIKE '表名'
修改引擎
方式 1:将 mysql.ini 中 default-storage-engine=InnoDB,重启服务.
方式 2:建表时指定 CREATE TABLE 表名(...)ENGINE=MYISAM;
方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB;
二、InnoDB
默认的存储引擎。支持事务,行级锁(并发量大),外键约束,容量大,支持索引,支持缓存,支持主键自增,不支持村拿出表总行数,需要sql逐行统计。
三、MyISAM
不支持事务,不支持行级锁,支持表锁并发量小,不支持外键,支持全文检索,MyISAM存储了表的总行数,统计速度快。
区别:
三、索引
对于少量的数据,查询 的速度会很快,但是,当随着数据量的增加,性能会急剧下降。100 万条数据逐 页查询的时间是无法被用户接受的。
一、什么是索引
排好序的快速查找的数据结构。
数据库在存储数据本身之外,还维护着一个满足特定查找算法的数据结构, 这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高 级查找算法,这种数据结构就是索引。
二、索引原理
索引类似于书的目录,在一本书前面加上目录,查找内容时不必逐页翻阅就 能够快速地找到所需的内容。快速地找到所需要的数据位置拿到物理地址。
三、索引优势
提高数据检索的效率,降低数据库的 IO 成本;
通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗;
四、索引劣势
索引也要占用空间,数据进行增删改索引也要更新时间。
五、索引创建原则
一、哪些情况需要创建索引
主键,外键,查询条件(where后边的列,例如新闻标题),排序分组使用的列。
二、哪些情况不要创建索引
表数据太少,增删改频繁的表(更改数据也要更改索引),Where 条件里用不到的字段不创建索引,数据重复且分布平均的表字段(例如:性别)。
六、索引分类
主键索引:
设定为主键后数据库会自动建立索引
ALTER TABLE表名 add PRIMARY KEY表名(列名);
删除建主键索引:
ALTER TABLE表名 drop PRIMARY KEY ;
单值索引:
即一个索引只包含单个列,一个表可以有多个单列索引
创建单值索引
CREATE INDEX索引名 ON表名(列名);
删除索引:
DROP INDEX索引名;
唯一索引:
索引列的值必须唯一,允许为 null
CREATE UNIQUE INDEX索引名 ON表名(列名);
删除索引
DROP INDEX索引名 ON表名;
组合索引(复合索引):
多个列打包,创建一个索引,降低了空间开销,
例如,几个查询条件经常组合使用。
创建复合索引
CREATE INDEX 索引名 ON 表名(列 1,列 2...);
删除索引:
DROP INDEX 索引名 ON 表名;
组合索引最左前缀原则:
在使用组合索引的列作为条件时,必须要出现最左侧列为条件,
否则组合索引不生效
全文索引:
需要模糊查询时,一般索引无效,这时候就可以使用全文索引了。
CREATE FULLTEXT INDEX索引名 ON表名(字段名) WITH PARSER ngram;
SELECT结果 FROM表名 WHERE MATCH(列名) AGAINST(‘搜索词')
查看索引:
SHOW INDEX FROM表名;
七、索引数据结构
mysql使用自增主键。
树状有:
二叉树:像链表一样单边增长。无效作用。
红黑树:虽然会旋转平衡,但是也是一边的增长,树的高度大。
B树:是多路平衡树,叶子节点之间没有联系。
B+树:多路平衡树,叶子节点之间有联系。
所以mysql中最终选择了B+树。
B+树一个节点中可以存储更多的数据,而且数据都存储在叶子节点,这样非叶子节点中就可以存储更多索引数据,叶子节点之间还有指针指向,便于范围查询。
八、聚簇索引和非聚簇索引
聚簇索引
找到了索引就找到了需要的数据。
innodb中的主键就是聚簇索引。
非聚簇索引
索引的存储和数据的存储分离,找到了索引但没找到数据,要根据索引上的值(主键)再次回表查询就是非聚簇索引。
MyISAM引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中。
九、回表查询
评判标准就是看查询是否能直接找到想要的数据。
主键,一级索引,可以直接找到数据, 不需要回表查询。
二级索引, 如果通过索引列还查询了其他列,需要回表查询。
如果只需要查询索引列本身,不需要回表查询。