性能监控
1、使用show profile查询剖析工具，可以指定具体的type
- 此工具默认是禁用的，可以通过控制台修改
set profiling=1;
- 当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。
select * from user;
- 在mysql的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间
show profiles;
- 执行如下命令可以查看详细的每个步骤的时间：
show profile for query 1;
- 还有其他监控的指标
all：显示所有性能信息 show profile all for query n
block io：显示块io操作的次数 show profile block io for query n
context switches：显示上下文切换次数，被动和主动 show profile context switches for query n
cpu：显示用户cpu时间、系统cpu时间 show profile cpu for query n
IPC：显示发送和接受的消息数量 show profile ipc for query n
swaps：显示swap的次数 show profile swaps for query n
2、使用performance_schema来更加容易的监控mysql
详见上篇文章：MYSQL performance schema详解
schema与数据类型的优化
1、数据类型的优化
- 应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型
- 简单数据类型的操作通常需要更少的CPU周期，例如，
1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
2、使用mysql自建类型而不是字符串来存储日期和时间
3、用整型存储IP地址
例如：select inet_aton(‘1.1.1.1’)
select inet_ntoa(16843009)- 如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列
2、合理使用范式和反范式
-
范式
- 优点
范式化的更新通常比反范式要快
当数据较好的范式化后，很少或者没有重复的数据
范式化的数据比较小，可以放在内存中，操作比较快 - 缺点
通常需要进行关联
- 优点
-
反范式
- 优点
所有的数据都在同一张表中，可以避免关联
可以设计有效的索引； - 缺点
表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失
- 优点
3、主键的选择
- 代理主键
与业务无关的，无意义的数字序列 - 自然主键
事物属性中的自然唯一标识 - 推荐使用代理主键
它们不与业务耦合，因此更容易维护
一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本
4、存储引擎的选择
5、适当拆分
- 当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。
执行计划
在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。
可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。
官网地址: https://dev.mysql.com/doc/refman/5.5/en/explain-output.html
1、执行计划中包含的信息
| Column | Meaning |
|---|---|
| id | The SELECT identifier |
| select_type | The SELECT type |
| table | The table for the output row |
| partitions | The matching partitions |
| type | The join type |
| possible_keys | The possible indexes to choose |
| key | The index actually chosen |
| key_len | The length of the chosen key |
| ref | The columns compared to the index |
| rows | Estimate of rows to be examined |
| filtered | Percentage of rows filtered by table condition |
| extra | Additional informat |
id
select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序
select_type
主要用来分辨查询的类型，是普通查询还是联合查询还是子查询
table
对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id
type
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下，得保证查询至少达到range级别，最好能达到ref
possible_keys
显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
key_len
表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好
extra
包含额外的信息。
通过索引进行优化
1、索引的基本知识
优点：
1、大大减少了服务器其需要扫面的数据量
2、帮助服务器避免排序和临时表
3、将随机IO变为顺序IO
用处：
1、快速查找匹配的where子句的行。
2、如果可以在多个索引进行选择、mysql通常会使用找到最少行的索引
3、如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
4、当有表链接的时候，从其他表检索行数据。
5、查找特定索引列的min或max值
6、如果排序或分组时在可用索引的最左前缀上完成，则对表进行排序和分组
7、在某些情况瞎，可以优化查新以检索值而无需查询数据行
2、索引的分类
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 组合索引
3、索引的匹配方式
- 全值匹配：全值匹配指的是和索引中的所有列进行匹配
- 匹配最左前缀：只匹配前面的几列
- 匹配列前缀：可以匹配某一列的值的开头部分
- 匹配范围值：可以查找某一个范围的数据
- 精确匹配某一列并范围匹配另外一列
- 只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
4、组合索引
- 当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要
- 例：建立组合索引 a、b、c
5、聚簇索引与非聚簇索引- 聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起
- 非聚簇索引：数据文件跟索引文件分开存放
6、覆盖索引
- 介绍：
1、如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
2、不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
3、不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引 - 优势：
1、索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
2、因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
3、一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
4、由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用
7、索引优化细节
- 1、当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层
- 2、尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询
- 3、使用前缀索引
- 4、使用索引扫描来排序
- 5、union all,in,or都能够使用索引，但是推荐使用in
- 6、范围列可以用到索引
范围条件是：<、>
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列 - 7、强制类型转换会全表扫描
- 8、更新十分频繁，数据区分度不高的字段上不宜建立索引
- 9、创建索引的列，不允许为null，可能会得到不符合预期的结果
- 10、当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
- 11、能使用limit的时候尽量使用limit 限制输出
- 12、单表索引建议控制在5个以内
- 13、单索引字段数不允许超过5个（组合索引）
查询优化
1、查询慢的原因
网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间
2、优化数据访问
- 查询性能低下的主要原因是访问的数据太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化
1、确认应用程序是否在检索大量超过需要的数据,用查询计划看扫描了多少行数据
2、确认mysql服务器层是否在分析大量超过需要的数据行
- 是否向数据库请求了不需要的数据
查询不需要的记录
多表关联时返回全部列
总是取出全部列(select *)
重复查询相同的数据
3、查询优化处理
mysql查询完缓存之后会经过以下几个步骤：解析SQL、预处理、优化SQL执行计划，这几个步骤出现任何的错误，都可能会终止查询
对于执行的sql 可以使用
show status like 'last_query_cost' 来查看执行成本
4、优化特定类型的查询
- 优化count()查询：count()是特殊的函数，有两种不同的作用，一种是某个列值的数量，也可以统计行数
总有人认为myisam的count函数比较快，这是有前提条件的，只有没有任何where条件的count(*)才是比较快的
- 优化关联查询
1、确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序
2、确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程
-
优化子查询：子查询的优化最重要的优化建议是尽可能使用关联查询代替，因为子查询的结果放在 临时表，会增加系统的io
-
优化limit分页：优化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列
-
优化union查询：mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化
建议：除非确实需要服务器消除重复的行，否则一定要使用union all，因此没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字，这个操作的代价很高
234
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
