MySQL数据库基础--性能分析，使用规则

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过 show [session|global] 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT,UPDATE,DELETE,SELECT的访问频次。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志。

慢查询日志默认是没有开启的，需要在MySQL的配置文件（etc/my.cnf）中配置如下信息。

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-show.log 。

profile

        show profiles 能够在做SQL优化时帮助我们了解时间都耗费在哪里去了。通过have_profiling 参数，能够看到当期MySQL是否支持profile操作：

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：

执行一系列的SQL的业务的操作，然后通过如下指令查看指令的执行耗时。

explain

        EXPLAIN 或者 DESC 命令获取MySQL如何执行SELECT 语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

语法：

使用规则

最左前缀法则

        入果索引了多列（联合索引），要遵守最左前缀法则，指的是查询从索引的最左列开始，并且不跳过所以中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

范围查询

        联合索引中，出现范围查询（<,>）,范围查询右侧的列索引失效。

索引列运算

        不要在索引列上进行运算操作，索引将失效。

字符串不加引号

        字符串类型字段使用时，不加引号，索引将失效。

模糊查询

        如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引将实效。

or连接的条件

        用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

        由于 age 没有索引，所以即使 id，phone 有索引，索引也会失效，所以需要针对于 age 也要建立索引。

数据分布影响

        如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL提示

        SQL 提示是优化数据库的重要手段，简单来说，就是在 SQL 语句中，加入一些人为的提示来达到优化操作的目的。

覆盖索引

前缀索引

        当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘 IO，影响查询 效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

单列索引和联合索引

        单列索引：即一个索引只包含单个列。

        联合索引：即一个索引包含了多个列。

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引。

单列索引情况：

多条件联合查询时，MySQL 优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

联合索引情况：

索引设计原则

        1.针对于数据量较大，且查询比较频繁的表建立索引。

        2.针对于常作为查询条件（where），排序（order by），分组（group by）操作的字段建立的索引。

        3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

        4.如果是字符串类型的字段，，字段的长度较长，可以针对于字段的特点，建立前缀索引。

        5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

        6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

        7.如果索引列不能存储 null 值，请在创建表时使用 not null 约束他，当优化器知道每列是否包含 null 值时，他可以更好的确定哪个索引最有效的用于查询。

索引总结

        索引概述：索引是高效获取数据的数据结构。

        索引结构：B+Tree ，Hash。

        索引分类：主键索引，唯一索引，常规索引，全文索引，聚集索引，二级索引。

        索引语法：create [unique] index xxx on xxx(xxx);

                          show index from xxxx;

                          drop index xxx on xxx;

        sql 性能分析：执行频次，慢查询日志，profile，edxplain。

        索引使用：联合索引，索引失效，sql 提示，覆盖索引，前缀索引，单利/联合索引。

        索引设计原则：表，字段，索引。

Ending： 

        OK，本篇文章就到此结束了，非常感谢你能看到这里，所以如果你觉得这篇文章对你有帮助的话，请点一个大大的赞，支持一下博主，若你觉得有什么问题或疑问，欢迎私信博主或在评论区指出~