MySQL的使用优化主要从优化库表结构、使用合适的索引、优化查询等方面考虑。mysql
在MySQL中表示字符串的类型有多种,其中常见的有Char和VarChar,BLOB和TEXT以及能够实现某些优化的ENUM。咱们要认识到不一样的数据类型在存储和使用的区别,而后合理的使用就能实现优化。sql
关于MySQL中数据类型的介绍,能够参看个人另外一篇博文: MySQL的常见操做数据库
Char和VarChar缓存
Char是定长类型,MySQL会根据定义的字符串的长度分配足够的空间,而且MySQL在存储CHAR值的时候会删除末尾全部的空格。对于常常变动的数据,通常采用CHAR来进行存储,由于CHAR类型在变化的时候不容易产生碎片。服务器
VARCHAR是变长类型,它比CHAR更加节省空间,可是VARCHAR在数据变化的时候容易产生碎片,因此通常用于做为不常常变化的数据的数据类型。VARCHAR须要多使用一个或者两个额外字节来记录字符串的长度,若是列的最大长度小于等于255字节就用一个额外的字节来存储长度,不然使用两个字节。例如VARCHAR(10)的列须要11个字节的存储空间,VARCHAR(1000)的列须要1002个字节。并发
注意,在5.0或者更高的版本中,MySQL在存储或者检索VARCHAR数据类型时保留末尾空格,可是在4.1或者更老的版本中,MySQL在存储或者检索VARCHAR数据类型时和CHAR同样都是删除末尾的空格app
BLOB和TEXT函数
BLOB即SMALLBLOB,TEXT即SMALLTEXT。BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符串的方式来存储。性能
使用ENUM来代替字符串类型测试
MySQL在存储枚举的时候很是的紧凑,会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每一个值在列表中的位置保存为整数,而且在表的.frm文件中保存"数字-字符串"映射关系的"查找表"。
例如:create table enum_test(e enum('apple','banana','pear'));insert into enum_test(e) values('apple'),('banana'),('pear');而后咱们所插入的数据在表中实际上是存储为整数的。
MySQL能够为整数类型指定宽度,如INT(1),INT(20),可是这对大多数应用是没有意义的。MySQL所指定的整数类型的宽度只是用于设置一些MySQL客户端用于显示字符的个数,对于存储和计算而言,INT(1)和INT(20)不会限制值的合法范围,这两种类型都是相同的。
DateTime和TimeStamp
DATETIME和TIMESTAMP是两种日期类型,两种类型在MySQL中存储数据的格式彻底相同(都是yyyy-MM-dd HH:mm:ss),可是二者也有不一样之处。
DATETIME能保存大范围的值,从1001年到9999年,精度为秒。MySQL采用8个字节来存储DATETIME数据类型所包含的值。默认状况下,MySQL以一种可排序的、无歧义的格式显示DATETIME的值。
TIMESTAMP保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数,它和UNIX的时间戳相同。TIMESTAMP仅仅使用4个字节的存储空间,因此它能表示的时间范围也比DATETIME小,只能表示从1970年到2038年。TIMESTAMP也有DATETIME没有的特殊属性,默认状况下,若是插入时没有指定第一个TIMESTAMP列的值,MySQL则设置该列的值为当前时间。
索引优化是一个很大的方面,这里只是简单的介绍一些基本使用,事后会推出关于索引优化与设计的专题。
在MySQL中,索引是在存储引擎层而不是服务器层实现的。Mysql中索引结构有:B-Tree索引、哈希索引、空间数据索引(R-Tree索引)、全文索引等索引结构,不一样的存储引擎对于上述索引结构的实现不一样,并且也不是全部的存储引擎都有这5种索引结构类型。
索引类型:
MySQL中的索引类型主要有5种:
ALTER TABLE article ADD INDEX index_title_name ON title(100);、CREATE INDEX index_name ON table(column(100)),或者直接在建立表的时候定义索引index index_title_name(title(100))ALTER TABLE article ADD UNIQU index_title_name ON title(100);、CREATE UNIQUE INDEX index_name ON table(column(100))或者直接在建立表的时候定义索引UNIQUE index_title_name(title(100))ALTER TABLE article ADD FULLTEXT index_content(content)、CREATE FULLTEXT INDEX index_content ON article(content)和FULLTEXT (content)。索引方法:
可使用B树索引的查询:
使用B树索引的限制
Hash索引的特色:
Hash索引是基于Hash表实现的,只有查询条件精确匹配Hash索引中的全部列时,才能使用Hash索引,Hash索引只适用于等值查询不适合模糊查询和范围查询。
对于Hash索引中的全部列,存储引擎都会为该列的每一行计算一个Hash码,Hash索引中存储的就是Hash码。
使用Hash索引的限制:
B树索引与Hash索引在不少地方是不一样的。B树索引除了能加快数据的查找速度以外还能够作到排序和分组,B树索引的叶子节点存储了索引关键字的值,能够直接经过索引查找关键字的信息从而避免了访问数据行。可是Hash索引的叶子节点中存储的是关键字信息的Hash码,咱们须要将查询信息转化成Hash在表中找到对应的数据行才能查找到数据的信息。所以Hash索引不能做为覆盖索引来使用。
覆盖索引:
若是一个索引包含全部须要查询的字段的值(where语句的参数、order by的参数、group by的参数),那么咱们一般称这个索引为覆盖索引。对于Memory存储引擎不能使用覆盖索引,查询过程当中若是包含了太多的列(如select *)也不适合使用覆盖索引。 使用覆盖索引也有不少的优势。
InnoDB做为MySQL最为著名的存储引擎,这里要作特别的介绍。InnoDB中存储引擎支持B+树索引、全文索引和哈希索引。InnoDB存储引擎支持的哈希引擎是自适应的,InnoDB存储引擎会根据表的使用状况自动为表生成哈希索引,不能人为干预是否在一张表中生成哈希索引。
传统意义上的索引就是指的B+树索引,这是目前关系型数据库系统中查找最为经常使用和有效的索引,其构造就是采用了二叉树的思想,根据键值对快速找到数据。经过B+树索引找到被查找数据行所在的页,而后数据库把页读入到内存,再在内存中进行查找,找到对应的数据。
InnoDB使用的是行锁,只有在修改行时,才会对行进行加锁。使用索引可以使得数据在查询过程当中锁定更少的行,增长了数据处理的并发性,提升了数据库的性能。
select id from article where id+1=5等状况的出现,不然索引将不能使用。pt-duplicate-key-checker h=127.0.0.1查找重复和冗余的索引,而后将重复冗余的索引删除。MySQL链接状态
MySQL客户端和服务器之间的通讯协议是"半双工"的,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器端发送数据,两个动做不能同时发生。对于每个时刻,能够经过命令show full processlist来查看mysql当前链接的状态(Command列就表明当前的状态)。
MySQL的状态以下:
Sleep: 线程正在等待客户端发送新的请求
Query: 线程正在执行查询或者正在将结果发送给客户端。
Locked: 在MySQL服务器层,该线程正在等待表锁.
Sorting result: 线程正在对结果集进行排序。
Copying to tmp table [on disk]: 线程正在执行查询,而且将其结果集都复制到一个临时表中,这种状态要么是在作GROUP BY操做,要么是文件排序操做,或者是UNION操做。若是状态上有on disk的标记,那么表示MySQL正在将一个内存临时表放到磁盘上。
Analyzing and statistics: 线程正在收集存储引擎的统计信息,并生成查询的执行计划。
Sending data: 这表示线程或者在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。
MySQL链接过程
在上面MySQL执行查询的过程当中,服务器已经提供了一些SQL的优化措施,咱们也须要正确理解并使用这些个优化措施
show status like 'last_query_cost'能够查询当前会话的last_query_cost值来得知mysql计算的当前查询的成本,mysql会进行评估并获得成本最小的执行计划。优化数据访问的关键在于:减小数据访问量,只检索必要访问的数据,保证向数据库发出的查询数据量只是实际须要的数据量。
为了实现数据访问量的优化,可使用: