性能下降，sql慢执行时间长，等待时间长

查询语句写的烂

索引失效

(前提是你建了索引,没有使用上索引)

  1. 索引是帮助mysql高效获取数据的数据结构.
  3. 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用数据
  4. ,这样就可以在这些数据结构上实现高级算法.这种数据结构,被称为索引
  6. 从本质上来说,索引就是数据结构.
  8. 可以简单地理解为:
  10. // 索引是排好序的快速查找的数据结构.
  11. 是为了解决SQL数据过于庞大引起效率下降的优化方案
  13. 如何创建索引呢?
  15. 例如: 一个表 user 有字段 id ,name ,email
  17. 建立单值索引(给name单字段建立索引)
  19. create index idx_user_name on user(name);
  20. 建立复合索引 (name,email)
  22. create idx_user_nameEmail on user(name,email);

单值索引

复合索引

关联查询有太多的join(设计缺陷或不得已的需求)

sql优化

服务器调优及各个参数设置(缓冲,线程数等)

调整 my.cnf

没有充分利用到索引

建立索引并优化

数据过多

分库分表

常见通用的Join查询

sql的执行顺序

手写

  1. select district 查询字段
  2. from 表名1
  3. [left]/[right]/[inner] join 表名2
  4. on 表1.字段=表2.字段
  5. where 查询条件
  6. group by 分组字段
  7. having 分组后判断条件
  8. order by 排序字段 [asc|desc]
  9. limit 分页字段

机读

  1. 1.执行from 表名1
  2. 2.执行 on 关联条件得到它们的交集
  3. 3.执行[left|right|inner] join 表名2 
  4. 4.执行 where 判断条件
  5. 5.执行 group by 进行分组
  6. 6.执行 having 对结果再次进行筛选
  7. 7.执行select 
  8. 8.执行 order by 进行排序
  9. 9.执行limit

总结

image.png

  1. 1.from 笛卡尔集
  2. 2.on 主表保留
  3. 3.join 不符合on也添加
  4. 4.where 非聚合,非select 别名
  5. 5.grouip by 改变对表的引用
  6. 6.having 只作用分组后
  7. 7.select distrinct
  8. 8.order by 可使用select 别名
  9. 9.limit rows offiset

join图

image.png
image.png

7种join

image.png

Case示例

  1. -- 建表sql语句
  3. /**部门表*/
  4. create table tbl_dept(
  5. id int not null primary key auto_increment,
  6. deptname varchar(30) default null,
  7. locAddr varchar(40) default null
  8. );
  11. /**员工表*/
  12. create table tbl_emp(
  13. id int not null primary key auto_increment,
  14. empName varchar(30) default null,
  15. deptld int not null,
  16. constraint fk_emp_dept foreign key (deptld) references tbl_dept(id)
  17. );
  19. insert into tbl_dept values (null,'RD',11),(null,'HR',12),(null,'MK',13),(null,'MIS',14),(null,'FD',15);
  21. insert into tbl_emp values (null,'zs1',1),(null,'zs2',1),(null,'zs3',1),(null,'wangwu1',2),(null,'王五2',2);
  23. alter table tbl_emp modify empName varchar(30) character set utf8;
  1. -- 7中join
  2. -- 员工表和部门表内连接
  3. select * from tbl_emp e inner join tbl_dept d on e.deptld = d.id;
  5. -- 左连接
  6. select * from tbl_emp e left join tbl_dept d on e.deptld = d.id;
  8. -- 右连接
  9. select * from tbl_emp e right join tbl_dept d on e.deptld = d.id;
  12. -- 左外连接
  14. select * from tbl_emp e left join tbl_dept d on e.deptld = d.id where d.id is null;
  16. -- 右外连接
  17. select * from tbl_emp e right join tbl_dept d on e.deptld = d.id where e.deptld is null;
  20. -- 全连接(mysql 不支持full outer )
  22. select * from tbl_emp e left join tbl_dept d on e.deptld = d.id 
  23. UNION 
  24. select * from tbl_emp e right join tbl_dept d on e.deptld = d.id;
  28. select * from tbl_emp e left join tbl_dept d on e.deptld = d.id  where d.id is null 
  29. UNION 
  30. select * from tbl_emp e right join tbl_dept d on e.deptld = d.id where e.deptld is null;

实际工作中都是使用左外连接,很少使用右外连接

索引

是什么

  • Mysql官方对索引的定义为: 索引(index) 是帮助mysql高效获取的数据结构。可以得到索引的本质:索引是一种数据结构。
    • 索引的目的是提高查询效率,可以类比字典。
    • 如果要查找mysql这个单词,我们肯定先定位m开头的字母,然后继续向下查找第二个单词是y的字母,在继续向后查找sql字母。
    • 如果没有索引,那么你需要a-z,如果我想找到java开头的单词呢?或者oracle开头的单词呢?
    • 是不是觉得如果没有索引,这些事情你根本无法完成。
  • 简单的理解为“排好序的快速查找数据结构”
    • 详情
    • image.png
    • 结论：数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引(B树 或 Btree)

  • 我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hashindex)等。

    优势

  • 类似于大学图书馆建立书目录索引,提高数据的检索的效率,降低了数据库的IO成本

  • 通过索引对数据列进行排序,降低数据排序的成本,降低了CPU的消耗

    劣势

  • 实际上索引是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也要占用空间的

  • 虽然索引大大提高了查询速度,但同时也降低了更新表的速度
    • 如对表进行 insert / update / delete 因为更新表时,Mysql不仅要保存记录,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新带来的键值变化后的索引信息。
    • 间隙锁 delete 删除一个数据后,数据之间会留下一个间隙

  • 索引只是提高效率的一个因素,如果你的mysql有大数据量的表,就需要花大量时间研究建立最优秀的索引,或者优化查询

    • 索引是在不断的分析不断的建立索引(删了在建…) 逐步优化出来的,
    • 索引建立不是一朝一夕的,它需要根据业务场景等诸多因素
    • 如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

      mysql索引分类

      单值索引

  • 即一个索引只包含某个单列,一个表中可以有多个单列索引

  • 建议:一张表的索引一般不要超过5个

    • create index 索引名 on 表名(索引字段);

      唯一索引

  • 索引列的值必须唯一,但允许为null值

    • create unique index 索引名 on 表名(表字段)

      主键索引

  • 设定为主键后数据库会自动建立索引, innoDB 为聚簇索引

    复合索引

  • 即一个索引包含多个列

    • create index 索引名 on 表名(索引字段1,索引字段2…);

      全文索引

      全文检索原理：mysql使用的是一个非常简单的剖析器来将文本分隔成词，空格、标点等，比如‘welcom to you’将分隔为三个词‘welcom’、‘to’、‘you’，但是对中文来说，比如‘人力方网站正式上线’，这将无法分隔，因此目前mysql只支持 英文的全文检索。

参考链接：https://www.cnblogs.com/shen-qian/p/11883442.html
image.png

索引下推

参考链接：https://www.cnblogs.com/Chenjiabing/p/12600926.html

  • 概述：索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询
  • 作用：索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

示例:
image.pngimage.png
image.pngimage.png

聚簇索引与非聚簇索引

参考链接：https://www.cnblogs.com/s-b-b/p/8334593.html
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致
注：除了自己建的主键之外,建的所有索引都是非聚簇索引
image.png

基本语法

索引

  1. 1.索引的建立
  3. CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
  4. ALTER mytable ADD [UNIQUE] INDEX [indexName](columnname(length))
  6. 2.索引删除
  7. drop index 索引名 on 表名
  9. 3.查询看索引
  10. show index from 表名

使用ALTER命令

image.png

不给你索引名根据表名,库名如何删除索引,只剩下主键索引

查询索引名(information_schema库的STATISTICS 表)

  1. SELECT s.INDEX_NAME,s.COLUMN_NAME,s.INDEX_TYPE,s.CARDINALITY FROM information_schema.STATISTICS s 
  2. WHERE s.TABLE_NAME = 't_emp' AND s.TABLE_SCHEMA = 'datelog' 
  3. and index_name <> 'PRIMARY' and seq_in_index=1

怎么把字符串转变为sql

使用游标 和 预编译

使用存储过程进行删除

  2. delimiter $$
  3. create procedure drop_all_index(dbname varchar(200),tablename varchar(200))
  4. begin
  5. -- 创建变量
  6. declare done int default 0;
  7. declare ct int default 0;
  8. declare _index varchar(200) default '';
  9. -- 建立游标 cursor for 查询全部的非主键的索引名
  10. declare _cur cursor for select s.INDEX_NAME from information_schema.STATISTICS s where s.table_name=tablename and s.table_schema=dbname and index_name <> 'PRIMARY' and seq_in_index=1;
  11. declare continue handler for not found set done=2;
  14. -- 打开游标
  15. open _cur;
  16. -- 取出游标的一个值 赋值给_index
  17. fetch _cur into _index;
  18. -- 循环
  19. while _index <> '' do
  20. -- 拼写drop 语句
  21. set @str=concat("drop index ",_index," on ",tablename);
  22. -- @str预编译为sql语句
  23. prepare sql_str from @str;
  24. -- execute 执行sql
  25. execute sql_str;
  26. deallocate prepare sql_str;
  27. -- _index 重新赋值为null
  28. set _index='';
  29. fetch _cur into _index;
  30. end while;
  31. -- 关闭游标
  32. close _cur;
  33. end $$

mysql索引结构（平衡树）

什么是平衡树

例如把5 删了新增一个24,这时这个树不平衡了,平衡树会进行旋转,然后会把22挪到了左边23挪到了上边24挪到了右边,这时有变平衡了

BTree索引

参考链接：https://blog.csdn.net/bible_reader/article/details/100007292

Btree索引原理图(指向数据指针, 数据 ,向下指针)

红色小方块 ->指向数据指针, 数据 ,向下指针
image.png
初始化介绍一颗B+树,浅蓝色的块我们称为一个磁盘块,可以看成每个磁盘块包含几个数据项(深蓝色所示) 和 指针(黄色所示),如磁盘块17包含数据项17-35,包含指针P1,P2,P3,
P1表示小于17的磁盘块,P2表示17-35的磁盘块,P3表示大于35的磁盘块。
真实的数据存在于叶子节点 即 3,5,9,10,13,15,28,29,36,60,75,79,90,99
非叶子节点不存储真实的数据,只存储指针引搜索方向的数据项,如17-35并不真实存储于数据表中。

[查找过程]
1.如果要查找数据项29,那么首先会把磁盘块17由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29-35之间,锁定磁盘块1的P2指针,内存查找的时间因为非常短(相比磁盘的IO)可以忽略不计
2.通过磁盘块1的P2指针的磁盘块3由磁盘加载到内存,发生第二次IO,
3.29在26-30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分法查找到29,查询结束,总结三次IO

B树只有三层结构,只能横向拓展

真实的情况是,3层的B树可以表示上百万的数据,如果上百万的数据查找需要经过三次IO,性能提高是巨大的,如果没有索引,每个数据项都要发生一次,那么共需百万次IO,显然成本是非常高的。

B+Tree检索原理(数据,向下指针)-MySQL索引的真正存储结构

image.png

B树和B+树的区别

  • B树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
  • 在B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

那么为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

  • B+树的磁盘读写代价更低
    • B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

  • B+树的查询效率更加稳定

    • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

      hash索引

      full-text全文索引

      r-tree索引

      时间复杂度(时间一去不复返)

  • 同一问题可用不同算法解决,而一个算法的质量优劣将影响到算法乃至程序的效率,算法分析的目的在于选择合适算法和改进算法

  • 我们希望随着问题规模的增长复杂度是趋于稳定地上升,但是上升的幅度不能太大
  • image.png
  • 拓展: log n是什么意思? 2的3次幂结果是8 ,log n就是 反向算 3

  • image.png

    哪些情况需要创建索引？

  • 主键自动建立唯一索引

  • 频繁作为查询字段应该建立索引
  • 查询中与其他表关联的字段,外键建立索引
  • 频繁更新的字段不适合建立索引
  • 因为你更新表的数据,还会更新你的索引
    • 因为每次更新不单单是更新了记录还会更新索引，加重了IO负担
  • where 条件用不到字段不需要创建索引
  • 单键/组合索引选择问题 who? 在高并发的情况下使用组合索引
  • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

  • 查询中统计或者分组字段

    那些情况下不要建立索引

  • 表记录太少

  • 经常增删改的字段
    • 提高了查询速度,同时却降低了更新表的速度,如对表进行 insert / update /delete 执行更新表,mysql 不仅要保存数据还需要保存一下索引文件
  • where 条件里用不到的字段不创建索引
  • 过滤性不好的不适合建立索引
  • 数据重复且分布均匀的表字段,因为应该只为最经常查询和最经常排序的数据列建立索引
    • 注意: 如果某个数据列包含许多重复的内容,为它建立索引就灭偶遇太大的实际效果。
    • 假如一个表中有10万行数据,有一个字段A只有 true 和 false 俩种值,且每个值的分布概率大约为 50%,那么对这种表A字段建立索引一般不会提高数据库的查询速度。
    • 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中2000条记录,表索引列又1980个不同的值,那么这个索引的选择性就是 1980/2000 =0.99 。 一个索引的选择越接近1,这个索引的效率就越高