MySQL索引优化

1.整合DDL语句:

 将多条ALTER语句整合成一条SQL语句是一种简单的优化改进

alter table test

add index(username),

drop index name,

add index name(last_name,first_name),

add column last_vistidate null;

2.去除重复索引

重复索引的影响:

1.DML语句会更慢

2.占用更多的磁盘空间

MYSQL不要求主码所在的例也被索引

primary key (id)

index (id)//要删除

当一个给定的索引的最左边部分被包含在其它索引中时也会产生重复索引

indexname1(last_name)  //要删除

indexname2(last_name,first_name)

maatkitmk-duplicate-index-checker可以搜索重复的索引

3.删除不用的索引

 

4.监控无效的索引

explain select ...

key_len

通过key_len来找到可能包含没有使用到的索引

 

索引列的改进

使用特定的数据类型以及列类型,可以减少磁盘空间的使用,从而减少了I/0的开销

数据类型:

BIGINTINT

bigint auto_increment替换成:int unsignedauto_increment  支持最大43亿

  8字节                                            4字节

DATETIMETIMESTAMP

8字节                 4字节

TIMESTAMP默认值为0,不支持NULL

ENUM

这个类型适合存储静态的代码值

gender1  varchar(6) not null

gender2 enum('male','female') not null

NULLNOT NULL

NOT NULL会在列的数据上添加额外的完整性约束检查

隐含的变换

signed unsigned

latin1 utf8

列的类型

IP地址的定义

int unsigned    4字节

varchar(15)     12字节

mysql> set@ip='123.100.0.16';

Query OK, 0 rowsaffected (0.01 sec)

mysql> select@ip,inet_aton(@ip) as str_to_ip,inet_ntoa(inet_aton(@ip)) as ip_to_str;

+--------------+------------+--------------+

| @ip          | str_to_ip  | ip_to_str   |

+--------------+------------+--------------+

| 123.100.0.16 | 2070151184 |123.100.0.16 |

+--------------+------------+--------------+

1 row in set (0.01 sec)

以上方法只能适用于IPV4

ipv6128bit的整数值以BINARY(16)的类型存储

MD5

char(32)来存储MD5的值   大小:32字节

使用unhex(),hex()函数来存储在binary(16)数据类型中更好  16字节

mysql> set@str='somevalue';

Query OK, 0 rowsaffected (0.00 sec)

 

mysql> selectmd5(@str),

    -> length(md5(@str)) as len_md5,

    -> length(unhex(md5(@str))) aslen_unhex;

+----------------------------------+---------+-----------+

| md5(@str)                        | len_md5 | len_unhex |

+----------------------------------+---------+-----------+

|d5d984e0a00665878320727318ac378c |      32 |        16 |

+----------------------------------+---------+-----------+

1 row in set (0.01sec)

 

减少SQL语句

删除内容重复的SQL语句

在开发环境中启用全面查询日志功能

删除重复执行的SQL语句

select name fromfirms where id=123;

select name fromfirms where id=758;

替换成:

select name fromfirms where id in (123,758);

删除必要的SQL语句

 

缓存SQL结果

mysql> showprofiles;

+----------+------------+-------------------------------+

| Query_ID |Duration   | Query                         |

+----------+------------+-------------------------------+

|        1 | 0.00044550 | SELECT DATABASE()             |

|        2 | 0.00087150 | show databases                |

|        3 | 0.00057050 | show tables                   |

|        4 | 0.00058475 | show tables                   |

|        5 | 0.00161125 | select * from artistlimit 10 |

|        6 | 0.00015200 | select * from artistlimit 10 |

+----------+------------+-------------------------------+

6 rows in set, 1warning (0.00 sec)

 

mysql> show profilefor query 6;

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| starting                       | 0.000036 |

| Waiting for querycache lock   | 0.000007 |

| init                           | 0.000004 |

| checking querycache for query | 0.000008 |

| checking privilegeson cached  | 0.000005 |

| checkingpermissions           | 0.000014 |

| sending cachedresult to clien | 0.000071 |

| cleaning up                    | 0.000008 |

+--------------------------------+----------+

8 rows in set, 1warning (0.01 sec)

 

mysql> show profilefor query 5;

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| starting                       | 0.000043 |

| Waiting for querycache lock   | 0.000005 |

| init                           | 0.000018 |

| checking querycache for query | 0.000222 |

| checkingpermissions           | 0.000008 |

| Opening tables                 | 0.000022 |

| init                           | 0.000111 |

| System lock                    | 0.000072 |

| Waiting for querycache lock   | 0.000005 |

| System lock                    | 0.000204 |

| optimizing                     | 0.000007 |

| statistics                     | 0.000044 |

| preparing                      | 0.000013 |

| executing                      | 0.000004 |

| Sending data                   | 0.000577 |

| end                            | 0.000006 |

| query end                      | 0.000067 |

| closing tables                 | 0.000026 |

| freeing items                  | 0.000025 |

| Waiting for querycache lock   | 0.000004 |

| freeing items                  | 0.000014 |

| Waiting for querycache lock   | 0.000004 |

| freeing items                  | 0.000003 |

| storing result inquery cache  | 0.000020 |

| cleaning up                    | 0.000090 |

+--------------------------------+----------+

25 rows in set, 1warning (0.00 sec)

一定不要忘记对表做的任何改动都要在缓存中有所反应,以下的代码是必须的

INSERT/UPDATE/DELETEFROM TABLE

REMOVE FROM CACHE

应用程序缓存:

memcache

 

简化SQL语句

改进列

改进连接操作

重写子查询

子查询的性能比直接使用一般的表连接要慢很多

使用MYSQL的复制功能

 

 

通过explain可以分析mysql如何执行sql语句

 

添加索引:

4种索引(主键索引,唯一索引,全文索引,普通索引)

 

添加索引

      

主键索引:

主键索引不能为NULL,也不能重复

 

在创建表时指定索引:

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

mysql> createtable aaa (id int unsigned primary key auto_increment, name varcha

r(32) not nulldefault '');

 

在原有表中新建索引:

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

 

mysql> alter tablebbb add primary key (id);

 

 

      

普通索引:

 

mysql> createindex id_index on ccc(id);

 

mysql> alter tableccc add index ind_name (name);

 

 

全文索引:

全文索引针对文本的检索,全文索引只针对MYISAM有效

 

创建:

CREATE TABLE articles(

id INT UNSIGNEDAUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

);

 

INSERT INTO articles(title,body) VALUES

('MySQLTutorial','DBMS stands for DataBase ...'),

('How To Use MySQLWell','After you went through a ...'),

('OptimizingMySQL','In this tutorial we will show ...'),

('1001 MySQLTricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs.YourSQL','In the following database comparison ...'),

('MySQLSecurity','When configured properly, MySQL ...');

 

使用全文索引:

mysql> select *from articles where match(title,body) against('database');

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  5 | MySQL vs. YourSQL | In the followingdatabase comparison ... |

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

+----+-------------------+------------------------------------------+

2 rows in set (0.02 sec)

 

错误用法:

select * fromarticles where body like '%mysql%';[错误用法]

 

explain:

mysql> explainselect * from articles where match(title,body) against('database'

)\G

***************************1. row ***************************

           id: 1

  select_type: SIMPLE

        table: articles

         type: fulltext

possible_keys: title

          key: title

      key_len: 0

          ref:

         rows: 1

        Extra: Using where

1 row in set (0.00sec)

 

全文索引的说明:

1.mysqlfulltext索引只针对myisam生效

2.mysql自己提供的fulltext只针对英文生产,中文无法使用,要使用中文全文索引,需要用sphinx(coreseek)技术来处理

3.使用方法是match(字段名) against(关键字)

4.全文索引有一个叫停止词的概念,因为在一个文本中,创建索引是一个无穷大的数,因此,对一些常用词和字符,就不会创建索引,这些词叫做停止词

 

mysql> selectmatch(title,body) against('database') from articles;    

+---------------------------------------+

| match(title,body)against('database') |

+---------------------------------------+

|                      0.65545834044456 |      65%左右可以匹配到database

|                                     0 |

|                                     0 |

|                                     0 |

|                      0.66266459031789 |      66%左右可以匹配到database

|                                     0 |

+---------------------------------------+

6 rows in set (0.00sec)

 

mysql> selectmatch(title,body) against('a') from articles;

+--------------------------------+

| match(title,body)against('a') |

+--------------------------------+

|                              0 |     只会针对生僻的词建立索引,常用的字母不会创建索引

|                              0 |

|                              0 |

|                              0 |

|                              0 |

|                              0 |

+--------------------------------+

6 rows in set (0.00sec)

 

 

唯一索引:

 

当表中有一列被指定为unique约束时,这列就是一个唯一索引

mysql> createtable ddd(id int primary key auto_increment,name varchar(32) unique);

 

唯一索引可以有多个NULL,(NULL代表什么都没有)但只能有一个''(空字符串,代表是空字符串,如果有多个将违反唯一的特性)

 

mysql> select *from ddd;

+----+------+

| id | name |

+----+------+

|  1 | NULL |

|  2 | NULL |

+----+------+

2 rows in set (0.00sec)

mysql> insert intoddd values(3,'');

Query OK, 1 rowaffected (0.00 sec)

 

mysql> insert intoddd values(4,'');

ERROR 1062 (23000):Duplicate entry '' for key 2

 

在创建表后创建唯一索引:

mysql> createtable eee (id int primary key auto_increment,name varchar(32));

Query OK, 0 rowsaffected (0.08 sec)

 

create unique index 索引名  on 表名(列名)

mysql> createunique index my_uni on eee(name);

Query OK, 0 rowsaffected (0.05 sec)

***************************2. row ***************************

       Table: eee

  Non_unique: 0

   Key_name: my_uni

Seq_in_index: 1

 Column_name: name

   Collation: A

 Cardinality: NULL

    Sub_part: NULL

      Packed: NULL

        Null: YES

  Index_type: BTREE

     Comment:

2 rows in set (0.00sec)

 

 

 

分割线

感谢打赏
江西数库信息技术有限公司
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS