MySQL索引与优化_csdn李嘉伟-CSDN博客

本文链接：https://blog.csdn.net/weixin_44734751/article/details/106361234

3索引

1）、什么是索引

索引（Index）是帮助mysql高效获取数据的数据结构

数据本身之外，数据库还维护这一个满足特定查找算法的数据结构，这些数据结构指向了数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

使用B数作为索引结构，如果删除数据树的结构改变会造成索引失效，所以一般 service层删除数据底层其实是updata操作，把这个数据改成为激活状态一般不删除数据。

一般来说索引本身很大，不可能全部存在内存中，因此索引旺旺以索引文件的形式存在磁盘上

2）、索引优势劣势

优势：提高数据检索效率，降低IO成本降低数据排序成本降低CPU消耗

劣势：索引也是一张表，存储了主键和索引字段，并指向实体记录，所以索引也占空间，虽然索引提高了查找效率，但是降低了更新表的速度，如插入，更新，删除操作，因为更新表时，MySQL不仅保存数据，还保存索引文件每次更新了索引列的字段，

3）、索引的分类

单值索引：一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有null

复合索引：一个索引包含多个列

基本语法：

创建： create index 索引名 on 表名（字段名。。。）

alter 表名 add index 索引名 on （字段名）

删除：drop index 索引名 on 表名

查看：show index from 表名

4）、索引结构

BTree索引：

Hash索引：

full-text索引：

R-Tree索引：

5）、哪些不要建索引

主键自动建立唯一索引

频繁作为查找条件的字段应该创建索引

外检建立索引

频繁更新的字段不适合创建索引

where条件用不到的字段不创建索引

排序字段通过索引会大大提高效率

统计或分组字段建索引

表记录太少不要建索引

经常增删改的表不要键索引

某一列有太多重复的内容不要键索引

4Explain

是什么？

简称执行计划

使用explain关键字可以模拟优化器执行SDQl查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

怎么玩？

explain+sql语句

就会显示这么一个表头

在这里插入图片描述

id

id相同，执行顺序由上至下

id不同，值越大执行越优先

id有相同也有不同值：有限制性值大的值相同的由上到下执行

select_type

在这里插入图片描述

simple 简单的查询，查询不包含子查询或union

primary 查询中包含任何复杂的子查询，最外层查询标记为primary

subquery 子查询被标记

derived 在from列表中包含的子查询被标记

union 如果第二个select出现在union之后则被标记

union result 从union表获取结果的select被标记

type

这个字段分八种类型

在这里插入图片描述

从好到坏依次是：system》const》eq_ref》ref》range》index》all
在这里插入图片描述

在这里插入图片描述

key

在这里插入图片描述

key_len

在这里插入图片描述

row

在这里插入图片描述

extra

十分重要的额外信息

在这里插入图片描述

5索引优化

单表

建表

在这里插入图片描述

虽然我们建立了索引解决了全表扫描但是没有解决 using filesort 因为范围字段键索引会失效

在这里插入图片描述

解决：不给comments键索引给其他俩个建立索引

俩表

建表

在这里插入图片描述

给连接字段建索引

左连接索引建立在右边右连接索引键在左边

三表

为后面的俩个表的连接字段建索引

在这里插入图片描述

注意连接的时候一定是小结果集驱动大结果集

6索引失效（应该避免）

1）、哪些情况索引会失效

在这里插入图片描述

1：全职匹配我最爱

有一张表三个字段 name age dev 我们都建立索引

分别执行三条sql语句的效果

在这里插入图片描述

如果不用条件没有name或没有age 会造成索引失效

在这里插入图片描述

2最佳左前缀法则

上面索引失效是因为违背了最佳左前缀法则

最佳左前缀法则：如果索引了多个列，查询从索引的最左列开始并且不跳过索引中的任何列，（说白了就是某个索引没用该索引后面的索引全部失效）

3索引列做计算

查询的时候索引列上做了计算该索引列会失效

4 范围查询会失效

查询条件某个索引字段是范围查询，后面的索引全部失效

5尽量使用覆盖索引

少用select * 尽量使用select 建过索引的列

6不等于

使用不等于就相当于使用了大于号小于号后面的索引会失效

7null

is null 和 is not null 索引会失效

8like

%加在左边会索引失效转而全表扫描尽量写到右边’ ‘张%’’ 在业务中就得加到左边怎么避免索引失效

使用覆盖索引可以避免加了%的索引失效 where中可以有主键也没事但要是出现非主键也不是索引列的就会失效

9字符串不加单引号索引值直接失效

varchar类型字段不写单引号查询是重罪直接索引失效

因为它违背了索引字段不能操作原则不写单引号会数据类型转换操作

在这里插入图片描述

总结

带头大哥不能死中间兄弟不能断索引列上不计算 like百分加右边范围之后全失效字符串里有引号
在这里插入图片描述

面试题分析

1、索引顺序为 1 2 3 4 搜索的时候 4 3 2 1 索引会失效么？不会因为底层有排序不论你的搜索顺序如何都会按照建立索引的顺序排序排序之后按照索引失效的是个情况判断具体用到了几个索引

2、

3order by 索引不会自动排序顺序不对会出现文件排序

4当索引出现在条件中并且出现在order by 顺序就无所谓了都对

5group by 后的索引顺序不对会出现临时表和文件排序

小表驱动大表

在这里插入图片描述

order by优化

order by 索引列会不会产生 filesort 如何避免？

不用 *

查询字段和排序字段相同

在这里插入图片描述

慢查询日志

在这里插入图片描述

慢查询日志

在这里插入图片描述

开启慢查询日志

在这里插入图片描述

开启之后只针对当前数据库生效重启mysql后失效

什么样的sql会记录到日志呢？
在这里插入图片描述

日志分析一般使用mysql自带的命令不会人工区查看日志

在这里插入图片描述

表锁

使用myisam引擎创建一个student表和teacher表使用该引擎无论上读锁还是写锁都是表级锁

myisam根据查询还是更新操作自动上读写锁

手动增加锁 lock table student read,teacher write

show open tables；查看哪些库的那些表被上了锁

unlock tables 释放全部表的锁

读锁

操作：

前提：session1 给student表上读锁 lock table student read

1：session1可以读student

2：session1更新student表报错

3：session1读取其他表报错因为没释放锁

4：session2 可以读student

5：session2更新student表被阻塞知道session1释放锁

写锁

操作：session1给student表上写锁 lock table studnet write

1：session1可以读studnet表

2：session1可以更改studnet表

3：session1读其他表报错因为没释放锁

4 session2 读studnet表被阻塞知道session1释放锁

性能分析

在这里插入图片描述

行锁

开销大，加锁慢，会出现死锁，锁粒度最小，发生锁冲突概率最低，并发度最高

使用innodb引擎建表 student 字段有 name age 并且为 name 和 age建立索引并关闭事务自动提交 set autocommit =0

操作行的时候会自动加行锁

索引使用不当行锁升级为表锁

varchar类型不写单引号查询直接回升级为表锁

间隙锁的危害

当我们使用范围条件搜索数据的时候，innodb会给范围内的所有行加锁，当满足范围但又不存在的数据就是间隙，innoDB也会对这个间隙加锁，这个就是间隙锁

危害：这些不存在的键值会被无辜的锁定，而造成在锁定的时候无法插入这条记录（阻塞），会对性能造成危害

手动上行锁

begin：

select * from 表名 where id =1 for update ; 为id=1的这行上锁之后再各种操作其他会话操作会被阻塞

各种操作

commit;

性能分析

主从复制

1）、复制原理

slave会从master读取binlog文件进行数据同步

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xxDwyElF-1590486046875)(C:\Users\李嘉伟\AppData\Roaming\Typora\typora-user-images\image-20200526170452420.png)]

2）、复制的原则

每个slave只有一个master

每个slave只能有唯一一个服务器ID

每个master可以有多个slave

3）、配置

mysql版本一致

主从都配置在mysqld节点下，都是小写

修改my.cnf配置文件

主机

1 配置id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sPk3NvF9-1590486046876)(C:\Users\李嘉伟\AppData\Roaming\Typora\typora-user-images\image-20200526171031322.png)]

2开启二进制日志

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F456hbHY-1590486046877)(C:\Users\李嘉伟\AppData\Roaming\Typora\typora-user-images\image-20200526171127608.png)]

从机

在mysqld下

server-id=2

最后

主机从机关闭防火墙

主机在命令行开启授权

设置从机的ip地址和从机可以以什么身份什么密码访问主机

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rPGtidPa-1590486046879)(C:\Users\李嘉伟\AppData\Roaming\Typora\typora-user-images\image-20200526172058848.png)]

主机查询master状态

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-22SCHPkO-1590486046879)(C:\Users\李嘉伟\AppData\Roaming\Typora\typora-user-images\image-20200526172250194.png)]

从那个文件哪一行开始赋值赋值哪些库不复制哪些库

从机命令行

设置主机ip地址以什么身份什么密码访问主机赋值主机的什么库从哪一行开始赋值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HyNJ17fr-1590486046880)(C:\Users\李嘉伟\AppData\Roaming\Typora\typora-user-images\image-20200526172618822.png)]

最后执行 start slave

查看是否成功

出现yes 代表成功

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oS15ChlR-1590486046880)(C:\Users\李嘉伟\AppData\Roaming\Typora\typora-user-images\image-20200526172838953.png)]

stop slave 停止主从复制