mysql的一些总结

mysql作为我们工作中经常结束的数据库，我们可能并没有深入了解过，那现在我们这篇文章一起来深入认识下它吧。

---

一、mysql存储引擎

mysql的存储引擎有两种：1、InnoDB 2、MyISAM
  InnoDB是mysql默认的存储引擎，能够支持事务;
  MyISAM对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它

InnoDB	MyISAM
支持事务	不支持事务
支持行级锁	只支持表级锁，不支持行级锁
支持外键	不支持外键
崩溃后发生损坏的概率大且恢复慢	崩溃后发生损坏的概率小且恢复快

二、mysql索引

1.什么是索引

  索引本质是为了满足高效查询数据的一种数据结构。数据库中的索引分为很多种，InnoDB中采用的是B+树。在讲B+树前我们先来了解下其他的索引。

1.1 hash索引

  大家应该都知道哈希表是数组加链表的结构，再查询数据时先计算hash值确定数据所在的位置，如果坐在位置是链表要遍历链表。而链表是无序的，这样虽然提高了数据插入的效率，但是在查询特定区间的数据（比如查询年龄在3-7）要遍历整个链表，显然效率是不高的。也正是这个原因，hash索引只适用在等值查询的场景，如memcached及其他一些nosql的引擎

1.2 二叉树

B+树是由二叉查找树、二叉平衡树、B树演变而来，所以在讲B+树前我们一起先来看看这三种数据结构

1.2.1 二叉查找树

  二叉查找树具有以下特点：
1、左节点的值要小于当前节点的值
2、右节点的值要大于当前节点的值
如下图，我们要查找19时，只需要3步就可以完成，很大程度提高了查询的效率。
但是在极端情况下二叉查找树会退化成一个链表，如下图。在这种情况下查询的时间复杂度就蜕变为了O(n)

也是上面二叉树的不稳定性，二叉搜索树没有作为mysql索引的数据结构

1.2.2 二叉平衡树

有没有方法能够解决二叉查找树的不稳定的问题呢？二叉平衡树就是一种解决方法。
  二叉树平衡树具有每个阶段左右子树的高度差不能超过1的特点。为了保持二叉树的平衡，在数据插入的时候，需要进行n此的左旋或者右旋处理，这就会导致插入的性能较差。
  那么为什么二叉平衡树叶没有作为mysql索引的数据结构呢，本质还是因为在数据量庞大的情况下，所有的树的高度会变的极高，查询的效率不高

1.2 B树

  B树是一种多路平衡查找树，它与类似于平衡二叉树，不同的是每个节点可以存在多子节点

  如上图，B树有这些特点：
1、每个节点同时存储数据和指针，指针指向其他子节点
2、查询一个数据最高效的时间复杂度是O1，正常情况下解决二分查找的效率
3、每次查询都需要节点的数据也加载到内存中，并不是最高效的

1.3 B+树

  B+树是B树的优化，非叶子节点上不存储数据，只存储键值，所有数据均存储在叶子节点。所有的叶子节点是双向链表链接，叶子节点的内容是是通过单向链表链接如下图是B+树的简图

由于以上的特性，也决定了B+树相较于B树的优势：
1、查询数据时更加稳定
2、读写磁盘的代价更低
3、更便于区间查询和扫库

三、聚簇索引和非聚簇索引

聚簇索引: 将数据和索引放在一块存储，找到索引页就找到了数据，也就是索引结构叶子节点存储着行数据，聚簇索引有且只有一个
非聚簇索引： 将数据和索引分开存储，索引结构的节点节点没有存储数据只是存储了数据对应的主键，非聚簇索引可以存在多个
聚簇索引选取的原则：
1、优先选用主键作为聚簇索引，如果主键存在，主键索引就是聚簇索引
2、如果主键不存在，选用唯一索引作为聚簇索引
3、如果主键和唯一索引均不存在，innode会自动生成一个行id（rowid）作为隐型的聚簇索引
下面我们在结合几个图来理解下这两种索引，假设我们现在有这么一个表。id是自增主键

id	name	age
1	tom	30
2	jack	50
3	zhaosi	10
4	wangwu	40
5	lili	33
6	zhangsan	29
7	shensui	12
8	shenhu	19

上图是主键索引的简图，我们可以看到当我们通过id查询时，比如查询id=8的数据，查到索引id=8时，行数据在当前节点存储，索引相当于同时找到了对应的数据，这种就是聚簇索引

上面这个简图是用age左右索引的结构，因为叶子节点存的是对于的主键，索引不能直接查询到对应的数据。需要再通过主键索引查询才能查询到数据。
回表查询： 比如select name from info where age=50；这个sql在查询数据时，先通过非聚簇索引查询到对应的主键id，再通过聚簇索引查询到对应的行数据，这个过程称之为回表查询。
覆盖索引： 查询数据时使用了索引，且查询的数据能够再返回的数据中均能够找到，这种现象就是覆盖索引
还用上面简单的sql来说明白：
1、select id,age from info where id=2；
  根据上面聚簇索引的图，通过id直接可以查询到当前行数据，行数据中包含id和age索引，这种是覆盖索引
2、select id,name from info where age=12；
  根据上面非聚簇索引的图，通过age不能直接查询到id和name，需要再经过一次回表查询，才能查询到id和name，所以这种不是聚簇索引
3、select id,age from info where age=12；
  根据上面非聚簇索引的图，通过age直接查询到对应的主键id，age不需要再次查询，所以这种也是覆盖索引

四、索引的创建原则&sql优化

4.1索引的创建原则

 &emsp通过上面的几节，相信我们已经对索引有较好的理解了，那我们在创建索引时有什么原则呢？
这里我总结几点：
1、对于数据量较大且查询频繁的表，我们需要创建索引
2、对于查询where，分组group by ，排序order by频繁使用的字段创建索引
3、尽量创建联合索引，避免单列索引，联合索引可以达到覆盖索引避免回表查询从而提高查询的效率
4、同一个表索引不需要太多，并不是越多越好
5、在创建索引时尽量使用区别度高的字段，比如国家country字段可能上千万个数据多家都是相同的，这个字段就不便于作为索引，区别太低
6、如果较长的字段太长且必须作为索引，可以采用前缀索引，取字段开头的部分字符作为索引
7、如果部分字段不能存放null的数据，在建表时需要限制字段 not null

4.2sql优化

   我们按照上面的创建的索引有时候也会出现失效的情况，那具体什么原因会造成原因失效呢？我们结合下面这个表来看假如有这样一个表

一个主键索引，一个唯一索引
sq_name_status unique (name, age, status)
1、违反最左前缀原则
如果索引了多列，查询时需要满足最左前缀原则。查询时从最左侧开始不能跳过。

explain select  * from sql_learn where name='马云' and age=40;
explain select  * from sql_learn where age=10 and status=1;

如上sql1查询条件的字段顺利和索引顺序一致，能够正确命中索引，对比sql2，没有只用name，使用age和status查询违反了最左前缀原则，sql执行计划中可以看到走的是全表扫描


2、查询条件全匹配如“%desc%“、“%desc“
3、索引列不能使用运算
4、字符串不加引号，会造成类型转换，也会造成索引失效
5、区间查询的右侧，索引会失效
6、如果mysql使用全表扫描要比使用索引快,则不使用索引
7、如果条件中有or，即使其中有条件带索引也不会使用（这也就是为什么尽量少用or的原因）