Mysql索引优化2

分页查询优化

原sql

select * from employee limit 9000,5;

优化1：前提是主键是连续且自增

select * from employee where id >9000 limit 5;

原sql

select * from employee order by name limit 90000,5;

优化2：根据非主键字段排序

select * from employee a inner join (select id from employee order by name limit 90000,5) ed on e.id=ed.id;

​

Join关联查询优化

嵌套循环连接 NLJ算法

T2表  100 条数据

T1表 10000条数据

A字段是索引

select * from t1 inner join t2 on t1.a=t2.a;

此时sql的大致流程是

1、把t2的读取一行数据

2、从第一步的数据中，取出关联字段a，到表t1中查找

3、取出表t1中满足条件的行，跟t2中获取到的结果合并，作为结果返回给客户端

4、重复上面三步

基于块的嵌套循环连接  BNL算法，在没有索引的时候采用BNL算法比较快

B字段不是索引

select * from t1 inner join t2 on t1.b=t2.b;

此时sql的大致流程是

1、把t2的所有数据放到join_buffer中

2、把表t1中每一行取出来，跟join_buffer中的数据做对比

3、返回满足join条件的数据

整个过程对表t1和t2都做了全表扫描，并且join_buffer中数据是无序的，所以t1对比数据的时候是需要在内存当中判断100*10000次

如果join_buffer中存放不下所有的t2表中的数据

join_buffer的默认大小是256k，如果放不下表t2的所有数据的话就会分段存放t2的数据去和join_buffer中的数据比较

关联字段没有索引为什么要选择BNL而不是选择NLJ算法呢？

NLJ算法的话是需要做100万次的磁盘扫描

而BNL的话磁盘扫描次数会少很多，主要是在内存中做100万次判断，因此BLJ算法性能更高

对于关联sql的优化

关联字段加索引

小表驱动大表也就是说小表先执行（小表：参与数据的关联集）

straight_join

straight_join 可以指定哪张表先执行，不过这个方法只适用于inner join 方法

Select * from t2 straight_join t1 on t2.a=t1.a; 这样表示t2这张表先执行

in和exists的优化

原则：小表驱动大表，即小的数据集驱动大的数据集

当B表的数据集小于A表的数据集时，in优于exists

Select * from A where id in(select id from B)

等价于:

For (Select  id from B ){

select * from A where A.id = B.id

}

当A表的数据集小于B表的数据集时，exists优于in

Select * from A where exists(select 1 from B where A.id=B.id)

注意： exists也可以用join来替代

count(*)查询优化

Select count(1) from A

Select count(id) from A

Select count(name) from A

Select count(*) from A

哪一条sql执行计划一样，这四个sql执行效率都差不多

count(*) = count(1) > count(字段) > count(主键id)  字段有索引，二级索引存储数据比主键索引少，所以count(字段) > count(主键id)

count(*)是例外，mysql不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高

常见优化方法

1、将总数维护到redis中，不一定缓存和数据库值一致

2、增加数据库计数表

Mysql数据类型选择

1、确定合适的大类型：数字、字符串、时间

2、确定具体的类型：有无符号，取值范围、变长定长

整型

Tinyint 1字节   有符号-128-127     无符号  0 -255

smallint 2字节

mediumint 3字节

int或integer  4字节

bigint 8字节

int(11) 长度

显示宽度，加一个填充0

’id’ int(5) unsigned zerofill 就有用了  00005

时间

小公司用timestamp 4个字节，占用的空间小，到了2038年就不能用了

大公司用datetime  8 个字节

字符串

Char 定长字符串，char(n)当插入的字符串实际长度不足n时，会插入空格进行补充保存

varchar：变长字符串，varchar(n) 的n代表最大列长度