MySQL优化基本常识之1-你忽视的基础

由于压力测试，每个大场景都有3个不同并发级别的小场景，但是在分析AWR报告时发现，其中SQL执行次数部分并没有明显的变化，100并发SQL执行次数30000，200并发SQL执行次数30000多，根据以往的压测经验来看，这肯定是有问题的，但是问题在哪呢？作为测试多年的小明无从着手。请DBA看吧，看他那个嫌弃和厌烦的表情就知道要吃闭门羹。

我们自己不能定位么？现在就开始了解下，需要学习那些，锁，索引，算法定位 优化等。先从基础开始

基本概念篇

通常我们在性能测试中，60%的性能瓶颈存在于数据环节，数据环节的一半性能问题又是在SQL。

一、为什么使用数据索引能提高效率？

    1、数据索引的存储是有序的

    2、在有序的情况下，通过索引查询一个数据是无需遍历索引记录的

    3、极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

二、关于 MySQL 联合索引

    1、联合索引是两个或更多个列上的索引。

对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

    2、利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。

如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不知道姓，电话簿将没有用处。

那么什么情况下不建或少建索引？

    1、表记录太少

    2、行为表 -如订单表

数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

三、key和index的区别

    1、key 是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等

    2、index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；

四、B+树索引和哈希索引的区别？

    B+树索引

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的，如下图：

    哈希索引

就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的

如下图所示：

 

二、哪个更好呢？

    等值查询

哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引，例如这种SQL：

# 仅等值查询

select id, name from table where name='李明';

如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

    通常情况下

B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

InnoDB 引擎中默认使用的是B+树索引，

    特别注意：

在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。