如何使用全文索引:
错误用法:
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 $表名”);
技术就是窗户纸.->经常和技术好人。 |
