做一个合格的程序猿之MYSQL 使用心得（一）

 开篇介绍，本人2014年6月毕业，目前一年半开发经验，来二流互联网工作一年，职位为典型的JAVA WEB 典型的CRUD选手。

今天分享一下入职以来，自己对mysql使用的心得，有错的地方，大家一起学习，欢迎指正

首先我觉得要知道自己使用的mysql的版本和mysql一些基本的常用的配置

select version()

版本是5.6 （我觉得现在企业应用mysql的版本最好是5.5以上，如果是低版本，应该升级）

show engines

默认存储引擎是Innodb，支持XA 和savepoints，本文只讨论Innodb

现在再看看我们这台mysql实例的一些具体的配置

show variables like 'innodb_buffer_pool_size'

内存为 42949672960/1024/1024/1024 = 40G (已经相当高了，自我感觉，如果是单表查询，合适的sql和一个三星索引，单表支持8000w左右的数据是没有问题的)

show variables like 'innodb_io%'

默认是200 如果是SSD，可以适当提高，（SSD，呵呵了，全公司貌似只有金融事业部是用的SSD）

初步了解一下作为开发人员来看下数据库的配置，内存和IO ,数据库的版本，其他的具体配置有空接触到再说，DBA去做就可以了

作为一个CRUD 逻辑业务型java web选手，先做好自己的本职工作

1）建表：

      1：如果该表不用于超高并发的业务场景，建议使用自增主键，好处：

               ①优化插入的效率和优化建立索引的效率

               ②方便查询和业务人员查看

               ......

       2:建议每个字段不要为空，NOT NULL,这样可以优化数据的存储和优化你的查询效率

       3:能用enum的不用varchar，能用varchar(50)不用varchar(100）,能用varchar不用text，能用tinyint不用int.....可以适当的冗余

         varchar(50) 和varchar（100）虽然占用的存储空间是一样的，但是在内存排序时，varchar（100）会占用更多的内存

       （欢迎补充）

2）建立索引

     1： 索引的重要性不言而喻，举例说明 单表2.5亿的数据，如果你的sql中的where条件中没有建立索引，40G的内存下没有1min中是无法返回结果的

相反建立单列索引，可以在毫秒级返回所有数据

（该表的数据量）

（根据某列查询）

这是以前一张废表，现在已经分表了，大家见笑了，当时的临时解决方案就是建立一个索引，以供暂时使用

这里只是简单的说明一个索引对查询的重要性，建立索引的好处有：

   ①：大大减少了服务器需要扫描的行数

   ②：尽可能的避免临时的排序和临时表

   ③：优化IO,随机IO变成了顺序IO

 

    2：哪些列需要建立索引（当然需要使用于你的业务场景，再好的索引，如果没有业务使用，也失去了意义）

select count(distinct xx列) / count(*) from **_table

该值越接近1说明该列越适合做索引，不是绝对的，如果该列是varchar且长度很长，或者该列为text，可以使用前缀索引，减少索引的大小

  建立好索引后

show index from xxx_table 

基数Cardinality/总行数 越大说明索引效率越高，默认建立的所有都是BTREE

   

   3:建立三星索引

    一星索引：索引将相关的记录放在一起则获取一星

    二星索引：索引顺序和查询顺序是一致的是二星（mysql索引是最左匹配）

    三星索引：索引的列包含所有的查询列

      ① 单列索引，没有什么可以说的，在你sql查询中非主键的列上加索引，可以加快查询速度，但mysql中例如

select * from user where age+1 =20

这种sql mysql是无法使用索引的，即使你的age列上建了索引，明明可以写成age = 19 的，偏偏不做，还有就是 mysql的函数库也是不支持索引的

  

    ②多列索引

          多列索引不是为多个列建立单独的索引，也不是按照错误的顺利为多列建立索引

多列索引建立原则：

      跟三星索引一样多列索引中覆盖的列是最好覆盖你查询sql中where后面所有的匹配列，且顺序最好与索引的顺序一致，虽然即使你不一致mysql优化器会优化，但是明明可以自己搞定的东西，为什么要系统搞定呢

    并且索引中覆盖的每列的顺序也是至关重要的，把count（distinct（列））/count(*)值大的放在前列，这样扫描的列会少一点，否则会产生无效索引，即便是一个所谓的“三星索引”，其实并没有优化多少

  举例说明，有一张表，列有：国家名，国家id，省份名，省份id，市级名，市级id，如果你的索引是index（国家id，省份id，市级id），你的sql如下

 select * from xxx_info_table where 国家id = ** and 省份id = ** and 市级id = **

explain一下：

  explain select * from xxx_info_table where 国家id = ** and 省份id = ** and 市级id = ** 感觉棒棒哒，覆盖所有索引，是否最优呢，其实不对

原因：国家本来就是不多，但是省份却很多，如果你的索引顺序是index（市级id，省份id，国家id），sql的顺序也是变化一样，你explain的结果虽然也是覆盖所有索引，但是效率确实高的多