mysql数据库调优总结(一)

关于mysql调优以前也做过一些总结，但是总是不全面，不能合理的调优；网上也有很多关于mysql调优的文章，今天作为回顾再次整理一下。我使用的是mysql5.7版本。闲话少说今天我们从一下几个方面来进行总结：
第一、性能监控，必须要有监控，没有监控是无法进行有效的调优的；
1、通过使用show profile查询工具，用来分析当前会话中sql语句执行时资源消耗情况，可以通过type指定；默认关闭状态，并保存最近15次的运行结果。可以通过
show variables like 'profiling'来查看状态，通过set profiling = 1; 命令来开启，如下图：


show profile工具，可以通过type指定(具体参数请看官网参数 SHOW PROFILE Statement)

以后的版本中将会移除show profiles

2、使用performance schema来更加容易的监控mysql，监控会消耗一些系统资源


这些表主要记录运行时元数据信息（一共87张表），不会进行持久化，存在内存中。
performance_schema中的事件与写入二进制日志中的事件（描述数据修改的events）、事件计划调度程序（这是一种存储程序）的事件
不同。 performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行的次数等情况。
3、使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征

id表示session id
user表示操作用户
host表示操作主机
db表示操作数据库
command 表示当前状态：sleep线程正在等待客户端发送新的请求、query线程正在执行查询或正在将结果发送给客户端、locked在mysql的服务层，该线程正在等待表锁、analyzing and statistic线程正在收集存储引擎的统计信息，并生成查询的执行计划、copying to tmp table线程正在执行查询；并且将结果都复制到一个临时表中、sorting result线程正在对结果进行排序、sending data线程可能在多个状态之间传送数据，或者向客户端返回数据。
info表示sql详情
time表示命令执行的时间
state表示命令执行的状态
第二、schema与数据库类型的优化；
1、数据类型的优化：更小的通常更好，应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内 存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围 的最小类型；简单就好，简单数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价更低，因为字符集和校对规则是字符比较比整 型比较更复杂；使用mysql自建类型而不是字符串来存储日期和时间；用整型存储IP地址。案例：创建两张相同的表，改变日期的数据类型，查看SQL 语句执行的速度。尽量避免null，如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加 复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可 为null的列。具体数据类型，整数类型：可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24， 32，64位存储空间。尽量使用满足需求的最小数据类型。字符和字符串类型：char长度固定，即每条数据占用等长字节空间；最大长度是255个字 符，适合用在身份证号、手机号等定长字符串；varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性；text不 设置长度，当不知道属性的最大长度时，适合用text；按照查询速度：char>varchar>text。如下图：


BLOB和TEXT类型：MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字 符方式存储。日期时间类型：datetime和timestamp，如下图：

使用枚举代替字符串类型：有时可以使用枚举类代替常用的字符串类型，mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节 中，mysql在内部会将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的查找表。
IP地址使用整型存储：select INET_ATON(‘IP’)转换为整型，select INET_NTOA(‘整型数’)转换为ip。
2、合理使用范式、反范式

3、主键的选择：代理主键（推荐使用）与业务无关无意义的数字序列，不与业务耦合，更容易维护、自然主键，业务中唯一自然标识。
4、字符集选择：如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。MySQL 的数据类型可以精确到字段，所以当我们需要大型数据库中存放多字节数据的时候，可以通过对不同表不同字段使用不同的数据类型来较大程度减小数 据存储量，进而降低 IO 操作次数并提高缓存命中率。
5、存储引擎的选择：innodb、myisam

6、适当的数据冗余：被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。这样的场景由于每次Join仅仅只是为了取得某个小 字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭 到破坏，确保更新的同时冗余字段也被更新。
7、适当拆分：当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我 们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以 大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。
第三、执行计划；
为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。可以使用explain+SQL语句来模拟优化器执行SQL查询 语句，从而知道mysql是如何处理sql语句的。官网地址

第四、通过索引进行优化。
1、索引基本知识：
索引的优点：大大的减少了服务器需要扫描的数据量；帮助服务器避免排序和临时表；将随机io变成顺序io
索引的用处：快速查询匹配WHERE子句的行；从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引；
如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行；当有表连接的时候，从其他表检索行数据；查找特定索引列的min或max 值；如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组；在某些情况下，可以优化查询以检索值而无需查询数据行
索引的分类：主键索引、唯一索引、普通索引、全文索引、组合索引
技术名词：回表、覆盖索引、最左匹配、索引下推
索引采用的数据结构：哈希表、B+Tree
索引匹配方式：请看下图：

2、哈希索引：基于哈希表的实现，只有精确匹配索引所有列的查询才有效；在mysql中，只有memory的存储引擎显式支持哈希索引；哈希索引自身只需 存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快；

3、组合索引：当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要
4、聚簇索引与非聚簇索引：聚簇索引，不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。非聚簇索引：数 据文件和索引文件分开存放
5、覆盖索引：基本介绍，如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引；不是所有类型的索引都可以称为覆盖索引，覆盖索引必须 要存储索引列的值；不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引。优势：索引条目通常远小于 数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量；因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随 机从磁盘读取每一行数据的IO要少的多；一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系 统调用，这可能会导致严重的性能问题；由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用。
6、优化小细节：当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层；尽量使用主键查询，而不是其他索引，因此主 键查询不会触发回表查询；使用前缀索引；使用索引扫描排序；union all,in,or都能够使用索引，但是推荐使用in；范围列可以用到索引，范围条件是： <、<=、>、>=、between，范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列；强制类型转换会全表扫描explain select * from user where phone=13800001234;不会触发索引；explain select * from user where phone='13800001234';触发索引；更新十分频 繁，数据区分度不高的字段上不宜建立索引（更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能，类似于性别这类区分不大的属性， 建立索引是没有意义的，不能有效的过滤数据，一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计 算）；创建索引的列，不允许为null，可能会得到不符合预期的结果；当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类 型必须一致；能使用limit的时候尽量使用limit；单表索引建议控制在5个以内；单索引字段数不允许超过5个（组合索引）；创建索引的时候应该避免 以下错误概念（索引越多越好、过早优化，在不了解系统的情况下进行优化）。
7、索引监控：show status like 'Handler_read%';

五、六、七、八章后续抽时间补充，作为第二节更新出来