您当前的位置:首页 > 计算机 > 软件应用 > 数据库 > MySQL

Mysql数据库:主键、唯一值、索引、分区列

时间:02-04来源:作者:点击数:

SQL语句检测

可以用EXPLAIN或者DESC查看sql是否使用到索引

1、然后给时间字段创建索引

2、可以用EXPLAIN查看sql是否使用到索引

3、使用函数时,索引会失效,用下面这种方式就可以使用索引

SELECT*FROM test_table WHERE create_time >= str_to_date('2019-12-30', '%Y-%m-%d');

select * from test_table where timestamp=str_to_date('2021-05-27 00:00:00', '%Y-%m-%d %H:%i:%s');

#str_to_date(‘09/01/2009’,’%m/%d/%Y’);
#str_to_date(‘20140422154706’,’%Y%m%d%H%i%s’);
#str_to_date(‘2014-04-22 15:47:06’,’%Y-%m-%d %H:%i:%s’);

4、用EXPLAIN查看一下sql,索引已经生效,效率提升了10倍

主键(primary key)

唯一

不允许为空

一张表中最多一个主键

可以组合

( 主键可以理解为是一个特殊的索引 )

唯一键(unique)

唯一

可以为空

一张表中可以有多个唯一键

可以组合

索引

索引可以提高查询效率

索引类型:

普通索引、唯一索引、主键索引、组合索引

索引(index)和约束(key)的区别

大多数情况下他们展示出来的效果都差不多,但是还是不能将他们划等号(至少理论上是这样)。索引(index)和约束(key)的区别主要在于二者的出发点不同,索引(index)负责维护表的查找和操作速度,约束(key)负责维护表的完整性。

使用索引的注意事项

1、索引不会包含有NULL值的列

2、使用短索引(提高查询速度,节省磁盘空间和IO操作)

3、不使用NOT IN 、<>、!=操作,但<,<=,=,>,>=,BETWEEN,IN是可以用到索引的

4、如果索引了多列,要遵守最左前缀法则,即查询从索引的最左前列开始,并且不会跳过索引中的列

5、索引列不应该作为表达式的一部分,即也不能在索引上使用函数

6、不要使用类型转换

7、order by,尽量使用index方式排序(表示mysql扫描索引本身完成排序),避免使用FileSort排序。order by满足一下情况会使用index方式排序:

  1. order by语句使用索引最左前列
  2. 使用where子句和order by子句条件组合满足索引最左前列规则

8、慎用left join (left join会创建临时表)。在join操作中(需要从多个数据表提取数据时),mysql只有在主键和外键的数据类型相同时才能使用索引,否则及时建立了索引也不会使用

9、谨防where子句中的OR(where语句使用or,且没有使用覆盖索引,会进行全表扫描),尽量使用UNION代替OR

10、善用LIMIT和覆盖索引,减少select * from ... 的使用

11、like语句操作。一般情况下不鼓励使用like操作,如果非使用不可,注意正确的使用方式。like ‘%aaa%’不会使用索引,而like ‘aaa%’可以使用索引。

12、mysql查询只使用一个索引,如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个列的排序,如果需要最好给这些列建复合索引。

时间字段索引

分区列

为什么分区时,主键必须加分区键?

mysql规定:作为分区的字段必须是主键。除非没有主键索引。

表里是否有主键字段,如果有的话,MYSQL的分区字段就必须包含在主键字段内(把分区列包含在主键/唯一约束/唯一索引的键列中),需要创建联合主键,如果原表没有主键,就不需要。

1、如果主键没有加分区键,那么只能 保证每个分区内是唯一的,而无法保证所有分区的数据唯一。

2、主键需要区分数据的唯一性,而不包含分区键的话,local index是无法做到这个唯一性要求的,这是技术上的限制,只能根据这个限制调整你的需求。

分区列是TIMESTAMP类型

ERROR 1659 (HY000): Field 'create_time' is of a not allowed type for this type of partitioning。

分区键必须包含主键或主键之一

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

最后再上一个时间列分区的例子:

示例分区表

CREATE TABLE range_timestamp (

id INT,

hiredate TIMESTAMP,

primary key(id,hiredate)

)

PARTITION BY RANGE ( UNIX_TIMESTAMP(hiredate) ) (

PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-02 00:00:00') ),

PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-03 00:00:00') ),

PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-04 00:00:00') ),

PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-05 00:00:00') ),

PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-06 00:00:00') ),

PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-07 00:00:00') ),

PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-08 00:00:00') ),

PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-09 00:00:00') ),

PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2015-12-10 00:00:00') ),

PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-11 00:00:00') )

);
方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门