文章目录

  • 优化索引
  • 为索引列选择合适的数据类型
  • 一般原则
  • 建立索引，但是不走索引的情况
  • 表分区
  • 表分区的功能
  • 范围分区（Range Partition
  • 列表分区（List Partition）
  • 哈希分区（Hash Partition）
  • 复合分区


优化索引

MySQL中，有两种方式生成语序结果集，一种是使用file sort（慢查询），一种是按索引顺序扫描（要在经常排序的字段上建立索引）注意：不是索引越多越好！

为索引列选择合适的数据类型

  • 越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和 CPU 缓存中都需要更少的空间，处理起来更快。
  • 简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。IP的存储问题。
  • 主键整型最好。
  • 避免 NULL，含有NULL的列做统计和查询优化很麻烦。

一般原则

  1. 有大量重复值、经常进行范围查询（=、> 、 < 、> =、< =、between、in）和order by、group by发生的列，可考虑建立聚簇索引。
  2. 经常同时存取多列，且每列都含有重复值可考虑建立组合索引，其前导列一定是使用最频繁的列。
  3. 索引不会包含有 NULL 值的列。只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  4. 尽量使用短索引。对字符串列进行索引，如果可能应该指定一个前缀长度。
  5. 索引列排序。MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  6. like 语句操作。一般情况下不鼓励使用 like 操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而 like “aaa%”可以使用索引。
  7. 尽量不要在列上进行运算。如，select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。

建立索引，但是不走索引的情况

1.存在null值

select * 
from 
	表名 
where 
	字段名 is not null;

2.NOT条件
包括：<>、NOT、in、not exists

select * 
from 
	表名 
where 
	字段名 in (数值1,数值2,,,数值n);

3.LIKE通配符的后匹配

select *
from
    表名
where
    字段名 like '模糊搜索的条件';

4.函数运算

select * 
from 
	test 
where 
	//比如upper函数
	upper(name)='SUNYANG';

5.数据类型的转换

select * 
from 
	sunyang 
where 
	id='123';

当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的int类型，但是在查询时，却用了上面的形式，此举例中id是int类型，但是输入了字符类型。

6.复合索引前导列区分不大

表分区

概念：表分区就是将一个大表按照mysql提供的几种方式，分成几个小表。
.
日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长、性能低下，如果涉及联合查询的情况，性能会更加糟糕。对表进行分区，目的就是减少数据库的负担，提高数据库的效率，通常来讲就是提高表的增删改查效率。
分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。应用程序读写的时候操作的还是大表名字，数据库系统自动去组织分区的数据。

表分区的功能

  • 与单个磁盘或文件系统分区相比，可以存储更多的数据。
  • 很容易就能删除不用或者过时的数据。
  • 一些查询可以得到极大的优化。
  • 涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行。
  • IO 吞吐量更大。
  • 分区允许可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。

范围分区（Range Partition

通常是使用频率最高的分区，如按月份划分，这样的数据保持均匀性比较好，如果划分的均匀性不是很好，需要考虑其他分区方法。
格式：

CREATE TABLE table_name(
	....//创建表的格式不变
)
partition by RANGE(table_column)(
	PARTITION p0 VALUES LESS THAN (values1),
	PARTITION p1 VALUES LESS THAN (values2),
	PARTITION p2 VALUES LESS THAN (values3)
	...
)

实例：可以将一个表通过年份划分成两个分区，2001-2010年、2011-2020。

列表分区（List Partition）

当需要明确控制如何将数据进行分区时，采用这种方式。只能进行单列分区，可以讲数据进行分组，比如按城市分区，几个城市放一起。
格式：

CREATE TABLE table_name(
	....//创建表不变
)
partition by LIST(table_column)(
	PARTITION p0 VALUES IN (0,4,8,12),
	PARTITION p1 VALUES IN (1,5,9,13), 
	PARTITION p2 VALUES IN (2,6,10,14), 
	PARTITION p3 VALUES IN (3,7,11,15)
	...
)

和范围分区差不多，只不过一个是范围，一个是具体数值。

哈希分区（Hash Partition）

如果数据不是那么容易进行划分，通过这种方式就很灵活了。可以将数据均匀的插入到不同的块，在并发时有利于提高效率，当无法用Range分区时，就可以用Hash分区。
格式：

CREATE TABLE table_name(
	....//创建表不变
)
 PARTITION BY HASH(table_column) 
 PARTITIONS nums;

和上述两种分表的格式不是太相同，只需要设置nums为分区个数就行了，是一个整数值。