mysql基础

一、索引

mysql索引.png

索引是一种帮助MySQL高效获取数据的排好序的数据结构，解决查询语句中where和order by部分的性能
索引是存放在磁盘上的/var/lib/mysql目录下
MyISAM引擎的文件：【.frm】表结构【.myd】即my data文件【 .myi】即my index文件
InnoDB引擎的文件：【.frm】表结构【.ibd】文件：存放用户数据库表数据和索引

1.1、为什么使用B Tree（B+Tree）

一般来说，索引本身也很大，不可能全存内存，往往以索引文件的形式存在磁盘

单节点能存储更多数据，使得磁盘IO次数更少。
B+树索引叶子节点形成有序链表，具有范围查找和前缀查找的能力，相当于二分查找。
聚集索引中，叶子节点的data直接包含数据；非聚集索引中，叶子节点存储数据地址的指针。

1.2、索引分类

普通索引index :加速查找
唯一索引
- 主键索引：primary key ：加速查找+约束（不为空且唯一）
- 唯一索引：unique：加速查找+约束（唯一）
联合索引
- primary key(id,name):联合主键索引
- unique(id,name):联合唯一索引
- index(id,name):联合普通索引
非聚集索引【MyISAM引擎】or 聚集索引【InnoDB引擎】
- MyISAM的key存放的是数据库的key，value存放的是数据库记录的地址，在通过地址定位到数据。
MyIsam.png
- InnoDB的key存放的是数据库的key，value存放的是数据库记录的内容，聚集索引叶子节点就是数据节点（叶节点包含了完整的数据记录），相比MyISAM效率要高一些，但是比较占硬盘内存大小。
InnoDB.png
- 关于聚集索引，为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？innodb会按照如下规则进行处理
  
  a. 如果使用自增主键，新增数据时都是尾部顺序插入，不用调整B+Tree树结构，也不用移动重排数据。
  b. 不建议使用uuid使用数据库主键，不支持范围查询
  c. B+树底层搜索的时候可能会发生值比较判断
  
  1. 如果定义了主键，那么这个主键就是作为聚集索引
  2. 如果没有定义主键，那么该表的第一个唯一非空索引被作为聚集索引
  3. 如果没有定义主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自

1.3、哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合建立索引，因为每次更新不单单是更新了记录还会更新索引
WHERE条件里用不到的字段不创建索引
单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

1.4 、哪些情况不要创建索引

表记录太少
经常增删改的表
提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

二、MySQL常见的三种引擎。

InnoDB:支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果对事物的完整性要求比较高(如银行)，要求实现并发控制(如售票)，那么选择InnoDB有很大的优势。如果需要频繁更新、delete数据，也可以选择InnoDB，因为InnoDB支持事物的提交commit和回滚rollback.
MyISAM:插入数据快，空间和内存使用比较低。如果表主要用于插入新记录和读出记录，那么选择MyISAM能实现处理高效。如果应用的完整性，并发性要求比较低，也可以使用。
MEMORY:所以数据都在内存中，数据处理速度快，但安全性差。它对表有要求，不能建立太大的表。只能应用于小表，对速度要求高，对安全性要求很低的场景。