在开始演示之前,我们先介绍下两个概念.
概念一,数据的可选择性基数,也就是常说的cardinality值.
查询优化器在生成各种执行计划之前,得先从统计信息中取得相关数据,这样才能估算每步操作所涉及到的记录数,而这个相关数据就是cardinality.简单来说,就是每个值在每个字段中的唯一值分布状态.
比如表t1有100行记录,其中一列为f1.f1中唯一值的个数可以是100个,也可以是1个,当然也可以是1到100之间的任何一个数字.这里唯一值越的多少,就是这个列的可选择基数.
那看到这里我们就明白了,为什么要在基数高的字段上建立索引,而基数低的的字段建立索引反而没有全表扫描来的快.当然这个只是一方面,至于更深入的探讨就不在我这篇探讨的范围了.
概念二,关于HINT的使用.
这里我来说下HINT是什么,在什么时候用.
HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作,使她生成最优的执行计划.一般来说,优化器的执行计划都是最优化的,不过在某些特定场景下,执行计划可能不是最优化.
比如：表t1经过大量的频繁更新操作,（UPDATE,DELETE,INSERT）,cardinality已经很不准确了,这时候刚好执行了一条SQL,那么有可能这条SQL的执行计划就不是最优的.为什么说有可能呢?
来看下具体演示
譬如,以下两条SQL,
A：
B：
这里顺带说下,MySQL提供了自动更新和手动更新表cardinality值的方法,因篇幅有限,需要的可以查阅手册.
示例表结构：
表记录数：
这里我们两条经典的SQL：
SQL C：
SQL D：
那我们来看SQL C的查询计划.
我们加上hint给相同的查询,再次看看查询计划.
我们再看下SQL D的计划：
不加HINT,
加了HINT,
对比下以上两个,加了HINT的比不加HINT的cost小了100倍.
总结下,就是说表的cardinality值影响这张的查询计划,如果这个值没有正常更新的话,就需要手工加HINT了.相信MySQL未来的版本会带来更多的HINT.
聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同,一个表中只能拥有一个聚集索引. 叶子结点存储索引和行记录,聚簇索引查询会很快,因为可以直接定位到行记录.
非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引. 叶子节点存储聚簇索引值（主键id）,需要扫码两遍索引树,先通过普通索引定位到主键值id,再通过聚集索引定位到行记录.
回表查询可以理解为普通索引的查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低.
索引覆盖,即将查询sql中的字段添加到联合索引里面,只要保证查询语句里面的字段都在索引文件中,就无需进行回表查询；
实际开发中,不可能把所有字段建立到联合索引,可根据实际业务场景,把经常需要查询的字段建立到联合索引中.
优化超多分页场景. 查询条件放到子查询中,子查询只查主键id,然后使用子查询中确定的主键关联查询其他的属性字段.
数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.
① 优化一览图
笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.
①首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
显示:
其中会显示索引和查询数据读取数据条数等信息.
在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,所以呢效率比嵌套子查询高.
索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者MySQL数据库索引一文,介绍比较详细,此处记录使用索引的三大注意事项:
对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,
对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.
类似于创建中间表,增加冗余也是为了减少连接查询.
分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.
① 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;
option 只对MyISAM有效,共五个参数值:
LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.
①配置多核心和频率高的cpu,多核心可以执行多个线程.
优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.
因为数据库压力过大,首先一个问题就是高峰期系统性能可能会降低,因为数据库负载过高对性能会有影响.另外一个,压力过大把你的数据库给搞挂了怎么办?所以此时你必须得对系统做分库分表 ◆ 读写分离,也就是把一个库拆分为多个库,部署在多个数据库服务上,这时作为主库承载写入请求.然后每个主库都挂载至少一个从库,由从库来承载读请求.
如果用户量越来越大,此时你可以不停的加机器,比如说系统层面不停加机器,就可以承载更高的并发请求.然后数据库层面如果写入并发越来越高,就扩容加数据库服务器,通过分库分表是可以支持扩容机器的,如果数据库层面的读并发越来越高,就扩容加更多的从库.但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的,所以通常来说,数据库单机每秒承载的并发就在几千的数量级,而且数据库使用的机器都是比较高配置,比较昂贵的机器,成本很高.如果你就是简单的不停的加机器,其实是不对的.所以在高并发架构里通常都有缓存这个环节,缓存系统的设计就是为了承载高并发而生.所以单机承载的并发量都在每秒几万,甚至每秒数十万,对高并发的承载能力比数据库系统要高出一到两个数量级.所以你完全可以根据系统的业务特性,对那种写少读多的请求,引入缓存集群.具体来说,就是在写数据库的时候同时写一份数据到缓存集群里,然后用缓存集群来承载大部分的读请求.这样的话,通过缓存集群,就可以用更少的机器资源承载更高的并发.
一个完整而复杂的高并发系统架构中,一定会包含：各种复杂的自研基础架构系统.各种精妙的架构设计.所以呢一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.