加快alter table操作的速度
Alter table sakila.film modify column rental_duration tinyint(3) not null default 5;
Show status 显示这个语句做了1000次读和1000次写。他拷贝了整张表到一行新表,甚至列的类型、大小和可否为NULL属性都没有改变。
--》
Alter table sakila.film alter column rental_duration set default 5;
这个语句会直接修改.Frm文件而不涉及到表数据。所以这个操作是非常快的。
创建高性能的索引
在mysql中,存储引擎用类似的方法使用索引,现在索引中找到对应值,其先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。
例如:select first from sakila.actor where actor_id=5;
如果在actor_id 列上建有索引,则mysql将使用该索引找到actor_id为5的行,也就是说,mysql先在索引上按值进行查找,然后返回所有包含该值的数据行
索引的类型
B-tree索引
b-tree 通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同。B-tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要
的数据,取而代之的是从索引根节点开始搜索。根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找。
b-tree对索引列是顺序组织存储的,所以很适合查找范围数据。例如,在一个基于文本域的索引树上,按字母顺序传递连续的值进行查找是非常合适的,所以向“找出所有以1
到k开头的名字”这样的查找效率会非常高。
哈希索引
哈希索引基于哈希表实现,只有精确匹配索引列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的
行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。
只有memory引擎显示支持哈希索引,默认索引类型。
哈希索引并不是按照索引列匹配查找,无法用于排序,也不支持部分索引列匹配查找,只支持等值比较查询,如果哈希索引有很多冲突的话,一些索引维护操作的代价也会
很高。
适用场景:在数据仓库应用中的一种经典的“星型”schema,需要关联很多查找表,哈希索引就非常适合查找表的需求。
创建自定义哈希索引:
如果存储引擎不支持哈希索引,则可以模拟像InnoDB一样创建哈希索引。
思路:在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事,因为还是使用B-Tree进行查找,但是它使用哈希值而不是键本身进行查找。你需要做的就是
在查询的where子句中手动指定使用哈希函数。
实例:例如需要存储大量的URL,并需要根据URL进行搜索查找。如果使用B-Tree来存储URL,存储的内容就会很大,因为本身URL都很长
Select id from url where url=”http://www.mysql.com”;
若删除原来列上的索引,而新增一个被索引的url_crc列,使用crc32做哈希
Select id from url where url=”http://www.mysql.com” and url_crc=url_crc(“http://www.mysql.com”);
缺点:是需要手动维护,但是可以通过写触发器解决;
如果url非常多,或导致哈希冲突的可能性增大,解决办法:
自己可以实现一个比较简单的哈希冲突,一个简单的办法是可以使用MD5()函数返回值的一部分来作为自定义哈希函数。
Select conv(right(MD5(‘http://www.mysql.com’),16),16,10) as hash64;
注:right(字符串,截取右边字符串的长度)
Conv(string,m,n) 将字符串string由m进制转为n进制,并返回一个字符串
所以使用哈希索引进行查询的时候,必须在where子句中包含常量值:
Select id from url where url_crc=CRC32(“http://www.mysql.com”) and url=”http://www.mysql.com”;
要避免冲突问题,必须在where条件中带入哈希值和对应列值。如果不是想查询具体值,例如只统计记录数,则可以不带如列值。
空间索引
全文索引:全文索引是一种特殊类型的索引,他查找的是文本中的关键词,而不是直接比较索引中的值。
索引的优点:
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O