MySQL阶段七——MySQL优化

蔷薇525

　　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>　　分析：
　　如果在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>　　>show profiles;
　　>show profile from query 号数；
　　//可以发现，大多数时间都浪费在了，sending data上；因为limite 是先查询再跳过。
　　优化：可以限制查询的条数，limit不超过10000之类的。
　　02.先跳过，再查询

　　Select>　　限制：01）需要保证数据物理上没有被删除过；02）数据不物理删除，只是逻辑删除。
　　03.延迟关联

　　Select lx_com.id,lx_com.name from lx_com inner join (select>-四、索引与排序
　　排序可能出现两种情况：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效果
　　>explain select *****;
　　Id:
　　Select_type:
　　Table:
　　Type:
　　Possible_keys:
　　Key:
　　Key_len:
　　Ref:
　　Rows:
　　Extra:
　　1 row in set(0.00 sec)
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>　　#explain之后可以发现，先是全表查询lx_com，再全表查询idea，查询很慢。
　　改进：

　　Select ls_com.id,name from lx_com inner join>　　#explain之后发现，先查询ids，然后查询ls_com(并且，type是eq_reg)