一.查询性能优化
为什么查询速度会慢：
通常来说，查询的生命周期大致可以分为以下顺序：从客户端，到服务器，然后在服务器上进行解析，优化后生成执行计划，执行，并返回结果给客户端。其中执行可以认为是整个生命周期最重要的阶段，这其中包含了大量为了检索数据到存储引擎的调用，以及调用后的数据处理，包括排序和分组。
在完成这些任务的时候，查询需要在不同的地方花费时间,包括网络、CPU计算，生成统计信息和执行计划、锁等待等操作，尤其是向底层存储引擎检索数据的调用操作。优化和查询的目的就是减少和消除这些操作所花费的时间。
查询性能低下的最基本原因是访问的数据太多，大部分性能低下的查询可以通过减少访问的数据量的方式进行优化：
1.确认程序是否在检索大量超过需要的数据，这通常意味着访问太多行，但有时也可能是访问太多列。
2.确认MySQL服务器层是否在分析大量超过需求的数据。
本文主要从以下几个方面去考虑查询性能优化：
单表优化
除非单标数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量。
字段
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们要将表中字段的宽度设得尽可能小，字段的数据类型要选得更合理一些。
（1）尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
（2）VARCHAR的长度只分配真正需要的空间
（3）使用枚举或整数代替字符串类型
（4）尽量使用TIMESTAMP而非DATETIME，
（5）单表不要有太多字段，建议在20以内
（6）避免使用NULL字段，很难查询优化且占用额外索引空间
（7）用整型来存IP
索引
当数据量过大，查询过慢的时候，我们往往都会想到去建索引。可是索引不是越多越好，要根据表查询字段的使用情况有针对性的去创建。
（1）考虑在WHERE和ORDER BY命令上涉及的列去建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描。
（2）应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
（3）值分布很稀少的字段不适合建立索引，例如“性别”这种只有两三个值得字段
（4）字符字段只建前缀索引
（5）字符字段最好不要做主键
（6）尽量不用外键以及UNIQUE，由程序保证约束
（7）使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引
优化查询语句
可通过开启慢查询日志来找出较慢的sql
（1）不做列运算：任何对列的操作都讲导致表扫描，它包括数据库教程函数、计算表达式等
等，查询时要尽可能将操作移至等号右边。反例：SELECT id WHERE age+1=10
（2）sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
（3）不用SELECT *
（4） OR改写成IN: OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
（5）不用函数和触发器，在应用程序中实现
（6）避免%xxx式查询
（7）连接（JOIN）代替子查询，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。但仍然要少用JOIN
（8）使用同类型进行比较，比如用‘123’和‘123’比，123和123比
（9）尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
（10）对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
（11）列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大
系统调优参数
具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数：
(1)back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到(2)max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500
(3)wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时
(4)max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限
(5)thread_concurrency：并发线程数，设为CPU核数的两倍
(6)skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
(7)key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like ‘key_read%’，保证key_reads / key_read_requests在0.1%以下最好
(8)innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like ‘Innodb_buffer_pool_read%’，保证(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好
innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小
(9)innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
(10)query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率
(11)(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大.
可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
(12)read_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
(13)sort_buffer_size：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
(14)read_rnd_buffer_size：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
(15)record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
(16)thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
(17)table_cache：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM
读写分离
也是目前常用的优化，从库读主库写，一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离
缓存
缓存可以发生在这些层次：
MySQL内部：上述有相关配置描述
数据访问层：比如一些orm上的缓存，存储的对象主要是持久化对象
应用服务层：可以通过编程手段对缓存做到更精准的控制和更多的实现策略 比如一些常用到的数据可以缓存在访问速度较快的redis里
web层：针对web页面做的缓存
浏览器客户端：用户端的缓存
可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务器的缓存实现，目前主要有两种方式：
直写式（Write Through）：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单，同步好，但效率一般。
回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。
表分区
MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码
对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的字表定义，没有全局索引
用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化。
垂直拆分
垂直拆分是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联。
垂直拆分的优点是：
可以试得行数据变小，一个数据块就能存放更多的数据，在查询时就会减少I/O次数（每次查询时读取的Block就少）
可以最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起
数据维护简单
缺点：
主键出现冗余，需要管理冗余列
会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力
依然存在单表数据量过大的问题（需要水平拆分）
事务处理复杂
水平拆分
（分为库内分表和分库两部分）
库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决
优点：
不存在单库大数据和高并发的性能瓶颈
应用端改造较少
提高了系统的稳定性和负载能力
缺点：
分片事务一致性难以解决
跨节点Join性能差，逻辑复杂
数据多次扩展难度跟维护量极大
分片
（1）能不分就不分，参考单表优化
（2）分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量
（3）分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容
（4）尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题
（5）查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。
（6）通过数据冗余和表分区赖降低跨库Join的可能
NoSQL
对于一些对事务要求不强且无太大关联操作的大表数据，可以考虑将这些表迁移到NoSQL，彻底解决水平扩展问题。比如：日志类、监控类、统计类数据，非结构化或弱结构化数据。
分片和分区的区别
分片 | 分区 | |
存储依赖 | 可跨越DB,可跨越物理机器 | 可跨越表空间，不同的物理属性，不能跨DB存储 |
数据划分 | 常见为时间、范围、面向服务等 | 范围、Hash、列表、混合分区等 |
存储方式 | 分布式 | 集中式 |
扩展性 | Scale Out（横向扩展） | Scale Up（纵向扩展） |
可用性 | 无单点 | 存在单点（DB本身） |
价格 | 低廉 | |
应用场景 | 常见于Web2.0网站 | 多数传统应用 |
二.索引优化
MySQL索引的概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快
要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
MySQL索引的功能
索引的功能就是加速查找，另外就是部分类型索引还有约束功能，例如唯一索引，主键索引
MySQL索引的分类
1.普通索引index：加速查找
2.唯一索引：
主键索引：primary key：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束（唯一）
3.联合索引
-primary key(id, name): 联合主键索引
-unique(id,name): 联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext：用于搜索很长一篇文章的时候，效果最好
5.空间索引spatial：了解就好，几乎不用
MySQL索引的两大类型hash与btree
#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
MySQL索引的正确使用
1.何时使用聚集索引或非聚集索引
动作描述 | 使用聚集索引 | 使用非聚集索引 |
列经常被分组排序 | 使用 | 使用 |
返回某范围内的数据 | 使用 | 不使用 |
一个或极少不同值 | 不使用 | 不使用 |
小数目的不同值 | 使用 | 不使用 |
大数目的不同值 | 不使用 | 使用 |
频繁更新的列 | 不使用 | 使用 |
外键列 | 使用 | 使用 |
主键列 | 使用 | 使用 |
频繁修改索引列 | 不使用 | 使用 |
2.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
4.索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引
5.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6. 不要在列上进行运算
例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。
7.使用组合索引代替多个单列索引
经常使用多个条件查询时，使用组合索引代替多个单列索引，但是组合索引注意最左前缀的匹配原则。例如：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))，这个其实只是建立了一下两种组合索引：
-title，time
-title
-time，title(注意这个是不成立，根据最左前缀原则是匹配不到组合索引)
最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。
参考链接： https://www.jianshu.com/p/f0e4b1905cff
