mysql to days 索引_mysql-黑马-day02- 优化与索引

image.png

image.png

image.png

存储引擎是作用在表上的

image.png

image.png

事务

-- Innodb

-- 事物

-- 开启事务

start transaction;

-- 操作

insert into project_user_visit(id, visit_user_name)

values ('10', '喜欢天文的pony');

-- 提交事务

commit;

-- 回滚事务

rollback;

MyISAM

image.png

image.png

image.png

三、 SQL优化

查看sql语句的执行频次，哪些操作执行的比较频繁(以插入还是查询为主)

show global status;

-- 查询操作次数

show global status like 'Com_______';

-- 查询Innodb存储引擎下操作的行的数量

show global status like 'Innodb_rows_%';

定位效率低的SQL

image.png

-- 查看实时状态-检测每个客户端正在执行的慢SQL

show processlist;

explain分析执行计划

image.png

id

如果id值一样，则按照从上到下顺序查询，

如果id不一样，则按照数值从大到小查询表。

select_type

image.png

table

查询的是哪张表

type

image.png

image.png

possible_keys

可能用到的索引

key

实际用到的索引

key_len

索引的长度(越短越好)

rows

扫描的行数

extra

image.png

show profiles分析SQL

-- 是否开启

select @@have_profiling;

-- 在当前Session会话开启profiling

select @@profiling;

-- 在当前会话开启profiling;

set profiling = 1;

-- 查看记录

show profiles;

image.png

各阶段时间分析 show profile for query [id]

image.png

show profile all for query 2;

show profile cpu for query 2;

优化器

image.png

四、索引的使用

索引能解决大多数mysql的查询性能问题。

正确创建索引，并且正确使用索引，才能提高查询效率。

避免索引失效:

创建索引

create index idx_username_usermobile_cityname on project_user_visit(visit_user_name,visit_user_mobile,visit_city);

全值匹配，对索引中所有的列都指定具体值。

explain select * from project_user_visit where visit_user_name='天文' and visit_user_mobile='123' and visit_city='上海市';

image.png

最左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左列开始，并且不能跳过索引中的列。(查询条件需要包含索引中的最左列，且不能跳过索引中的某一列)

image.png

image.png

与查询条件出现的顺序无关，只与是否出现索引的最左列有关。

image.png

如果跳过了索引中的某些列，则只有前几列索引有效。如下面的例子中，其实只走了visit_user_name的索引。

image.png

范围查询右边的列，不能使用索引。

image.png

image.png

不要在索引列上进行运算操作，否则索引将失效

image.png

字符串不加单引号会导致索引失效

why：因为mysql会对字段进行隐式转换，而这个转换过程被认为是对字段的运算操作。导致索引失效。

尽量查询覆盖索引(索引完全包含查询列)，不要使用select *，避免回表查询。

两个OR关联的条件，OR之后的条件没有索引，则整个查询都不走索引。

explain

select *

from project_user_visit

where visit_user_name = '天文'

or visit_user_position_name = '促销员';

image.png

以%开头的like查询不走索引，只加在后面走索引。

image.png

解决方案：使用覆盖索引

image.png

如果Mysql评估使用索引比全表扫描更慢，则不走索引。

is null, is not null，有时走索引，有时不走索引。

需要看数据列，mysql会判断走索引还是全表扫描更快。

比如某一列的值的数据基本都是null，那么在查询is null的时候，就不会走索引，而是全表扫描。

in走索引。not in 不走索引。---测试下来结果不对

我测试下来如果查询的列被索引字段覆盖了就都走，如果没覆盖就都不走

还要看in里面的内容，如果过长也不会走索引。

image.png

尽量使用复合索引，而少使用单列索引。

如: create index idx_xx on tab(a,b,c);

相当于建立了三个索引

a

a + b

a + b + c

如果分别在单个索引上建立索引，则只会走一个最佳的索引，不会每个索引都走。所以效率没有组合索引高。

查看索引的使用情况

-- 查看索引的使用情况

show global status like 'Handler_read%';

image.png

五、 SQL优化

1. 大批量插入数据

在Innodb存储引擎下，使导入的主键是有序的。

image.png

image.png

关闭唯一性校验

image.png

手动提交事务

image.png

2. 优化insert语句

image.png

values(),(),()组合在一起，使用一条sql插入。

事务提交改为手动提交，并批量开启事务，如每1W条提交一次。

主键顺序插入。

3. 排序优化

image.png

image.png

image.png

4. group by语句优化

image.png

5. 优化嵌套查询

使用多表连接查询代替子查询

image.png

6. OR的优化

需要保证OR的每个查询条件都有索引，如果有一个条件没有索引，则整个OR条件都不走索引。

使用union代替OR

7. 优化分页查询

image.png

8. 使用sql提示

image.png

image.png

image.png