精华内容
下载资源
问答
  • 前段时间在公司工作我建议创建的某个表字段索引在内部评审的时候被删除了,理由是”状态枚举值少的索引效果不好,甚至会引发全扫描,例如性别男女“。但我觉得,只查未发送的记录,取结果较少,索引效率会高。...

    状态status字段是否要创建索引?

    背景

    ​ 前段时间在公司工作中我建议创建的某个表字段索引在内部评审的时候被删除了,理由是”状态枚举值少的索引效果不好,甚至会引发全表扫描,例如性别男女“。虽然不是什么大事,直到现在也没有什么明显影响,但这个事情给我心底留下了疑问,“状态枚举字段是否应该建立索引?”。

    ​ 业务场景:商品报名业务,报名以后保存了其中一个表,状态为“待发送”。另外有个界面可查询此状态数据进行手动操作调用下游接口完成相应业务操作,或者由定时任务定期扫描待发送数据自动完成。send_flag字段0-未发送,1-已发送。send_flag=0的数据每天小于500条,表中记录约30万条记录,表在以每个月5万条记录增长。(数据库是mysql,使用的是Innodb引擎)

    ​ 相信这种类似业务在平时工作中经常会遇到。比如说状态status(0\1\2\3)、性别sex(0未知\1男\2女)等。你在遇到这类场景的时候,是否会给"send_flag"创建索引?为什么很多人会说可能会引发全表扫描?

    准备数据

    为了测试,我在本地创建了一个表test_product,插入了150万条记录,开启了慢查询时间为10毫秒。除自增ID以外,无索引。

    验证以下几个问题

    加索引以后是否会提升查询效率?

    为了模拟业务场景,我设置500条记录的send_flag=0,设置send_flag的记录是随机选的。

    CREATE PROCEDURE test.test()
    begin
    	DECLARE i INT;# 申明变量
    SET i = 0;  # 变量赋值
    WHILE i<500 DO # 结束循环的条件
    update test_product set send_flag = 0 where id = (select ceiling(rand()*1500000) from dual);
    SET i = i+1;    # 循环一次,i加1
    END WHILE;  # 结束while循环
    #结束循环执行语句
    end 
    

    因为自己PC配置问题,执行时间较长。

    • send_flag未添加索引的查询情况

    执行语句

    mysql> select count(1) from test_product where send_flag=0;
    +----------+
    | count(1) |
    +----------+
    |      503 |
    +----------+
    1 row in set (2.26 sec)
    
    mysql>
    

    通过上述可以看到,大约在2秒。同样的语句我执行5次后看下慢查询

    mysql> select start_time, query_time ,sql_text from mysql.slow_log order by start_time desc limit 5;
    +----------------------------+-----------------+-----------------------------------------------------+
    | start_time                 | query_time      | sql_text                                            |
    +----------------------------+-----------------+-----------------------------------------------------+
    | 2021-04-23 16:08:54.139661 | 00:00:02.260723 | select count(1) from test_product where send_flag=0 |
    | 2021-04-23 16:08:51.221102 | 00:00:02.257314 | select count(1) from test_product where send_flag=0 |
    | 2021-04-23 16:08:48.203821 | 00:00:02.361179 | select count(1) from test_product where send_flag=0 |
    | 2021-04-23 16:08:45.247554 | 00:00:02.311678 | select count(1) from test_product where send_flag=0 |
    | 2021-04-23 16:08:42.210513 | 00:00:02.402186 | select count(1) from test_product where send_flag=0 |
    +----------------------------+-----------------+-----------------------------------------------------+
    5 rows in set (0.00 sec)
    
    mysql>
    

    可以看到每次的执行时间都在2秒+。

    为了看出效果,此处我执行一个无效语句用于分隔查询结果select sleep(5)

    • send_flag添加索引的查询情况

    添加索引语句

    CREATE INDEX test_product_send_flag_IDX USING BTREE ON test.test_product (send_flag);
    

    同样执行语句

    mysql> select count(1) from test_product where send_flag=0;
    +----------+
    | count(1) |
    +----------+
    |      503 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql>
    

    执行了5次,结果都一样查询时间都是0秒。

    再看下慢查询

    mysql> select start_time, query_time ,sql_text from mysql.slow_log order by start_time desc limit 5;
    +----------------------------+-----------------+-----------------------------------------------------+
    | start_time                 | query_time      | sql_text                                            |
    +----------------------------+-----------------+-----------------------------------------------------+
    | 2021-04-23 16:13:39.746433 | 00:00:05.001184 | select sleep(5)                                     |
    | 2021-04-23 16:08:54.139661 | 00:00:02.260723 | select count(1) from test_product where send_flag=0 |
    | 2021-04-23 16:08:51.221102 | 00:00:02.257314 | select count(1) from test_product where send_flag=0 |
    | 2021-04-23 16:08:48.203821 | 00:00:02.361179 | select count(1) from test_product where send_flag=0 |
    | 2021-04-23 16:08:45.247554 | 00:00:02.311678 | select count(1) from test_product where send_flag=0 |
    +----------------------------+-----------------+-----------------------------------------------------+
    5 rows in set (0.00 sec)
    
    mysql>
    

    可以看到,慢查询日志最后一条语句还是我们刚才执行的sleep(5)分隔,并没有刚才执行的几条语句。

    由此可以证明:为send_flag创建索引能有效的提升查询效率!

    枚举值较少时是否会引发全表扫描?

    以我的理解,应该分3种情况来验证:1)枚举值对应的数据量有较大差别,查询较少的这部分数据;2)枚举值对应的数据量有较大差别,查询较多的这部分数据;3)各类枚举值对应的数据量比较接近时查询;

    接下来,我们先验证

    • 数据量有较大差别,查询较少的这部分数据

    验证仍然使用上面的测试数据,总记录150万,send_flag=0有500条,剩下全部是send_flag=1。

    执行explain分析

    mysql> select count(1) from test_product where send_flag=0;
    +----------+
    | count(1) |
    +----------+
    |      503 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> explain select count(1) from test_product where send_flag=0;
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys              | key                        | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | test_product | NULL       | ref  | test_product_send_flag_IDX | test_product_send_flag_IDX | 5       | const |  503 |   100.00 | Using index |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>
    

    不出意外,此时肯定是在使用索引test_product_send_flag_IDX且查询类型type=ref。

    因此,各枚举值对应的数据量有较大差别,查询较少的这部分数据是会使用索引的。

    • 数据量有较大差别,查询较多的这部分数据

    验证仍然使用上面的测试数据,总记录150万,send_flag=0有500条,剩下全部是send_flag=1。

    执行explain分析

    mysql> select count(1) from test_product where send_flag=1;
    +----------+
    | count(1) |
    +----------+
    |  1521367 |
    +----------+
    1 row in set (0.37 sec)
    
    mysql> explain select count(1) from test_product where send_flag=1;
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys              | key                        | key_len | ref   | rows   | filtered | Extra       |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    |  1 | SIMPLE      | test_product | NULL       | ref  | test_product_send_flag_IDX | test_product_send_flag_IDX | 5       | const | 866953 |   100.00 | Using index |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>
    

    此时可以看到查询执行相对比上一个示例长了一些,但是仍然在使用索引test_product_send_flag_IDX查询类型是type=ref。

    因此,各枚举值对应的数据量有较大差别,查询较多的这部分数据也是会使用索引的。

    • 各类枚举值对应的数据量比较接近时查询

    此时我们需要修改表中的数据,使send_flag=0和send_flag=1的数据差不多。

    执行explain分析

    mysql> select count(1) from test_product where send_flag=1;
    +----------+
    | count(1) |
    +----------+
    |   760672 |
    +----------+
    1 row in set (0.20 sec)
    
    mysql> select count(1) from test_product where send_flag=0;
    +----------+
    | count(1) |
    +----------+
    |   761198 |
    +----------+
    1 row in set (0.20 sec)
    
    mysql> explain select count(1) from test_product where send_flag=1;
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys              | key                        | key_len | ref   | rows   | filtered | Extra       |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    |  1 | SIMPLE      | test_product | NULL       | ref  | test_product_send_flag_IDX | test_product_send_flag_IDX | 5       | const | 752050 |   100.00 | Using index |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>
    

    可以不管是查询send_flag=0还是1,查询耗时都差不多,也都使用了索引。

    因此,当各类枚举值对应的数据量比较接近时,查询也是会使用索引的。

    为什么反对在status这类字段上加索引呢?

    通过上面几个实验,我们可以看到索引对查询send_flag的效率有非常明显的提升。那为什么会有很多人反对在status这类字段上增加索引呢?我们再来做几个试验证。

    • 索引各类枚举值对应的数据量比较接近时,带条件查询

    此时我们接着上面的数据(send_flag=0和1的数据相差不大)测试,但这次不同的是,除了索引字段我加入了其他过滤条件。

    mysql> select count(1) from test_product where send_flag=1 and commodity_code = '1018223171';
    +----------+
    | count(1) |
    +----------+
    |        1 |
    +----------+
    1 row in set (4.00 sec)
    

    慢查询

    mysql>  select start_time, query_time ,sql_text from mysql.slow_log order by start_time desc limit 5;
    +----------------------------+-----------------+---------------------------------------------------------------------------------------+
    | start_time                 | query_time      | sql_text                                                                              |
    +----------------------------+-----------------+---------------------------------------------------------------------------------------+
    | 2021-04-25 11:10:56.584538 | 00:00:03.861784 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 11:10:44.473629 | 00:00:03.987070 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 11:10:38.429774 | 00:00:03.932597 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 11:10:31.988441 | 00:00:03.855316 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 11:10:26.854730 | 00:00:04.032600 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    +----------------------------+-----------------+---------------------------------------------------------------------------------------+
    5 rows in set (0.00 sec)
    
    mysql>
    

    我们看到执行已经为4秒左右,这样的效果已经很差了,我们来看看是否使用了索引?

    explain

    mysql> explain select count(1) from test_product where send_flag=1 and commodity_code = '1018223171';
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys              | key                        | key_len | ref   | rows   | filtered | Extra       |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    |  1 | SIMPLE      | test_product | NULL       | ref  | test_product_send_flag_IDX | test_product_send_flag_IDX | 5       | const | 752050 |    10.00 | Using where |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql>
    

    我们可以看到这里的这里仍然使用到了索引test_product_send_flag_IDX查询类型type=ref。但是查询耗时却很多。

    • 无索引各类枚举值对应的数据量比较接近时,带条件查询

    删除掉索引字段后,同样的语句我们再来执行一下。

    mysql> select count(1) from test_product where send_flag=1 and commodity_code = '1018223171';
    +----------+
    | count(1) |
    +----------+
    |        1 |
    +----------+
    1 row in set (2.82 sec)
    

    慢查询(执行5次)

    mysql>  select start_time, query_time ,sql_text from mysql.slow_log order by start_time desc limit 5;
    +----------------------------+-----------------+---------------------------------------------------------------------------------------+
    | start_time                 | query_time      | sql_text                                                                              |
    +----------------------------+-----------------+---------------------------------------------------------------------------------------+
    | 2021-04-25 14:26:14.341608 | 00:00:02.635658 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 14:26:10.999191 | 00:00:02.816881 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 14:26:07.321773 | 00:00:02.844335 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 14:25:49.832506 | 00:00:02.821655 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    | 2021-04-25 14:24:51.352390 | 00:00:02.982645 | select count(1) from test_product where send_flag=1 and commodity_code = '1018223171' |
    +----------------------------+-----------------+---------------------------------------------------------------------------------------+
    5 rows in set (0.00 sec)
    

    有没有发现很奇怪的现象,这次执行是2.8秒,同样的语句删除掉索引以后反而比加了索引查询时间少了很多。

    explain

    mysql> explain select count(1) from test_product where send_flag=1 and commodity_code = '1018223171';
    +----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | test_product | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1504101 |     1.00 | Using where |
    +----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    

    可以看到执行语句并没有任何的索引,使用的是全表扫描type=ALL。但事实情况就是这样,没有索引反而比有索引的时候要快1.2秒左右

    我们再看下数据相差较大时的情况。

    • 有索引枚举字段相差较大时查询带条件

    此时我对数据进行了修改,使send_flag=0只有343条,而send_flag=1有150万。send_flag有索引。

    执行查询较多部分数据(send_flag=1)

    mysql> select count(1) from test_product where send_flag=1 and commodity_code = '1018223171';
    +----------+
    | count(1) |
    +----------+
    |        1 |
    +----------+
    1 row in set (10.54 sec)
    

    查询耗时10秒

    explain

    mysql> explain select count(1) from test_product where send_flag=1 and commodity_code = '1018223171';
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys              | key                        | key_len | ref   | rows   | filtered | Extra       |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    |  1 | SIMPLE      | test_product | NULL       | ref  | test_product_send_flag_IDX | test_product_send_flag_IDX | 5       | const | 752050 |    10.00 | Using where |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+--------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    

    查询较多的这部分数据也是使用了索引的,但耗时长达10秒。

    执行查询较多部分数据(send_flag=0)

    mysql> select count(1) from test_product where send_flag=0 and commodity_code = '108023319';
    +----------+
    | count(1) |
    +----------+
    |        1 |
    +----------+
    1 row in set (0.36 sec)
    

    查询耗时0.3秒左右

    explain

    mysql> explain select count(1) from test_product where send_flag=0 and commodity_code = '108023319';
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------------+
    | id | select_type | table        | partitions | type | possible_keys              | key                        | key_len | ref   | rows | filtered | Extra       |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------------+
    |  1 | SIMPLE      | test_product | NULL       | ref  | test_product_send_flag_IDX | test_product_send_flag_IDX | 5       | const |  343 |    10.00 | Using where |
    +----+-------------+--------------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    

    可以看到查询较少的这部分数据时孔在用索引,但耗时较少。

    而如果没有索引时不管是查询send_flag=1或者send_flag=0查询耗时都比较平均

    mysql> select count(1) from test_product where send_flag=1 and commodity_code = '1018223171';
    +----------+
    | count(1) |
    +----------+
    |        1 |
    +----------+
    1 row in set (2.93 sec)
    
    mysql> select count(1) from test_product where send_flag=0 and commodity_code = '108023319';
    +----------+
    | count(1) |
    +----------+
    |        1 |
    +----------+
    1 row in set (2.75 sec)
    

    实验总结

    我们将上述的实验数据整理出来

    • 只查询索引字段条件
    send_flag索引 查询条件 send_flag数量 耗时/秒 索引/全表
    send_flag=0 503 2.3 全表
    send_flag=1 503 0 索引
    send_flag=1 150万 0.37 索引
    send_flag=1 75万 0.2 索引
    • 查询索引字段+其他非索引条件
    send_flag索引 查询条件 send_flag数量 耗时/秒 索引/全表
    send_flag=1 and commodity_code = ‘1018223171’ 75万 4 索引
    send_flag=1 and commodity_code = ‘1018223171’ 75万 2.8 全表
    send_flag=1 and commodity_code = ‘1018223171’ 150万 10 索引
    send_flag=0 and commodity_code = ‘108023319’ 343 <0.5 索引
    send_flag=1 and commodity_code = ‘1018223171’ 150万 2.9 全表
    send_flag=0 and commodity_code = ‘108023319’ 343 2.7 全表

    通过上述的实验数据,我们可以得出关于枚举字段索引的结论

    1. 如果where 只查索引字段,查询会使用索引,且效率提升明显!
    2. 如果where 查询索引字段+非索引字段,如果查询索引枚举值较少的这部分数据,效率有提升;
    3. 如果where 查询索引字段+非索引字段,如果查询枚举值相差不大或者查询较多的这部分数据时,索引大大降低了查询效率!可怕的是,比全表索引效率还要低的多!

    枚举值是否需要建立索引?

    通过上述的实验,我们可以看到有时我们添加索引不仅不会提升效率,反而变成了累赘。

    因此对于“枚举值字段是否要建立索引?”这个问题需要考虑的因素比较多,比如表中已有总索引数量、查询频率、索引字段修改是否频繁、是否会索引字段与非索引字段组合查询等等,需要综合考虑,这可能也是为什么我的添加索引提议评审时被取消的原因。

    经过这一系列的实验,我对评审的结果还是赞同的,并不是说增加索引不会提升效率,而是防止出现“索引字段+非索引字段”这种情况,毕竟这类枚举值字段用到的地方比较多。

    但如果片面的来看的话,单纯从“提升查询未发送记录数据的效率” 角度来说的话,为send_flag建立索引是会提升效率的。但是得保证不能出现send_flag条件与其他非索引字段同时使用的情况,否则反而成了累赘。因此是否有必要创建索引,需要综合考虑到项目其他因素!

    例如“sex男女(0\1\2)”这类字段无特殊要求不需要单纯查询,则不需要建立索引。

    展开全文
  • vb创建数据库

    2013-04-10 23:55:12
    举例:alter table student add column xb text(2) '在学生表中添加性别字段 删除字段 ALTER TABLE 数据表名 DROP COLUMN 字段名 举例: alter table student drop column nl '将学生表中的年龄字段删除 数据查询...
  • Mysql 设计【总结】

    2020-09-18 11:20:44
    外键:表中用于建立关系的字段称为外键,一张表可能有多个外键,但只会有一个主键。 如果建立关系:从表中添加外键指向主表的主键。 例:创建user和userinfo表:假如用户包含用户名、密码、昵称、年龄、地址、性别...

    一、表设计之关联关系:

    1、一对一:数据一部分频繁被访问,一些不怎么被访问,则这样可以创建两张表。

    • 什么是一对一:有AB两张表,其中A表的一条数据对应B表的一条数据,同时B表的一条数据也对应A表的一条数据。
    • 应用场景:用户表和用户信息扩展表;商品表和商品信息扩展表;

    外键:表中用于建立关系的字段称为外键,一张表可能有多个外键,但只会有一个主键。

    • 如果建立关系:从表中添加外键指向主表的主键。

    例:创建user和userinfo表:假如用户包含用户名、密码、昵称、年龄、地址、性别字段。我们就可以将常用的用户名和密码存储到user表,并未其添加主键id,同时创建从表userinfo,存储其他昵称、年龄等信息,并添加外键userid对应主表的主键id,这样就保证了一对一的关系,在我们需要查询某用户的userinfo时可以用id对应从表外键userid进行匹配查询即可。

    2、一对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的一条数据。

    • 应用场景:用户表和部门表;商品表和分类表(一个分类里包含很多商品,但是一种商品只能属于一个分类)。
    • 建立关系:在多个一段添加外键,指向另外一张表的主键。

    例:创建emp表和dept表:比如我们创建的dept部门表并添加主键id,中有两个部门一个技术部id = 1、一个设计部对应id=2,此时我们再创建一个emp表,并为其添加外键deptid = 1对应主表dept的主键id,例如我们添加小张 deptid = 1,小李deptid = 1,小王deptid = 2,小赵deptid = 2这是我们通过dept表中的主键id 为 1去从表emp中对应的deptid相匹配的1去查找,可以找出小张和小李两个人,这就实现了一对多,一个主表dept对应了从表emp中的多条数据。

    3、多对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的多条数据。

    • 应用场景:老师表和学生表;用户表和角色表;角色表和权限表;
    • 建立关系:通过第三张关系表保存两张主表的关系;第三张关系表里记录的是两个主表的主键;

    例:创建老师表,学生表和关系表:

    create table teacher(id int primary key auto_increment,name varchar(10));
    create table student(id int primary key auto_increment,name varchar(10));
    create table t_s(tid int,sid int);
    

    查询每个学生姓名和对应的老师姓名:

    select s.name,t.name from t_s ts join teacher t on ts.tid = t.id join student s on ts.sid = s.id;
    

    查询李老师的学生都有谁:

    select s.name,t.name from t_s ts join teacher t on ts.tid = t.id join student s on ts.sid = s.id where t.name = '李老师';
    

    查询小丽的老师都有谁:

    select s.name,t.name from t_s ts join teacher t on ts.tid = t.id join student s on ts.sid = s.id where s.name = '小丽';
    

    4、自关联:当前表中添加外键,外键的值指向当前表的主键,这种关联方式称为自关联;

    • 应用场景:在用户表中,有的用户有上级领导,有的没有,此时可以添加一个外键指向上级领导的主键id。

    例:创建person表,主键id,name,mgr领导指向主键id

    create table person(id int primary key auto_increment,name varchar(10),mgr int);
    

    保存以下数据:效果如下:

    insert into person values(null,'如来',null),(null,'唐僧',1),(null,'悟空',2),(null,'猴孙',3);
    

    在这里插入图片描述
    查询每个人的名字和上级的名字:

    select p.name,p2.name as 上级	 from person p left join person p2 on p.mgr = p2.id; 
    

    在这里插入图片描述
    5、表设计案例:权限管理

    • 实现方式:实现权限管理功能需要准备三张主表和两张关系表;
    • 应用场景:
      • 主表:一张用户表、一张角色表、一张角色权限表
      • 关系表:用户和角色对应关系,角色和角色权限对应关系

    练习:创建表:

    create table user(id int primary key auto_increment,name varchar(10));
    create table role(id int primary key auto_increment,name varchar(10));
    create table module(id int primary key auto_increment,name varchar(10));
    create table u_r(uid int,rid int);
    create table r_m(rid int,mid int);
    

    插入数据:

    insert into user values(1,'刘德华'),(2,'凤姐');
    insert into role values(1,'男游客'),(2,'男会员'),(3,'女游客'),(4,'女管理员'); 
    insert into module values(1,'男浏览'),(2,'男发帖'),(3,'女浏览'),(4,'女发帖'),(5,'女删帖');
    

    保存用户和角色的关系 刘德华:男会员和女游客 凤姐:女管理员和男游客:

    insert into u_r values(1,2),(1,3),(2,1),(2,4);
    

    保存角色和权限:

    insert into r_m values(1,1),(2,1),(2,2),(3,3),(4,3),(4,4),(4,5);
    

    查询每个用户权限有哪些:

    select u.name 名字,m.name 权限 from user u join u_r on u.id = ur.id join r_m rm on ur.rid = rm.rid join module m on rm.mid = m.id;
    

    查询凤姐的权限:

    select u.name 名字,m.name 权限 from user u join u_r ur on u.id=ur.uid join r_m rm on rm.rid=ur.rid join module m on rm.mid=m.id where u.name='凤姐';
    

    查询拥有男预览权限的用户有谁:

    select m.name 权限,u.name 预览 from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on m.id=rm.mid where m.name in('男浏览','女浏览');
    
    展开全文
  • 避免对经常更新的表进行创建过多的索引,因为当表中有数据更改时,索引也会进行调整和更新,十分消耗系统资源 数量小的表建议不要创建索引,数据量小时索引不仅起不到明显的优化效果,对于索引结构的维护反而消耗...

    MYSQL索引设计和创建原则

    1. 对于经常查询的字段,建议创建索引
    2. 索引不是越多越好,一个表如果有大量的索引,不仅占用磁盘空间,而且对于写操作会造成性能底下
    3. 避免对经常更新的表进行创建过多的索引,因为当表中有数据更改时,索引也会进行调整和更新,十分消耗系统资源
    4. 数量小的表建议不要创建索引,数据量小时索引不仅起不到明显的优化效果,对于索引结构的维护反而消耗资源
    5. 不要在区分度地的字段建立索引,比如性别字段,只有男女,建立索引完全起不到优化效果
    6. 定义有外键的数据列一定要建立索引,因为如果外键列上缺少索引会带来两个问题:
      1. 限制并发性:如果子表外键没有创建索引,那么在子表进行DML操作时,将会锁住整个父表
      2. 影响性能:首先如果外键上缺少索引,从主表关联子表的查询就只能对子表进行全表扫描的查询
    展开全文
  • 避免对经常更新的进行过多的索引,并且索引的列尽可能少。而对经常用于查询的字段(外键)应该创建索引,但要避免添加不必要的字段。...比如在学生的“性别字段上只有“男”与“女”两个不同...
    1. 避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。而对经常用于查询的字段(外键)应该创建索引,但要避免添加不必要的字段。
    2. 数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
    3. 在条件表达式中经常用到的、不同值较多(主键的列)的列上建立索引,在不同值少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值,因此就无须建立索引。如果建立索引,不但不会提高查询效率,反而会严重降低更新速度。
    4. 频繁进行排序或分组(即进行GROUPBY或ORDERBY操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度.对于那些定义为text, image和bit数据类型的列不应该增加索引。

      

    1) 定义主键的数据列一定要建立索引(主键--唯一标识一条记录,不能有重复的,不允许为空,用来保证数据完整性,只能有一个)。

    2) 定义有外键的数据列一定要建立索引( 外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值,用来和其他表建立联系用的,一个表可以有多个外键)。

    3) 对于经常查询的数据列最好建立索引。

    4) 对于需要在指定范围内的快速或频繁查询的数据列;

    5) 经常用在WHERE子句中的数据列。

    6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8) 对于定义为text、image和bit的数据类型的列不要建立索引。

    9) 对于经常存取的列避免建立索引 

    9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

    转载于:https://www.cnblogs.com/seacher/p/8580817.html

    展开全文
  • 在实际操作过程中,应该选取表中哪些字段作为索引? 为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段创建索引和创建什么类型的索引; 索引使用心得总结: 频繁作为查询条件的字段应该创建索引 多表...
  • 数据库查询习题.pdf

    2020-12-21 14:02:42
    字段包括 院系 主任 书记 电话 地址 建立两表的关联并在每张表中填入若干条记录 请在设计视图 下建表 , 请考虑红色字段的 数据类型 请将第一个字段 设置为主键 1创建选择查询查找并显示学生的编号 姓名性别年龄4 个...
  • hiveSql-分区

    千次阅读 2018-03-25 13:31:49
    hive中创建表分区: 分区什么时候用? 例子1:假如我有一个log日志表,我要对其建立分区的话,...如下,我写的是将性别进行分区注意:你不能写成 name String,也就是分区字段不能与创建表字段一致,否则会报错c...
  • 游标应用

    千次阅读 2020-06-09 08:42:58
    2、 1)创建考生字段包括:准考证号,姓名,性别,报考专业、总分(初始值为空),准考证号为主键。 2)考生成绩字段包括准考证号、科目名称(设定英语、政治、数学、专业综合),成绩,准考证号为外部键,...
  • 转:oracle 索引

    2010-10-28 16:45:33
    索引创建策略 1.导入数据后再创建索引 2.不需要为很小的表创建...对于取值范围很小的字段(比如性别字段)应当建立位图索引 4.限制表中的索引的数目 5.为索引设置合适的PCTFREE值 6.存储索引的表空间最好单独设定 ...
  • 首先可以在mysql表中创建一个users表 除了设置一些username,password等必要字段以外还要设立sex(性别) 这里规定sex = 1为男 sex = 0 为女 然后建立表格genderstatistics(性别统计表)设立两个字段分别为 boy_...
  • mysql知识点总结1

    2018-01-19 11:31:15
    简介主要知识点包括:能够与mysql建立连接,创建数据库、,分别从图形界面与脚本界面两个方面讲解相关的知识点包括:E-R关系模型,数据库的3范式,mysql数据字段的类型,字段约束数据库的操作主要包括:数据库的...
  • 主要知识点包括:能够与mysql建立连接,创建数据库、,分别从图形界面与脚本界面两个方面讲解 相关的知识点包括:E-R关系模型,数据库的3范式,mysql数据字段的类型,字段约束 数据库的操作主要包括: ...
  • 数据重复且分布平均的字段,因此他建立索引就没有太大的效果(例如性别字段,是否上架等等,不适合建立索引) 数据变更需要维护索引,意味着索引越多维护成本越高。当对表的数据进行增加、删除、修改时,索引也...
  • oracle 索引详解

    2009-06-30 09:51:07
    Oracle的索引 索引和对应的应该位于不同的空间,oracle能够并行读取位于不同硬盘上的数据,可以避免产生I/O冲突B树索引:在B树的叶节点存储索引...对于取值范围很小的字段(比如性别字段)应当建立位图索引4...
  • 索引是建的越多越好吗

    千次阅读 2020-06-30 15:19:42
    4.数据重复且分布平均的字段,因此他建立索引就没有太大的效果(例如性别字段,是否上架等等,不适合建立索引) 5.数据变更需要维护索引,意味着索引越多维护成本越高。当对表的数据进行增加、删除、修改时,索引...
  • 实例237 利用聚合函数First或Last求数据表中第一条或最后一条记录 10.11 多表查询(连接查询) 实例238 利用FROM子句进行多表查询 实例239 使用表别名 实例240 合并多个结果集 10.12 嵌套查询 实例241 简单嵌套查询 ...
  • 数据优化笔记

    2019-04-24 14:13:51
    1、索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全扫描 2、应尽量避免在WHERE子句字段进行NULL值判断,否则将导致...
  •  例如:在刚创建的aaa库中建立表name,表中有id(序号,自动增长),xm(姓名),xb(性别),csny(出身年月)四个字段  use aaa;  mysql> create table name (id int(3) auto_incrementnot null primary key,
  • 索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全扫描 应尽量避免在WHERE子句字段进行NULL值判断,否则将...
  • MySQL优化

    2020-08-22 11:38:24
    1.1 索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全扫描 1.2 应尽量避免在WHERE子句字段进行NULL值判断,否则将导致...
  • oracle约束

    2021-06-09 20:01:10
    约束是保证表中数据完整性的一种有效检测手段。在之前建立完数据表之后,可以发现里面能够随意设置数据,例如:设置性别的时候,可以设置“不男不女”,在设置编号的时候,编号也可以重复,这些都是因为数据表缺少...
  • java+mysql代码

    2018-05-13 18:56:55
    5.取消报名:提示输入要取消报名的学生姓名,从报名信息表中删除该学生记录。如图5所示,成功后提示“取消报名成功!” 6.退出系统:提示“谢谢使用!”后退出系统,如图6所示。 图1 主菜单 图2 学生报名 图3 ...
  • 5 分别用企业管理器和查询分析器修改表的结构 在“图书”表中 增加两个字段 分别为“数量”和“购买日期” 在“借阅”表中增加一个“还书日期”字段 6 用企业管理器在上述三个表中输入部分虚拟数据 7 在查询分析器...
  • 附加的内容:在jsp连接数据库的代码(前提是在login数据库新建了一个stuinfo,并且有编号(SID)、场馆(SName)、性别(SSex)、年龄(SAge)、体重(SWeight) 这些字段: <%@ page language=“jav...
  • b) STUDENT表中增加一个字段SBIRTH,类型设置为日期时间类型,增加一个ADDRESS字段,类型为文本(字符); c) 删除STUDENT表中ADDRESS字段; d) COURSE表中CNO字段设为非空和唯一; 9. 重新定义一个简单表,然后用...
  • 在“借阅”表中增加一个“还书日期”字段。 6、用企业管理器在上述三个表中输入部分虚拟数据。 7、在查询分析器中实现基于但个表的查询 ① select * from Book ② select * from book where Bclass=’计算机’ ③ ...

空空如也

空空如也

1 2 3
收藏数 58
精华内容 23
关键字:

创建表中建立字段性别