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)
maatkit的mk-duplicate-index-checker可以搜索重复的索引
3.删除不用的索引
4.监控无效的索引
explain select ...
key_len
通过key_len来找到可能包含没有使用到的索引
索引列的改进
使用特定的数据类型以及列类型，可以减少磁盘空间的使用，从而减少了I/0的开销
数据类型：
BIGINT和INT
bigint auto_increment替换成：int unsignedauto_increment 支持最大43亿
8字节 4字节
DATETIME和TIMESTAMP
8字节 4字节
TIMESTAMP默认值为0,不支持NULL值
ENUM
这个类型适合存储静态的代码值
gender1 varchar(6) not null
gender2 enum('male','female') not null
NULL和NOT 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
ipv6将128bit的整数值以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.在mysql中fulltext索引只针对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)
