MySQL阶段七——MySQL优化
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.11ALL对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记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)
页:
[1]