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:所以数据都在内存中，数据处理速度快，但安全性差。它对表有要求，不能建立太大的表。只能应用于小表，对速度要求高，对安全性要求很低的场景。
三、MySQL查询性能
- explain + SQL(SELECT *), 要学会其中各个字段的含义，及背后的原理。
- 避免 SELECT ，当只要一行数据时使用LIMIT 1（按需取数据*）。
- 为搜索字段建索引，如：永远为每张表设置一个 ID，方便检索。
- 为查询缓存优化你的查询
- 千万不要 ORDER BY RAND()
- 使用 ENUM 而不是 VARCHAR
- 尽可能的使用 NOT NULL
- 把 IP 地址存成 UNSIGNED INT
- 固定长度的表会更快
- 越小的列会越快
- 选择正确的存储引擎
网友评论