MySQL 学习总结 08 - 优化(设计层)- 索引与分区分表
Write By CS 逍遥剑仙
我的主页: http://www.csxiaoyao.com/
- GitHub: https://GitHub.com/csxiaoyaojianxian
- Email: sunjianfeng@csxiaoyao.com
- QQ: 1724338257
设计层的优化大致有: 索引, 分区, 分表
1. 索引应用
1.1 索引类型
普通索引:(index) 对关键字没有要求, 如果一个索引在多个字段提取关键字, 称为复合索引
唯一索引:(unique key) 关键字不能重复, 同时增加唯一约束
主键索引:(primary key) 关键字不能重复, 且不能为 NULL, 同时增加主键约束
全文索引:(fulltext index) 关键字来源于字段中提取的特别关键词
1.2 创建索引
- # 建表时直接添加索引
- MySQL> create table t2 (
- -> id int primary key auto_increment, # 主键索引
- -> name varchar(32) not null,
- -> age tinyint not null,
- -> intro text,
- -> unique key(name), # 唯一索引
- -> index(age), # 普通索引
- -> fulltext index(intro), # 全文索引
- -> index(name,age) # 复合索引
- -> )engine myisam charset utf8;
- # 表创建后添加索引
- MySQL> alter table t2 add unique key(name), add index(age), add fulltext index(intro), add index(name,age);
注意:
表创建后添加索引, 表中已有数据要符合唯一 / 主键约束才能创建成功.
auto_increment 属性依赖于某个主键 / 唯一 key
1.3 查看索引
MySQL> show create table <表名>;
MySQL> show index from <表名>;
MySQL> show indexes from <表名>;
MySQL> show keys from <表名>;
MySQL> desc <表名>;
1.4 索引删除
主键索引删除
- # 如果有 auto_increment 属性, 要先去掉该属性再删除
- MySQL> alter table t2 modify id int unsigned;
- # 没有 auto_increment 属性时
- MySQL> alter table t2 drop primary key;
其他索引删除
# 如果没有指定索引名可以通过查看索引获取
MySQL> alter table <表名> drop index <索引名>;
1.5 创建索引的场合
频繁作为查询条件的字段应该创建索引, 如学生学号
唯一性不强的字段不适合单独创建索引, 即使频繁作为查询条件, 如性别
更新频繁的字段不适合创建索引, 如登录次数
不会出现在 where 子句中的字段不应该创建索引
1.6 执行计划分析
通过执行计划可以分析 sql 的执行效率
MySQL> explain select * from t2 where id=1\G
1.7 索引数据结构
1.7.1 myisam 引擎
BTREE, 索引的节点中存储数据的物理地址(磁道和扇区).
查找时, 找到索引后根据索引节点中的物理地址查找具体数据内容.
索引和数据分开存储.
1.7.2 innodb 引擎
主键索引: 索引文件中不仅存储主键值, 还直接存储行数据, 称为聚簇索引.
非主键索引: 索引中存储主键 id (指向对主键的引用), 而 myisam 的主键 / 非主键索引都指向物理地址.
如果没有主键, 则 unique key 作为主键; 如果没有 unique key, 则系统生成内部 rowid 作为主键
例如: 通过 age 创建的索引查询年龄为 25 岁的人, 先根据 age 建立的索引找到该记录的主键 id, 再根据主键 id 通过主键索引找出该条记录.
聚簇索引: 优势, 根据主键查询条目比较少时, 不用回行(数据在主键节点下); 劣势, 碰到不规则数据插入时会造成频繁的页分裂.
1.8 索引覆盖
如果查询的列恰好是索引的一部分, 那么查询只需在索引区进行, 不需要到数据区再找数据, 速度非常快. 负面影响是增加了索引尺寸.
1.9 索引使用原则
(1) 列独立
索引列不能作为表达式的一部分, 也不能作为函数参数.
- # ID 上有主键索引但没有用到索引的情况
- MySQL> desc select * from user where id+2=4\G
- # 可以使用索引的情况
- MySQL> desc select * from user where id=4-2\G
- MySQL> desc select * from user where 4-2=id\G
(2) like 查询
模糊匹配, 左侧没有通配符可以使用索引, 以 % 开头的 like 查询不使用索引.
- # 索引覆盖
- MySQL> desc select * from user where name like '%cs'\G # 不使用索引
- MySQL> desc select * from user where name like 'cs%'\G # 使用索引
(3) OR 运算
参与 OR 运算的字段都必须拥有索引才能使用使用.
(4) 复合索引
对于创建的多列 (复合) 索引, 只要查询条件使用了最左边的列, 索引一般就会被使用.
- MySQL> alter table user add index(name,age);
- MySQL> select * from user where name = "cs"; # 使用索引
- MySQL> select * from user where age = 25; # 未使用索引
1.10 MySQL 智能选择
如果 MySQL 认为全表扫描不会慢于使用索引, 则 MySQL 会选择放弃索引, 直接全表扫描. 一般当取出的数据量超过表中数据的 20%, 优化器就不会使用索引, 直接全表扫描.
1.11 group by 优化
默认情况下, MySQL 会对 group by col1,col2 进行排序, order by col1,col2, 可以通过 group by null 禁止排序, 优化查询速度.
1.12 前缀索引
占据空间更小, 运行速度更快.
MySQL> alter <表名> add key (字段(前 n 位位数))
如何确定位数?
MySQL> select count(distinct left(id,9)) from t1;
1.13 全文索引
全文索引把内容中的一些单词 (非简单单词) 拆分作为索引字段使用, 可以解决模糊查询不能使用索引的问题.
- # 添加全文索引
- MySQL> alter table t1 add fulltext index(name);
- # 使用方法 select * from t1 where match(<字段>) against(<模糊内容>);
- # 错误案例 select * from t1 where <字段> like '%<模糊内容>%';
- # 使用
- MySQL> select * from t1 where match(title) against('well');
注意:
字段类型必须为 varchar/char/text
mysql5.6.4 前只有 Myisam 支持, 之后 Myisam 和 innodb 都支持
目前只支持英文, 中文支持需要使用 Sphinx
生产活动中不常用, 可以通过 Sphinx 代替
全文索引有额外操作, 对常用单词不建索引
2. 分区技术
2.1 分区概念
如果数据表记录非常多, 达到上亿条, 表活性降低, 影响 MySQL 整体性能, 可以使用分区技术, 把一张表, 从逻辑上分成多个区域, 便于存储数据. MySQL 本身支持分区技术.
MySQL> create table <表名> (
-> <字段信息>,
-> <索引>
-> )<表选项>
-> partition by <分区算法>(<分区字段>)(
-> <分区选项>
-> );
分区算法:
条件分区: list(列表), range(范围), hash/key(取模轮询)
2.2 list
list: 条件值为一个数据列表
例: 职员表 p_list(id, name, store_id) // store_id 为分公司的 id
区域 | store_id |
---|---|
北部 | 1, 4, 5, 6, 17, 18 |
南部 | 2, 7, 9, 10, 11, 13 |
东部 | 3, 12, 19, 20 |
西部 | 8, 14, 15, 16 |
实际 sql 操作
- MySQL> create table p_list(
- -> id int,
- -> name varchar(32),
- -> store_id int
- -> )engine innodb charset utf8
- -> partition by list(store_id)(
- -> partition p_north values in(1,4,5,6,17,18),
- -> partition p_east values in(2,7,9,10,11,13),
- -> partition p_south values in(3,12,19,20),
- -> partition p_west values in(8,14,15,16)
- -> );
- MySQL> insert into p_list values(12,'csxiaoyao',3); # 存入东部区域
查询分区的使用情况, 注意: 只有 where 子句含分区字段 store_id 才能使用分区.
- MySQL> explain partitions select * from p_list where store_id=20\G
- 2.3 range
range 模式允许将数据划分不同范围, 例如按照月份划分若干分区.
- MySQL> create table p_range(
- -> id int,
- -> name varchar(32),
- -> birthday date
- -> )engine myisam charset utf8
- -> partition by range(month(birthday))(
- -> partition p_1 values Less than(3),
- -> partition p_2 values Less than(6),
- -> partition p_3 values Less than(9),
- -> partition p_4 values Less than MAXVALUE
- -> );
- # 插入数据
- MySQL> insert into p_range values(1,'csxiaoyao','2018-08-24'),(2,'sunshine','2018-11-28'); # 存入 p_3 和 p_4
- 2.4 hash
hash 模式通过对表的一个或多个列的 hash key 计算得到的数值对应的数据区域进行分区. 例如可以建立一个对主键进行分区的表.
- MySQL> create table p_hash(
- -> id int,
- -> name varchar(20),
- -> birthday date
- -> )
-> -- 按生日月份 hash 值将数据划分到 5 个区中
-> partition by hash(month(birthday)) partitions 5;
2.5 分区管理
2.5.1 删除分区
(1) key/hash 类分区删除不会造成数据丢失, 删除的分区的数据会重新整合到剩余分区, 至少要保留一个分区, 可以使用 drop table 删除整个表.
- # 求余方式(key/hash)
- # alter table <表名> coalesce partition <数量>;
- MySQL> alter table p_hash coalesce partition 4;
(2) range/list 类分区删除会造成数据丢失
- # 范围方式(range/list)
- # alter table <表名> drop partition <分表名>;
- MySQL> alter table p_list drop partition p_north;
2.5.2 增加分区
(1) key/hash 类增加分区
- # 求余方式(key/hash)
- # alter table <表名> add partition partitions <数量>;
- MySQL> alter table p_hash add partition partitions 5;
(2) range/list 类增加分区
- # 范围方式(range/list)
- # alter table <表名> add partition(
- # partition <分区名> values Less than (<常量>)
- # 或
- # partition <分区名> in (n,n,n)
- # );
2.6 说明
注意: 创建分区的字段必须是主键 / 唯一键或其中的一部分.
3. 分表技术
水平分表: 把一个表的记录信息存储到分表中.
垂直分表: 把一个表的全部字段存储到分表中.
3.1 水平分表
物理方式分表, 程序需要考虑分表算法, 即判断读写的表.
比如, 根据 id 参数来选择对应的表
- <?PHP
- $id = $_GET['id'];
- $tableArea = $id % 4;
- $tableName = 'tb_' . $tableArea;
- $sql = "insert into $tableName values( ... )";
- ...
问题: 添加数据时没有 id, 如何确定待添加的分表名?
解决方案: 创建一个独立的数据表 flag, 专门对记录的 id 值进行维护, 每次插入数据先通过 flag 表确定 id, 再使用该 id 完成计算确定插入的分表, flag 表需要定期 delete 清空.
- MySQL> create table flag(id int primary key auto_increment)engine myisam charset utf8;
- <?PHP
- $sql = "insert into flag values(null)";
- mysql_query($sql);
- $id = mysql_insert_id();
- $area = $id % 4;
- ...
3.2 垂直分表
一个数据表中的不常用字段也会占据一定资源, 对整体性能产生影响, 可以将不常用的字段存储到另外的辅表中, 通过主键关联.
来源: https://www.qcloud.com/developer/article/1352200