mysql优化概述3

1、前缀索引
建立索引关键字一种方案。
通常会使用字段的整体作为索引关键字。
有时，使用字段前部分数据，也可以去识别某些记录。
语法：
index `索引名` (`字段`(N)); 使用字段前N个字符建立索引。
N，究竟是多少？ 
使用N长度所达到的辩识度，极限接近于使用全部长度的辩识度概可。
> select count(*) from student; --总记录数，比如1000000
> select 1000000/count(distinct ename) from student; --ename能够达到的最大辩识度
> select 1000000/count(distinct substring(ename,1,5)) from student; --测试前5个字符达到的辩识度
依次取前N个字符，进行对比，找到极限接近辩识度的长度。

> alter table student add index `i_ename` (`ename`(9));
(*前缀索引不能使用索引覆盖。)

2、全文索引
特殊：关键字的创建上。
为了解决like '%keyword%'这类查询的匹配问题。

表结构如下：
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT
);

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly,MySQL ...');
如果我们要查询title和body中包含 database的记录，sql语句如下：

> select * from articles where title like '%database%' or body like '%database%';
这种情况无法通过创建普通索引来提高查询效率。
只能建立全文索引：

> alter table articles add fulltext index `fi_tb` (`title`,`body`);
使用全文索引需要使用特殊的匹配语法：

> select * from articles where match(title,body) against('database');
match() against() 返回的关键字的匹配度，(关键字与记录的关联程序)

> select * from articles where match(title,body) against('in');
上述语句没有返回结果。
原因？全文索引索引的关键字，不是整个字段数据，而是从数据中提取的关键词。
而停止词是不被索引的。

(*mysql的全文索引不支持中文)

3、索引的数据结构
hash
b-tree
两种数据结构，指的是mysql存储索引所采用的数据结构。其中用户所维护的所有索引结构b-tree结构。

4、聚簇索引
在innodb存储引擎上，主索引是与数据记录存储在一起的(聚簇在一起)。

带来的问题？
innodb的其他索引，非主键索引(二级索引)，
关键字对应的不再是记录的地址，而是记录的主键。
所以，查询需要二次检索，先检索到ID，在检索记录。

5、查询缓存query_cache
将select的结果，存储起来供二次使用。
开启查询缓存：

> show variables like 'query_cache%';

> set global query_cache_type = 1;

> set global query_cache_size = 1024*1024*32;
注意事项：
(1)、查询缓存的存在判断是严重依赖于select语句本身，严格保证sql一致(包括大小写)。
(2)、如果查询时包括动态数据则不能缓存。
(3)、一旦开启了查询缓存，mysql会将所有可以被缓存的select语句都缓存，如果不想被缓存，
可以使用SQL_NO_CACHE来指定不缓存。

> select SQL_NO_CACHE * from student where id = 5;

?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!