性能参数 show status
使用show status查看一些性能参数
show status like 'value';
其中value表示参数值，常见：
connections:连接数据库次数
uptime：服务器上线时间
slow_queries：慢查询次数
com_select：查询操作次数
com_update：更新操作次数
com_insert：插入操作次数
com_delete：删除操作次数
查看允许最大连接数：
show variables like 'max_connections';
慢查询日志
配置
慢查询日志记录了所有执行时间超过参数long_query_time 设置值并且扫描记录数不小于min_examined_row_limit的所有SQL日志。long_query_time 默认值为10s，最小为0，可以精确到毫秒。
在配置文件中增加以下配置：
# 控制慢查询日志开启，1开启，0关闭
slow_query_log=1
#指定慢查询日志文件名
slow_query_log_file=slow_query.log
#查询时间限制，默认10s
long_query_time=10
日志分析工具 mysqldumpslow
帮助信息
- s:是表示按何种方式排序
- c:访问次数
- l:锁定时间
- r:返回记录
- t:查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间
- t:即为返回前面多少条的数据
- g:后边搭配一个正则匹配模式，大小写不敏感的
-- 返回记录集最多的10条SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow_query/log
-- 返回访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow_query/log
-- 返回按照查询时间排序的前10条中含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow_query/log
-- 配合| more ，防止爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/slow_query/log | more
查询语句分析 explain
通过查询语句的分析，可以知道查询语句的执行情况，找出查询语句执行的瓶颈，从而优化。MySQL提供了explain和describe语句用来分析。
explain [extended] select...
使用explain关键字可以知道MySQL是如何处理SQL语句的，还会告诉我们索引主键是如何被利用的，数据表是如何被搜索或排序的等等
- id
id是select查询的序列号，是一组数字，表示的是查询在执行select字句或者操作表的顺序。id相同表示加载表的顺序从上到下，id值越大表示优先级越高 - select_type
所使用的查询类型。- SIMPLE：简单的select查询，查询中不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为
- SUBQUERY：在SELECT或者WHERE列表中包含了子查询
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
- UNION RESULT：从UNION表获取结果的SELECT
- table
表名 - type
访问类型，较为重要- null：MySQL不访问任何表，索引，直接返回结果
- system：表只有一行记录（等于系统表），这是const类型的特例，一般不会出现
- const：通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量。
- eq_ref：类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。
- range：只检索给定的行，使用一个索引来选择行。where之后出现between，< ,>，in等操作
- index：与all的区别为，index只遍历了索引树，all遍历全表
- all：遍历全表
性能由高到低，一般至少要达到range级别，最好是ref。
- possible_key
查询时可选用的索引 - key
实际选用索引 - key_len
索引按字节计算的长度，数值越小越快 - ref
显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值 - rows
预计执行查询时从数据表中查询出的行数 - extra
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序”
- Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by
- Using index：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。
- Using where：使用了where过滤
- Using join buffer：使用了连接缓存
- impossible where：where子句的值总是false，不能用来获取任何元组
- select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
show profiles分析
查看是否支持
select @@have_profiling
开启
set profiling=1
查看SQL执行结果
show profiles
诊断SQL
show profile [type] cpu,block io for query 7
type:
- all:显示所有开销信息
- block io：显示块IO相关开销
- context switches：上下文切换相关开销
- CPU：CPU相关开销信息
- IPC：发送和接收相关开销信息
- memory：内存相关开销
- page faults：页面错误相关开销
- source：与source_function，source_file，source_line相关开销
- swaps：交换次数相关开销
需要注意的结论
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了
- Creating tmp table 创建临时表
- Copying to tmp table 拷贝数据到临时表
- removing tmp table 用完再删除
- Copying to tmp table on disk 把内存中临时表复制到磁盘
- locked
优化查询
关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?
在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们.
慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?
所以优化也是针对这三个方向来的,
首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.
为搜索字段创建索引，尽可能避免索引失效
索引不一定就是给主键或是唯一的字段，如果在表中，有某个字段经常用来做搜索，需要在该列上建立索引。
注意：复合索引的生效原则是，最左前缀法则，从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用，造成断点的原因：
- 前边的任意一个索引没有参与查询，后边的全部不生效
- 前边的任意一个索引参与的是范围查询，后边的不生效
- 断点跟索引字段在SQL语句中的位置前后无关，只与是否存在有关
- 在索引列上进行运算操作，索引失效
- 在索引列上，字符串不使用单引号，索引失效
- 尽量使用覆盖索引，避免使用select *
- 以%开头的like模糊查询，索引会失效，如果仅仅是尾部的%模糊查询，索引不会失效。
- MySQL会优化查询语句，判断如果全表扫描比索引来得快，即使有索引也不会走索引，is null/is not null
- 尽量使用复合索引，减少使用单列索引，因为创建了一个复合索引相当于创建了多个索引。
例如，在a,b,c三列上添加组合索引后，会出现以下问题：
-- 3个索引都用到且发挥作用
select * from table where a=3 and b=5 and c=4;
-- 组合索引在SQL的位置先后无关
select * from table where b=5 and c=4 and a=3;
-- a用到索引，b没有yong，所以c没有用到
select * from table where a=3 and c=4;
-- a用到，b用到，但b为范围搜索，所以c没用到
select * from table where a=3 and b>5 and c=4;
-- a没有用到，所以bc都没有用到
select * from table where b=5 and c=4;
普通索引和唯一索引的选择
change buffer
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。
在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。
因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。
前缀索引：优化长字段索引
核心是提高区分度，可以截取特征子段或者hash为另一个子段。
优化order by
两种排序方式：
- 通过对返回数据进行排序，filesort排序，所有不是通过索引直接返回排序的结果都叫filesort排序。
- 通过有序索引顺序扫描直接返回有序数据，这种情况为using index，不需要额外排序，操作效率高。
优化：
3. 尽量使用覆盖索引
4. 多字段排序尽量使用相同顺序排序
优化group by
group 不要实际上也同样会进行排序操作，而且与order by相比，group by主要是多了排序之后的分组操作，当然，如果在分组的时候还使用了其他的聚合函数，那么还需要一些聚合函数的计算。
优化：
为了避免排序造成的消耗，可以执行order by null禁止排序
优化子查询
如果可以的话，将子查询转化为关联查询
优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好的提高性能。常见又头疼的问题就是limit 2000000,10,此时需要MySQL排序前2000010条记录，仅仅返回最后10条记录而丢弃其他记录，查询代价非常大。
优化：
在索引上完成分页操作，最后根据主键关联回原表查询其他所需要的内容
当只要一行数据时使用limit 1
加上limit 1可以增加性能。MySQL数据库引擎会查找到一条数据后停止搜索。
避免使用select *
从数据库中读出越多的数据，那么查询就会变得越慢。并且，如果我们的数据库服务器和web服务器是两台独立的服务器的话，这还会增加网络传输的负载。所以应该养成需要什么就取什么的好习惯。
设置一个id主键最好是int类型（unsigned）并设置自动增加auto_increment
为数据库里的每张表都设置一个id作为主键，而且最好是int类型（unsigned）。并设置自动增加auto_increment。即使是user表中有一个叫“email”的字段，也别让它成为主键。使用VARCHAR类型当主键会使性能下降。
尽可能的不要赋值为null
在MySQL中对于int类型，empty为0，而null是空值。而在Oracle中null和empty的字符串都是一样的。null也需要占用存储空间，并且会使我们的程序判断时更加复杂。
固定长度的表会更快
如果表中的所有字段都是固定长度的，整个表会被认为是static或fixed-length。例如，表中没有VARCHAR，text，blob。只要包含了其中一个这些字段，那么这个表就不是固定长度了，MySQL引擎会用另一种方法来处理。固定长度的表会提高性能，因为搜索得会更快一些，很容易计算下一个数据偏移量，所以读取自然会很快。而如果字段不是定长了，每一次要找下一条，需要程序找到主键。并且，固定长度的表更容易被缓存和重建。不过，唯一副作用是，会浪费空间，因为定长字段无论是否被使用，都是要分配固定的空间。另外在取出值时需要使用trim去除空格。
越小的列越快
对于大多数数据库引擎来说，硬盘操作可能是最大的瓶颈。所以，把我们的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。如果我们不需要记录时间，使用date要比datetime好的多。
优化数据库结构
合理的数据库结构不仅可以占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余，查询和更新的速度，字段的数据类型是否合理等内容。
将字段很多的表分解成多个表
对于字段较多的表，如果某些字段的使用频率比较低，可以将这些字段分离出来构成新的表，因为当一个表的数据量很大时，会由于使用频率低的字段存在而变慢。
增加中间表
对于经常需要联合查询的表，可以增加中间表来提高查询效率。通过建立中间表，将经常联合查询的数据插入中间表，然后将原来联合查询改为中间表查询，提高效率。
增加冗余字段
设计数据库表时应尽量遵守范式理论的规约，尽可能减少冗余字段，但是，合理的加入冗余字段可以提高查询速度，比如通过添加冗余字段减少联合查询。
优化插入
使用批量插入
insert into tab values(),(),()...
分析表，检查表，优化表
analyze [local|no_write_binlog] table tab_name[,tab_name...]
分析表的过程中，数据库自动对表加只读锁
check table tab_name[,tab_name...] [option]
option={quick|fast|medium|extended|changed}
检查表和视图是否存在错误，option只对MyISAM有效
optimize [local|no_write_binlog] table tab_name[,tab_name...]
优化表中的VARCHAR，blob，text类型的字段，可以消除删除或更新造成的文件碎片，加只读锁
优化MySQL服务器
优化硬件
配置较大的内存，配置高速磁盘系统，配置多处理器
服务参数优化
修改my.ini配置文件参数
116
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
