mysql 优化(一)
数据库的操作越来越成为整个应用的瓶颈,mysql优化是提高应用性能的重中之重,今天来讲讲最近研究的mysql 的一些性能优化
Mysql的性能优化
(一) 开启查询缓存优化的你查询速度
如何开启mysql的查询缓存?你的mysql数据库是否支持mysql查询缓存?? (查询缓存是一把双刃剑,这里就不多说了)
查询缓存的工作流程:
1. 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)
2. 如果找到了缓存,则直接返回缓存(性能提升)
3. 如果没有找到缓存,则执行SQL查询,包括原来的SQL解析等.
4. 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)
打开命令行终端 输入 show variables like "%query_cache%"; 查看你是否开启缓存
这里的参数
have_query_cache: 你的mysql版本是否支持查询缓存
query_cache_size : 缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同
query_cache_type: 缓存的方式 有三个值 1) OFF: 关闭 2) ON: 总是打开 3) DEMAND: 只有明确写了SQL_CACHE的查询才会吸入缓存
query_cache_min_res_unit: 分配内存块时的最小单位大小
如果你的 query_cache_type =0 or query_chache_size =0 那么表示没有开启缓存,可以修改配置文件来开启
当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表 而直接访问缓存结果了。
如果你的查询条件有包含一些mysql的内置函数 比如 有时间 now() ,rand()等,那么讲不会缓存.
比较一下下面我执行的sql语句,当我开启查询缓存的时候 第一次执行所消耗的时间跟第二次执行所消耗的时间 以及相同查询语义但是大小写不一样.(sql语句绝对相等)
(2) EXPLAIN你的查询语句
EXPLAIN关键字能够让你知道索引的使用,如何搜索数据的,扫描行数等等
可以帮助你分析你SELECT 语句的瓶颈因此可以优化你的SELECT语句
选择一个复杂的sql语句
可以看到mysql是怎么样处理你的sql语句
select_type: 有三个参数(simple,primary, union,dependent union,union result) simple 它表示简单的select,没有union和子查询(这里只介绍simple)
table : 出自哪一张表
type :显示的访问类型,从性能最好到最坏以此是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
表中只有一行;const类型的特例
2)const: 表中最有有一行匹配,const用户比较primary key或者unique索引,因为只有一行,所以很快
3)eq_ref : mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。即比较带索引的列
4)ref : 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。这里的索引不包括 primary和unique.
5)rang : 给定范围内的索引,如 EXPLAIN SELECT * FROM user WHERE id IN (1,5) 或者是 BETWEEN
6)ALL : 全表扫描
possible_key : 显示 使用的哪个索引在该表中找到行
key : 该查询时所用到的索引
key_len : 使用索引的长度
ref : ref列显示使用哪个列或常数与key一起从表中选择行。
rows : 显示查询时扫描的行数,值越大越不好,所以根据这个可以判断mysql语句的好坏以及建立索引优化
extra: 额外的信息
可以根据EXPLAIN你SELECT的查询语句 进行相关的优化
(3) 为你的表合理的建立索引
这里为什么是合理呢,索引不是建得越多就越好,索引太多 对于 UPDATE DELETE INSERT 的效率都会有影响,
上面提到的EXPLAIN SELECT 语句我们可以进行分析
在table user(及table a)中 它的扫描行数是 180207行.而且是全表扫描,没有用到索引,
在命令行中我们来执行以下该sql,查询的时间是0.63sec
我们可以给user表中的school_id加个索引 CREATE INDEX schoolIndex ON `user`(school_id);
这时来看一下查询的时间和EXPLAIN SELECT 语句,
总结:可以很明显的看出 执行时间大大减少了,而且在EXPLAIN中可以看到 type相比于之前的ALL 现在是ref (索引) ,row也相比于180207行到2385行 性能大大的提升了许多
另外需要注意的是:当你的WHERE 后面的条件是 a.name like %陈%; 这样是不会的查询语句 就算你给name加一个索引 也会没有意义.
1、建立多表（三个表或以上）关联视图时，如果是主表和副表都有的字段，尽量使用主表的字段（特别是主表的主键）
2、副表的字段（无论是普通字段还是主键、索引字段）作为查询条件对查询都没有帮助，都需进行全表检索
(4)如果查询一条数据的时候使用limit
举个例子 : SELECT * FROM user WHERE name='vDobgB';
当你知道 name='vDobgB'在数据库中只有一条数据的时候使用limit会大大提升效率,这个时候mysql找到该行的时候就会返回这条数据,而不会继续往下查找
(5)在join表的时候 连接条件的字段类型,应当一致,并且将其索引
如果你的应用中使用到了很多表连接查询,应该确认表与表连接字段已经建立了索引,并且两个字段类型是一致的.
向我上面两表连接的字段类型都是int类型,且已经加了索引.如果你要把DECIMAL(小数)类型字段和int(整形)类型的字段连接在一起,那么Mysql就无法使用它们的索引
(6)避免使用 SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。
应该养成,需要什么数据就拿什么数据
(7)建立主键索引 即id
为每一个表都建立主键索引 id,而且这个id还是 AUTO_INCREMENT 最好是INT类型 ,
如果你有一张表name是唯一的,并且你给name这个字段设立为主键,这样效率会减低,因为使用VARCHAR类型的主键低于INT类型.
而且，在MySQL 数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如:mysql的分表, 集群等
(8)某些情况下使用ENUM而不是VARCHAR (但是也有一些人说慎用ENUM类型)
如果你的表中的某个字段 例如: 省份,而这个字段经常出现的且只会出现的只有 广东省,福建省,海南省等国家的所有省份.
那么你应该给该字段的类型应该是ENUM而不是VARCHAR.
ENUM 类型是非常快和紧凑的。在实际上，其保存的是TINYINT，但其外表上显示为字符串。
例如，指定为 ENUM("one", "two", "three") 的一个列，可以有下面所显示的任一值。每个值的索引值也如下所示：
| 值 | 索引值 |
NULL |
NULL |
"" |
0 |
"one" |
1 |
"two" |
2 |
"three" |
3 |
(9) 使用PROCEDURE ANALYSE()取得建议
其中 optimal_fieldtype会推荐我们使用怎么样的数据类型,当表中数据了越大的时候,就越准确,但是不一定是完全准确的,你需要思考..哈哈哈哈
(10)建立表的时候使用NOT NULL,而且尽量给表设定默认值
NULL 需要额外的空间,mysql的上的文档是这么说的
如果你的表的字段是int 那么应该给默认值 DEFAULT 0 ,如果是varchar类型 DEFAULT ' '
mysql的部分优化先暂时讲这么多,如果有疑惑的或者是有其他见解的欢迎评论..
今天php7发布了,php7的性能相比于原来提高了百分之40%-200%.
