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

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') )

);