深度解析mysql优化手段，轻松解决千万级数据量操作

1.理解索引优化作用

索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。

2.索引与优化

2.1 索引类型

mysql支持很多的数据类型，选择合适的数据类型对于数据库存储操作性能有很大的影响。

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。

(3)尽量避免NULL：应该指定列为NOT  nuLL,在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂

2.2索引选择标识符

选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。
(1)    整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。

(2)    字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

3.索引入门

3.1索引简介

对于任何DBMS，索引都是进行优化的最主要的因素。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。
如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

3.2索引类型

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

3.3基本索引类型

3.3.1B-tree索引

3.3.2 Hash索引

2.编写sql其他优化策略

1.'*'通配符用具体查询字段代替

实现过程如下图：mysql自带分析sql语句执行语句工具 “profiles"

启动dos命令，进入mysql数据库中

设置show profiles;提示为空，前一步需要设置成开启，"set profiling=1;"。

查询表数据，多执行几次，进行show profiles 查询sql语句执行时间 （实现数据太少还体现不出来，第一次时间长是因为完全加载数据，第二次以后能用到缓存数据）

很多数据体现出现，具体查找某些字段，能节约占内存空间，并且提升查询时间

2.添加索引

通过如上方法，测试添加索引以后（直接寻找数据）耗时是没有添加索引耗时的十几分之一。而且我们在mysql数据库中，默认添加的索引，一般都为BTREE索引

1.mysql添加索引方式

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

1.添加PRIMARY KEY（主键索引）：

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

2.添加UNIQUE(唯一索引) ：

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

 

3.添加INDEX(普通索引) ：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

或者 create index index_name('column'(length)) on talbe_name;

如果是char,varchar类型，则指定的长度可以稍微短点，如果是 log,text 则必须指定相同长度

 

4.添加FULLTEXT(全文索引) ：

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

 

5.添加多列索引：

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age   usernname,city   usernname  为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：
[code]
SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin"

6.建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

复制代码代码如下:

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

复制代码代码如下:

SELECT * FROM mytable WHERE username like'admin%'

而下句就不会使用：

复制代码代码如下:

SELECT * FROM mytable WHEREt Name like'%admin'

3.利用查询缓存，优化查询速度

MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，

因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而 开启缓存。

4.EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

加了索引name，实现快速定位，只查询一行数据。

5.当你查询语句只是为了查询是否存在，或者只有一条数据时候，用limit 1查询

select 1 from user where country="china" limit 1; //只是为了查询中国用户是否存在

6.永远为每张表设置一个主键

主键并且为 int 类型，当设置为varchar类型时候，可能就会降低检索速度。

7.使用enum而不是使用varchar，对于有些字段例如性别

参考文章：实现enum 到java 类型映射（ENUM 类型是非常快和紧凑的）

http://blog.csdn.net/theonegis/article/details/41749773

1.新建enum类型

2.映射到java类型中去

8.使用procedure analyse()分析你建表字段

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

9.尽可能的使用 NOT NULL尽可能的使用 NOT NULL

首先，问问你自己“Empty”和“NULL”有多大的区别(如果是INT，那就是0和NULL)?如果你觉得它们之间没有什么区别，那么你就不要使用NULL。(你知道吗?在 Oracle 里，NULL 和 Empty 的字符串是一样的!)

不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂

10.垂直分割（也就是把没用的字段和不经常操作的字段独立别的表中）

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。

你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，

会是极数级的下降

11.固定长度表，提升优化速度

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

可以采用手段10，进行分割表结构为：固定长度，非固定长度

12.拆分打的Delete语句和insert语句

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个

网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

13. 越小的列会越快

对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。

稍微的总结下优化策略算法：

1.合理的增加索引，并且不去破坏索引

2.合理的设计表结构 

13,12，11,10

3.尽量避免在sql语句中，调用mysql自己内部的一些方法

参考文章:

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html   //深度理解索引和优化，比较厉害的一个大神

http://www.cnblogs.com/phpshen/p/6150709.html  //mysql添加索引进行优化，引入"profiles" 查询sql执行时间

http://www.cnblogs.com/itsharehome/p/4955162.html  //基本索引的分类

http://www.jb51.net/article/49346.htm   //mysql创建索引手段，技巧

http://www.ihref.com/read-16422.html   //常用的优化手段