文章目录
存储引擎
1.INnnoDB与MyISAM
对比项 | MyISAM | INnnoDB |
---|---|---|
主外键 | 不支持 | 支持 |
事物 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录 | 行锁 |
缓存 | 只缓存索引,不缓存真实数据 | 索引与数据都会缓存,堆内存要求较高, 且内存大小对性能有决定性的影响 |
表空间 | 小 | 大 |
关注点 | 性能 | 事物 |
默认安装 | Y | Y |
索引
1.索引的定义
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高校获取数据的数据结构。
可以得到索引的本质:索引是数据结构。
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,
这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
我们平时所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉树)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引(hash index)等。
2.索引的优势
- 类似大学图书馆建书目索引,提高数据检索效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序成本,降低了CPU的消耗
3.索引的劣势
- 实际上索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也是要占用空间的
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时,MySQL不仅要不存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立优秀的索引,或优化查询语句
4.索引的分类
- 单值索引
- 即一个索引只包含单个列,一个表可以有多个单列索引
- 建议一张表索引不要超过5个,优先考虑复合索引
- 唯一索引
- 索引列的值必须唯一,但允许有空值
- 复合索引
- 即一个索引包含多个列
- 基本语法
- ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));。
- DROP INDEX [indexName] ON mytable;
- SHOW INDEX FROM table_name
- 覆盖索引
- SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。
- 全文索引
只有在MyISAM引擎上才能使用,而且只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。 - 聚集索引(MyISAM)
B+树叶子节点只会存储数据行(数据文件)的指针,简单来说数据和索引不在一起,就是非聚集索引
5.哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询的条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 频繁更新的字段不适合创建索引
- Where条件里用不到的字段不创建索引
- 单间/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
- 查询中统计或者分组字段
6.哪些情况不要创建索引
- 表记录太少
- 经常增删改的表
- 数据重复且分布平均的表字段,因此应该只为经常查询和经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
性能分析
1.执行计划信息
-
id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
包括三种情况
- id相同,执行顺序由上至下
- id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
- id相同不同,同时存在
-
select_type:查询的类型,主要用于区别普通查询、联合查询、子查询等的复杂查询,包括六种
- SIMPLE:简单的select查询,查询中不包含子查询或者