一、MySQL逻辑结构分层
- 连接层:提供与客户端连接的服务
- 服务层:提供各种用户使用的接口。 如select、insert等;提供SQL优化器(MySQL Query Optimizer)
- 引擎层:引擎层提供各种数据存储的方式。MySQL的存储引擎有很多,比较常用的比如有InnoDB, MyISAM
- 存储层:最终的数据存储在存储层。
二、 存储引擎
1.MyISAM和InnoDB区别
- InnoDB支持事务,MyISAM不支持
- InnoDB支持外键,而MyISAM不支持
- InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的;MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引保存的是数据文件的指针。
- InnoDB不保存表的具体行数,执行select count() 时需要全表扫描;而MyISAM用一个变量保存了整个表的行数,执行select count() 时,查询的是该变量,但是不能添加任何where条件。
- InnoDB支持表、行级锁,默认为行级锁;而MyISAM支持表级锁
- Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI
Innodb:frm是表定义文件,ibd是数据文件
Myisam:frm是表定义文件,myd是数据文件,myi是索引文件
三、MySQL性能下降原因
- sql查询语句存在问题
- 索引失效
- 关联查询(join)
- 服务器调优及各个参数设置
四、索引
1.什么是索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为:快速查找排好序的一种数据结构。Mysql索引主要有两种结构:B+Tree索引和Hash索引。我们平常所说的索引,如果没有特别指明,一般都是指B树结构组织的索引(B+Tree索引)。
2.索引的优缺点
优点:
(1)提高了检索效率,降低IO成本
(2)对数据进行排序,降低数据库排序成本,降低了CPU损耗
缺点:
(1)索引需要占物理空间
(2)降低了数据库插入,修改,删除的效率
3.MySQL索引分类
(1)单值索引:一个索引只能包含单个列
(2)唯一索引:索引列的值必须唯一,可以为空值
(3)复合索引:一个索引包含多列
4.基本语法
(1)创建:
CREAT [UNIQUE(唯一索引需要添加)] INDEX 索引名称 ON 表名 (字段名);
ALERT 表名 ADD [UNIQUE(唯一索引需要添加)] INDEX 索引名称 ON (字段名);
(2)删除:
DROP INDEX 索引名称 ON 表名;
(3)查看:
SHOW INDEX FROM 表名;
5.索引结构
(1)BTree索引:
(2)Hash索引
(3)full-text全文索引
(4)R-Tree索引
6.索引是否创建的条件
(1)创建索引
- 主键自动创建唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
(2)不创建索引 - 频繁更新的字段不适合建立索引
- where条件用不到的字段不适合建立索引
- 表记录太少
- 经常增删改的表
- 数据列存在较多重复内容,建立索引没有太大效果