需提升MySQL设计优化能力

       过去对MySQL不够重视，仅停留在应用阶段，对原理和优化不求甚解，这种拿来主义在数据库引擎和索引方面尤为明显，是眼界限制了我的想法。幸运的是得到两位前辈的及时指出，我感觉距离成为一名高水平的研发人员又更进一步。这两天经过几番查阅资料，这个知识盲区正在被照亮，但是这仅停留在理论知识，还需要更加开阔的眼界看问题，还需要更多的实践在实验中验证想法，最终在实践中完成知识的吸收内化。

 

参考书籍《MySql技术内幕 InnoDb存储引擎》 第五章 索引与算法

数据库引擎的区别。
答：
MyISAM：高速查询及插入。擅长插入和查询，不支持事务。
Innodb ：数据完整性，并发性处理，擅长更新，删除，支持事务。
MyISAM强调性能，而Innodb强调安全。

为什么Innodb适合擅长更新和删除而MyISAM擅长插入和查询？
答：
前者Innodb擅长处理并发的。
因为它使用了行级锁定，只该行锁了，其它行没有锁，
把数据和索引存放在表空间里面。
后者MyISAM擅长高速读与写。
因为它本身是一种文件系统，是索引顺序存取方法的缩写，
索引文件和数据文件是分离，维护索引效率高。

数据和索引各种独立表空间和共享表空间的优缺点。
答:
独立的优点：
维护索引的效率高，
碎片化不明显，可回缩不用的空间。
共享的缺点：
建立索引并且大量删除记录后，共享表空间不能回缩。
共享的优点：
管理方便，如：分表操作方便。
独立的缺点：
库中表数量多时，难以拆库拆表。

索引有哪些？
答：
按功能分有：
主键索引、唯一索引、普通索引、联合索引
按搜索引擎分类：
hash索引、btree索引（二分叉算法）

索引为什么会快？
答：
没有索引时，查找记录会按主键从头到尾扫描，
    需要扫描N行，时间复杂度O(n)。
有索引时，索引分3种，hash，btree，全文。
hash查找时，直接定位到记录的主键，时间复杂度O(1)。
二分查找时，因为B+树的高度一般都在2-4层，
最多只需要2-4次IO，效率也比较可供。
全文检索用了反向索引，假如关键字被收录，时间复杂度O(1)。
若关键字没收录，则根据最左前缀匹配来查找。

 

若记录大量重复，建立索引会有什么问题？
答：建索引先要考虑区分度，这种情况下会有性能问题，
反面教材举例：给性别字段建立普通索引。
将导致普通索引和聚集索引中来回切换，总开销大。
从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。
如果是从10行数据中取5行数据，相当于访问5次索引，再访问5次表，
加起来的开销并不会比直接对表进行一次完整扫描小。

 

为什么varchar不适合建立索引？
答:
区别性能的是采用哪种索引方式，
而不是索引的数据类型。
若采用hash作为索引，效率不会降低。
若采用btree索引，维护索引很可能会消耗大量性能。
这种非单调的字段在插入新记录时，
数据文件为了维持B+Tree的特性而频繁的分裂调整，
在某些情况下会十分低效，不过也有改善的办法，
可以使用短索引，计算最大的辨识度M=m/q，
比如说前5位近似是唯一的，
那么就不要对整个列进行索引。

存了很多IP到数据库中，可能重复，如何取出最高效？
答：
1.给IP建立普通索引，大部分人都能想到。
2.点分法IP转十进制数值类型，使用int UNSIGNED类型存储更高效。
3.若不需要模糊查询，启用自适应hash索引。（针对Innodb）
4.若IP前缀相同导致区分度不高，
    但是又需要按区间取出可考虑多列索引。
5.考虑是否可以覆盖索引查询。
6.字段参与的表达中，保证字段独立在运算符一侧。
7.若需要模糊查询，左边不要以通配符开头。

 

在查询上，有什么高效的技巧或注意事项？
答：
建复合索引时，区别度最高在最左边。
多判断条件查询时，等号的条件放最左边。
使用覆盖索引来进行查询，避免回表。
索引文件有最左前缀匹配特性，避免使用左模糊或全模糊。

 

如何高效查询一个时间段的记录？
答：
1.建立索引可以加快查询速度
2.时间类型datetime,
    时间戳考虑用数值类型int(11)
3.使用BETWEEN-AND，索引建立后，可以用索引扫描提高了查询的效率。
4.不要使用数据库函数，会导致无法使用索引。

不走索引可能的情况有哪些？
答：
1.函数操作
2.隐式转换
3.模糊查询
4.范围查询
5.计算操作

 

全文索引是什么原理？
答：
过去只有MyISAM支持全文索引，现在Innodb也支持全文索引。
但是不支持没有单词界定符的语言，如中日韩文。
需要安装sphinx等搜索引擎支持全文索引。
原理是使用倒排索引，
innodb会把单词拆分进行存储，查找时，根据单词匹配。
这里颇有搜索引擎的味道，
但是仅仅是精确的查询方式，
lucene等搜索引擎更擅长分词并且舍去了事务、
访问控制以及可能遗漏的一小部分记录（文档），
换取来快速的搜索响应。

 

数据库优化技能还有哪些？
答：
较频繁的作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合创建索引
不会出现在 WHERE 子句中的字段不该创建索引