如何优化MySQL及其索引？

最近经常看一下有关于索引的博客和书籍，加上抽空听了一次开课吧的公开课，借鉴一下他人的博客和开课吧的课件，对这几天的学习做个总结

1. 优化数据库可以从哪些方面入手？

这里我找了一张图片来简单的说明一下，当我们访问数据库感觉很慢时优先考虑数据库的表结构和sql及索引是否可以优化，因为从实际考虑，在这两点上做出优化的效果是最好的，并且代价也是最小的。

2. 如何在系统配置层面优化数据库
2.1 将数据保存在内存中，保证从内存中读取数据
2.1.1 设置足够大的innodb_buffer_pool_size，以便将数据读取到内存中，建议讲大小设置为内存的3/4左右。
2.1.2 怎么确定innodb_buffer_pool_size是否够大？数据是否真的从内存中读而不是从硬盘中？
执行下面命令查看

找到字段，如果该value是0的话则代表buffer_pool已经用完。
2.2内存预热

2.3 降低磁盘写入次数
关闭一些不必要的日志，使用足够大的innodb_log_file_size。
2.4提高磁盘读写
大佬的选择，考虑使用SSD硬盘。

3.如何通过优化索引来达到优化数据库
3.1什么是索引
高效获取数据的数据结构（B+树，有时也会是哈希索引）。是存放在磁盘中的（innodb引擎的索引和数据存放在一起，myisam引擎的索引单独存放）。
3.2为什么使用索引？用了索引一定好么？
首先使用索引类似于给数据库中的每行数据加了一个目录，通过这个目录我们就可以快速的找到想要的数据，就不用一行一行的去扫描了。提高了数据的检索效率，并且通过索引列给数据排序，降低了数据排序的成本，降低了CPU的消耗。但是索引会额外的占用磁盘空间，并且维护需要一定成本，如果使用不当反而会拖累数据库。
3.3索引的分类
按照索引的使用来分可以分为单列索引和组合索引。
单列索引又可以分为：

3.4 索引的结构
3.4.1 B-Tree索引
B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。并且树的高度一般在2-4层左右。一个索引的大小在16K(这个大小可以自己设置，不过使用默认的就好)，这就意味着一个三层的B+树可以存10亿条bigint类型的数据，即使是存的是字符型的，也能存个几千万条。

上图向我们展示了一个innodb引擎的主键索引结构，可以发现使用innodb引擎构建的索引，它的非叶子节点是不存放数据的，所有的数据都和索引一起存放在叶子节点上，所以innodb引擎构建的表它的索引文件和数据文件是存放在一起的，这点区别于myisam引擎构建的索引，myisam引擎构建的主键索引叶子节点存放的是数据行所在的地址，所以该引擎构建的表索引文件和数据文件是分开的。

上面这张图片展示的是innodb引擎的辅助索引（次要索引），辅助索引叶子节点存放的是主键索引的值，所以在使用辅佐索引来检索数据时实际上要发生搜索两次索引。另外我们在设计表的时候应该显式的申明一个自增的主键索引，否则innodb引擎会自动帮我们选一个唯一非空列作为主键索引，如果没有找到这种列，它会默认帮我们创建一个隐式的列出来当做主键索引。而对于myisam引擎来说，辅助索引和主键索引没什么区别，唯一的区别就是主键索引不能重复，而辅助索引可以。
B-Tree索引的限制
1.如果不是按照索引的最左列开始查找，则无法使用索引。
2.不能跳过索引中的列
3.如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查询。
3.4.2 哈希索引（hash index）
哈希索引基于哈希表实现的，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值（hash code），哈希值是一个较小的值，并且不同键值的行计算出来的哈希值不一样。哈希索引将所有的哈希值存储在索引中，同时保存指向每个数据行的指针，这样就可以根据，索引中寻找对于哈希值，然后在根据对应指针，返回到数据行。

mysql中只有memory引擎显式支持哈希索引，innodb是隐式支持哈希索引的。

哈希索引限制：
哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用"覆盖索引"的优化方式，去避免读取数据表。
哈希索引数据并不是按照索引值顺序存储的，索引也就无法用于排序
哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值的。
哈希索引只支持等值比较查询，包括=，in(),<=>，不支持任何范围查询。列入where price>100
访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）
如果哈希冲突很多的话，一些索引维护操作的代价也会很高。
因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。
3.5组合索引
3.5.1为什么使用组合索引？
使用组合索引的目的是为了节省索引存储空间以及提升检索效率。并且能用组合索引的情况下就不要用单列索引了。
3.5.2如何创建一个组合索引？

这句话就相当于创建了一个组合索引，其等价于同时创建三个索引且性能更优。
但是如何使用组合一个组合索引并且效果能够达到最优就值得考虑了，因为组合左右遵循最左前缀原则，所以我们在where后面跟条件的时候一定要想清楚，这样写是否真的走了索引，例如：select age where table_name where col2=’’;这样其实是没有走到索引的，因为根据最左前缀原则，没有匹配到col1，所以就没有走到索引，如果是select age where table_name where col2=’’ and col1=’??’;这样就走了组合索引，因为匹配到了col1,注意where后面跟的条件顺序不一样是执行的顺序，因为mysql有一个sql优化器，会自动帮我们优化sql。
3.6 哪些情况下需要索引？
1.主键自动创建唯一索引
2.在where后面频繁使用的字段应该创建索引
3.多表关联查询中，关联的字段应该使用索引
4.查询中统计或者分组的字段应该创建索引
5.查询中排序的字段，应该创建索引
6.索引不是越多越好，冗余的索引反而是数据库的负担，一般来说，一张表的索引不应该超过5个。


（注：参考博客https://www.cnblogs.com/Aiapple/p/5693239.html以及开课吧公开课课件）