MySQL优化
MySQL常见性能瓶颈：
CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
硬件：top, free, iostat和vmstat等命令来查看系统性能状态
I/O:磁盘I/O瓶颈发生装入数据远大于内存容量的时候
SQL：程序中SQL语句问题，Explain查看执行计划。
Explain简介：
EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法,MYSQL会在查询上设置一个标记,当执行查询时,这个标记会使其返回关于在执行计划中每一步的信息,从而可以从分析结果中找到查询语句或是表结构的性能瓶颈
Explain能干什么？
1：分析出表的执行顺序
2：数据读取操作的操作类型
3：哪些索引可以使用
4：哪些索引被实际使用
5：表之间的引用
EXPLAIN结果参数含义：id
样例：explain select * from tbl_dept;
1.id: id代表执行select子句或操作表的顺序,id分别有三种不同的执行结果
id相同,执行顺序由上至下
2.id不同,如果是子查询,id的序号会递增,id值越大,优先级越高,越先被执行
3. id相同和不同,同时存在,遵从优先级高的优先执行,优先级相同的按照由上至下的顺序执行
EXPLAIN结果参数含义：select_type
查询的类型,主要用于区别普通查询,联合查询,子查询等复杂查询
SIMPLE：简单的select查询,查询中不包含子查询或union查询
PRIMARY/UNION： PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
SUBQUERY： 在select 或where 列表中包含了子查询，子查询中首个SELECT（如果有多个子查询存在）
EXPLAIN结果参数含义：type
查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All, 一般情况下,得至少保证达到range级别,最好能达到ref
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
ref:非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
range:只检索给定范围的行,使用一个索引来选择行,如where语句中出现了between,<,>,in等查询,这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index:index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小,index是从索引中读取,all从硬盘中读取
all:全表扫描,是最差的一种查询类型
EXPLAIN结果参数含义：possibles_keys
显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的.
EXPLAIN结果参数含义：key
实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引。
EXPLAIN结果参数含义：key_len
表示索引中使用的字节数,而通过该列计算查询中使用的 索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即,key_len是根据表定义计算而得么不是通过表内检索出的
EXPLAIN结果参数含义：ref
显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值
EXPLAIN结果参数含义：rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
EXPLAIN结果参数含义：Extra
不适合在其他列显示的额外信息
Using temporary ：使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 其中的覆盖索引含义是所查询的列是和建立的索引字段和个数是一一对应的
Using where:表明使用了where过滤
Using join buffer：表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表
impossible where：表示where子句的值总是false,不能用来获取任何元祖。如下例：
select * from t1 where id='1' and id='2';
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化 ，COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作，即一旦MySQL找到了与行相联合匹配的行，就不再搜索了。
索引介绍
索引（Index） 是帮助MySQL高效获取数据的数据结构
索引为什么是一种数据结构，它又是怎么提高查询的速度？我们拿最常用的二叉树来分析索引的工作原理。看下面的图片： 
创建索引的优势
1 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
2 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则真好降低了排序的成本。
创建索引的劣势
1 占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
2 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。
3 优质索引创建难：索引的创建并非一日之功，也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。
常见索引类型有哪些？
普通索引：普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
唯一索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值
组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合，比如说，INDEX(A, B, C)可以当做A或(A, B)的索引来使用，但不能当做B、C或(B, C)的索引来使用
全文索引(倒排文档技术)： 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
数据库索引的设计原则:
1．选择唯一性索引
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度
4．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文，检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。
6．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度
7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
8.小表不应建立索引，包含大量的列并且不需要搜索非空值的时候可以考虑不建索引
9.尽量不要对数据库中某个含有大量重复的值的字段建立索引。
对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，例如“sex”字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。
索引中需要注意的事项
1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.以%开头的LIKE查询不能使用索引
explain select deptName,remark,createDate from dept where deptName like'%2' and remark = 'test'and createDate ='2018-07-22'; type index
SELECT * FROM `houdunwang` WHERE `uname` LIKE'金蝶%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE "%金蝶%" -- 不走索引
3.数据类型出现隐式转换时也不能使用索引
explain select deptName,remark,createDate from dept where deptName =2 and remark = 'test'and createDate ='2018-07-22'; type index
4.复合索引时 不符合最左匹配原则
增加联合索引 ALTER TABLE dept ADD INDEX index_all (`deptName`,`remark`,`createDate`);
explain select deptName,remark,createDate from dept where remark = 'test'and createDate ='2018-07-22'; type index
5. 用or分隔开的条件，如果or前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用到
增加联合索引 ALTER TABLE dept ADD INDEX index_all (`deptName`,`remark`,`createDate`);
explain select deptName,remark,createDate from dept where deptName ='2' and remark = 'test'and createDate ='2018-07-22' or salary =200; type ALL
6.order by 字段混合使用DESC ASC 不会使用索引
select * from table order by key1 desc,key2 asc (尽量不要出现这种混合排序)
7.Where条件过滤的关键字和Order by中所使用的不同 不会使用索引
select * from table where key2 = ? order by key1 (order by 出现的关键字 尽量 在where条件中也出现)
8.多表连接的时候 join on(a.id=b.id2) 连接外键id、id2 必须加索引。MySQL规定作为外键的字段必须有索引 也是为了让我们表连接的时候 用到索引
9. SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引，因为所有索引列参与了计算
10. SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引，因为使用了函数运算
11.字符串与数字比较不使用索引;
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引，同样也是使用了函数运算
总结:
索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大.
