高性能MySql--02（SQL的优化思路索引和锁）

1.SQL的优化思路

1.选择最有效率的表名顺序

   数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理

   在FROM子句中包含多个表的情况下：

• 如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推

• 也就是说：选择记录条数最少的表放在最后

  如果有3个以上的表连接查询：

• 如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。

• 也就是说：被其他表所引用的表放在最后

2.WHERE条件的顺序

    数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

3.SELECT子句中避免使用*号

  我们当时学习的时候，“*”号是可以获取表中全部的字段数据的。但是它要通过查询数据字典完成的，这意味着将耗费更多的时间

4.删除表的全部记录，除了表结构就用Truncate代替Delete

   DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快

5.多使用内部函数提高SQL效率

  例如使用mysql的concat()函数会比使用||来进行拼接快，因为concat()函数已经被mysql优化过了。

6.使用表或列的别名

    如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。。。

7.善用索引

    索引就是为了提高我们的查询数据的，当表的记录量非常大的时候，我们就可以使用索引了。

8.SQL写大写

   我们在编写SQL    的时候，官方推荐的是使用大写来写关键字，因为Oracle服务器总是先将小写字母转成大写后，才执行

9.避免在索引列上使用NOT

   因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

10.避免在索引列上使用计算

   WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢

11.用 >= 替代 >

      低效：
      SELECT * FROM EMP WHERE DEPTNO > 3   
      首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
      高效：
      SELECT * FROM EMP WHERE DEPTNO >= 4  
      直接跳到第一个DEPT等于4的记录

12. 用IN替代OR

      select * from emp where sal = 1500 or sal = 3000 or sal = 800;
      select * from emp where sal in (1500,3000,800);

13.总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。 当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引

      create index emp_sal_job_idex
      on emp(sal,job);
      ----------------------------------
      select *
      from emp  
      where job != 'SALES';    


      上边就不使用索引了。

 2. 索引的基础知识

   首先Mysql的基本存储结构是页(记录都存在页里边),

           

    

 

• 各个数据页可以组成一个双向链表

• 而每个数据页中的记录又可以组成一个单向链表

  • 每个数据页都会为存储在它里边儿的记录生成一个页目录（记录他的数据的主键的最小值和数据的页号。），在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

  • 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写 select*fromuserwhereusername='Java3y'这样没有进行任何优化的sql语句，默认会这样做：

• 定位到记录所在的页

  • 需要遍历双向链表，找到所在的页

• 从所在的页内中查找相应的记录

  • 由于不是根据主键查询，只能遍历所在页的单链表了

           

  页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储，比如把他们放到一个数组里，就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为20的记录，具体查找过程分两步：

• 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 < 20 < 209），它对应的页是页9。
• 再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

至此，针对数据页做的简易目录就搞定了。不过忘了说了，这个目录有一个别名，称为索引。

   聚集索引和非聚集索引的区别：

• 聚集索引就是以主键创建的索引

• 非聚集索引就是以非主键创建的索引

 区别：  聚集索引在叶子节点存储的是表中的数据

             非聚集索引在叶子节点存储的是主键和索引列

             使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)
               

 非聚集索引也叫做二级索引，不用纠结那么多名词，将其等价就行了~非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。创建多个单列(非聚集)索引的时候，会生成多个索引树(所以过多创建索引会占用磁盘空间)

 覆盖索引：我们前面知道了，如果不是聚集索引，叶子节点存储的是主键+列值，最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！比如：现在我创建了索引 (username,age)，在查询数据的时候： selectusername,agefromuserwhereusername='Java3y'andage=20。很明显地知道，我们上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了~，所以要尽量使用覆盖索引。

 3.索引最左匹配原则

  

• 索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。

• 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。

• 因此，列的排列顺序决定了可命中索引的列数。

explain select * from test where b<10 and c <10;、

explain select * from test where a<10 and c <10;

为什么 b<10 and c <10,没有用到索引？而 a<10 and c <10用到了？

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

索引总结

• 1，最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询 （>,<,BETWEEN,LIKE）就停止匹配。

• 3，尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

• 4，索引列不能参与计算，尽量保持列“干净”。比如， FROM_UNIXTIME(create_time)='2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time=UNIX_TIMESTAMP('2016-06-06')。

• 5，尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

• 6，单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，~~MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引~~(经指正，在MySQL5.0以后的版本中，有“合并索引”的策略，翻看了《高性能MySQL 第三版》，书作者认为：还是应该建立起比较好的索引，而不应该依赖于“合并索引”这么一个策略)。

• “合并索引”策略简单来讲，就是使用多个单列索引，然后将这些结果用“union或者and”来合并起来

 4. 锁

          

 

  学习数据库锁知识就是为了:

• 能让我们在特定的场景下派得上用场

• 更好把控自己写的程序

• 在跟别人聊数据库技术的时候可以搭上几句话

• 构建自己的知识库体系！在面试的时候不虚