MySQL原理及优化（简明版）

一、思想

MySQL按照分块查找的思想，通过特定的查找方式进行数据操作（插入、查找）

二、优化

1.分页

1.1为什么分页？

页是计算机管理存储器的逻辑块，主存和磁盘以页为单位交换数据，当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行

1.2 MySQL分页

1. 数据库索引是存储在外存(磁盘，即数据库数据资料)、对数据库的相关操作命令是基于主存发出的。在MySQL中使用的数据结构是B+Tree，根节点在内存中，数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入(I/O操作相当耗费系统资源)，我们可以通过分页将数据存入数据库。
2. 对于B树来说，深度过高会大大增加查找难度，所以处理方案是增加宽度。B树的搜索复杂度为O(h)=O(logdN)（注：h，深度，d，宽度；N，关键字个数），所以树的宽度d越大，深度h就越小，I/O的次数就越少。B+Tree恰恰可以增加出度d的宽度，因为每个节点大小为一个页大小，所以出度的上限取决于节点内key和data的大小

2.索引

概述：索引，建立在一种数据结构上的查找方法(B/B+),在MySQL按照分块查找的思想，通过特定的查找方式进行数据操作（插入、查找）

2.1MySQL的存储方式

2.1.1InnoDb/MyISAM

1. MYISAM：基于表级锁，线程只能一个一个操作数据库(上一个线程释放锁下一个才能进行)，但读的性能很高，全表扫描。
   -数据存储方式，索引与数据文件分离。在B+树的叶节点上存储表的索引key为，保存数据记录的地址。
   -主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
   
2. InnoDB：基于行级锁，支持多线程并发同时更新，与更新有关用此引擎
   -数据存储方式，索引，即数据文件本身。在B+树的叶节点data上存储表的完整表的信息，索引的key是数据表的主键。
   
   注意：InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

简单优化方法

InnoDB：

1. 使用自增字段作为主键(因为非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效)
2. 不建议使用过长的字段作为主键(因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大)

3.优化策略

3.1联合索引及最左前缀原理与前缀索引

a 联合索引

定义：相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。
–比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，因此，联合索引的特点：
 第一个字段一定是有序的
 当第一个字段值相等的时候，第二个字段又是有序的

b 最左前缀原理

定义：在联合索引中，先根据第一个字母查，然后再根据第二个字母查，或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。

c 前缀索引

定义： 除了联合索引之外，对mysql来说其实还有一种前缀索引。前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销
- 应用
1.字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
2.字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。

3.前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了

注意事项：MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

3.2索引优化策略

1. 最左前缀匹配原则，上面讲到了主键外检一定要建索引。对 where,on,group by,order by 中出现的列使用索引

2. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0

3. 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键

4. 索引列不能参与计算，保持列“干净”，比如fromunixtime(createtime) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成createtime = unixtimestamp(’2014-05-29’);

5. 为较长的字符串使用前缀索引

6. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

7. 不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建

8. 对于like查询，”%”不要放在前面。
   

9. 查询where条件数据类型不匹配也无法使用索引
   

10. 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

注：原文有更详细链接，可以从原文拜读大佬文章https://blog.csdn.net/suifeng3051/article/details/52669644