MySQL高级学习-第一节-数据库与索引简单介绍

一、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 TABLE 表名 ADD [UNIQUE（唯一索引需要添加）] INDEX 索引名称 (字段名);
（2）删除：
DROP INDEX 索引名称 ON 表名;
（3）查看：
SHOW INDEX FROM 表名;

5.索引结构

（1）BTree索引：
（2）Hash索引
（3）full-text全文索引
（4）R-Tree索引

6.索引是否创建的条件

（1）创建索引

• 主键自动创建唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
  （2）不创建索引
• 频繁更新的字段不适合建立索引
• where条件用不到的字段不适合建立索引
• 表记录太少
• 经常增删改的表
• 数据列存在较多重复内容，建立索引没有太大效果

7.索引失效

（1）查询条件包含or，可能导致索引失效
（2）如何字段类型是字符串，where时一定用引号括起来，否则索引失效
（3）like查询以%开头（当满足覆盖索引的条件下，不会失效）
（4）联合索引，需要符合最佳左前缀原则，查询时的条件列不是联合索引中的第一个列，索引失效。并且不能跳过联合索引中的任何一个。
（5）在索引列上使用mysql的内置函数，索引失效。
（6）对索引列运算（如，+、-、*、/），索引失效。
（7）索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
（8）索引字段上使用is null， is not null，可能导致索引失效。
（9）左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
（10）mysql估计使用全表扫描要比使用索引快,则不使用索引