AutoCAD 3DMAX C语言 Pro/E UG JAVA编程 PHP编程 Maya动画 Matlab应用 Android
Photoshop Word Excel flash VB编程 VC编程 Coreldraw SolidWorks A Designer Unity3D
 首页 > MySQL

韩顺平版:mysql优化详解

51自学网 2016-09-18 http://www.51zixue.net

如何使用全文索引:
错误用法:
select * from articles where body like ‘%mysql%’; 【不会使用到全文索引】
证明:
explain  select * from articles where body like ‘%mysql%’
 
正确的用法是:
select * from articles where match(title,body) against(‘database’); 【可以】
 
☞ 说明:
1.       在mysql中fulltext 索引只针对 myisam生效
2.       mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
3.       使用方法是 match(字段名..) against(‘关键字’)
4.       全文索引一个 叫 停止词,  因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建,这些词,称为停止词.
 
1.4唯一索引
①当表的某列被指定为unique约束时,这列就是一个唯一索引
create table ddd(id int primary key auto_increment , name varchar(32) unique);
 
这时, name 列就是一个唯一索引.
 
unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容,则不能重复.
 
主键字段,不能为NULL,也不能重复.
 
②在创建表后,再去创建唯一索引
create table eee(id int primary key auto_increment, name varchar(32));
 
create unique index 索引名  on 表名 (列表..);
 
 
 
2.       查询索引
 
desc 表名 【该方法的缺点是: 不能够显示索引名.】
show index(es) from 表名
show keys from 表名
 
3.       删除
 
alter table 表名 drop index 索引名;
 
如果删除主键索引。
alter table 表名 drop primary key       [这里有一个小问题]
 
 
 
4.       修改
 
先删除,再重新创建.
 
 
 
为什么创建索引后,速度就会变快?
 
原理示意图:

 
.
 
索引使用的注意事项
 
索引的代价:
1.       占用磁盘空间
2.       对dml操作有影响,变慢
在哪些列上适合添加索引?

 
总结: 满足以下条件的字段,才应该创建索引.
a: 肯定在where条经常使用 b: 该字段的内容不是唯一的几个值(sex) c: 字段内容不是频繁变化.
 
 
使用索引的注意事项
 
把dept表中,我增加几个部门:
 
alter table dept add index my_ind (dname,loc); //  dname 左边的列,loc就是右边的列
 
说明,如果我们的表中有复合索引(索引作用在多列上), 此时我们注意:
1,  对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。
 
explain select * from dept where loc='aaa'/G
就不会使用到索引
 
2,对于使用like的查询,查询如果是  ‘%aaa’ 不会使用到索引
       ‘aaa%’ 会使用到索引。
 
比如: explain select * from dept where dname like '%aaa'/G
不能使用索引,即,在like查询时,关键的 ‘关键字’ , 最前面,不能使用 % 或者 _这样的字符., 如果一定要前面有变化的值,则考虑使用 全文索引->sphinx.
 
3.       如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字
 
select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45
 
4.       如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是,如果列是字符串类型,就一定要用 ‘’ 把他包括起来.
 
5.       如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
 
explain 可以帮助我们在不真正执行某个sql语句时,就执行mysql怎样执行,这样利用我们去分析sql指令.

 
如何查看索引使用的情况:
show status like ‘Handler_read%’;
 
大家可以注意:
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
       handler_read_rnd_next:这个值越高,说明查询低效。
 
 
 sql语句的小技巧
 
1.       在使用group by 分组查询是,默认分组后,还会排序,可能会降低速度.
 
比如:

在group by 后面增加 order by null 就可以防止排序.
2.       有些情况下,可以使用连接来替代子查询。因为使用join,MySQL不需要在内存中创建临时表。
 
select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
 
select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接,更ok!]
 
如何选择mysql的存储引擎
 
在开发中,我们经常使用的存储引擎 myisam / innodb/ memory
myisam 存储: 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表,回复表.
 
INNODB 存储: 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.
 
 
问 MyISAM 和 INNODB的区别
1. 事务安全
2. 查询和添加速度
3. 支持全文索引
4. 锁机制
5. 外键 MyISAM 不支持外键, INNODB支持外键. (在PHP开发中,通常不设置外键,通常是在程序中保证数据的一致)
 
 
 
Memory 存储,比如我们数据变化频繁,不需要入库,同时又频繁的查询和修改,我们考虑使用memory, 速度极快.
 
 
 
 
如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理
 
举例说明:
create table test100(id int unsigned ,name varchar(32))engine=myisam;
 
insert into test100 values(1,’aaaaa’);
insert into test100 values(2,’bbbb’);
insert into test100 values(3,’ccccc’);
 
我们应该定义对myisam进行整理
optimize table test100;
 
mysql_query(“optimize tables $表名”);
 
技术就是窗户纸.->经常和技术好人。

 
上一篇:mysql数据库的备份和恢复  下一篇:SQL左联右联内联全联基础语法