mysql优化之索引_mysql优化之索引篇_MySQL

最新推荐文章于 2024-06-27 09:28:17 发布

Beyond Imp

最新推荐文章于 2024-06-27 09:28:17 发布

阅读量86

点赞数

文章标签： mysql优化之索引

本文链接：https://blog.csdn.net/weixin_29365481/article/details/113649567

版权

对mysql优化是一个综合性的技术，主要包括

a: 表的设计合理化(符合3NF)

b: 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

c: 分表技术(水平分割、垂直分割)

d: 读写[写: update/delete/add]分离

e: 存储过程 [模块化编程，可以提高速度]

f: 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]

g: mysql服务器硬件升级

h: 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

i: sql语句优化

本篇主要总结下索引有关内容

索引

四种索引(主键索引/唯一索引/全文索引/普通索引)CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_name [(length)] [ASC | DESC] (列部分值作为索引)

列部分值作为索引::

对于CHAR和VARCHAR列，只用一列的一部分就可创建索引。

创建索引时，使用col_name(length)语法，对前缀编制索引。

前缀包括每列值的前length个字符。

BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。

此处展示的语句用于创建一个索引，索引使用列名称的前10个字符。

CREATE INDEX part_of_name ON customer (name(10));

因为多数名称的前10个字符通常不同，所以此索引不会比使用列的全名创建的索引速度慢很多。

另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

前缀最长为255字节。对于MyISAM和InnoDB表，前缀最长为1000字节。

注意前缀的限长以字节计，而CREATE INDEX语句中的前缀长度指的是字符的数目。

对于使用多字节字符集的列，在指定列的前缀长度时，要考虑这一点。

在MySQL 5.1中：

· 只有当您正在使用MyISAM, InnoDB或BDB表类型时，您可以向有NULL值的列中添加索引。

· 只有当您正在使用MyISAM, BDB或InnoDB表类型时，您可以向BLOB或TEXT列中添加索引。

index_type::

部分储存引擎允许在创建索引时指定索引类型。

index_type指定语句的语法是USING type_name。

不同的储存引擎所支持的type_name值已显示在下表中。

如果列有多个索引类型，当没有指定index_type时，第一个类型是默认值。存储引擎允许的索引类型

MyISAMBTREE

InnoDBBTREE

MEMORY/HEAPHASH, BTREE

主键索引::

当一张表，把某个列设为主键的时候，则该列就是主键索引

1,create table testtable

(id int unsigned primary key auto_increment ,

name varchar(32) not null defaul '');

2,alter table 表名 add primary key (列名);

普通索引::

一般来说，普通索引的创建，是先创建表，然后在创建普通索引

比如:

create table ccc(

id int unsigned,

name varchar(32)

)

create index 索引名 on 表 (列1,列名2);

全文索引::

1，FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引，

2，在mysql中fulltext 索引只针对 myisam生效

3，mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文

4，使用方法是 match(字段名..) against(‘关键字’)

5，全文索引一个叫停止词, 因为在一个文本中，创建索引是一个无穷大的数，

6，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.

7，任何过于短的词都会被忽略。全文搜索所能找到的词的默认最小长度为 4个字符。

8，MATCH()列列表必须同该表中一些 FULLTEXT索引定义中的列列表完全符合,除非MATCH()在IN BOOLEAN MODE(布尔全文搜索)。

9，对AGAINST() 的参数必须是一个常数字符串。

创建：CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) )engine=myisam charset utf8;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 ...');

如何使用全文索引:

错误用法:

select * from articles where body like '%mysql%'; [不会使用到全文索引]

证明:

explain select * from articles where body like '%mysql%'

正确的用法是:(match中的列值，跟定义fulltext时的一样)

select * from articles where match(title,body) against('database'); 【可以】