关于mysql数据库以及sql语句的优化技术

最新推荐文章于 2022-07-30 11:10:54 发布

wdj3166

最新推荐文章于 2022-07-30 11:10:54 发布

阅读量514

点赞数

分类专栏： # 数据库

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

SQL语句优化

SQL优化的一般步骤

①通过show status命令了解各种SQL的执行频率。

②定位执行效率较低的SQL语句-（重点select）

③通过explain分析低效率的SQL语句的执行情况

④确定问题并采取相应的优化措施

MySQL客户端连接成功后，通过使用show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。
下面的例子：
show status like ‘Com_%’;
其中Com_XXX表示XXX语句所执行的次数。
重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

还有几个常用的参数便于用户了解数据库的基本情况。
Connections：试图连接MySQL服务器的次数
Uptime：服务器工作的时间（单位秒）
Slow_queries：慢查询的次数 (默认是慢查询时间10s)

在默认情况下mysql不记录慢查询日志，需要在my.ini中开启

long-query-time=1
log-slow-queries="G:/amp/MySQL/log/long-query.log"

long-query.log 为自定义日志名

或在启动的时候指定bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句。
查看超时时间show variables like ‘long_query_time’;默认为10秒太多了
设置超时时间set long_query_time=2;设置为2秒

查看慢查询日志：找到my.ini设置的日志文件即可

如果启动时设置：默认为数据目录data中的host-name-slow.log。低版本的mysql需要通过在开启mysql时使用- -log-slow-queries[=file_name]来配置

通过explain分析sql语句

Explain select * from emp where ename=“zrlcHd”
会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描出的行数(估算的行数)
Extra:执行情况的描述和说明

可以根据这些信息优化sql语句

常用SQL优化

优化group by 语句
默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

有些情况下，可以使用连接来替代子查询。
因为使用join，MySQL不需要在内存中创建临时表

如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引

当只一行数据使用limit 1

对mysql优化时一个综合性的技术，主要包括

a: 表的设计合理化(符合3NF)

b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引

没有索引时会遍历整个表数据全部检索完毕返回结果集

添加索引：二叉树算法->索引能够快速定位

BTREE

索引是以文件形式存储的，记录记录的磁盘位置，

若数据库迁移要重新生成索引

使用索引时注意：

下列几种情况下有可能使用到索引：
1，对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
2，对于使用like的查询，左匹配，最左边的不能是变化的，

如果like ‘%name‘ 不会使用索引

如果like ‘_name‘ 不会使用索引

如果 like ‘name%‘ 会使用索引

如果一定要前面有变化值则考虑使用全文索引 spihnx

下列的表将不使用索引：
1，如果条件中有or ，所有的条件都要建索引才会使用索引建议尽量避免使or。
2，对于多列索引，不是使用的第一部分，则不会使用索引。
3，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’)
4，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

查看索引的使用情况
show status like ‘Handler_read%’;
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

哪些列需要创建索引

·较频繁的作为查询条件字段应该创建索引

select * from emp where empno = 1

·唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex = '男'

·更新非常频繁的字段不适合创建索引

select * from emp where logincount = 1

·不会出现在WHERE子句中字段不该创建索引

c: 分表技术(水平分割、垂直分割)

水平分割：

如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能，那么把表按月拆分成12个，每个查询只查询一个表就够了。如果非要按照地域来分，即使把表拆的再小，查询还是要联合所有表来查，还不如不拆了。所以一个好的拆分依据是最重要的。UNION

垂直分割：

有些表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。 (JOIN)

d: 读写[写: update/delete/add]分离

如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。