本文目录一览：
超详细MySQL数据库优化
数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.
1. 优化一览图
2. 优化
笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.
2.1 软优化
2.1.1 查询语句优化
1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
2.例:
显示:
其中会显示索引和查询数据读取数据条数等信息.
2.1.2 优化子查询
在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.
2.1.3 使用索引
索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者MySQL数据库索引一文,介绍比较详细,此处记录使用索引的三大注意事项:
2.1.4 分解表
对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,
2.1.5 中间表
对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.
2.1.6 增加冗余字段
类似于创建中间表,增加冗余也是为了减少连接查询.
2.1.7 分析表,,检查表,优化表
分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.
1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;
2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]
option 只对MyISAM有效,共五个参数值:
3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;
LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.
2.2 硬优化
2.2.1 硬件三件套
1.配置多核心和频率高的cpu,多核心可以执行多个线程.
2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.
2.2.2 优化数据库参数
优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.
2.2.3 分库分表
因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。
2.2.4 缓存集群
如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。
一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.
六、MySQL数据库之数据插入（insert into）
本节介绍数据的插入，复制数据到另一张表的Sql语法，主要语法有： insert into，insert into select，select into from 等用法，下面将一一为大家详细说明：
以下面两张表进行sql脚本说明
insert into有两种语法，分别如下：
语法1：INSERT INTO table_name VALUES (value1,value2,value3,...); --这种形式无需指定要插入数据的列名，只需提供被插入的值即可：
语法2：INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...); --这种形式需指定要插入数据的列名，插入的值需要和列名一一对应：
eg：insert into customer values('1006','14006','王欣欣','27','深圳市'); --向表customer插入一条数据
eg：insert into customer values('1007','14007','孟一凡','27',''); --向表customer插入一条数据，最后一个值不填表示对应的值为空，非必填项可以不用插入值
eg：insert into customer (cus_id,cus_no,cus_name,cus_age,cus_adds) values('1008','14008','孔凡','26','广州市'); --向表customer插入一条数据，插入的值与列名一一对应
详解：insert into select --表示从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
语法1：INSERT INTO table_name2 SELECT * FROM table_name1; --表示将表table_name1中复制所有列的数据插入到已存在的表table_name2中。被插入数据的表为table_name2，切记不要记混了。
eg：insert into customer select * from asett --将表asett中所有列的数据插入到表customer中
语法2：INSERT INTO table_name2 (column_name(s)) SELECT column_name(s) FROM table_name1; --指定需要复制的列，只复制制定的列插入到另一个已存在的表table_name2中：
eg：insert into customer (cus_id,cus_no) select ast_id,ast_no from asett --将表asett中列ast_id和ast_no的数据插入到表customer对应的cus_id，cus_no列中
详解：从一个表复制数据，然后把数据插入到另一个新表中。
语法1：SELECT * INTO newtable [IN externaldb] FROM table1; --复制所有的列插入到新表中：
eg：select * into customer from asett --将asett表中数据插入到customer中，被插入的 表customer不存在
eg：select * into customer from asett where ast_id = '1008' --只复制表asett中ast_id=1008的数据插入到customer中，被插入的 表customer不存在
语法2：SELECT column_name(s) INTO newtable [IN externaldb] FROM table1; --只复制指定的列插入到新表中：
eg：select ast_id,ast_no into customer from asett --将asett表中列ast_id,ast_no数据插入到customer中，被插入的 表customer不存在
区别1：insert into customer select * from asett where ast_id='1009' --插入一行,要求表customer 必须存在
区别2：select * into customer from asett where ast_id='1009' --也是插入一行,要求表customer 不存在
区别3：select into from ：将查询出来的数据复制到一张新表中保存，表结构与查询结构一致。
区别4：insert into select ：为已经存在的表批量添加新数据。
MySQL的数据库文件有几种?扩展名及作用分别是什么?
在MySQL中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。
1、“.frm”文件 与表相关的元数据（meta）信息都存放在“.frm”文件中，包括表结构的定义信息等。不论是什么存储引擎，每一个表都会有一个以表名命名的“.frm”文件。所有的“.frm”文件都存放在所属数据库的文件夹下面。
2、“.MYD”文件“ .MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。
3、“.MYI”文件 “.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM存储来说，可以被cache的内容主要就是来源于“.MYI”文件中。每一个MyISAM表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。
管理工具：
可以使用命令行工具管理 MySQL 数据库（命令 mysql 和 mysqladmin)，也可以从 MySQL 的网站下载图形管理工具 MySQL Administrator, MySQL Query Browser 和 MySQL Workbench。
phpMyAdmin是由 php 写成的 MySQ L资料库系统管理程程序，让管理者可用 Web 界面管理 MySQL 资料库。
phpMyBackupPro也是由 PHP 写成的，可以透过 Web 界面创建和管理数据库。它可以创建伪 cronjobs，可以用来自动在某个时间或周期备份 MySQL 数据库。另外，还有其他的 GUI 管理工具，例如 mysql-front 以及 ems mysql manager, navicat等等。
以上内容参考：百度百科-mySQL
