mysql优化2
一、索引的概述
为什么要去用索引?
因为提高了查询效率。
为什么加了索引就能提高查询效率?
有关索引的实现原理,就是折半查找,B+tree B加树。
索引的分类:
主键索引:主键索引就是主键 primary key 保证唯一,不重复。
唯一索引:
组合索引
全文索引
普通索引
二、主键索引
创建表 并加上索引
在需要经常查询的字段上,一定要加上索引,效率提高1000倍。
-- 创建主键索引 只要加了
create table aaa
(id int unsigned primary key auto_increment ,
name varchar(32) not null default '');
-- 创建一个没有加索引的表
create table bbb (id int , name varchar(32) not null default '');
-- 给没有索引的表加上主键索引
alter table 表名 add primary key (列名);
ALTER TABLE bbb add PRIMARY KEY (id)
select * from emp where empno = '1389058'
-- 没有加索引查询时间是 0.240s
-- 加上主键索引
alter table emp add primary key (empno);
-- 加上主键索引查询时间是: 0.003s 大概效率提升了1000多倍
-- 注意经常查询的字段一定要加上索引。效率提高很多倍
三、索引实现原理
索引底层采用排序算法 B-tree。算法 折半查找。
没有加索引的表,查找数据的时候需要对表进行全表扫描。
加上索引的时候,会先生成一个索引文件,
B-tree怎么查找?
首先会先取一个中间数。
简单原理图:
索引有什么缺点:做增加、删除的时候,索引文件也需要更新。
所以做增加删除的时候效率低。
四、唯一索引
加上字段unique 就是唯一索引,表示这个字段是唯一、不重复的,
不能存有重复的空字符串。
如:
用途:在数据不允许重复的时候可以,但是这个有时候又被主键索引代替了。
-- 创建唯一索引
create table ddd(id int primary key auto_increment , name varchar(32) unique);
五、普通索引
-- 创建普通索引
create table ccc(
id int unsigned,
name varchar(32)
)
-- 调用这个创建普通索引
create index ccc_index on ccc (name);
六、执行计划查看 explain
这里使用的是加上了索引字段来查询
这里使用了没有加上索引字段来查询
使用索引文件来查询了
七、使用全文索引的时候注意点:
1、不要使用like
-- 这样用,索引是不会生效的
explain select * from articles WHERE title like '%mysql%';
explain select * from articles where match(title,body) against ('database')
企业实际中不会采用表的全文索引。采用第三方框架 slor、es
八、索引的优缺点
索引的优缺点:
优点:提高程序效率
缺点:增加、删除慢、索引文件需要更新,增加磁盘内存。
什么字段适合加索引?
答:1、条件查询次数比较多、值不同的。
2、建立索引where条件需要查询的,并且值非常多的不同,
唯一几个值不需要建立索引。
索引使用注意事项
1、如何使用组合索引
-- 创建组合索引
alter table dept add index my_ind (dname,loc);
explain select * from dept where dname = 'IMCC2bFBSr';
explain select * from dept where loc = 'noG6gRna';
1、第一个可以不用第二个一起作为条件查找 -- 会使用索引查找
2、第二个条件,不使用第一个条件查找 -- 不会使用索引
3、使用第一个条件和第二个条件一起查找的时候 --会使用索引
4、条件上加like会怎么样?
使用索引的时候,不要使用like "%hhhh%" -- 会使用全表扫描
使用索引的时候 like "hhh%" --会使用索引
开头不加%都会使用索引。
5、使用or的时候,条件必须加上索引,
只要有一个条件不加索引,就不会使用索引来进行查找了。
6、判断是否为null的时候,使用is null 不要使用=null.
7、使用group by 分组的时候,不会使用索引,,会使用全表扫描。
并会自动排序。
8、分组需要效率高,需要禁止排序,order by null
9、select * from userId >= 101
select * from userId >100
不要使用>= 因为会做两次全表扫描的。
10、 int 和 not in 也要慎用,会导致全表扫描。、
11、查询量比较大的时候使用分表、分页、缓存
具体如下:
九、MySQL的存储引擎
innodb: 用的最多。有事务机制,保证数据的一致性。
myisam:
memory:
innodb与myisam区别?
答:做批量添加的时候,myisam效率高
innodb 事务机制非常安全
锁机制:myisam 表锁
innodb行锁。
数据结构:
myisam 支持全文检索
innodb 全文检索和b-tree数据结构。
十、mysiam使用注意事项
mysiam:要定时清理碎片化,否则不会删除。