mysql优化技术

最新推荐文章于 2024-08-25 20:30:00 发布

李荣强

最新推荐文章于 2024-08-25 20:30:00 发布

阅读量316

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/li951418089/article/details/50392720

版权

数据库专栏收录该内容

14 篇文章 0 订阅

订阅专栏

MySql数据库的优化技术;
1 表的设计合理化,符合3NF,
2 添加适当的索引(index):普通索引;唯一索引,主键索引,全文索引;
3 分表技术:水平分割,垂直分割;
4 读写[update,delete,insert]分离技术;
5 存储过程;
6 对mysql配置优化[配置最大并发数;]
100默认,1000比较合理;
调整缓存大小:
7 mysql服务器硬件的升级;
8 定时的去清楚不需要的数据,定时进行碎片整理; optimize table table_name;//清理碎片指令;

数据库的三层结构:程序连接--->DBMS-->.dbms操作数据库的文件;
为什么说使用存储过程的效率要更高呢?
1 首先我们dao层通过数据库驱动发出sql语句
进入的是DBMS,由dbms去将sql语句编译后,在去操作数据库文件;
而编译的过程是很费时间的;
那么存储过程就是我们事先在数据库中写好的程序,
无需在编译,客户端调用时直接调用即可;
无需编译,提高了数据库的访问效率;但是他的移植性不好
mysql和oracle在存储过程上差距很大;

数据库表设计的三范式:NF:Normal Form
表的范式:是首先符合1NF,才能满足2NF,进一步满足3NF;
1NF:表的列具有原子性,不可在分解,列不可在分解;
2NF:表中的记录是唯一的,通过主键来约束;
3NF:表的记录可以通过其他表关联得到,却仍然保留在主表中;
比如员工和部门的关系:
一个部门有多个员工,
对应员工表来说,员工中只要存有部门的id就可以了,即外键;
如果员工表中还有部门名称,部门描述,等其他部门的信息时,
就是数据的冗余,不满足三范式;

反三范式:考虑到实际情况,有些数据的冗余是有必要的;
冗余要有足够的理由;

如何从一个大项目中,迅速定位执行效率低的sql语句:
定位慢查询;

show status:
常用的参数:
show status like 'uptime';显示数据库的启动时间;以秒为计算单位;
show status like 'com_select;显示执行select语句的次数;
show status like 'com_delete';显示delete语句执行的次数;
show status like 'com_update';显示update语句执行的次数;
前面可以加参数:比如
show golbal/session like 'com_select';global表示显示全局的select语句次数
即mysql启动的时算起;而session表示当前的回话或者说窗口;
默认的就是session范围的;

show status like 'connections';//显示当前连接数;

show status like 'slow_queries';//显示慢查询次数;
如何定位慢查询:
默认情况下,mysql认为10秒才是一个慢查询,可以
设置mysql的慢查询下限;

show variables like 'long_query_time';
show variables like 'long_query_time';
set variables query_long_time=2;//

设置慢查询下限为2秒,超过2秒就是慢查询;

set long_query_time=1;

delimiter $$
构建大表:
显示慢查询的语句条数,也就是执行时间超过我们预定义的时间的sql语句条数;
但是我们要做到定位到具体的某一条sql语句才有用;
我们可以将慢查询的sql语句记录到我们的一个日志中;
在默认情况下,我们的mysql不会记录慢查询.
通过一个命令:可以让他记录日志;

添加索引:主键索引,唯一索引,全文索引,普通索引;

添加:
当一张表把某个列设置文主键后,就拥有了主键索引;主键所在的列就是主键索引;

查询索引:
desc tablename--desc role;
show indexes from role;
show keys from role;

添加主键指令:
alter table add primary key (id);

show index from emp\G
显示emp表的索引:
Index_type:BTREE;
索引文件是根据二叉树算法来的;

二叉树右边大,左边小; 算法的复杂度:log2N:
会记录磁盘的物理位置,如果直接拷贝索引文件;
需要重新建过,因为不同的磁盘,物理位置肯定不一样;
如果不重新建索引,虽然数据可以查出来,但是效率很低;
create index indexname on table column:
for example:
create index ename_index on emp (ename);
创建全文索引:
全文索引,主要是针对文本文件,文本的检索,比如文章,
全文索引仅仅针对MYISAM数据库存储引擎有用,
innodb无效;

CREATE TABLE article(
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title varchar(200),
body text,
FULLTEXT (title,body)
)engine myisam charset utf8;

//仅仅只对字符串,varchar,char,text等文本型数据由用,int型不能创建全文索引,同时要求使用的数据库引擎为myisam;innodb

不行;
> INSERT INTO article (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
错误用法:
select * from articles where body like '%mysql%';//不会使用到全文索引;

select * from articles WHERE MATCH(title,body) against
('database');

explain select * from articles where match(title,body)
against('database');

注意:
1:全文索引只针对myisam生效
2:针对英文生效,--->sphinex技术处理中文;
3 : match (字段名) against('关键字')
4 :停止词的概念:因为在一个文本中创建索引是一个无穷大的数,因此
对于一些常用词和字符,就不会创建索引,这些词称之为停止词;

唯一索引:当表中某个字段添加了unique的约束时,自动创建唯一索引;
unique字段可以为null;
null永远不等于null;
主键字段:不能为null,也不能重复;

创建唯一索引;
create unique index myuniqueindex on test (name);

create index indexname on tablename (column);
比如:
create index age_index on test2 (age);
删除索引:alter table test2 drop index age_index;
alter table tablename drop index indexname;//适用于所有索引;

alter table tablename drop primary key;//删除主键,也就干掉了
主键索引;

修改索引:先删除后添加;

索引的适用场景:
索引的代价:
1 占用磁盘空间,

2 影响DML,曾删改变慢,需要更新索引表;

3 索引文件不可直接复制,因为里面存储的是磁盘的物理位置,而不是逻辑位置;
如果真要这么做,需要重建索引

存储引擎: MyISAM:InnoDB: BTREE二叉树事件结构;

1:不会出现在where 子句中字段不该创建索引
2:区分度不大的字段不适合建立索引;
3: 频繁变化的字段不适合添加索引,每次更新都得更新索引;

使用索引的注意事项:

create index myindex on dept (dname,loc);

explain select * from dept where dname='dname';

explain:
出现的各个参数的含义:
id:查询的序列号
select_type:查询类型
table:查询的表名
type:扫描的方式:all,代表全表扫描,应该尽量避免;如果使用到了索引;
type就是ref形式,否则就是all,即全表扫描;
possible_keys:可能使用到的索引:
key:实际使用的索引;
key_len:
ref:
rows:该sql语句扫描了多少行,可能得到的记录;
extra:额外信息,比如排序方式

create index myindex on dept (dname,loc);
select * from dept where dname='aaa';//这个会使用到索引;
而
select * from dept where loc='bbb';//这个不会使用到索引;
原因是,对于多列索引,只用使用到左边的列才有可能使用到索引;

同时对于模糊查询,即使你的sql的where条件引用到多列索引的最左边的字段
而你的%或者_出现在最左边,就不会使用到索引;
比如
select * from emp where dname like '%aa';//不会使用到索引,type为all,全表扫描;
select * from emp where dname like 'aa%;;//可以使用到索引;
如果确实有这种需求时,可以使用全文索引;

如果你的sql语句中where条件出现or判断;
那么or中出现的所有条件字段都必须添加索引,否则不会使用到索引;
尽量避免使用or关键字;

如果列是字符串类型必须要用''号引起来;
如果sql发现全表扫描效率比使用index更高,则用全文索引;

如何查看索引的使用情况:
show status like 'Handler_read%';
Handler_read_key:这个值如果越大,表示使用到索引的频率越高:
Handler_read_rnd_next:这个值越小越好;

sql语句的小技巧

1 在使用group by分组查询时,默认分组后,还会排序;
所以如果你不想排序时,可以加 order by null进行指定,提高效率
在explain中的参数中extra:using filesort进行查看;
2 有些情况下,可以使用连接来替代子查询,因为使用join,MYSQL不需要
在内存中创建临时表;

比如:
select * from dept,emp where dept,deptno=emp.deptno;
---简单处理方式;
select * from dept left join emp on dept.deptno=emp.deptno
---左连接查询;

3 选择合适的MySQL存储引擎;
a:myisam,如果表对事务要求不高,同时是以查询和添加为主的,
我们考虑使用myisam存储引擎;-----BBS中的帖子表;---支持全文搜索;
b:INNODB:存储:对事务要求高,保存的数据都是重要数据,
必须要使用Innodb;
c:Memory存储,比如数据变化比较频繁,不需要入库,同时又频繁的查询和查询,
我们考虑使用Memory

myisam和innodb的区别:
1 批量插入语句,myisam效率高,
2 myisam支持全文索引,而innodb不支持
3 事务的安全性机制,innodb级别更高;事务机制
4 锁机制,myisam是表级别的锁机制,而innodb是行级别的锁;
5 MyISam不支持外键;而Innodb有外键;

memory存储:比如我们的数据更新频繁不需要入库,可以使用它,效率高;
一旦服务器关闭数据丢失,因为他的数据是放在内存中的;

对应存储引擎是MyISAM的数据库:如果经常做删除和修改的操作,要定时执行
optimize table table_name;功能对表进行碎片管理;
如果是truncate表,数据和结构全部丢失;
技术就是窗户纸-->经常和技术牛人一起,你就会牛B;

mysqldump -u root -p123456 temp dept>d:\temp.bak
注意以管理员身份打开cmd窗口;
mysqldump -u root -p123456 temp>d:\temp.bak;

把备份指令写入到bat文件,然后通过任务管理器去定时调用;

使用定时器完成备份任务:
路径中最好没有空格;

E:\MySQL\bin\mysqldump -u root -p123456 temp >d:\temp.bak;

这两天把以前的东西复习复习,把sql这块完全搞定;

关系型数据库:mysql,db2,oracle,sybase,sql server;
非关系型数据库:分布式数据库Hbase,Hadoop
NoSQL:数据库:MongoDB:特点是面向文档;

添加普通索引:
create index name_index on xiaobao (name);//给xiaobao这张表的name字段添加一个名为name_index的索引;
删除索引:
alter table drop index name_index;//删除xiaobao这张表中名称为name_index的索引;
对应主键索引可直接删除主键;
添加主键:alter table xiaobo add primary key(id);//给xiaobao这张表这张字段id为主键;
删除主键索引:alter table xiaobao drop primary key;

查看某条语句的执行情况;
explain select * from article where body like '%mysql%'; //这样没有使用到全文索引;
select * from article where match(body) against('database');

"C:\Program Files\QvodPlayer\QvodPlayer.exe" G:\java进阶\06-动态代理实现过滤器.avi
这个批处理命令是打开那个文件,用快播软件
添加主键的意义:主键索引;

阅读(47) | 评论(0)

喜欢推荐转载

历史上的今天

热度

在LOFTER的更多文章

this.p={  m:2,
              b:2,
              loftPermalink:'',
              id:'fks_087064081087082070082094<wbr>081064072084080068086081<wbr>080065084095',
              blogTitle:'mysql优化技术',
              blogAbstract:'MySql数据库的优化技术;1 表的设计合理化,符合3NF,  <wbr>2 添 适当的索引(index):普通索引;唯一索引,主键索引,全文索引;3 分表技术:水平分割,垂直分割;4 读写[update,delete,insert]分离技术;5 存储过程;6 对mysql配置优化[配置最大并发数;]100默认,1000比较合理;调整缓存大小:7 mysql服务器硬件的升级;8 定时的去清楚不需要的数据,定时进行碎片整理; <wbr> optimize table table_name;//清理碎片指令;数据库的三层结构:程序连接--->DBMS-->.dbms操作数据库的文件;为什么说使用存储过程的效率要更高呢?1 首先我们dao层通过数据库驱动发出sql语句',
              blogTag:'',
              blogUrl:'blog/static/21727620920139131616349',
              isPublished:1,
              istop:false,
              type:0,
              modifyTime:1380613094703,
              publishTime:1380611776349,
              permalink:'blog/static/21727620920139131616349',
              commentCount:0,
              mainCommentCount:0,
              recommendCount:0,
              bsrk:-100,
              publisherId:0,
              recomBlogHome:false,
              currentRecomBlog:false,
              attachmentsFileIds:[],
              vote:{},
              groupInfo:{},
              friendstatus:'none',
              followstatus:'unFollow',
              pubSucc:'',
              visitorProvince:'',
              visitorCity:'',
              visitorNewUser:false,
              postAddInfo:{},
              mset:'000',
              mcon:'',
              srk:-100,
              remindgoodnightblog:false,
              isBlackVisitor:false,
              isShowYodaoAd:false,
              hostIntro:'JAVA软件工程师,有扎实的Java基础,熟悉JavaEE技术,对框架的底层原理熟悉,学 能力强。',
              hmcon:'0',
              selfRecomBlogCount:'0',
              lofter_single:''
            }

{if x.visitorName==visitor.userName} ${x.visitorNickname|escape}

{else}

{/if}

{if x.moveFrom=='wap'} {elseif x.moveFrom=='iphone'} {elseif x.moveFrom=='android'} {elseif x.moveFrom=='mobile'} {/if} ${fn(x.visitorNickname,8)|escape}

{/if} {/list}

${a.selfIntro|escape}{if great260}${suplement}{/if}

{/if}