1.sql及索引优化
# 查看慢sql日志是否开启，以及日志文件位置
show variables like 'slow_query_log%'; #查看慢sql的界定时间 s show variables like 'long_query_time'; #是否记录未使用索引的查询sql show variables like 'log_queries_not_using_indexes%'; mysqldumpslow -h;
#慢查询日志分析工具
#1.官方：mysqldumpslow 命令
2.pt-query-digest slow-log 将慢查询日志输出到数据库表中
如何通过慢查日志发现有问题的SQL？
1.查询次数多且每次查询占用时间长：
通常为pt-query-digest分析的前几个查询
2.IO大 :
注意pt-query-digest 分析中的Rows examine项
3.未命中索引
注意pt-query-digest分析中的Rows examine(扫描行数) 和 Rows Send(发送行数)对比
使用explain对慢SQL进行分析
explain返回各列含义
table: 显示这一行的数据是关于哪张表的
type: 显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index 和 ALL
possible_keys:显示可能应用在这张表中的索引。为空表示没有可能的索引。
key:实际使用的索引。如果为NULL，则没有使用索引。
key_len：使用索引的长度。在不损失精确性的情况下，长度越短越好。
ref:显示索引的哪一列被使用了
rows: mysql认为必须检查的用来返回请求数据的行数。
分页优化：
原sql: select * from table limit 500,5 order by name;
优化1：select * from table limit 500,5 order by id; 使用有索引的列进行排序 ，扫描行数为505（缺点是翻页越往后IO越大）
优化2：select * from table where id > 500 and id <= 505 order by id; 通过缩小范围降低IO，扫描行数为5(依赖id自增，IO稳定)
索引优化：
1.当一个索引包含了查询条件中的所有列，称之为覆盖索引。
2.在where、group by、order by、on从句中出现的列建索引
3.索引字段越小越好(单页取出的索引数量更多，以减少IO)
4.离散度大的列放在联合索引的前面
5.索引不是越多越好：数据库在查询分析时需要选择使用哪些索引，过多索引会减缓分析过程，不需要的索引应该删除。
- 使用pt-duplicate-key-checker工具检查重复及冗余索引 pt-duplicate-key-checker -uroot -proot -h 127.0.0.1
- 使用SQL判断：select a.table_schema,a.table_name,a.index_name,b.index_name,a.column_name as '重复列名' from statistics a join statistics b ON A.TABLE_SCHEMA=B.TABLE_SCHEMA AND A.TABLE_NAME=B.TABLE_NAME AND A.SEQ_IN_INDEX=B.SEQ_IN_INDEX AND A.COLUMN_NAME=B.COLUMN_NAME WHERE A.SEQ_IN_INDEX = 1 AND A.INDEX_NAME <> B.INDEX_NAME
6.删除不用的索引：在MYSQL中通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析
2.数据库表结构
- 选择合适的数据类型：
- 使用可以存下你的数据的最小的数据类型
- 使用简单的数据类型。Int比varchar类型在mysql处理上更简单
- 尽可能使用not null定义字段(InnoDB引擎下对允许为null的字段需要使用额外字段存储)
- 尽量少用text类型，或考虑分表（附加表）
- MYSQL数据类型优化示例
- 使用int替代datetime存储时间：使用函数UNIX_TIMESTAMP()将时间字符串转换为int存储，函数FROM_UNIXTIME将int还原为时间字符串
- 使用bigint替代varchar存储IP：使用函数INET_ATON('192.168.0.1') 将ip转换为long存储，函数INET_NTOA()将long还原为ip字符串
- 数据库表的范式化优化：第3范式要求表中不存在非关键字段对任意关键字段的传递函数依赖
- 如订单表中出现商品描述字段，应该将商品描述放在商品表中
- 数据库表的反范式化：为了查询效率的考虑把原本符合第3范式的表适当的增加冗余，以优化查询（空间换时间）
- 垂直拆分(解决表的宽度问题)：将表中不常用字段和text类型字段独立出去，减少常见场景下的查询IO
- 水平拆分(解决表的数据量问题)：单表数据量过大时考虑将一张表拆根据特定的hash规则分为多张表(表结构一致)
- 拆分后查询效率提升了，但是因为数据被拆分到多张表，处理数据汇总业务时变得难以处理，解决：建一张总表存储全部数据，汇总业务慢慢处理。
3.系统配置
MYSQL配置优化(/ect/mysql/my.cnf)
- innodb_read_io_threads、innodb_write_io_threads，这两个参数决定InnoDB读写的IO进程数，默认为4，可根据服务器cpu核心与读写业务需求进行调整(V5.5版本后)。
- innodb_buffer_pool_size，配置InnoDB的缓冲池大小。如果数据库中只有InnoDB表，则推荐配置量为总内存的75%。列出当前数据库所有表数据+索引的数据大小sql:
select ENGINE,round(sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024,1) as totalMB
from information_schema.TABLES where TABLE_SCHEMA not in
('information_schema','performance_schema') group by ENGINE ;
应该满足条件：innodb_buffer_pool_size >= totalMB (前者单位是字节需要转换) - innodb_buffer_pool_instances MYSQL5.5中新增的参数，可以控制缓冲池的个数，默认只有一个缓冲池。硬件支持的情况下配置多个缓冲池可以提升并发。
- innodb_log_buffer_size InnoDB log缓冲的大小，由于日志最长也会每秒刷新所以不用设置太大(能存下一秒的数据即可)
- innodb_flush_log_at_trx_commit 决定数据库从缓冲池刷新到磁盘的频率，可选值0/1/2，默认为1(最安全)，表示每次提交都会将数据从缓存刷新至磁盘，0表示定时每1秒将缓存刷新到磁盘，2表示每次提交将数据保存至缓冲区，然后每秒从缓冲区刷新至磁盘。
- innodb_file_per_table 控制InnoDB每个表是否使用独立的表空间，默认为OFF，即所有表都会建立在共享表空间中(只有一个文件，会成为竞争资源不利于发挥性能)，建议设置为ON
- innodb_stats_on_metadata 决定mysql在什么情况下会刷新innodb表的统计信息。建议设置OFF，避免频繁刷新表信息。
4.硬件（内存/IO/CPU）硬件优化成本最高，效果最差
mysql 隐式字段
DB_TRX_ID：
1.6byte
2.记录创建这条记录时，最后一次修改该数据的事务ID(初始为空)
DB_ROLL_PTR:
1.7byte，回滚指针
2.指向这条记录上一个版本(存储于rollback segment回滚段里)
DB_ROW_ID
1.6byte ，隐藏的自增ID（隐藏主键）
2.如果数据表没有主键，innoDB会自动以DB_ROW_id产生一个聚簇索引
