Mysql索引优化
表样例:
-- 创建表
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
--插入测试数据
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
-- 插入一些示例数据
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();
注意: 表 employees 的联合索引字段为: name,age,position
1.联合索引第一个字段用范围不会走索引
explain select * from employees where name >'Lilei' and age =22 and position ='manager';
联合索引第一个字段就用范围查找不会走索引，mysql内部判断第一个字段就用范围，结果集很大，回表效率不如全表扫描.所以使用全表扫描(type:ALL)
---------------------------------------------------------------------------------分割线--------------------------------------------------------------------------------
2.强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高.
---------------------------------------------------------------------------------分割线--------------------------------------------------------------------------------
3.覆盖索引优化
索引覆盖: 指查询语句的结果只需要从索引中获取,不需要使用索引到表中查询获取
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
---------------------------------------------------------------------------------分割线--------------------------------------------------------------------------------
4.in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position='manager';
---------------------------------------------------------------------------------分割线--------------------------------------------------------------------------------
5.like KK% 一般情况都会走索引
索引下推:mysql5.6引入 对于联合索引场景的优化操作. 先对第一个索引进行查询,然后再比较联合索引剩余的索引字段.
对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数.
可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。
什么情况 索引下推失效?
Mysql认为范围查找过滤的结果集过大时,与全表扫描进行效率比较时,发现跟全表扫描效率差不多,Mysql会考虑使用全表扫描.
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
---------------------------------------------------------------------------------分割线--------------------------------------------------------------------------------
Mysql如何选择合适的索引
EXPLAIN select * from employees where name > 'a';
从type字段的内容可以看出使用的全表扫描.
因为where 条件name 是name,且符合最左前缀原则.就进行修改.
EXPLAIN select name,age,position from employees where name > 'a' ;
Mysql优化方式
1.Order by与Group by优化
Demo1
explain select * from employees where name='Lilei' and position='dev' order by age;
利用最左前缀原则:中间字段不能断,所以查询用到了索引name,从key_len=74 可以算出.
age索引在进行排序过程中使用,体现在Extra字段中没有using filesort;
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Demo2
explain select * from employees where name='Lilei' order by position;
使用了索引字段name,排序的时候跳过了字段age,所以 Extra 字段中显示了Using filesort.
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Demo3
explain select * from employees where name='Lilei' order by age, position;
查询的时候使用索引name,排序的时候按顺序使用age,position,所以Extra 没有 Using filesort.
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Demo4
explain select * from employees where name='Lilei' order by position, age ;
查询使用索引name,但是进行排序的时候使用将age和position的位置颠倒了,所以Extra 中有Using filesort
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Demo5
explain select * from employees where name='Lilei' and age =18 order by position, age ;
因为age 为常量,在排序中被优化,索引没有出现颠倒, 所以Extra中没有 Using filesort
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Demo6
explain select * from employees where name='Lilei' order by age asc ,position desc ;
虽然字段排序顺序与索引顺序相同,但是order by默认升序(asc),position使用降序(desc) 使得索引排序方式不同,所以Extra 中有 Using filesort.
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Demo7
explain select * from employees where name in ('Lilei','zhuge') order by age ,position ;
对于排序来说,多个相等条件也是范围查询,所以Extra 中Using filesort.
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Demo8
explain select * from employees where name >'a' order by name ;
使用范围查询,所以mysql认为使用全表扫描要比索引快,所以在进行排序的时候也直接使用 Using filesort
优化方式,使用索引覆盖
explain select name,age,position from employees where name >'a' order by name ;
对比: group by 底层是基于order by进行排序,然后再进行分组.
order by 优化方案:
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
2、order by满足两种情况会使用Using index。 1) order by语句使用索引最左前列。 2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。
对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，
能写在where中 的限定条件就不要去having限定了。
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
分表查询Demo1
使用主键字段排序
explain select * from employees limit 10000,10;
优化后
select * from employees where id > 90000 limit 5;
mysql 会查询1w条数据后,再取出这1w条数据后面10条数据;
该方式 存在一定缺陷,如果前面的id被删除后,展示的数据就与实际要求不相同.
-----------------------------------------------------------------------------------分割线---------------------------------------------------------------------
分表查询Demo2
使用非主键字段排序
explain select * from employees ORDER BY name limit 90000,5;
优化后
explain select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
mysql在进行分析之后,判定使用全表查询效率比使用name索引效率高,所以使用全表查询.
排序时,排序返回的字段越少,进行查询的效率越高
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
索引设计原则:
1.代码先行,索引后上;
2.尽量创建联合索引.单独的索引占用的较多内存空间,而且单独的索引作用没有联合索引大.
3.不要在基数类型字段创建索引,如,性别这类型的字段.基数是2,创建索引的作用不大.
4.长字符串可以采用前缀索引.如 name字段长度为varchar(255),在创建联合索引的时候选择前缀一部分作为索引,
KEY index(name(20),age,position),但是order by,group by 会失效;
5.where 与 order by冲突的时候 优先选择where的条件来确定索引;
6.根据慢sql查询来创建索引优化;
项目分析:
1.根据业务场景分析创建联合索引.如,年龄,地区,性别,但对于年龄一般是范围查找,建议放到联合索引的最后;
2.根据项目查询sql情况,可以建立多个联合索引.
3.对于一个范围查询的数据,创建一个新的字段来做成索引,如,七日内登陆的用户,
使用一个定时任务进行统计,然后标记状态,进行维护.
慢SQL:
set global slow_query_log=1//开启慢查询日志,重启mysql后会失效
Extra中的参数
Using index是指MySQL扫描索引本身完成排序;
Using filesort:分为单路排序,双路排序;
文件排序 包含内存排序和文件排序两种模式.
1.内存排序: 将数据从磁盘中读取出来直接在内存中进行排序;
2.磁盘排序: 从磁盘中读取的数据量过大,mysql会在磁盘临时开辟一块空间,用来存储读取出来的数据,
然后再将这些数据分批读取到内存中,进行比较;
**单路排序：**是一次性取出满足条件行的所有字段，然后在sort buffer(缓存)中进行排序；用trace工具可 以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
**双路排序（又叫回表排序模式）：**是首先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具 可以看到sort_mode信息里显示< sort_key, rowid >
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
Mysql中的Join关联
嵌套循环连接 Nested-Loop Join(NLJ) 算法:
1. 从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
3. 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4. 重复上面 3 步。 整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表
中 a 的值索引扫描 t1 表 中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，
也就是总共 t1 表也扫描了100 行)。因此整个过程扫描了 200 行。 如果被驱动表的关联字段没索引，
使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join 算法。
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据
对于关联sql的优化 关联字段加索引，让mysql做join操作时尽量选择NLJ算法 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间.
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
in和exsits
小表驱动大表，即小的数据集驱动大的数据集
in：当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}
exists：当A表的数据集小于B表的数据集时，exists优于in 将主查询A的数据，放到子查询B中做条件验证，
根据验证结果（true或false）来决 定主查询的数据是否保留
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,
官方说法是实际执行时会 忽略SELECT清单,因此没有区别 ;
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比 ;
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析;
select * from A where exists (select 1 from B where B.id = A.id)
--等价于
for(select * from A){ select * from B where B.id = A.id }
---------------------------------------------------------------------------------分割线-----------------------------------------------------------------------
count(*)
字段有索引：count(*)≈count(1)>count(字段)>count(主键 id) //字段有索引，
count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引,
count(字段)统计走不了索引， count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
优化方法
1、查询mysql自己维护的总行数;
2、show table status;
3、将总数维护到Redis里;
4、增加数据库计数表.
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
