【mysql优化部分】 优化大致思路: a. 表的设计合理化(符合3NF) b. 添加适当的索引(index) mysql的索引大致分为四类: 普通索引、主键索引、唯一索引、全文索引 c. 分表技术(水平分割、垂直分割) d. 读写分离(读 select 写 insert/delete/update) e. 存储过程(模块化编程,可以提高速度) f. 对MySQL的配置优化(如 最大并发数 max_connections等) g. MySQL服务器硬件升级 h. 定时清除不必要的数据 定时进行碎片整理(尤其是myisam存储引擎)
① 事务安全(innodb) ② 查询和添加速度(myisam) ③ 支持全文索引(myisam) ④ 锁机制(innodb) ⑤ 外键(innodb)
3、大量数据写入
① 对于myisam,关闭索引 alter table table_name disable keys; 插入加载数据 alter table table_name enable keys; 因为创建表的时候会自动创建索引,这样负载会加大
② 对于innodb 将要插入的数据按主键进行排序 set unique_checks=0;#关闭唯一索引(唯一性检查影响效率) set autocommit=0; #关闭自动提交
【三、建立合适的索引】
四种索引的使用(主键、唯一、全文、普通索引)
1、主键索引
添加索引 alter table art add primary key(id); 删除索引 alter table art drop primary key;
2、唯一索引
表的某一列被指定为unique 关键字是时 即为唯一索引 唯一索引允许为null 和 '' 但是可以允许多个null值存在,不能有多个''(空串)存在 create unique index 索引名 on 表名 (列名1,...)
3、全文索引
在创建表的时候创建 create table art( id int primary key, title varchar(20), body text, FULL TEXT(title,body) ) engine=myisam charset utf8;
注意: ① 全文索引只支持myisam引擎 ② mysql 系统提供的全文索引,只支持英文,不支持中文 如果要支持中文的话,需要下载sphinx插件 ③ 全文索引有一个停止词,在一篇文章中,创建全文索引是一个 无穷大的数,所以只会给不常见的词创建全文索引。 ④ 使用全文索引必须遵循使用规则 match() against(); select * from art where match(title,body) against('daye');
4、普通索引
create index 索引名 on 表名(列名); alter table 表名 add index 索引名(列名);
删除索引: alter table 表名 drop index 索引名
5、索引的查询
① 表结构查询 desc 表名;
② 查询单个索引 select index(索引名) from 表名\G
③ 查询表的所有索引 show keys from 表名\G
④ 查看索引的使用情况 show status like 'handler_read%' handler_read_key 高 说明索引使用率高 handler_read_rnd_next 高 说明查询效率低
6、索引的使用原则
① 创建了多列的索引,只有最左侧的列被使用时,索引才会被使用 ② 使用like 关键字进行查询时,开头不能有通配符'%'、'_'等 否则不会使用索引 ③ 条件中含or关键字 不会使用索引
即将一张表中 常用 和不常用的字段分离出来,组成两张不同表 原则: ① 将表中不常用的字段分离出来 ② 将表中数据量较大,会影响查询速度的表分离出来 ③ 注意分离表与原表的关联关系
【五、读写分离】
1、表的主从复制
insert into tab1 select col1 col2 ... from tab2;
【六、主从复制】
(略) 详细后面章节进行讲解
【七、定位慢查询sql】
(注意:这里慢查询不一定只指select语句,其它语句执行速度 比较慢的也叫慢查询) SQL优化一般思路: 1、通过show status 命令了解各种sql执行的效率 2、定位执行效率较低的sql语句 3、通过explain 分析低效率sql语句的执行情况 4、确定问题采取相应的措施
1、通过show status 命令了解各种sql执行的效率
show [session|global] status like '%%'; 其中:session为当前的会话窗口统计。默认项 global 则为所有会话窗口统计。
① mysql的运行时间: show status like 'uptime';
② 一共执行的次数: select: show status like 'com_select'; update: show status like 'com_update'; insert: show status like 'com_insert'; delete: show status like 'com_delete';
③ 当前连接数 show status like 'connections';
④ 显示慢查询次数 show status like 'slow_queries';
2、定位执行效率较低的sql语句
我们要通过以下几步定位慢查询sql语句: ① 关闭mysql服务 在windows下,打开"服务",找到mysql,关闭服务 在Linux下,直接找到mysqld 进程,kill掉
② 命令行进入mysql的安装目录 输入 版本5.5及以后 bin\mysqld.exe --safe-mode --slow-query-log 版本5.0及以前 bin\mysqld.exe -log-slow-queries=d:/ab.log
回车
③ 再次进入mysql命令行模式 更改慢查询设定的时间限制为1s set long_query_time = 1;
④ 此时慢查询日志已开启 记录地址在:my.ini 中的datadir所指的目录中
3、通过explain 分析低效率sql语句的执行情况
mysql> explain select * from emp where empno = 345680\G *************************** 1. row *************************** id: 1 #查询序列号 select_type: SIMPLE #查询类型 PRIMARY/ table: emp #查询的表名 type: ALL #扫描方式 ALL(全表扫描,尽量避免) SYSTEM 表仅有一行 CONST 表匹配到的仅有一行
possible_keys: NULL #表中可能使用到的索引 key: NULL #实际使用的索引 key_len: NULL ref: NULL rows: 4000000 #该sql语句扫描了多少行,可能得到记录数 Extra: Using where #额外信息 比如排序方式 如filesort等 1 row in set (0.00 sec)
4、确定问题采取相应的措施
优化sql语句
① 优化group by 语句 使用group by子句后 系统会默认进行排序 如果不需要进行排序,则建议加上 order by null