MySQL性能优化

一 Mysql Sql语句技巧与优化

1.Mysql Sql语句技巧

1).正则表达式的使用

正则表达式使用很广泛，很多编程语言都会使用到，MySQL也是有很好的支持的。例如使用LIKE进行模糊查询会很方便，让语句简短。

可以通过以下命令查看MySQL正则表达式的语法：

mysql> ? regexp

比如：

mysql> SELECT "java is OK" REGEXP "java*";
mysql> SELECT name,email FROM t1 WHERE email REGEXP "@163[.,]com$";

符号	说明
^	在字符串开始处进行匹配
$	在字符串末尾处进行匹配
.	匹配单个任意字符（包括换行符）
[…]	匹配出[]中的任意字符
[^…]	匹配不出现[]中的任意字符
*	匹配0个或多个字符（包含空串）
+	匹配至少1个字符（不含空串）
?	匹配0个1个字符
a|b	匹配a或b
a(m)	匹配m个a
a(m,)	匹配至少m个a
a(m,n)	匹配m到n个a
a(,n)	匹配0到n个a
(…)	将模式元素组成单一元素

建议看JavaScript、Linux Shell编程中的正则表达式使用。

2).巧用RAND()提取随机行

MySQL数据库中的RAND()函数是获取一个0~1之间的数，利用这个函数和ORDER BY一起能够很好的把数据随机排序，可能有时会对记录进行抽样，例如：

mysql> SELECT * FROM stu ORDER BY RAND() LIMIT 3;

3).利用GROUP BY的WITH ROLLUP子句统计

使用GROUP BY的WITH ROLLUP子句可以检索出更多的分组聚合信息，效果会更好，例如：

mysql> SELECT cname,pname COUNT(pname) AS total FROM demo BROUP BY cname,pname WITH ROLLUP;

注意：WITH ROLLUP不能和ORDER BY同时使用！！！缺点是有时候不好把控。

4).用BIT GROUP FUNCTIONS做统计

在使用GROUP BY语句的同时可以使用BIT_AND、BIT_OR函数来完成统计工作，这两个函数主要是做数值之间的逻辑位运算。例如，对t_order表的id分组时对kind做位运算：

mysql> SELECT id，BIT_AND(kind) FROM t_order GROUP BY id;
mysql> SELECT id，BIT_OR(kind) FROM t_order GROUP BY id;

5).使用外键需要注意的问题

InnoDB类型的表支持外键，MyISAM类型的表虽然创建外键可以成功，但是不起作用，主要原因是MyISAM数据引擎不支持外键。

6).mysql中help的使用

？命令用来进行帮助查询。

二 Mysql Sql语句优化

1).优化Sql语句优化的一般步骤

a) 通过SHOW status 命令了解各种SQL语句的执行频率，格式：
SHOW [session|global] status;
其中：session（默认）表示当前连接会话；global表示数据库启动至今
例如：

mysql> SHOW status;                     -- 查看全部status
mysql> SHOW status LIKE "Innodb_rows%"; -- InnoDB引擎CRUD相关
mysql> SHOW global status;              -- 数据库启动至今的status
mysql> SHOW global status LIKE "Com_";  -- 数据库启动至今的CURD统计数据
mysql> SHOW status LIKE "Com_";         -- CRUD次数统计相关
mysql> SHOW status LIKE "Connect%";     -- 连接MySQL的数量
mysql> SHOW status LIKE "Uptime%";      -- MySQL服务器已经工作的秒数
mysql> SHOW status LIKE "%slow_%";      -- 慢查询相关

b) 定位执行效率较低的SQL语句

mysql> EXPLAIN SELECT * FROM stu WHERE score > 80\G;
mysql> DESC SELECT * FROM stu WHERE score >= 80\G;

结果中重点查看以下数值：

• rows： 影响行数
• type： 连接类型
  （性能：system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all）
• select_type： 查询类型
• possible_keys：可能使用到的索引
• key： 是否使用到索引
• key_len： 使用到的索引列中某个值最大长度
• Extra： SQL执行情况说明和描述

c) 根据慢查询日志，分析执行效率低的SQL语句，对该创建索引的列创建索引、复杂查询创建视图等。

三 Mysql索引优化

1. 索引优化

2. 表检查与表优化

1) check表检查
2) optimize表优化

3. 常用sql的优化

1) 大批量导入数据
2) insert优化
3) group by优化
4) 嵌套查询优化

四 数据库优化与服务器优化

1. 数据库优化

1) 优化表的类型
2) 分库分表
3) 分区技术
4) 使用中间表提高统计查询速度

2. mysql服务器优化

1) myisam表锁
2) 四种字符集问题
3) binary log日志问题
4) slow log慢查询日志
5) socket丢失问题
6) root密码破解