MySQL性能优化（一）

1.基本硬件环境，服务环境
1.1 硬件环境
1.2 服务环境
1.3 如何选择合适的存储引擎
2.MySQL索引建立与查询的关系
2.1 MySQL索引的概念
2.2 MySQL索引的分类
2.3 MySQL索引数据结构
2.4 MySQL索引的分类
2.5 MySQL索引建立对数据查询的影响
3.MySQL索引对性能的影响
3.1 环境说明
3.2 实验案例
3.3 实验结果
3.4 结论与分析

1、基本硬件环境，服务环境
1.1、硬件环境：
版本信息：5.7.16-10-log。
基本配置：1 CPU，465M。
1.2、服务环境：
1.2.1、主流存储引擎的比较和选择：
InnoDB
InnoDB是MySQL使用率最高的存储引擎，目前市面上至少95%的MySQL采用该存储引擎，因此从MySQL5.5开始，它替代MyISAM成为默认的存储引擎。它有如下几个特点：
1）支持事务：采用MVCC（多版本并发控制）来支持高并发，实现了四个ANSI标准的隔离级 别，默认为Repeatable Read，并且通过间隙锁(next-key lock)防止幻读。
2）InnoDB的表是基于聚簇索引(cluster index)，对主键查询有很高的性能，但它的二级索引必须包含主键列。
3）支持热备份。
4）支持行级锁。（这个很重要！更好的支持多线程多用户并发）
MyISAM
1）不支持行级锁，也就是说任何操作都对整张表加锁。影响性能。
2）不支持事务，无法回滚，崩溃后无法安全恢复。
3）支持全文索引、压缩、空间函数等。MyISAM设计简单，数据以紧密格式存储，在某些场景下性能很好，但它最典型的问题还是表锁。
1.3、如何选择合适的存储引擎？
准则1：除非需要用到InnoDB没有的特性，否则都应该优先选择InnoDB引擎。
准则2：不要混合使用多种存储引擎。

2、Mysql索引建立与查询的关系
鉴于实际应用环境，以下讨论的都是基于InnoDB引擎。
2.1 MySQL索引的概念：官方定义为索引（Index）是帮助MySQL高效获取数据的数据结构。所以索引的本质是一种满足特定查找算法的数据结构的集合，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。
2.2 索引的分类：
MySQL主要提供2种方式的索引：B-Tree（包括B+Tree）索引，Hash索引。
B树索引具有范围查找和前缀查找的能力，对于N节点的B树，检索一条记录的复杂度为O(LogN)。哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。显然，如果值的差异性大，并且以等于查找为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。
综上，在实际业务场景中，Hash索引因为只能支持等值查找，所以应用范围和概率极低，在以下的论述中将不予讨论。
2.3 Mysql索引数据结构：目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。
2.4 Mysql索引的分类：普通索引、唯一索引、主键索引、组合索引。
2.5 Mysql索引建立对数据查询的影响：
2.5.1 简单单表查询：
创建表tab_sell_r_order_detail_temp
复制这张表的结构一部分数据，进行10w单表10w级别的测试。
初始化时，这张表有1809662条数据。
全表查询：
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t;
耗时100ms。
条件查询:
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid = 13;
耗时10ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid = 5 and t.orderid = 1;
耗时10ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid in(‘5’,’13’);
耗时50ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid <100;
耗时50ms。
考虑到以下情况：
1.WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引。
2.如果条件中有or，即使其中有条件带索引也不会使用。
3.like查询是以%开头则不会使用索引。
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引。等等情况，作为定性分析建立索引对单表查询的影响，只做对全表查询和条件查询的分析。
现在来对表建立索引：

1.全表查询：select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t;耗时90ms。
2.条件查询 ：
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid = 13;
耗时10ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid = 5 and t.orderid = 1;
耗时10ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid in (‘5’,’13’);
耗时50ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail_temp t where t.userid < 100;
耗时50ms。
由此可见，通过单表10w级别的数据，索引建立前后，对查询效率的形象甚小，基本可以忽略不计。
现在对单表180w级别的数据做索引后查询分析。
未建立索引：
1、全表查询：
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t;
耗时1520ms。
2、条件查询：
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid = 13;
耗时460ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid = 5 and t.orderid = 1;
耗时470ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid in (‘13’);
耗时650ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid < 100;
耗时660ms。
建立索引：（同上图）
1、全表查询：
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t;
耗时1640ms。
2、条件查询：
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid = 13;
耗时20ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid = 5 and t.orderid = 1;
耗时10ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid in (‘13’);
耗时20ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t where t.userid < 100;
耗时670ms。
select SQL_NO_CACHE t.* from tab_sell_r_order_detail t FORCE INDEX (index_userid) where t.userid < 100;
耗时1490ms。
通过对比可以发现。当单表数据量达到180w+级别的时候，索引建立后，对目标表进行全表扫描仍未有性能上的提升。但是当进行条件查询时，效率提升很多，效果是很明显的。值得注意的一点是，执行查询语句时，当mysql查询优化器分析当前语句，认为该条语句在不使用索引比使用索引效率高的话，会默认不使用索引，这种情况和表本身的聚簇因子相关，优化器可能会认为会扫描到表中大部分数据块，相当于全表扫描，这种情况下如果强制使用索引会导致效率变慢。
2.5.2 复杂多表查询
建立这样的一个多表结构：
用户账户表（tab_sell_account）数据量：6772行。
销售信息表（tab_sell_order_detail）数据量：276790行。
网点信息表（tab_sell_network）数据量：4059行。
条码机身编号关联表（tab_sell_base_machine）数据量：3005172行。
机器销售状态表（tab_sell_r_sell_status）数据量：276775行。
现查询需求是：查询某个导购员销售的所有的机器信息数据。包括：
产品名称，sn码，销售时间，销售网点名称，导购员姓名，顾客姓名，顾客电话，顾客地址，机身编码等维度。
SELECT SQL_NO_CACHE
b. NAME produce_name,
a.sn sn,
a.create_time create_time,
d. NAME network_name,
e.true_name true_name,
a.customer_name customer_name,
a.customer_telephone customer_telephone,
a.customer_address customer_address,
b.Softwarecode machine_no
FROM
tab_sell_order_detail a
LEFT JOIN tab_sell_base_machine b ON a.sn = b.Flowcode
LEFT JOIN tab_sell_r_sell_status c ON a.sn = c.sn
LEFT JOIN tab_sell_network d ON a.network_id = d.id
LEFT JOIN tab_sell_account e ON a.userid = e.id
WHERE
a.userid = 17
可以看出，执行这样一个sql耗时12.64s，时间是相当长的。基本上对于业务系统是不可以接受的。
现在来根据查询需求建立索引,对于复杂关联关联查询，建立索引的基本原则为：
1.在关联表的字段之间建立索引。
2.在查询条件的字段上建立索引。
用户账户表（tab_sell_account）建立索引
销售信息表（tab_sell_order_detail）建立索引
网点信息表（tab_sell_network）建立索引
机器销售状态表（tab_sell_r_sell_status）建立索引
条码机身编号关联表（tab_sell_base_machine）建立索引
索引建立完毕，执行查询：

从sql执行计划可以看出，基本上创建的索引都用到了，而且数据扫描行数非常少。可见，建立索引前后，查询时间从12.64s到20ms性能提升了632倍！

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
Tips：需要先对sql本身先进行优化，在进行索引优化。
3.MySQL索引对性能的影响
以上讨论了Mysql索引和查询的关系中，可以看见，适当的给表加上索引可以很大的程度的提升查询性能。那么对于写数据，索引建立前后对效率有什么影响呢？
3.1 环境说明：
用mysqlslap压测mysql性能
压测的目的是为了尽量模拟真实情况。测试的表都是由10个int型字段和10个字符串型字段组成。测试项目使用innodb引擎。测试的方法都是用两个线程并发，一共跑100000个请求。
压测的机器用的是自己的笔记本。CPU是2.7 GHz Intel Core i5， 8G内存， 256G闪存。
3.2 实验案例：
使用的mysqlslap参数释义如下：
–concurrency=2 两个线程
–iterations=1 执行一次
–number-int-cols=10 10个int型字段
–number-char-cols=10 10个字符串字段
-a 等同于–auto-generate-sql
–auto-generate-sql-add-autoincrement 创建auto increment的主键
–auto-generate-sql-secondary-indexes=2 创建2列索引
–auto-generate-sql-guid-primary 创建guid作为主键
–number-of-queries=100000 所有并发线程的请求次数和
–auto-generate-sql-load-type=write/key/read/update/mixed 测试的query类型
–engine=myisam,innodb 存储引擎
3.2.1 写性能测试
写性能测试1，guid做主键, 无索引
测试命令：
mysqlslap -u root –p 123456 –concurrency=2 –iterations=1 –number-int-cols=10 –number-char-cols=10 -a –auto-generate-sql-guid-primary –number-of-queries=100000 –auto-generate-sql-load-type=write
–engine=innodb
执行结果：
不加索引的情况下，每秒处理12818次。
写性能测试2，guid做主键，有索引（2列）
测试命令：
mysqlslap -uroot -p123456 –concurrency=2 –iterations=1 –number-int-cols=10 –number-char-cols=10 -a –auto-generate-sql-guid-primary –auto-generate-sql-secondary-indexes=2 –number-of-queries=100000 –auto-generate-sql-load-type=write –engine=innodb
执行结果：
加两列索引的情况下，每秒处理11080次。
写性能测试3，guid做主键，有索引（10列）
测试命令：
mysqlslap -uroot -p123456 –concurrency=2 –iterations=1 –number-int-cols=10 –number-char-cols=10 -a –auto-generate-sql-guid-primary –auto-generate-sql-secondary-indexes=10 –number-of-queries=100000 –auto-generate-sql-load-type=write –engine=innodb
执行结果：
加10列索引的情况下，每秒处理7867次。
3.3 实验结果
项目 innodb每秒吞量

写性能测试1，guid做主键, 无索引 12818

写性能测试2，guid做主键，有索引（2列） 11080

写性能测试3，guid做主键，有索引（10列） 7867
3.4 结论与分析
结论：在包含10个int字段，10个字符串字段的表中，在创建2列索引后写性能比未创建索引时下降14%；而在创建10列索引后写性能比未创建索引时下降39%。综上，在MySQL数据表写入时，表索引创建的个数和表写入性能呈反比，即索引创建越多，写入效率越低。

分析：实际上，当建立索引后mysql表写入数据效率降低很好理解。因为索引的本质其实是数据结构，所以当每次写入时会锁表建立索引结构和创建或更新索引文件也需要时间。所以，在实际业务中，读频繁，写稀疏这种场景下，应当采取以空间换时间策略合理的增加索引的建立以提升业务系统整体性能。而在写频繁，读稀疏这种场景下，应当尽量避免索引的建立。