mysql高级部分学习

一、mysql的架构介绍

1.mysql采用的是分层设计模式，结合可插拔技术开发的。

2.mysql层次结构为：连接层>服务层>引擎层>存储层。

3.插件式的存储引擎架构将查询处理和其他系统任务以及数据的提取相分离。

4.mysql最常用的两种搜索引擎是：MyISAM 和 InnoDB（默认）。

5.MyISAM 和 InnoDB的区别以及选择：

两种类型最主要的差别就是InnoDB 支持事务处理与外键和行级锁.而MyISAM不支持.读多写少用MyISAM,读少写多用InnoDB

二、索引的优化分析1.索引的分类：

单指索引：create index id_user_name on user(name)

复合索引：create index id_user_nameEmil on user(name,email)

2.SQL的执行顺序

from>on>join/where>groupby>having>select>order
by>limit

3.索引是什么

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

简单说索引就是：排好序的快速查找的数据结构

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

4.索引的相关内容
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

索引默认的是B树索引（多路搜索树，并不一定是二叉树），还有聚集索引、次要索引、复合索引、哈希索引等。

5.索引的优缺点

优点：提高了数据的检索效率，降低了数据库的IO成本。降低了数据库的排序成本，降低了CPU的消耗。

缺点：索引也要占空间，而且修改数据时，索引也要跟着修改。

6.索引的分类

单指索引：一个索引包含一个列，一个表可以有多个单指索引。

唯一索引：索引列的值必须唯一，但允许为空。

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

7.索引的基本语法

创建：create [unique] index indexName on table(column)

alter table add [unique] index [indexName] on (column)

删除：drop index [indexName] on table

查看：show index from table

8.Explain(执行计划)的相关内容

id：id如果相同，则按从上往下的顺序执行，id值越大，越先执行。

select_type：查询类型，它的值有：simple、primary、subquery、derived、union等等。

table：操作的是哪张表。

type：type的值有：all、index、range、ref、eq_ref、const、system、null

其中查询使用的类型从最好到最差依次是：

system->const->eq_ref->ref->range->index->All

(1)system：表示只有一行记录，一般不会出现，没有意义。

(2)const：表示通过索引一次就可以找到。

(3)eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条数据与之对应。

(4)ref：非唯一性索引扫描，返回匹配的所有行。

(5)range：值检索给定范围的行，between、<>、in等。

(6)index：遍历所有索引。

(7)all：遍历全表。

possible_keys：显示可用的一个或多个索引，但不一定被查询实际使用。

key：实际使用到的索引。

key_len：表示索引中是用到的字节数，其值越小越好。

ref：显示索引的哪一列被使用了，如果可以，是一个常数。

rows：大致估算出找到所需记录需要读取的行数。

Extra：包含了不适合在其他列展示，但十分重要的信息。

(1) Using filesort：文件内排序，说明mysql使用了外部排序。

(2) Using temporary：使用了保存中间结构。（order by 、group by）

(3) Using index：效率不错，表名使用了索引。

(4) Using where

9.何时需要建立索引主键自动建立唯一索引。

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

查询中与其他表关联的字段，外键关系建立索引。

频繁更新的字段不适合建立索引，因为更新不单单要跟新 数据还要跟新索引，加重了IO的负担。

where条件里用不到的字段不创建索引。在高并发下推荐创建复合索引。

查询中排序的字段，排序字段如果通过索引去访问将大大提高排序速度。

查询中统计或分组字段。

10.索引优化
join语句的优化：尽可能减少join语句中的NestedLoop的循环总次数。
永远用小结果集去驱动大结果集。
优先优化NestedLoop的内层循环。

本文地址：https://blog.csdn.net/paparuazi/article/details/107166688