mysql之数据库优化

首先声明，非本人同意不得转载！！！

数据库优化

 

整形存储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';

 

小结论：索引列的顺序非常重要