mysqlOptimization2 - juedaiyuer/researchNote GitHub Wiki

#SQL优化#

#查看服务器状态信息
#global 自数据库启动至今的统计结果
show [session|global] status

#当前session中所有统计参数的值
#作用于所有存储引擎
#com_select 执行select操作的次数
#com_insert 对于批量插入,只累加一次
#com_update com_delete

show status like 'com_%';

#只针对Innodb存储引擎
show status like 'innodb_rows_%';

| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 4     |
| Innodb_rows_read     | 20    |
| Innodb_rows_updated  | 0     |

#试图连接MySQL服务器的次数
connections

#服务器工作时间
uptime

#慢查询的次数
slow_queries

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

#慢查询日志,在查询结束后才记录
--log-slow-queries[=file_name]

#实时查看
show processlist

##通过explain分析##

表的全表扫描导致效率的不理想,创建索引

##索引问题##

MyISAM存储引擎的表的数据和索引是自动分开存储的

InnoDB存储引擎的数据和索引存储在同一个表空间里面,但是也可以设定分开存储

###使用索引###

索引的前缀特性

复合索引时,可以匹配索引的最左侧,如果where条件使用复合索引的最右侧,索引不会被用到

使用like的查询,%放于首位索引不会被使用

like后面跟着一个列的名字,索引也不会被使用

如果对大的文本进行搜索,使用全文索引而不用使用like '%...%'

如果列名是索引,使用col_name is null将使用索引

###存在索引但不使用索引###

#如果使用索引比全表扫描更慢,则不使用索引
#譬如一个列均与分布在某一个区间[m,n]
select * from tb_name where key_part >m and key_part < n;


#如果使用memory/heap表并且where条件中不使用=进行索引,则不使用索引

#用or分割开的条件,前面列有索引,后面列没有索引,则不使用索引

#如果列类型时字符串,where条件中把字符常量值用引号,否则不使用索引

###查看索引使用情况###

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 8     |
| Handler_read_key      | 16    |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 4     |
| Handler_read_rnd_next | 110   |
+-----------------------+-------+
7 rows in set (0.00 sec)

Handler_read_key:索引正在工作的数值

Handler_read_rnd_next:如果进行大量的表扫描,数值会很高

##两个简单使用的优化方法##

analyze,check,optimize执行期间对表进行锁定

###定期分析表和检查表###

#用于分析和存储表的关键字分布	
#对于MyISAM表,本语句同 myisamchk -a相当
analyze [local|no_write_to_binlog] table tb_name [,tb_name] ...

#检查表
#对于MyISAM表,关键字统计数据被更新
check table tb_name [,tb_name] ... [option] ... option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

###定期优化表###

#使用该命令的情况:
#1.删除表的一大部分
#2.对含有可变长度行的表(含有VARCHAR,BLOB或TEXT列的表)进行更改
#作用:将表中的空间碎片进行合并
#只对MyISAM,BDB,InnoDB有效
optimize [local|no_wirte_to_binlog] table tb_name [,tb_name] ...

##常用SQL的优化##

###大批量插入数据###

#导入大量数据到一个非空MyISAM	表时,使用下面的命令
#DISABLE KEYS和ENABLE KEYS用来打开或者关闭MyISAM表非唯一索引的更新
#导入大量数据到一个空的MyISAM表,默认就是先导入数据,然后创建索引,所以不用设置
ALTER TABLE tb_name1 DISABLE KEYS;
loading the data
ALTER TABLE tb_name1 ENABLE KEYS;


#InnoDB
#导入的数据按照主键的顺序排列,可以提高效率
#关闭唯一性校验,SET UNIQUE_CHECKS=0,导入结束后恢复唯一校验
#关闭自动提交 SET AUTOCOMMIT=0,导入结束后恢复

###优化INSERT语句###

#尽量使用多个值表的insert语句
#大大缩短客户端与数据库之间的连接,关闭等消耗
insert into test values(1,2),(1,3),(1,4)...	

#如果从不同客户插入很多行,能通过使用insert delayed语句得到更高的速度

#将索引文件和数据文件分在不同的磁盘上存放

#批量插入,可以增加bulk_insert_buffer_size变量的值提高速度,仅对MyISAM表使用

#当从一个文本文件装载一个表时,使用LOAD DATA INFILE,比使用INSERT语句快20倍

###优化GROUP BY语句###

默认情况下,GROUP BY会对字段进行排序,想要避免排序结果的消耗,使用ODER BY NULL禁止排序

###优化嵌套查询###

#从sales表中找到那些在company2表中不存在的所有公司的信息
explain select * from sales2 where company_id not in (select id from company2)\G;

#使用连接优化
#尤其是company2表中id有索引
explain select * from sales2 left join company2 on sales2.company_id = company2.id where sales2.company_id is null \G;

###MySQL如何优化OR条件###

对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引

###使用SQL提示###

#强制生成一个提示结果集,生成完成后,所有表上的锁均被释放
#遇到表锁定问题或要花很长实践将结果传给客户端时有用
SELECT SQL_BUFFER_RESULTS * FROM ...

#USE INDEX
#可以不再考虑其它可用的索引
explain select * from sales2 use index(ind_sales2_id) where id = 3\G;

#忽略一个或者多个索引
explain select * from sales2 ignore index(ind_sales2_id) where id = 3\G;

#强制使用一个特定的索引
explain select * from sales2 where id > 0 \G; 默认全表扫描,而不使用索引

explain select * from sales2 force index(ind_sales2_id) where id > 0 \G; 强制使用索引

##source##

  • MySQL数据库开发,优化与管理维护(PDF:242.18章)