Mysql(三) 架构优化和索引
一、选择优化的数据类型
Mysql支持很多种不同类型的数据类型,并且选择正确的数据类型对于获取高性能至关重要,不管选择何种数据类型,下面简单的原则会有助于做出更好的选择:
更小通常更好
简单就好
尽量避免NULL
1.整数
tinyint smallint mediumnint int bigint 他们分别需要8、16、24、32、64位存储空间,它们的范围为-2(N-1)到2(N-1)-1
2.实数
3.字符串类型
BLOB和TEXT类型
BLOB和TEXT分别以二进制和字符形式保存大量的数据。字符类型有TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT,二进制有TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。
BLOB和TEXT唯一的区别就是BLOB保存的是二进制数据,没有字符集和排序规则,但是TEXT有字符集和排序规则
使用ENUM代替字符串类型
4.日期和时间类型
DateTime 从1001年到9999年精度为秒(8字节)
TimeStamp保持了自1970年1月1日午夜(格林尼治标准时间)以来的秒数(4字节)只能表示1970年到2038年,mySql提供了FROM_UNIXTIME()函数把Unix时间戳转换为日期,并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。
5.位集数据类型(Note)
SET
6.选择标识符(Note)
7.特殊类型的数据(Note)
mysql> select inet_aton('192.168.0.100');
+----------------------------+
| inet_aton('192.168.0.100') |
+----------------------------+
| 3232235620 |
+----------------------------+
1 row in set (0.01 sec)
mysql> select inet_ntoa(3232235620);
+-----------------------+
| inet_ntoa(3232235620) |
+-----------------------+
| 192.168.0.100 |
+-----------------------+
1 row in set (0.00 sec)
二、索引基础知识
1.索引类型--索引是存储引擎层实现,而不是服务器层
B-Treee索引 能很好地用于全键值、键值范围或键前缀查找;
哈希索引(Note) 建立在哈希表的基础上,它只对使用了索引中的每一列的精确查找有用;
空间(R-Tree)索引
全文索引 FULLTEXT是MyISAM表的一种特殊索引;
三、高性能索引策略
1.隔离列
2.前缀索引和索引选择性
3.聚集索引 不是一种单独的索引类型而是一种存储数据的方式(SolidDB和InnoDB)。如果没有定义主键,InnoDB会试着使用唯一的非空索引(Unique Nonnullable Index)来代替;如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集。InnoDB只聚集在用一页面中的记录,包含相邻键值的页面也许会相距甚远。
比较InnoDB和MyISAM的数据布局
在InnoDB中按照主键顺序插入行(高并发时可能会造成单点竞争)
4.覆盖索引
索引的叶子节点包含了他们索引的数据;
包含(或者覆盖)所有满足查询需要的数据的索引叫覆盖索引(Covering Index)
5.为排序使用索引扫描
Mysql有两种产生排序结果的方式:使用文件排序(filesort)或扫描有序的索引
EXPLAIN的输出中Type列的值为“索引”,说明MySql会扫描索引,不要把这个和Extra列中的“Using
Index”混淆。
6.压缩(前缀压缩)索引
7.多余和重复索引
8.索引和锁定
四、索引实例研究
1.支持多种过滤条件
2.避免多个范围条件
3.优化排序
五、索引和表维护
1.查找并修复表损坏
check table(不是所有的存储引擎都支持)
REPAIR TABLE命令来修复损坏的表
离线引擎相关的修复工具,如:myisamchk
2.更小索引统计
records_in_range()它接受范围结束点并且返回该范围内记录的数量
info()它返回不同类型的数据包括数据基数性(每个键值有多少记录)
analyze table统计
可以是哟韩国show index from命令检查索引的基数性
3.减少索引和数据碎片
两种类型的数据碎片
行碎片
内部行碎片
为了消除数据碎片可以运行OPTIMIZE TABLE或转储并重新加载数据
MyISAM能通过排序算法重建索引来消除索引碎片
六、正规化和非正规化
七、加速alter table