高性能Mysq数据库的表的设计原则

插播广告：

sql的编写过程

  select******from******join*******on******where***********group by*********having*********order by*******limit******

sql的解析过程

  from******on*******join*******where*****group by******having******select*******order by*****limit******

数据库表设计

也是设计一个数据表步骤：

1.  根据业务规划出表的基本结构

2. 大致设计表的字段类型

3. 选择特定类型（尽量越小） 比如整形有 tinyint, smallint,int ,bigint，对应 8 16  32   64 ，时间类型一般选择时间戳                                    System.currentTimeMilis();

4. 如果有必要，为属性设置默认值

5. 尽量使用整形定义标识列

6. 范式与反范式的设计

 

索引分类：

唯一索引       主键索引       普通索引            联合索引

索引的设计（首先需要对索引有个分类Btree索引和hash索引）

hash索引： 查找某一条数据特别快 。不适合范围查询与排序查询。

Btree索引的设计：

1.  独立的列，不去使用函数，因为函数的话，在查询过程中不会查询索引。

2. 索引前缀和索引选择   select count(distinct left(city,3))/count(*) as s1  from city_demo;  求出的值最接近0.031即可

    前缀索引： 无法orde by desc /group up /无法使用覆盖索引

 

什么是覆盖索引？

我们先来看下面这张表（表中我建立了2个索引，一个id主键索引，一个name普通索引）

我们分别来执行这两句

explain select * from test where  name LIKE "发%";

explain select id from test where  name LIKE "发%";

对于第一个select来说，数据库会先查到第一个与“发%”匹配的索引，然后获得它的主键id(也就是主键索引的位置)，然后拿到数据，继续判断，这个回主键索引查询值的过程叫回表。

对于第二个selct来说，数据库会拿到第一个与“发%”匹配的id号，因为name的索引中就存的是主键索引的位置，我们不去要再区主键索引拿值了，减少了回表次数，这样也就查询快了一些，其实这就是覆盖索引的本质。

我们可以从Extra列的Using where可以看出来。

什么是索引下推？（MySql5.6引入的索引下推）

 explain select * from test where  name = "发发发" and number > 1515;

比如我要查询这么一些信息：（前提：我建立了一个name_number联合索引）

我们肯定回先找到第一条“发发发”的数据，然后去主键索引里面拿数据，再去判断number,

5.6索引下推就是，判断为“发发发”的数据后，接着去判断number是否大于1515，只将大于1515的值进行回表。这就是索引下推的优化。

什么是最左前缀原则？

排序方面（查询大家应该都熟悉就不说了）

最左前缀原则不光用在查询方面，也用在了排序上。用上面的例子有一个（name_number）的索引我们来分析下面两条语句。

explain  select  name from   test  ORDER BY  name;

这里的Extra这一列来说，它只用了Using index

explain  select  name from   test  ORDER BY  number;

这里的Extra这一列，它用了Using  filesort ，它的意思就是使用了额外的排序，不是索引内部排序，所以我们排序的时候就应该避免Using filesort。