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）

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

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

1.2、索引分类

1. 普通索引index :加速查找

2. 唯一索引

   • 主键索引：primary key ：加速查找+约束（不为空且唯一）
   • 唯一索引：unique：加速查找+约束（唯一）

3. 联合索引

   • primary key(id,name):联合主键索引
   • unique(id,name):联合唯一索引
   • index(id,name):联合普通索引

4. 非聚集索引【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
• 固定长度的表会更快
• 越小的列会越快
• 选择正确的存储引擎