3分钟速读原著《高性能MySQL》(二)

第五章创建高性能的索引

1.索引类型

1.1 普通索引 NORMAL: 是最基本的索引，它没有任何限制。
1.2 唯一索引 SPATIAL: 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
1.3 主键索引: 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
1.4 组合索引: 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
1.5 全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

2.缺点

2.1 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2.2 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
2.3 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

3.注意事项

3.1 索引不会包含有null值的列: 只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
3.2 使用短索引对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.3 索引列排序查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
3.4 like语句操作一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “�a%” 不会使用索引而like “aaa%”可以使用索引
3.5 不要在列上进行运算这将导致索引失效而进行全表扫描

例如 SELECT * FROM table_name WHERE YEAR(column_name)<2017;

3.6 不使用not in和<>操作

4.索引方法

hash:精准查询使用
B-Tree:范围查询使用

5.通过EXPLAIN优化索引

推荐文章

第六章查询性能优化

1.慢查询

本质是访问的数据太多,原因可能有如下

1.1 检索大量超过需要的数据
1.2 服务层在分析大量超过需要的数据行

2.慢查询解决方案

2.1 不查询不需要的记录
2.2 多表关联时不要返回不必要的全部列
2.3 不重复查询相同的数据

3.查询优化器的局限性

3.1 子查询大部分情况下都很差,但并不代表所有情况都很差,所以可以通过EXPLAIN看它的执行计划来判定最终是否要使用子查询

第七章 MySQL的高级特性

一.分区表

1.分区表需要具备以下条件

1.1 有唯一索引，分区列必须是唯一索引的一部分，索引列也可以是null
1.2 没有唯一索引，可以指定任何列
1.3 主键和唯一索引都存在，主键包含在唯一索引中，只能以主键进行分区
1.4 两个唯一索引列也不能创建分区

2.分区表的类型

2.1 RANGE分区：行数据基于一个给定连续范围分区
2.2 LIST分区：同RANGE，区别在于给定的不是连续范围，是离散的值
2.3 HASH分区：根据用户自定义的表达式的返回值进行分区，返回值不能是负数
2.4 KEY分区：根据MySQL内部提供的哈希函数进行分区
2.5 COLUMNS分区：5.5开始支持，可以直接使用非整形的数据进行分区，分区根据类型直接比较而得，不需要转换为整形

3.RANGE分区

可以根据对列的指定,如下所示,当主键ID值大于10小于20的就会存储在p1区,当ID值小于10时就会存储在p0区,并且在磁盘当中也会分成两个ibd文件来进行存储,这种情况下如果插入的数据范围不在定义范围时,例如插入30,则会报错.RANGE分区主要适用于日期列分区,如销售类的表,可以根据年份来分区存放销售记录

代码语言：javascript复制

create table t(
    id int(11)
)engine=innodb
partition by range(id)(
    partition p0 values less than(10),
    partition p1 values less than(20)
);

4.LIST分区

LIST分区和RANGE分区非常相似，只是LIST分区的值是离散的。与RANGE分区的 VALUES LESS THAN 不同，LIST分区使用 VALUES IN，所以每个分区的值是离散的，只能是定义的值。如

代码语言：javascript复制

create table t(
    a int(11),
    b int(11)
)engine=innodb
partition by list(b)(
    partition p0 values in(1,3,5,7,9),
    partition p1 values in(2,4,6,8,10)
);

相对来说RANGE分区比LIST分区要好用的多,LIST分区很容易漏了一些我们想要定义的范围,但是对于一些表中可以区分的种类较少时,使用LIST分区就会十分实用

5.HASH分区

要使用HASH分区分割一个表，要在CREATE TABLE 语句上加一个 PARTITION BY HASH(expr)子句，其中expr是一个返回整数的表达式，如果没有PARTITIONS num子句，则默认的分区数量是1

代码语言：javascript复制

create table t(
    id int(11),
    date datetime
)engine=innodb
partition by hash(year(date))
partitions 4;

5.1 LINEAR HASH分区
- 优点:增加、删除、合并和拆分分区变得更加快捷，这有利于处理含有大量数据的表。
- 缺点:与HASH分区相比，数据分布可能不大均匀。

MySQL数据库还支持一种LINEAR HASH的分区，他使用一个更复杂的算法来确定新行插入的分区。语法为 linear hash(expr)，只是将关键字 HASH 换成了 LINEAR HASH。

6.KEY分区

KEY分区和HASH分区相似。KEY分区支持除text和BLOB之外的所有数据类型的分区，而HASH分区只支持数字分区，KEY分区不允许使用用户自定义的表达式进行分区，KEY分区使用系统提供的HASH函数进行分区。

二.视图

1.什么是视图

视图就是一条SELECT语句执行后返回的结果集

2.视图的特性

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）
可以跟基本表一样，进行增删改查操作

3.视图的作用

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性
更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别

4.视图的使用场景

权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如工资
关键信息来源于多个复杂关联表，可以创建视图提取我们需要的信息，简化操作

三.外键

1.定义

代码语言：javascript复制

表的外键是另一表的主键, 外键可以有重复的, 可以是NULL

2.作用

代码语言：javascript复制

用来和其他表建立联系用的

3.个数

代码语言：javascript复制

一个表可以有多个外键

4.建议

引用自阿里Java开发手册:【强制】不得使用外键与级联,一切外键概念必须在应用层解决。
总的来说不要在数据库层面使用外键,了解即可

四.查询缓存

代码语言：javascript复制

在Navicat中执行 SHOW VARIABLES LIKE '%query_cache%';

1.query_cache_type

代码语言：javascript复制

查询缓存类型，有0、1、2三个取值。0则不使用查询缓存。1表示始终使用查询缓存。2表示按需使用查询缓存。

2.query_cache_size

代码语言：javascript复制

默认情况下query_cache_size为0，表示为查询缓存预留的内存为0，则无法使用查询缓存

3.缓存条件

代码语言：javascript复制

查询缓存可以看做是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）且开启了查询缓存，那么第二次查询就直接从查询缓存中取结果

4.缓存数据的失效时机

代码语言：javascript复制

在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

全文检索数据库 sql

0 人点赞

3分钟速读原著《高性能MySQL》(二)

第五章 创建高性能的索引

1.索引类型

2.缺点

3.注意事项

4.索引方法

5.通过EXPLAIN优化索引

第六章 查询性能优化

1.慢查询

2.慢查询解决方案

3.查询优化器的局限性

第七章 MySQL的高级特性

一.分区表

1.分区表需要具备以下条件

2.分区表的类型

3.RANGE分区

4.LIST分区

5.HASH分区

6.KEY分区

二.视图

1.什么是视图

2.视图的特性

3.视图的作用

4.视图的使用场景

三.外键

1.定义

2.作用

3.个数

4.建议

四.查询缓存

1.query_cache_type

2.query_cache_size

3.缓存条件

4.缓存数据的失效时机

第五章创建高性能的索引

第六章查询性能优化