一步一步优化你的mysql------创建高性能的索引

加快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