Contents

总结

索引的创建，和执行计划的查看
根据情况采取 query_cache 功能
根据情况采取分区、分表、读写分离集群

索引

索引的使用规范

禁止在频繁变更的表上添加索引
联合索引应该把区分度更大的字段放前面，效果更好
Innodb 不建议使用过长的字段作为主键，因为是聚集式索引，其他索引会存储不压缩的主键
最左匹配原则有2个含义：like匹配通配符不能在前面、联合索引顺序也是按照从左到右的顺序
索引字段查询条件中含有函数或表达式，索引无效：比如 emp_no -1=3， left(title,6）=‘huangz’
少基数的字段不建议使用索引，性能提升不明显，同时要DML的时候要维护索引，空间和性能上浪费。比如男女字段，比例大约1:1，提升不明显，如果男女比率 1000:1，那么查询女的用户的时候，性能提升明显。
mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。
删除不再使用的索引
字符串使用短索引，可以提高查询速度和节省磁盘空间和I/O操作

索引的算法

一般还是BTree用的更多，除非该表多是精确查询的话。InnoDB 和 MYISAM不支持HASH索引。
ALTER TABLE user3 ADD INDEX INDEX_PASSWORD_HASH USING HASH (password);

hash算法索引

精确查找（等值查找、in 查找、不等于查找）效率最高，但是范围查找效率低
排序操作时无效
可能存在hash冲突的情况，取决于mysql对应键的hash算法处理，所以也不一定比Btree快
组合的hash索引无法单个字段使用，也不支持多列联合索引的最左匹配规则

BTree算法(默认的算法)索引

排序
范围查找
联合索引的最左匹配原则

测试结果：5.6.13版本

btree 索引（联合索引）支持 like查询，最左匹配
btree 联合索引支持最左匹配规则

全文索引

适用场景：数据量不是特别大，小于10万条的时候适用，比like查询支持更多的功能。

默认全文索引不区分大小写，若要分大小写，column的character set要从utf8修改成utf8_bin
MATCH()函数中的列必须与FULLTEXT索引中的列相同。如MATCH(title,body)与FULLTEXT(title,body)
支持扩展全文搜索，根据词库查询相关的扩展数据

中文全文索引相关：

5.7中文全文检索介绍博客
https://www.2cto.com/database/201704/624860.html
5.6.4之后支持中文的索引，但是在5.7中支持就很好了，英文的功能，中文也支持了。非常棒的功能。

MySQL不会断中文字：MySQL内建的字依据是空白、逗号和点，对此内建机制的白痴解法是，存中文字时自行塞入空白断字
查询字符串最少四个字符的限制：所以一二三个中文字都不能查，必须将ft_min_word_len从预设的4改成1。

过滤模式 过滤 -YourSQL
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL-YourSQL' IN BOOLEAN MODE);
扩展全文索引
SELECT * FROM articles
 WHERE MATCH (title,body)
AGAINST ('database' WITH QUERY EXPANSION);

联合索引

https://www.cnblogs.com/tgycoder/p/5410057.html

联合索引最左匹配原则,且查询优化器会自动调整顺序来使用这个原则

比如联合主键索引 titles表（emp_no，title，from_date)
如果条件是 emp_no 和 from_date，则索引只用了前面4个字节，如果条件是 emp_no和title，则可以使用更多的索引字节。

EXPLAIN select * from titles where from_date='2017-01-03 00:00:00'and title='tech' and emp_no=3   ;

联合索引的范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。

order by 索引

只有查询的列全部是索引，或者是部分索引值时，才可以走索引查询，否则还是全表扫描。
EXPLAIN select uid from friends order by uid

应用场景，查询所有的身份证号码，并且按照身份证字母顺序排序，在该字段建立索引，查询会走索引。
select identity_id from user order by identity_id;

执行计划

http://blog.csdn.net/mr253727942/article/details/51201222

select_type
每个select子句的类型，主要分成下面几种：
a:SIMPLE:查询中不包含任何子查询或者union
b:PRIMARY:查询中包含了任何复杂的子部分，最外层的就会变成PRIMARY
c:SUBQUERY:在SELECT或者WHERE列表中包含了子查询
d:DERIVED：在FROM中包含了子查询
e:UNION:如果第二个SELECT出现在UNION之后，则被标记为UNION，如果UNION包含在FROM子句的子查询中，外层SELECT会被标记为：DERIVED
f：UNION RESULT从UNION表获取结果的select
type
是指MySQL在表中找到所需行的方式，也就是访问行的”类型”，从a开始，效率逐渐上升:
a：all：全表扫描，效率最低
b:index:index会根据索引树遍历
c:range:索引范围扫描，返回匹配值域的行。
d:ref:非唯一性索引扫描，返回匹配某个单独值的所有行。一般是指多列的唯一索引中的某一列。
e:eq_ref:唯一性索引扫描，表中只有一条记录与之匹配。
f:const、system：主要针对查询中有常量的情况，如果结果只有一行会变成system
g:NULL:显而易见，既不走表，也不走索引
possible_keys
possible_keys列预估了mysql能够为当前查询选择的索引，这个字段是完全独立于执行计划中输出的表的顺序，意味着在实际查询中可能用不到这些索引。
如果该字段为空则意味着没有可使用的索引，这个时候你可以考虑为where后面的字段建立索引
key
这个字段表示了mysql真实使用的索引。如果mysql优化过程中没有加索引，可以强制加hint使用索引

性能明细查看

show profiles;（如果未开启的话，set profiling=1;)
可以查看具体的耗时信息

query_cache功能(默认关闭)

使用场景：多读数据很少写的情况。内存的时候储存sql结果。因为DML的时候缓存会失效。
DML的时候，mysql会自动更新缓存集的。

查看缓存情况
show variables like '%query_cache%';  
设置缓存（针对整个mysql 服务）：
query_cache_size=128M
query_cache_type=1

可以通过 show status like ‘%Qcache%’; 命令中的 Qcache_hits 来查看命中次数。
最简单的使用：（不指定就不走索引）,而且不指定SQL_CACHE不会缓存结果
把query_cache_type=2，这样除非是指定 SQL_CACHE，否则都不读缓存。
select SQL_CACHE * from user;

mysql 分区

参考博客：
http://blog.csdn.net/three_man/article/details/46982105

解决的问题：

分区表解决的是单表数据过大，索引效率低的问题，很适合大量历史数据，少量活跃数据的场景。把数据保存在不同的区域。

条件：

主键要是联合主键，因为分区字段要部分包含在主键当中。分区字段和主键一起作为联合主键。
分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL，除了使用YEAR, TO_DAY等日期函数外，还可以使用其数学函数，比如取模，按7取模是周几等
对分区表的分区键创建索引，那么这个索引也将被分区,分区键没有全局索引一说，索引有效

优势：

精确查询和范围查询都只走相应的分区，海量数据的时候，性能好。

查看分区情况

来查看分区查询走了哪几个块区。
EXPLAIN PARTITIONS select * from pizza_order where CREATED>’2016-02-02 00:01:01’;
查看该表的所有分区块的存储情况
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME=’pizza_order’;

Demo：

创建订单表，按照创建日期进行分区，这样按照日期查询的时候，就会根据区块查询，而不是所有的数据扫描了
CREATE TABLE pizza_order(
id bigint(20) NOT NULL AUTO_INCREMENT,
created DATETIME NOT NULL COMMENT '创建时间',
PRIMARY KEY (id, created)) ENGINE=InnoDB PARTITION BY RANGE(YEAR(created))(
  PARTITION p1 VALUES LESS THAN (2015),   
PARTITION p2 VALUES LESS THAN (2016), 
PARTITION p3 VALUES LESS THAN (2017),
  PARTITION p_latest VALUES LESS THAN MAXVALUE); 
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('3', '2014-01-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('4', '2015-07-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('1', '2016-01-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('2', '2016-07-01 00:00:00');
INSERT INTO `test`.`pizza_order` (`id`, `created`) VALUES ('5', '2017-02-02 00:01:01');
EXPLAIN select * from pizza_order where CREATED='2014-01-01 00:00:00';
EXPLAIN select * from pizza_order where CREATED>'2016-02-02 00:01:01';

List分区,这种如果插入语句不在IN中，则会插入失败

PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

常见类型

水平分区

Range 分区（例如小于2015 年小于2016年小于2017年)
List 分区（例如批次 01 、 02 批次)
HASH 分区（预先设定好几个块，按照hash算法填充)

HASH分区优点在于分布均匀，精确查找比较快，但是不适合范围查找。

垂直分区
把不常用的大字段的字段单独分区出来，提高效率，又不影响数据的结构和完整性。

mysql分表

应用场景就是避免跨表查询，如果频繁的跨表查询的话，还不如不分表。分表不能解决全部的问题。比如我按照年份来分表，你偏要按照name来做查询，肯定需要每个表都做查询，还不如不分。
但是如果我绝大部分都是建立在年份的基础之上来做查询，查询对应的分表，然后在分表上走索引，也都很快的。Merge引擎只是额外的支持了，全表查询的额外功能，大部分操作还是在子表上操作的。

分表的要求

基本表必须是MYISAM类型的。
基本表的数据结构必须一致。

其他

也可以通过过代码的方式实现类似的分表的效果，但是可扩展性很差

特点

插入的时候可以根据规则插入到小表中，提高并发，因为竞争少，索引小。
查询的话，如果是按照分表规则的条件进行查询，可以直接小表，不需要查大表。但是也支持总表查。
水平分表，典型缺点，对于group by或order by之类的查询是灾难

分表和分区的区别

实现方式不同，分区是对mysql进行改造，而分表更多需要代码层面进行改造（读取写都针对子表）
分表的跨表查询，可能也需要union all来拼接。而分区都不需要认为干预。

分区比分表稍微好点，起码对应用透明，不需要在应用层面做sql修改而实现类似的效果。

Tornado_Huang's BLOG

Happy Coding

mysql优化_索引、缓存、分区、分表、执行计划

总结

索引