MySql架构总结

影响MySql的性能的方面

硬件方面简单总结

（1）CPU：64位的cpu一定要工作在64位的操作系统下
（2）对于高并发，数量比频率重要，复杂性sql，频率比数量重要
（3）内存：选择主板所能使用的最高频率内存，内存尽可能大最好

操作系统对性能影响

（1）其他操作系统这里略过，只总结CentOS
（2）CentOS

（1）内核参数（/etc/sysctl.conf）,连接数、缓存大小、内存等参数，自己搜索看手册。
（2）增加资源限制（/etc/security/limit.conf）,如打开文件限制。
（3）磁盘调度策略

文件系统对性能的影响

（1）windows选择ntfs
（2）linux：EXT3/EXT4/XFS（性能最好）

存储引擎

MyISAM特性：

（1）表级锁，所以读取和插入是互斥的，对读写混合操作新能支持不好；
（2）表损坏修复，通过 check table table_name 检查表，通过 repair table table_name 进行修复；
（3）支持全文索引；
（4）支持表的压缩；

MyISAM使用场景：

（1）非事物应用；
（2）只读类应用；
（3）空间类应用，利用空间函数运算；

Innodb
系统表空间和独立表空间比较：

（1）系统表空间无法简单的收缩文件大小，独立表空间可以通过 optimize table 命令收缩系统文件；
（2）独立表空间可以同时向多个文件刷新数据；
（3）独立表空间是默认的配置，也推荐使用

innodb特性：

（1）事务性存储引擎；
（2）支持行级锁；
    什么是锁：锁的主要作用是（1）管理共享资源的并发访问；（2）锁用于实现事物的隔离性；锁分为共享锁（读锁）和独占锁（写锁），读锁和写锁都是行锁；了解阻塞和死锁。

innodb适用场景：

（1）5.7版本前除空间应用和全文索引外的应用；
（2）5.7版本后，innodb也支持全文索引和空间应用

其他引擎不做介绍，大部分场景都可以选择Innodb，除非万不得已，不要混合适用存储引擎。

MySQL服务器配置的影响

（1）内存相关配置

（1）确定可以使用内存上限，不要超过服务器限制；
（2）确定每个链接内存使用上限，sort_buffer_size/join_buffer_size/read_buffer_size/read_rnd_buffer_size，如果以上内存过大，可能会造成内存浪费；
（3）确定要为操作系统保留的内存，建议数据库使用单独的服务器；
（4）为缓存池分配足够内存，Innodb_buffer_pool_size，key_buffer_size;

（2）IO配置参数（此部分不常用到，需要时差文档）。
（3）安全配置相关参数

expire_logs_days 指定自动清理binglog的天数
max_allowed_packet MySQL可以接收的包的大小
skip_name_resolve 禁用DNS查找（一般启用）
sysdate_is_now sysdate()返回确定的日期
read_only 禁止非超级用户的读写权限
skip_slave_start 禁用slave自动恢复
sql_model SQL模式，此选项慎用，很可能导致现有程序报错

（4）数据库设计对性能的影响

（1）建立太多的列；一张表的列太多会降低性能
（2）造成太多的表关联；
（3）使用外键保证数据数据完整性；在数据修改时，mysql都要对数据进行检查，在备份、归档清理时也会产生问题，建议不使用外键约束；

数据库结构设计的影响

这个是最主要的影响，一言半语难以描述
（1）尽量满足数据库设计一二三范式要求

索引优化

索引的好处：
（1）大大减少要扫描的数据的数量。
（2）可以利用Btree索引进行排序，避免建立临时表。
（3）由于btree索引的键值是按顺序存放，可以把随机I/0变成顺序I/0，充分发挥磁盘性能。

索引不是越多越好：
（1）增加数据库写入成本。innodb为此引入了插入缓存，把多次插入合并成一次。
（2）增加查询优化器的选择时间。mysql查询优化器会根据查询信息及索引的条件进行合适的查询，如果对于同一个信息有很多个索引可以使用，就会增加mysql分析的时间。

B-tree索引方法和Hash索引方法

索引方法：B-tree索引，以B+树结构存储数据。

---

btree索引的好处：
（1）能够加快数据查询的速度

通常情况下，索引的大小远小于数据的大小，查找数据时通过根节点开始查找，根节点存储了下层节点的指针，存储引擎通过这些指针向下层查找，通过比较节点叶的值和要查找的值找到符合要求的节点。

（2）以顺序的方式存储，适合范围查找

---

什么情况下适合btree索引：
（1）全值匹配的查询。如 order_sn=’123456789’。
（2）匹配最左前缀的索引。比如 order_sn 和 order_data 组成了联合索引，依然可以使用 order_sn= ‘123456789’ 的方式进行查找，只要联合索引的第一列符合查找条件，就可以使用。
（3）匹配范围的查询。
（4）精确匹配左前列并范围匹配另外的列。

---

btree索引的限制
（1）不是按照索引最左列开始的
（2）不能跳过索引中的列。例如：a+b+c组成联合索引，如果查询b+c，那么只能查找b，因为跳过了a。
（3）ont in 和 <>操作无法使用索引
（4）如果查询中有某个列出现范围查询，那么右边所有列都无法使用索引

---

索引方法：hash索引。
基于hash表实现，只有查询条件精确匹配查询中的列时才可以使用。对于hash索引中的列，存储引擎会为其建立hash码，hash索引中存储的就是这些hash码。

---

hash索引的限制：
（1）必须进行2次查找。因为只保留了指针，先找到指针，然后对指针中的值进行读取。
（2）按照hash码进行存储，无法进行排序
（3）不能进行部分查找和范围查找
（4）可能存在hash码的重复，不能用于如性别这样的列，可以用于如身份证这样的列。

索引优化策略

（1）索引列上不能使用表达式或者函数，比如操作日期时：

（2）前缀索引和索引列的选择性
可以使用如下语法创建前缀索引:

CREATE INDEX index_name ON table(col_name(n));

列的唯一性越高，选择性越高，所以唯一索引和主键所以比较适合。由于前缀索引只选择列的部分，所以有可能会重复，前缀字节数的选择，让其尽可能的不重复。

（3）联合索引

（1）经常被使用的列放到左边。
（2）选择性高的列优先，比如性别这样的列，即便创建了索引，mysql也不一定会使用，mysql可能觉得直接扫描的代价比使用索引查找要小。
（3）宽度小的列优先。

（4）覆盖索引
我们可以通过btree索引直接获得我们需要的数据，btree的叶子节点上，存储了关键字的值，如果查询列（所有列）都被索引覆盖，那么就不在需要查询数据库了。
一般而言，索引的列远小于表的列，如果能通过覆盖索引获取到数据的值，也会大大提高性能。

（1）优化缓存，减少磁盘IO操作
（2）btree是按照键值存储的，可以变随机IO为顺序IO
（3）避免对Innodb主键索引的二次查询
（4）避免MyISAM表进行系统调用

（5）使用索引来优化查询

（1）索引的列顺序和order by 的字句要一致
（2）索引中列升序或者降序，要和order by的字句要一致
（3）order by中的字段全部在关联表中的第一张表中

（6）查找未被使用的索引
通过如下的sql语句

查询优化

MySql处理查询的一次过程可以简单这样理解：
（1）客户端发送SQL请求给服务器
（2）服务器检查是否可以在查询缓存中命中该SQL
（3）服务器进行SQL解析，预处理，再由优化器生成对应的执行计划
（4）根据执行计划，调用存储引擎API来查询数据
（5）讲结果返回客户端
如果查询缓存是打开的，会优先对缓存中的SQL进行查询，这个缓存查询是通过一个队大小写敏感的hash查找来实现的，hash查找只能进行全值匹配实现的，所以即便有一个字节不相同，也不能命中缓存中的查询。命中缓存中的查询是不太容易的，对一个读写频繁的系统，不建议开启缓存查询。系统缓存通过 query_cache 开头的参数设置：

query_cache_type 设置缓存是否可用
query_cache_size 缓存内存的大小
query_cache_limit 缓存可用存储的最大值
query_cache_wlock_invalidate 设置表被锁后是否返回缓存中的数据

获取有问题SQL的常用方法：
（1）通过用户反馈或者测试反馈
（2）通过慢查询日志获取有问题SQL
（3）实时获取有问题SQL

使用慢查询日志

配置参数说明：
slow_query_log 启动/停止 慢查询日志参数。
slow_query_log_file 慢查询日志存储路径及文件，建议日志和数据分开。
long_query_time 慢查询的SQL的伐值，默认值为10秒。
long_querys_not_using_indexs 是否记录未使用索引的查询。

通过工具分析慢查询日志，以获取日志信息。如 pt_query_digest（如何安装找教程）
执行命令示例

pt_query_digest --explain h=127.0.0.1,u=root,p=your_pwd log_name.log >slow.rep

实时获取问题SQL

最方便的方法是在查看 infomation_schema 库中 PROCESSLIST 表。

获取查询各个阶段消耗的时间

（1）可以在SQL命令中使用 profile

set profiling=1;（这个是一个session级的设置，在sql命令中输入）
执行查询
show profiles； （查看每个查询所消耗的总时间的信息）
show profile for query N; （查询的每个阶段所消耗的时间）

（2）配置文件中配置 performance_schema
先要执行MySQL监控，执行如下sql语句：

这个是对数据库全局有效的
通过sql语句查看各个线程所消耗的时间，在 performance_schema表中保存所有记录。

特定SQL优化

（1）对于大表的数据修改最好进行分批处理。比如要在1000万行记录中跟新100万行记录，最好每次更新5000行，然后暂停几秒，可以使用存储过程或者再程序中实现。
（2）对大表结构的修改相对复杂。先建立新表，把老表的数据复制到新表，然后在老表上建立触发器，同步跟新新表的数据，再系统维护时间把老表删除，重命名新表。
（3）not in 和 <> 最好改写成关联查询

（4）使用汇总表优化查询。所谓汇总表就是把需要的信息提前汇总，存储到表中。

分库分表

不到万不得已不要做这个操作。我没有做过，以后补充。