mysql优化

-一、硬件优化（优化的主要点）

    01.CPU----最好是64位的，例：8-16颗CPU

    02.内存----例：96G-128G，3-4个实例

    03.disk----数量越多越好，性能：ssd（适合高并发业务）>sas（普通上线业务）>sata（适合线下）

        RAID----部署合适的RAID

    04.网卡----多块网卡bond，以及buffer，tcp优化

-二、软件优化

    01.操作系统----x86_64

    02.软件：mysql，编译优化

-三、my.cnf里面参数优化 (一般效果不是很明显)

    01.使用调优工具

-四、sql语句的优化

a.抓取出慢查询sql

        b.可以使用慢查询日志分析工具

        （对于查询慢的select语句，可以使用explain查看查询；一般在唯一值或者重复比较少的列上面简历索引，如果多条件查询中的条件，全部都是唯一值少，重复较多，这时候可以采用联合索引，对于多个列一起进行建立索引）

        c.晚上零点分析慢查询，发到核心开发，dba分析。

    02.拆分大的复杂的sql语句

        子查询，jion查询。

    03.数据库是存储数据的地方不是计算数据的地方

    04.对于LIKE "%XXXIII%"之类的前后都是%的查询，sql索引不起作用（一般是一些搜索会出现这种问题）

    解决：a.从业务上，可以实现用户登录后再查询或者搜索，减少搜索次数

        b.如果大量频繁的搜索，一般是爬虫在爬，这时就可以分析web日志，将频繁查询的IP封掉

        c.配置主从同步，程序实现读写分离

        d.在数据库前端加memcached缓存服务器

        e.不用数据库进行查询，用搜索软件

-五、架构上的优化

    01.业务拆分，搜索功能，like "%XXX%"，一般不用mysql数据库

    02.数据库前端必须加cache，例如：memcached

    03.业务拆分，某些业务应使用nosql持久化存储

     比如：粉丝关注、好友关系等

    04.动态数据静态化

    05.数据库集群与读写分离，一主多从

    06.单表过多，进行拆库拆表

-六、流程，制度，安全优化

01.使用show status;

    通过观察Queries(当前执行的查询数量)、Threads_connection(几个线程已经连接)、Threads_running(几个线程正在执行)，并编写脚本，刷新观察是否周期性故障或波动；一般由于访问高峰或缓存崩溃引起。

    可以加缓存或者更改缓存失效策略，使失效时间分散或者夜间失效。

02.使用show processlist

    这个命令是显示当前所有连接的工作状态。

    脚本中可以使用：mysql -uroot -e 'show processlist\G' |grep State|uniq|sort -rn >>proce.txt

    然后：more proce.txt | sort|uniq -c |sort

    查看一些state状态，特别注意copying to tmp table\sending data\sorting result一些状态

索引优化

索引类型和表类型优化

    1.索引类型

    2.B-tree索引的常见误区

    3.聚簇索引

    4. 索引覆盖

    5. 例：3、4知识点解决此问题

高性能的索引策略

    -一、索引长度与区分度

    -二、伪哈希函数降低索引长度

    -三、大量数据查询操作优化

    -四、索引与排序

    -五、重复索引和冗余索引

    -六、索引碎片修复

开发中常用优化

    -一、explain分析sql效果

        01.id

        02.select_type

        03.table

        04.type

        05.Possible_keys

        06.key

        07.key_len

        08.ref

        09.rows

        010.extra

    -二、in型子查询陷阱

1.索引类型

    1.1B-tree索引

    1.2hash索引

        hash索引的理论查询时间复杂度是O(1);

2.B-tree索引的常见误区

    2.1在where条件后（比如where a=10 and b=20;）,不需要a、b都加上索引，因为是独立的索引，同时只能用上一个；

    2.2在多列上建立索引后，索引发挥作用将要满足前缀条件。

    例如index(a,b,c)；

例：一个表有一个联合索引(c1,c2,c3,c4):

    where c1=x and c2=x and c4=x and c3=x;四个索引都用到，c4\c3顺序不影响，MySQL会自动优化。

    where c1=x and c2=x and c4=x order by c3;c2下面的c3是有序的，c3也发挥作用了，不用查找的时候只有c1和c2发挥作用，而c3发挥作用是在排序上。

03.当索引c2,c3位置放反，则c2就是一个常量；当where c1=’a’ order by c3,c2,这时需要使用filesort

04.索引和order by的关系

3.聚簇索引

01.myisam的次索引和主索引都指向物理行

02.innodb的次索引指向对主键的引用

InnoDB的主索引文件上，直接存放该行数据，成为聚簇索引，次索引指向对主键的引用。

B-tree可能会有节点分裂，因为innodb的节点带有数据，索引分裂带来的影响可能会比较大；因此我们最好采用递增的整型来做主键；如果是无规律的数据，将会产生叶子的分裂，影响效率。

4.索引覆盖

如果查询的列敲好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再查找数据，这种查询速度相当快。

Extra：using index

5.例：3、4知识点解决此问题

Create table A{
id varchar(64) primary key,

Ver int,

}

在id、ver上面有联合索引，10000条数据，在表中有几个很长的字段：varchar(3000);

问题：select id from A order by id;特别慢，但是select id from A order by  id,ver很快。

分析：

如果在myisam上：

可见两个查询速度是差不多的；

推断：

01.是innodb引擎

02.有多个比较长的列：如果是聚簇索引，导致沿ID排序时，要跨好多小文件块。

03.如果没有多长的几个char字段，差别也不会这么大。

高性能的索引策略

-一、索引长度与区分度

注：区分度达到0.1索引的性能就可以接受。

Key-len:14:因为一个汉子在utf8编码中有3个字节的长度，因为是varchar有变化，会有2个字节的变动，所以是4*3+2=14；如果是char，如果不指定索引长度，那么就是42字节的长度。

-二、伪哈希函数降低索引长度

对于前缀不易区分的列，如url:http://www.baidu.com，列的前几个字符都是不一样的，不易区分，解决：

01.把列的内容倒过来存储，并建立索引

02.伪哈希效果

(upda tb_name set crcurl=crc32(utl));

-三、大量数据查询操作优化

01.使用limit offset

Select id,name from tb_name limit 10000,10; //然后数字逐渐增大；

>show profiles;

>show profile from query 号数；

//可以发现，大多数时间都浪费在了，sending data上；因为limite 是先查询再跳过。

优化：可以限制查询的条数，limit不超过10000之类的。

02.先跳过，再查询

Select id,name from tb_name where id>50000 limit 10;

限制：01）需要保证数据物理上没有被删除过；02）数据不物理删除，只是逻辑删除。

03.延迟关联

Select lx_com.id,lx_com.name from lx_com inner join (select id from lx_com limit 50000000,10) as tmp on lx_com.id=tmp.id;

-四、索引与排序

排序可能出现两种情况：01.对于覆盖索引，直接在索引上查询时，就是有序的。02.先取出数据，形成临时表，做filesort文件排序，但文件可能在磁盘上，也可能在内存中。

-五、重复索引和冗余索引

01.重复索引

Alter table goods add index ck1(click_count);

Alter table goods add index ck2(click_count);

02.冗余索引

Alter table goods add index ck1(click_count);

Alter table goods add index cat_click(cat_id,click_count);

Index(x,y)和index(y,x)是不一样的。

-六、索引碎片修复

-一、explain分析sql效果

01.id

SELECT识别符。这是SELECT查询序列号。

02.select_type

2.1 simple 它表示简单的select,没有union和子查询

2.2 primary 包含子查询或者派生查询

2.3非from子查询

2.4from型子查询

2.5 union / union result

03.table

查询的表名，未必肯定为表名，也可能是表的别名。

04.type

连接类型。

4.1 system

表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计

4.2 const

表最多有一个匹配行，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快

记住一定是用到primary key 或者unique，并且只检索出两条数据的 情况下才会是const,

4.4 ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

4.5 ref_or_null 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

4.6 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

4.7 unique_subquery

4.8 index_subquery

4.9 range 给定范围内的检索，使用一个索引来检查行。

4.10 index     该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）（扫描所有索引文件）

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

4.11  ALL  对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。(全表扫描)

05.Possible_keys

可能用到的索引列。

06.key

实际用到的索引列

07.key_len

key的长度。

08.ref

ref列显示使用哪个列或常数与key一起从表中选择行。ref列显示使用哪个列或常数与key一起从表中选择行。

简单来说就是，通过索引列，直接索引某一行。

09.rows

估计扫描了多少行。

010.extra

10.1 using index

该值表示相应的select操作中使用了覆盖索引（Covering Index）.

10.2 using where

表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。

10.3 using temperary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

这个值表示使用了内部临时(基于内存的)表。一个查询可能用到多个临时表。有很多原因都会导致MySQL在执行查询期间创建临时表。两个常见的原因是在来自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：

1)内部临时表占用的空间超过min(tmp_table_size，max_heap_table_size)系统变量的限制

2)使用了TEXT/BLOB 列

10.4 using filesort

MySQL中无法利用索引完成的排序操作称为“文件排序”

10.5 range checked for each recond

-二、in型子查询陷阱

Select id,name from lx_com where id in (select id from idea);

#explain之后可以发现，先是全表查询lx_com，再全表查询idea，查询很慢。

改进：

Select ls_com.id,name from lx_com inner join idea on lx_com.id=ids.id;

#explain之后发现，先查询ids，然后查询ls_com(并且，type是eq_reg)

本文出自 “秦斌的博客” 博客，谢绝转载！