插播广告:
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。