表设计及其他优化

（1）在创建业务表时，库名、表名、字段名必须使用小写字母，采用“_”分割。

（2）MYSQL数据库中，通过lower_case_table_names参数来区分表名的大小写，默认为0，代表大小写敏感。如果是1，代码大小写不敏感，以小写存储。为字段选取数据类型时，要秉承着简单、够用的原则。表中的字段和索引数量都不宜过多，要保证SQL语句查询的高效性，快速执行完，避免出现堵塞、排队现象。

（3）表的存储引擎一定要选择使用innodb,之前的章节中已经反复强调过了innodb和myisam的区别。mysql5.7基本已经废弃myisam，从8.0版本开始，系统表也彻底与myisam告别了。

（4）要显示地为表创建一个使用自增列INT或者BIGINI类型作为主键，可以保证写入顺序是自增的，和B+tree叶子节点分裂顺序一致。写入更加高效，TPS性能会更高，存储效率也是最高的。

（5）金钱、日期时间、IPV4尽量使用int来存储。用int来存储金钱，让int单位为分，这样就不存在四舍五入了，存储的数值更精确。

　　日期时间可以选择使用datetime,datetime的可用范围比timestamp大，物理存储上仅比timestamp多占1个字节的空间，整体性能上的消耗并不算太大。因此在生产环境可以使用datetime时间类型。当然也可以使用int来存储时间，通过转换函数from_unixtime和unix_timestamp来实现。

 

IPv4字段基本上可以不使用char(15)来存储，使用int来存储，通过转换函数inet_aton和inet_ntoa来实现。

 

有些字段一看就知道该选择什么数据类型进行存储，比如性别sex字段、状态status字段，基本上选择tinyint就可以了。

（6）text和blob这种存大量文字或者存图片的大数据类型，建议不要与业务表放在一起。

注：主要业务表切忌出现这样大类型的字段。

SQL语句中尽量避免出现or子句，这种判断的子句可以让程序自行完成，不要交给数据库判断。也要避免使用union,尽量采用union all,减少去重和排序的工作。

（7）用select查询表时只需要获取必要的字段，避免使用select *。这样可以减少网络带宽消耗，还有可能利用到覆盖索引。

建立索引时不要在选择性低的字段上创建，比如sex、status这种字段。

 

索引的选择性计算方法：

select count(distinct coll)/count(*)  from table_name;

越接近1，证明选择性越高，越适合创建索引。

（8）很长的字符串列可以考虑创建前缀索引，提高索引利用率。

单表索引数量不要太多，一般建议不要超过4~5个（根据实际业务表再确定）。当执行DML语句操作时，也会对索引进行更新，如果索引数量太多，则会造成索引树的分裂，性能也会下降。

（9）所有字段定义中，默认都加上not null约束，避免出现null。在对该字段进行select count()统计计数时，可以让统计结果更准确，因为值为null的数据不会被计算进去。

（10）表的字符集默认使用UTF8,必要时可申请使用UTF8mb4字符集。因为它的通用性比GBK、Latin1都要好。UTF8字符集存储汉字占用3个字节，如果遇到表情存储的需求，就可以使用UTF8mb4

（11）建议模糊查询select ....like '%**%'的语句不要出现在数据库中，可以使用搜索引擎sphinx代替。

（12）索引字段上面不要使用函数，否则使用不到索引，也不要创建函数索引。

（13）join列类型要保持一致，其中包括长度、字符集都要一致。

（14）当在执行计划中的extra项看到Using filesort,或者看到Using temporary时，也要优先考虑创建排序索引和分组索引。

注：排序、分组字段上都需要创建索引。

（15）limit语句上的优化，建议使用主键来进行范围索引，缩短结果集大小，使查询更搞笑。

（16）通常情况下，可以使用Mysql自带的工具mysqldumpslow或者最常使用的第三方工具软件pt-query-digest来捕获线上的影响业务的慢查询SQL语句。

（17）还可以使用MySQL提供的可以用来分析当前会话中语句执行资源消耗情况的命令show  profile。

　　首先查看profile参数（默认是关闭的）：

 

 　　我们需要开启该参数，执行set profiling=1(开启)。

（18）在MYSQL数据库中，可以通过使用show global status 命令来查看数据库的运行状态，通过得出的数值优化MYSQL运行效率。

　　接下来细说一下show global status 输出的重点参数项。

　　aborted_clients:由于客户端没有正确关闭连接导致客户端终止而中断的连接数。比如连接时间超过设置的连接超时时间（wait_timeout和interactive_timeout）就会退出，会使aborted_clients计算器加1。

　　aborted_connects:试图连接到MYSQL服务器而失败的连接数。例如，输错密码，无法连接数据库，就会在aborted_connects该参数值上加1。

　　binlog_cache_disk_use:使用临时二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中语句的事务数量。

　　binlog_cache_use:使用临时二进制日志缓存的事务数量。

　　binlog_stmt_cache_disk_use:当非事务语句使用二进制日志缓存，但是超出binlog_stmt_cache_size的大小时，就会使用一个临时文件来存放这些语句。

　　binlog_stmt_cache_use:使用二进制日志缓存文件的非事务语句数量。

　　create_tmp_disk_tables:服务器执行语句时在硬盘上自动创建的临时表的数量。

　　注：排序过程中，内存不够用，就需要在磁盘上创建临时表来完成排序工作。

　　created_tmp_tables:服务器执行语句时自动创建的内存中的临时表的数量。如果created_tmp_disk_tables参数值较大，可能要增加tmp_table_size值，使临时表基于内存而不基于磁盘。

　　handler_commit：内部提交的语句数量。执行update或者delete语句使该数量加2，执行select 语句使该值加1。

　　handler_rollback:内部rollback语句的数量。

　　handler_read_rnd:根据固定位置读一行的请求数。如果正执行大量查询并需要对结果进行排序，则该值较高。你可能使用了大量需要mysql扫描整个表的查询或连接没有正确使用索引。比如针对分页查询优化时，使用select ...limit 100,1.

　　handler_read_rnd_next:在数据文件中读下一行的请求数。如果正进行大量的表扫描操作，该值较高，通常说明表索引不正确或写入的查询没有利用索引。

　　handler_read_first:索引中第一条被读的次数。如果较高，它建议服务器正执行大量全索引扫描。列如，使用select 字段A from table,假定字段A有索引。

　　handler_read_key:根据索引读一行的请求数。如果较高，说明查询和表的索引正确。

　　

下面介绍mysql innodb存储引擎中三个重要的等待事件。

innodb_buffer_pool_wait_free:一般情况下 ，通过后台向innodb缓冲池写。但如果需要读或创建页，并且没有干净的页可用，则它还需要先等待页面清空，该计数器对等待实例进行计数。如果已经适合设置了缓冲池大小，该值应小。

如果innodb_buffer_pool_wait_free参数值大于0，就需要添加innodb buffer pool的大小了。

innodb_log_waits:必须等待的时间，因为日志缓冲区太小，在继续前必须先清空它。

 

 如果innodb_log_waits参数不为0，则证明当前的redo log buffer size 太小了，需要增大。

innodb_row_lock_waits:当前等待行锁的数量。如果该参数值较高，需要通过show engine innodb status \G或者利用information_schema库下INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS进一步分析锁等待过程。

open_tables:当前打开表的数量。

opened_tables:已经打开表的数量。

 

 如果opened_tables的数值非常大，说明table_open_cache值太小，导致要频繁得“open table”,可以查看当前的table_open_cache设置：

 

 MYSQL5.6之后多了一个table_open_cache_instances参数。该参数代表表缓存实例数，作用就是对table cache进行划分，减少锁竞争。

　　threads_cached:线程缓存内的线程的数量。

　　threads_connected:当前打开的连接的数量。

　　threads_created:创建用来处理连接的线程数。如果Threads_created较大，可能要增加thread_cache_size值。

　　threads_running:激活的（非睡眠状态）线程数。

 

 select_full_join:没有使用索引的连接的数量。如果该值不为0，应该仔细检查表的索引。

select_full_range_join：在引用的表中使用范围搜索的连接的数量。

select_range：在第一个表中使用范围的连接的数量。一般情况下不是关键问题，即使该值相当大。

select_range_check:在每一行数据后对键值进行检查，检查不带键值的连接的数量。如果不为0，应仔细检查表的索引。

select_scan:对第一个表进行完全扫描的连接的数量。

 

sort_merge_passes:排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加sort_buffer_size系统变量的值。

sort_range:在范围内执行的排序的数量。

sort_rows:已经排序的行数。

sort_scan:通过扫描表完成的排序的数量。