索引优化：见mysql索引及数据库引擎
mysql查询执行的过程：
1、mysql客户端/服务端通信 -> 2、查询缓存 -> 3、查询优化处理 -> 4、查询执行引擎 -> 5、返回客户端
1、mysql客户端/服务端通信：半双工通信方式（两端都可以给对方发送信息，但是同一时间只能有一个方向的传输）
对于一个mysql连接，或者说一个线程，时刻都有一个状态标识这个连接正在做什么
查看命令 show full processlist/ shou processlist
Sleep:线程正在等待客户端发送数据
Query：连接线程正在执行查询
Locked：线程正在等待表锁的释放
Sorting result：线程正在对结果进行排序
Sending data: 向请求端返回数据
2、查询缓存
使用场景：已读为主的业务，数据生成之后就不长改变的业务（新闻、论坛）
缓存select操作的结果和sql语句
新的select语句，先去查询缓存，判断时候存在可用的记录
判断标准：与缓存的sql语句是否完全一致（简单认为存储了一个key-value结果，key为sql，value为sql查询结果集）
show variables like 'query_cache%'；
query_cache_type 为0关闭缓存 ，为1完全开启缓存，为2按需开启缓存（只有带SQL_CACHE的sql才开启缓存）
mysql缓存修改语句（set gobal query_cache_type = 0\1\2\）,注修改主配置文件要重启mysql
查询缓存情况（show status like 'Qcache%'）
3、查询优化处理
查询优化分为三个阶段：
1解析sql 2预处理阶段 3 查询优化器（作用：找到最优的执行计划）
执行计划-id：
1、查询的序列号，标识执行的顺序
2、id不同，如果是子查询，id的序号会递增，id值越大越，优先级越高，越被先执行
3、id相同：从上往下顺序执行
执行计划-select-type：
SIMPLE:简单的select查询，查询中不包含子查询或者union
PRIMARY：查询中包含子查询部分，最外层查询被标记为primary
SUBQUERY/METAERIALIZED：SUBQUERY标识在select或者where列表中包含子查询
METAERIALIZED表示where后面in条件的子查询
UNION:若第二个select出现在union之后，则被标记为union
UNION RESULT:从union表获取结果的select
执行计划-table
查询涉及到的表 <union1,3>1和3指执行计划id
执行计划-type（重要）
访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system>const>eq_ref>range(好的sql至少要达到这个级别)>index>ALL
执行计划-possible_keys、key、 rows、 filtered
possible_keys:查询过程中可能用到的索引
key实际使用的索引，如果为NULL，则没有使用索引
rows大致估算出找到所需记录所需要读取的行数
filtered（5.7版本后才有）返回结果的行数占需要读到的行数的百分比，越大越好
执行计划-Extra（重要的额外信息）
Using filesort :mysql对数据使用了外部文件内容进行了排序，没有按照表内的索引进行排序读取（性能比较差）
Using temporary ： 使用了临时表保存中间结果，常见于order by或group by
Using index：使用了覆盖索引(效率高)
Using where：使用到了where过滤条件
select tables optimized away :基于索引优化min/max或者myisam优化count（*）操作
4、查询执行引擎
调用插件式的存储引擎的原子ApI的功能进行执行计划的执行
5、返回客户端
1、有需要缓存的，执行缓存操作
2、增量的返回结果：开始生成第一条结果时，mysql就开始往请求方逐步返回数据
好处：mysql服务器无须保存过多的数据，浪费内存，用户体验好，马上就拿到数据
慢查询日志配置（定位性能差的sql）
show variables like 'slow_query_log' 显示慢查询日志状态
set global slow_query_log = on 开始慢查询日志记录
set global slow_query_log_file = '/var/lib/mysql/wangzhengxin-slow.log' 设置慢查询日志记录位置
set golbal log_queries_not_using_indexes = on 没有命中索引的全部记录
golbal_long_query_time = 1:sql执行超过1秒，会记录日志
日志分析:
Time:日志记录的时间
User@Host：执行的用户及主机
Query_time查询耗时，Lock_time锁表时间 ， Rows_sent发送给请求方的记录条数 Rows_examined语句扫描的记录条数
SET timestamp 语句执行的时间点
select .... 执行的具体语句
慢查询日志分析工具
mysqldumpslow
语句（mysqldumpslow -t 10 -s at /var/lib/myssql/gupaoedu-slow.log）
其他工具
mysqlsla、 pt-query-digest
全局配置文件配置：
最大连接数配置: max_connections(受mysql系统句柄数限制（limitnofile）和linux系统句柄数限制（文件打开数）limits.conf)
内存参数配置
sort_buffer_size connection排序缓冲区大小 建议（256k（默认值）->2M之间）
当查询语句中需要文件排序功能时，马上为connection分配配置的内存大小
join_buffer_size connection关联查询缓冲区大小 建议（256k（默认值） -> 1M 之间）
当查询语句中有关联查询时，马上分配配置大小的内存用于这个关联查询，
上述配置4000链接占内存：4000（0.256 + 0.256M） = 2G
innodb_buffer_pool_size（很关键） innodb buffer/cache 的大小（默认128K）
缓存的内容：数据缓存，索引缓存，缓存数据，内部结构
大的缓冲池可以减少磁盘I/O次数，可以提高性能
参考计算公式：
innodb_buffer_pool_size = （总的物理内存-系统运行所用 - connection）90%
字段设计规范：必须把字段定义为NOT NULL并且提供默认值
null使用索引，索引统计，值比较都比较复杂，对mysql来说更难优化
null会降低数据库性能
null值需要更多的存储空间
null处理只能使用is null或is not null， 而不能采用 =、in、<、>、<>、 not in
使用TINYINT 代替枚举（枚举底层也是TINYINT ）
索引设计规范：
单表索引建议控制在5个以内
单索引字段数不超过5个
静止在更新十分频繁、离散型低的属性上建立索引
建立联合索引，必须把区分高度的字段放在前面
禁止大表使用join
禁止使用or必须改为in（mysql对in做了特别的优化）
mysql使用in的话会对in里的内容进行排序，然后二分查找（时间复杂度是O（logN）），用or的话会将满足条件的记录会一条一条比对（时间复杂度是O（n））
网友评论