在谈SQL优化前,我么先分析下SQL的架构图
![ea71783ebded335c078ca3c92598f510.png](https://i-blog.csdnimg.cn/blog_migrate/50596ef5b3a323dc232ab0a86094ce17.jpeg)
mysql客户端通过协议与mysql服务器建连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,也就是说,在解析查询之前,服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可,这将大大提高系统的性能。
语法解析器和预处理:首先mysql通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询;预处理器则根据一些mysql规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。
最后会找到相应引擎,进行查询。
总结:MySQL连接池--缓存--sql接口分析sql--解析器 复杂sql解析--优化器生成执行计划--存储引擎执行--存入缓存
MySQL中的各个引擎
1、InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎,它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。
2、MyISAM存储引擎
MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务和行级锁,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
3、Archive引擎
Archive档案存储引擎只支持INSERT和SELECT操作,在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。
根据英文的测试结论来看,Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。
4、Blackhole引擎
Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存。但服务器会记录Blackhole表的日志,所以可以用于复制数据到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。
5、CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引。
CSV引擎可以作为一种数据交换的机制,非常有用。
CSV存储的数据直接可以在操作系统里,用文本编辑器,或者excel读取。
6、Memory引擎
如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。
7、Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的。
引擎中最重要的就是MyISAM和InnoDB,下面分析下这两个引擎
MyISAM
外键不支持
事务不支持
行表锁表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作
缓存只缓存索引,不缓存真实数据
关注点节省资源、消耗少、简单业务
默认安装Y
默认使用N
自带系统表使用Y
InnoDB
对比项
外键 支持
事务 支持
行表锁行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作
缓存 不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响
关注点并发写、事务、更大资源
默认安装Y
默认使用Y
自带系统表使用N
MyISAM主要用在MySQL自带的表中,而InnoDB用在我们自己建的表中
了解了mysql架构之后,我们再来了解一下mysql的索引机制。
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质:索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上
优势
类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
劣势
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。 因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
mysql索引结构
首先我们要了解两种索引结构
BTree索引
![d891816bbf549e04166657dfe62b4e1d.png](https://i-blog.csdnimg.cn/blog_migrate/1175018a2bb8c65639135cb6e8cebfd4.jpeg)
一颗b树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),
如磁盘块1包含数据项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并不真实存在于数据表中。
如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。
真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
B+Tree索引
![ac14fc13b634f57542d4b7d024ee7f83.png](https://i-blog.csdnimg.cn/blog_migrate/1e9e575e6c9f2e676aa233d96e9d5621.jpeg)
B+Tree中非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。B+Tree中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好,而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比B-树多,树高比B-树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,而且B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用B+树的缘故。
思考:为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引?
1) B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
2) B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
MySQL用的就是B+Tree索引。
mysql索引分类
单值索引
即一个索引只包含单个列,一个表可以有多个单列索引
唯一索引
索引列的值必须唯一,但允许有空值
主键索引
设定为主键后数据库会自动建立索引,innodb为聚簇索引
复合索引
即一个索引包含多个列
基本语法
创建
CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
删除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM table_nameG
哪些情况需要创建索引
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段,外键关系建立索引
单键/组合索引的选择问题, 组合索引性价比更高
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
具体怎么建索引优化,我会在下一篇文章中详谈,之后还会陆续更新 jvm虚拟机,juc多线程编程,gc垃圾回收,activeMQ消息队列等java高级技术,对java感兴趣的朋友记得关注一下。