MySQL高级部分知识整理

一、索引介绍：

1.什么是索引？

        官方的解释是这样的：索引(Index)是帮助MySQL高效获取数据的数据结构。由这句话我们可以得到索引的本质：索引就是数据结构。那么，在我的理解看来，索引最好的解释是这样“索引是排好序的，可以快速查找数据结构”。怎么理解这句话呢？除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构即使索引。

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

2.使用索引有什么好处，又有什么坏处呢？

         好处：由第一个问题，我们可以知道，索引可以提高数据检索的效率，降低数据库IO成本；通过索引对数据进行了排序，降低了数据排序的成本，降低了CPU的消耗。

         坏处：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记库，所以索引列也是要占用空间的；每次对表的记录进行写操作时，不仅要修改表的内容，也要修改索引的内容，因此会降低更新表的速度。

3.索引有哪些类型？

•          单值索引      ——即一个索引只包含单个列，一个表可以有多个单列索引。
•          唯一索引      ——索引列的值必须唯一，但允许有空值。
•          符合索引      ——即一个索引包含多个列

4.索引怎么用?

#创建索引
CREATE [UNIQUE] INDEX indexName ON tableName(columnName);
ALTER tableName ADD [UNIQUE] INDEX ON(columnName);

#删除索引
DROP INDEX indexName ON tableName;

#查看某个表的索引
SHOW INDEX FROM tableName\G;

5.哪些情况下需要创建索引？哪些情况下不需要创建索引？

需要创建索引的情况：

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 查询中排序的字段，排序的字段若通过索引去访问将大大提高排序的速度
5. 查询中统计或者分组的字段

不需要创建索引的情况：

1. 频繁更新的字段不适合创建索引
2. 表记录太少
3. 数据重复且分布平均的表字段

二、索引性能分析

首先，我先列举一下MySQL常见的瓶颈：

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈

那么，在MySQL中，我们应该怎样来分析SQL语句以及索引的执行呢？

       使用EXPLAIN关键字：可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句。分析你的查询语句或是表结构的性能瓶颈。

案例一：     

                                                        

案例二：  

                                                       

接下来，我给大家来介绍这个表格里这些关键字的具体含义：

1. id:这个属性表示查询中select子句或操作表的顺序。分为三种情况：

①id相同：执行顺序从上到下；

②id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先执行；

③id有相同，也有不同：执行的规则结合①②。

2. select_type:这个字段表示查询的类型是什么，常见有如下几个值：

①SIMPLE 简单的select查询，查询中不包含子查询或者UNION；

②PRIMARY 一条SQL语句中包含了复杂的查询子部分，最外层查询被标记为PRIMARY查询

③SUBQUERY 在select或where列表中包含了的子查询会被标记为SUBQUERY

④DERIVED 在from列表中包含的子查询会被标记为DERIVED(衍生)

⑤UNION 若第二个select出现在UNION之后，则被标记为UNION

⑥UNION RESULT 从UNION表获取结果的select查询会被标记为UNION RESULT

 3.table:explain显示的表格中每条记录是关于哪一张表的

 4.type:显示每个查询使用了何种类型：（下面列举的顺序是从最好到最差依次排列的）

①system   表只有一条记录（等于系统表），这是const类型的特例，平时不会出现。

②const    表示通过索引依次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快。（比如将主键置于where列表中，MySQL就可以将该查询转换为一个常量。）

③eq_ref   唯一性索引扫描，对于每一个索引键，表中只有一条记录与其匹配。常见于主键或者唯一索引扫描。

④ref   非唯一性扫描，返回匹配某个单独值的所有行。本质上来说其也是一种索引访问其所有匹配某个单独值的行，然而，它可能会找个多个符合条件的行，所以其属于查找和扫描的混合体。

⑤range   只检索给定范围的行，使用一个索引来选择行，一般出现在between，in，<,>等查询中。

⑥index   index和all的区别为index类型只遍历索引树，这通常比all快。

⑦all   将遍历全表。
5.possible_keys: 显示可能应用到的在这张表中的索引，一个或者多个。
6.key: 实际使用的索引，如果没有则为NULL。
7.key_len: 表示索引使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
8.ref:  哪些列或者常量被用于查询索引列上的值。如果可能的话，是一个常数。
9.rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
10.Extra： 包含不适合在其他列中显示但十分重要的额外信息：
          ①Using filesort    说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。
          ②Using temporary    使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by和group by。
          ③Using index      表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错。如果同时出现了Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
          ④Using where     表示使用了where过滤。
          ⑤Using join buffer     表示使用了连接缓存。
          ⑥impossible where     where子句的值总是false，不能用来获取任何的元组。
          ⑦distinct     优化distinct操作，在找到第一个匹配的元组后停止找同样值。
          说明：最好不要出现using filesort和using temporary

三、索引优化

  这里，我不再上案例一一进行分析了，直接上硬货：

1. 单表索引建立时，索引列最好是where筛选中出现的列名。
2. 两表连接时，例如左连接，则对右表创建on后面列名的索引。

       证明： class和book做左连接，如果给class创建索引：

                      

       class和book做左连接，如果给book创建索引：

                    

从rows和Extra这两个参数的值哦我们就可以得出结论：对后表建立索引，其效率更高。

3.三表连接时，假设(A left join B on ....  left join   C on.... )，此时只需要给B和C创建对应列的索引。

 

-------为了我们所建立的索引可以被成功使用，我们也必须知道在哪些情况下，索引会失效：

• 在索引列上进行计算、使用函数、自动或者手动类型转换。
• 存储引擎不能使用索引中范围条件右边的列。（select * from emp where id >2 and age =12）如果对id和age建立了复合索引，则这个索引中只age这个列会失效。
• 在使用不等于(!=，<>)时，无法使用索引。
• sql语句出现is null,is not null 也无法使用索引。
• like以通配符开头('%ab'),索引会失效
• 字符串不加单引号索引也会失效
• 使用or，索引也会失效

-------通过上面的内容我们大概也了解了索引失效的基本情况，接下来我来给大家一些好的建议：

• 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 尽量使用覆盖索引（值访问索引的查询--索引列和查询列一致），减少select *
• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引时，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合所以时，尽量选择可以包含当前query中的where子句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法老达到选择合适索引的目的
• 使用小表驱动大表的原则，即小的数据集驱动大的数据集（IO的消耗的资源少）

对于ORDER BY 关键字的优化：

 ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序，尽可能在索引列上完成排序操作，遵循索引建立时的最佳左前缀原则。

        -----ORDER BY 满足两种情况，会使用Index方式排序：①ORDER BY 语句使用索引最左前列。

                                                                                                ②使用WHERE子句和ORDER BY 子句条件列组合满足索引最左前列的原则。

        ------如果没有使用index方式排序，mysql会启动双路排序和单路排序，此时，我们可以通过设置并提高服务器的sort_buffer_size 以及max_length_for_sort_data参数的值来提高排序效率。

对于GROUP BY 关键字的优化：

   GROUP BY实质是先排序后分组的，优化时可以遵照索引建立的最佳左前缀原则。

   当无法使用索引列，也可以提高服务器的sort_buffer_size 以及max_length_for_sort_data参数的值来提高排序和分组效率。

    where执行的优先级比having高，能在where中写的限定条件最好不要在having中写。

 四、慢查询日志

在MySQL中，提供了一种慢查询日志功能。它的本质是一种日志记录，用来记录在MySQL中响应时间超过阙值的语句，具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中。

long_query_time的默认值为10s。我们可以通过该日志的记录，结合之前的EXPLAIN进行全面分析。

      1.怎么开启慢查询日志功能呢？

          MySQL默认关闭该功能：

                    

            show variables like '%slow_query_log%';指令可以查看是否开启了慢查询日志功能是否开启。slow_query_log_file 属性表示我们的慢查询日志文件存放的位置。

            set global slow_query_log=1;  该指令可以开启慢查询日志功能。

            show variables like '%long_query_time%'; 该指令用来查看设置的阙值。

                                 

             默认long_query_time的值为10秒。

             set global long_query_time=3; 该指令可以自定义慢sql的时间。该指令执行成功以后，需要重新开启一个会话才能看到修改的值。

     2.slow_query_log_file里面的记录是怎么的呢？

        进入到slow_query_log_file文件，内容如下：

                                                          

          红色标记的就是慢sql（执行时间超过了我们设置的阙值3秒）；

      3.使用日志分析工具mysqldumpslow来分析我们的慢查询日志文件：

                                                                 

            使用上述参数来筛选出我们想要查找的慢SQL：

                                                      

五、使用SHOW Profile来进行SQL调优

           show profile是mysql提供的可以用来分析当前会话中语句执行的资源消耗的情况，可以用来SQL调优的测量。默认情况下，参数除于关闭状态，并保存最近的15次运行结果。

1.怎么开启？

      show variables like 'profiling';  查看当前 show profile服务是否开启。

                              

      set profiling =on;来开启show profile服务。

2.怎么查看sql的执行情况？

     先执行几条SQL语句，然后通过show profiles;指令来查看之前执行的sql语句：

                               

     通过show profile cpu,block io for query [Query_ID]来查看具体的某一条sql语句：

                      

  注意，show profile功能和慢查询日志功能在我们再一次重启mysql服务时，是默认关闭的，要是想长期开启，可以在mysql的启动配置文件中设置参数。

六、MySQL锁机制

1.在mysql中，从对数据操作的类型（读\写）分：分为读锁和写锁。

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行并且不会相互影响。

写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

2.从对数据操作的范围分：分为表锁和行锁。

表锁：偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定范围大，发生锁冲突的概率小，但是并发度低。（偏读）

行锁：偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定的范围小，发生锁冲突的概率更低，并发度高。（偏写）

在linux环境中，我通过两个session来模拟演示锁的功能：

-----对于MyISAM引擎：

                    MyISAM引擎在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

   1.手动增加读锁：

           

             ①由上图可以得出，当session1加了读锁以后，读操作可以共享；

       

         ②由上图可以得出：当session1加了读锁以后，其他的会话不可以对锁定的表进行写操作。（其他session会一直等待锁释放）

                                            

          ③由上图可以得出：当session1加了读锁以后，不能查询其他没有锁定的表，而其他的session可以查询或者更新其他未锁定的表。

                                          

          ④由上图可以得出：当session1加了读锁以后，不可以插入或者更改被锁定的表和其他未锁定的表。

2.手动添加写锁：

                                                      

           ①由上图可以得出：当session1加了写锁以后，可以查询被锁定的表，也可以对锁定的表做写操作，但是不能读和写其他未锁定的表。

                                        

           ②由上图可以得出：当session1加了写锁以后，其他session对被锁定表的查询被阻塞，需要等待锁被释放。

由以上的分析我们可以得出：

        1.对MyISAM表的读操作（加读锁），不会阻塞其他会话对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他会话的写操作。

        2.对MyISAM表的写操作（加写锁），会阻塞其他会话对同一表的读和写操作，只有当写锁释放后，才会执行其他会话的读写操作。

            简而言之：读锁会阻塞写，但不会阻塞读；而写锁则会把读和写都阻塞。

如何分析表锁？

      通过show open tables;   可以看哪些表被锁了。

      通过show status like 'table%'；指令来查看mysql内部标记锁定的情况：

                                                 

      Table_locks_immediate:表示产生表级锁定的次数。

      Table_locks_waited:出现标机锁定争用而发生等待的次数。

   此外：MyISAM的读写调度是写优先，这也是其不适合做写操作的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

   

-----对于InnoDB引擎：

                InnoDB引擎支持事务，且默认是自动提交事务的。则我们在模拟时，需要关闭自动提交事务。

                                                     

演示：

                                

               ①由上图可以发现，当session1的事务未提交时，session2查询出来的值并没有显示session1修改后的值。

            

            ②由上图可以发现，当session1的事务未提交时，session2无法对session2所修改的行进行修改。

         

         ③由上图可以发现，当session1的事务未提交时，session2可以对其他行进行修改。

                

          ④当索引失效时，行锁会转换为表锁。（这里故意将更新语句中的b字段写成int类型，但是mysql优化器会自动将其转化为varchar类型，所以这里索引会失效）。

----间隙锁的危害

当我们用范围条件，而不是相当条件检索数据时，并请求共享或排它锁时，InnoDB会给符合条件的自由数据记录的索引项加锁；对于简直在条件范围内但并不存在的记录，叫做“间隙”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

危害：当锁定一个范围值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的危害。

              

       此时，session1会对1<a<6的行进行加锁，就算表中没有a=2的记录，也会加锁，所以此时session2就无法对a=2的记录进行操作。

--如何锁定一行？

     使用begin标记开始。

     使用select ... from ...  where ...   for update;来锁定某一行

     执行完所需的操作后，commit提交，就会解锁改行。

                       

                                    当我们对某一行锁定后，其他会话的写操作会被阻塞，直到锁定行的会话commit后，则其他操作才会执行。

行锁总结：

       Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会由比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，其整体性能不仅不能与MyISAM高，甚至可能会更差。

行锁的分析： show status like 'innodb_row_lock%';

                                                     

通过该指令，我们可以分析出行锁的相关参数时间，比如锁定的总时间，总等待次数等，从而对mysql进行一些优化。

综上我们可以得出一些优化建议：

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 合理涉及索引，尽量减小锁的范围
• 尽量控制事务的大小，减少锁定资源量和时间长度
• 尽可能较少检索条件，避免间隙锁