首先声明,非本人同意不得转载!!!
数据库优化
整形存储IP:15位字符 192.168.110.113
select INET_ATON('192.168.110.113');
select INET_NTOA(3232263793);
尽量避免可为NULL的列:可为NULL的列被索引时,每个索引值需要一个额外的bit。
CHAR与VARCHAR
CHAR 空格补齐列定义的长度, 查询结果时后面的空格删除。
慷慨是不明智的选择:定义字符类型的长度,选择最合适的长度即可,不要随意定义超大长度,浪费空间。
数据库设计总结
1.尽量避免过度设计,例如会导致及其复杂查询的schema设计,或者有很多列的表设计。
2.使用小而简单的合适数据类型,除非真实数据模型中有确切的需要,否则应该尽可能地避免使用NULL值。
3.尽量使用相同的数据类型存储相似或相关的值,尤其是需要在关联条件中使用的列。
4.注意可变长字符串,其在临时表和排序时可能导致悲观的按最大长度分配内存。
5.尽量使用整形定义标识列。
6.避免使用MySQL已经废弃的特性,例如指定浮点数的精度等。
7.小心使用ENUM和SET。虽然他们用起来很方便,但是不要滥用,否则有可能变成陷阱。
索引
BTree索引,是一种树结构,索引速度比全表查询速度快。
每个叶子节点即使MySQL中的一个页,默认每页16KB大小。
MySQL中InnoDB使用B+Tree,B+Tree中每个叶子节点都有一个指向下一个叶子节点的指针。
组合索引
InnoDB中BTree索引生效的情况【customer创建组合索引(last_name, first_name, email)】
匹配最左前缀:查找姓为MILLER的人,只使用索引的第一列。
explain select * from customer where last_name='MILLER';
全值匹配:全值匹配是指和索引中所有的列进行匹配,例如查找姓名为MARIA MILLER,email为MARIA.MILLER@sakilacustomer.org的人。
explain select * from customer where last_name='MILLER' and first_name='MARIA' and email='MARIA.MILLER@sakilacustomer.org';
匹配列前缀:可以匹配某一列值的开头部分,例如查找以M开头的姓的人。
EXPLAIN select * from customer where last_name like 'M%';
匹配范围值:查找姓大于等于WEINER的人。
EXPLAIN select * from customer where last_name>='WEINER';
精确匹配某一列并范围匹配另一列:查找所有姓为MILLER,且名字是字母M开头的人。即第一列last_name的全匹配,第二列first_name范围匹配。(也是前缀匹配)
EXPLAIN select * from customer where last_name='MILLER' and first_name like 'M%';
#如果不是按照索引的最左列开始查找,则无法使用索引【不从last_name列开始检索】
explain select * from customer where first_name='MARIA' and email='MARIA.MILLER@sakilacustomer.org';
#不能跳过索引中的列【没有first_name列】
explain select * from customer where last_name='MILLER' and email>'SHEILA.WELLS@sakilacustomer.org';
#如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找【first_name为范围查找,email列无效】
explain select * from customer where last_name='MILLER' and first_name>'MARIA'
and email='MELANIE.ARMSTRONG@sakilacustomer.org';
小结论:索引列的顺序非常重要