MySQL数据库优化

数据库性能下降可能的原因

• I/O吞吐量小，形成了瓶颈效应
• CPU性能差
• 内存不足，磁盘空间不足
• 没有检索或者没用到检索
• 查询语句性能差，没有优化
• 检索返回的数据量太大
• 返回了不必要的字段
• 锁或者死锁
• 配置参数没有优化

如果是服务器性能问题，可以使用这些指令进行查询：top命令、htop命令、free命令和df命令等等。

SQL执行顺序

如果我们想知道我们的SQL操作为什么性能低下，我们就需要对SQL执行顺序有所了解。

你看见SQL是这样的：

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_list>
LIMIT <limit_number>

系统看见SQL是这样的：

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT <select_list>
DISTINCT <select_list>
ORDER BY <order_by_list>
LIMIT <limit_number>

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2. ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7. HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

常见表连接

左连接

获取以table1为基础的所有数据

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id

右连接

获取以table2为基础的所有数据

mysql> SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id

内连接

获取两张表的交集数据

mysql> SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id

左独查询

获取只有table1有的数据

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL

右独查询

获取只有table2有的数据

mysql> SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id WHERE t1.id IS NULL

全连接

获取两张表的全部数据，MySQL没有FULL关键字，只能用别的方法查询

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id UNION SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id

注：UNION有去重功能，而UNION ALL则没有。

非交集查询

获取两张表自己独有的数据

mysql> SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id WHERE t2.id IS NULL UNION SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id WHERE t1.id IS NULL

索引

索引是通过BTREE结构进行数据检索的，以平衡二叉树检索的方式缩短数据查询的时间。

索引类型

• 主键索引（primary），在innodb存储引擎下，由于数据和索引都在ibd文件里存储，所以数据的组织方式是由主键索引的BTREE结构，即聚簇索引，如果表没有主键系统会查找一列唯一数据列当主键，如果还没有唯一数据列，系统则虚拟主键索引。在innodb存储引擎下，其他索引都引用主键索引的地址，即非聚簇索引。

• mysql> create table t1(id int primary key);
  #或
  mysql> alter table t2 add primary key(id);

• 普通索引（normal）

  mysql> create index idx_name on t1(name);

• 唯一索引（unique）

  mysql> create table t1(id int unique);

• 全文索引（full）

　　由于MySQL默认的全文索引对中文的支持不好，所以通常使用别他工具来实现，比如：sphinx 或 coreseek

查询索引

mysql> show keys from table_name;
mysql> show index from table_name;

删除索引

mysql> alter table table_name drop index index_name

索引的优点和缺点

• 优点
  • 提高检索速度，降低磁盘读取I/O
  • 索引是排序好的，降低数据排序运算的成本，也就降低了CPU的消耗
• 缺点
  • 索引也需要存储，所以也需要空间
  • 降低更新表的速度，更新不仅仅只是数据本身，如果有索引也需要更新索引信息

Explain

语法

EXPLAIN SELECT ...

作用

• 描述MySQL如何执行查询操作、执行顺序、使用到的索引和MySQL成功返回结果集需要执行的行数等信息。
• 可以帮我们分析SELECT语句，让我们知道查询效率低下的原因，从而改进我们的查询，让查询优化器能够更好的工作。

• id：标识符，表示执行顺序
• select_type：查询类型
• table：查询的表
• partitions：使用的哪个分区，需要结合表分区才能看到，MySQL 5.7版本之前需要在EXPLAIN 和 SELECT 之间加 PARTITIONS 才能看见
• type：连接的类型
• possible_keys：可能使用到的索引，保存索引名称，如果多个则用逗号分隔
• ken_len：使用到的索引长度
• ref：引用索引对应表中哪些行
• rows：显示MySQL认为执行查询时必须要返回的行数
• filtered：通过过滤条件之后对比总数的百分比，MySQL 5.7+才有该属性
• Extra：额外信息

id

　　当多行id值都一致时，则顺序执行SQL

上图中先执行teacher表，再执行course表，最后执行student表。

 

　　当多行id不一致时，则按从大到小执行

上图中先执行teacher表，再执行course表，最后执行score表。

 

　　当多行id部分一致时，则先按从大到小，一致的id顺序执行

上图中先执行course表，再执行teacher表，最后执行score表。

 

select_type

　　SIMPLE：简单的查询

 

　　PRIMARY：主查询，或者说是最外层查询

　　SUBQUERY：子查询

 

　　UNION：UNION中第二个或者后面那个SELECT查询

　　UNION RESULT：UNION之后的结果

 

 

　　DEPENDENT UNION：UNION中第二个或者后面的SELECT

　　DEPENDENT SUBQUERY：子查询中第一个SELECT

 

　　DERIVED：衍生表，只有在MySQL 5.5x 和 5.6x里面有这个类型

 

table

所使用的表

partitions

使用到的表分区，只有在创建表分区之后才有效

type

表示按照某种类型来查询

　　const：表示表中最多有一个匹配行

　　eq_ref：对于每个来自于前面表的记录，所有匹配的行从这张表中取出

　　ref：对于每个来自于前面表的记录，所有匹配的行从这张表中取出，后张表id是唯一索引，于前表id一致

　　ref_or_null：类似于ref，但是可以搜索包含null值得行，address建立索引

　　index_merge：出现在使用一张表中的多个索引时，如果数据量太小，优化器判断全表扫描更快就不会使用index_merge

　　rang：按指定范围来检索

　　index：从索引数中查找

　　ALL：全表扫描

 

possible_key，key

表示可能用到的索引和用到的索引

key_len

表示索引长度，长度根据一套算法得来

key_len的长度计算公式：

varchr(10)变长字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)

int类型且允许NULL : 4+1(NULL)
int类型且不允许NULL : 4

详细可参考该文

ref

表示引用

rows

表示扫描的行数，值越小越好，说明扫描的范围小

 

Extra

　　using where：表示用到where

　　using index：表示用到索引

　　using join buffer：表示使用了连接缓存

　　using filesort：表示使用了文件内存排序，必须优化，严重影响性能

　　using temporary：表示使用了中间表或者临时表

 

适合建索引的情况

• 频繁作为WHERE条件语句查询的字段
• 关联字段需要建索引
• 排序字段可以建索引
• 分组字段需要建索引
• 使用到聚合函数的字段

不适合建索引的情况

• 频繁更新的字段
• WHERE条件用不到的字段
• 表数据量较小
• 数据重复较为均匀，例如性别，布尔类型等
• WHERE条件中参与列计算的字段

索引失效的情况

• 选择列用到*号，给name加索引
  • 
• 不遵循复合索引的字段顺序，复合索引具有传递性，如果中间某个字段无效索引，则后面的字段也不会索。给name，age，phone加复合索引
  • 复合索引全部使
    • 
  • 复合索引无效
    • 
  • 复合索引部分有效，这里虽然使用到里复合索引，但真正的只有name字段使用到了索引，而phone字段没有使用索引。可以通过key_len与全部使用索引比较，这个较少说明索引的字段少于正常，间接反映age和phone字段没有参与索引
    • 
• 复合索引字段用到>和<查找，这里age字段使用大于号，后面的phone字段没有索引，从key_len显示出来的结果可以看出。
  • 
• 在索引列上做计算，给age加索引
  • 
• 在索引列上做类型转换，MySQL5.7 +以上
• 在索引列做函数计算
  • 
• 在索引列上使用不等于!=或<>
  • 
• 在索引列上使用IS NULL或IS NOT NULL，可能会导致索引无效
  • 

    

• 在索引列上使用like，并且最左边有%，右边有%则可以
  • 
• WHERE语句里出现OR
  • 

 复合索引相关测试

1、复合索引使用> 或 <符号

可以看出这个是使用了复合索引的，但是key_len为65说明phone并没有用到索引。

2、复合索引进行运算

复合索引只有name有效。

3、复合索引不等于<>或!=

复合索引phone无效。

4、复合索引使用IS NULL

奇迹的是IS NULL竟然对复合索引无效，而且不管位置在哪。

但是对于IS NOT NULL，当在第一个字段时索引无效，而在第二个字段时，phone无效。

 5、复合索引使用LIKE

LIKE的使用与单索引效果一致。

6、复合索引使用OR

OR简直是大杀器，不管在哪里用都会导致索引失效。

慢查询

　　慢查询是MySQL提供的一种查询SQL执行效率的工具，通过该工具的设置可以获取SQL执行的信息。

查看慢查询

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';

　　

开启/关闭慢查询

mysql> SET GLOBAL slow_query_log = 1;    #开启
mysql> SET GLOBAL slow_query_log = 0;    #关闭

查看慢查询时间

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

　　

设置慢查询时间

mysql> SET GLOBAL long_query_time = 5;

查看慢查询日志测次数

mysql> SHOW STATUS LIKE 'slow_queries';

　　

查看没有使用索引的查询 

mysql> SET GLOBAL log_queries_not_using_indexes = 1;    #开启
mysql> SET GLOBAL log_queries_not_using_indexes = 0;    #关闭

如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启。

设置慢查询输出格式

mysql> SHOW VARIABLES LIKE '%log_output%'
mysql> SET GLOBAL log_output = 'TABLE'

可以为FILE、TABLE或FILE,TABLE

查看文件地址

mysql> SHOW VARIABLES LIKE 'slow_query_log_file'

如果是TABLE，在mysql库里slow_log表

 

慢查询日志分析工具mysqldumpslow

mysqldumpslow --help  查看帮助信息 或者man mysqldumpslow

whereis mysqldumpslow 查看linux命令安装在哪个目录

-s:排序

　　t:查询时间

　　c:访问次数

　　l:锁定定时

　　r：返回的记录

-g:后边可以跟正则表达式，用于过滤

-t NUM :显示的条数

案例：

　　1、取出耗时最长的前2条sql

[root@localhost ~]# mysqldumpslow -s t -t 2 test-slow.log

　　2、–g从结果中过滤-g后面的正则表达式的内容

[root@localhost ~]# mysqldumpslow -s t -t 2 -g 'exists' test-slow.log

 

show profile

　　用于分析当前会话中语句执行的资源消耗情况

查看profile

mysql> SHOW GLOBAL VARIABLES LIKE 'profiling';

　　

开启/关闭profile

mysql> SET GLOBAL profiling = 1;　　#开启
mysql> SET GLOBAL profiling = 0;　　#关闭

显示当前执行的语句和时间

mysql> SHOW PROFILES;

显示当前查询语句执行的时间和系统资源消耗

mysql> SHOW PROFILE cpu, block io FOR QUERY 1;