MySQL数据库sql语句调优


MySQL索引设计原则：
索引列一般为where子句中的列或连接字句中的列
尽量不对基数小的列做索引，如性别列
尽可能使用短索引：如果对字符列索引尽量指定最小长度。
（short Keys are better,Integer best）
create index cityname on city(city(10));
复合索引前缀特性，索引的顺序很重要。
key(a,b,c)联合索引：
可以走索引的组合：key(a),key(a,b ),key(a,b,c)
下列索引无法走索引key(b),key(b,c),key(a,c)
key(a,b)...where b=5 will not use index

创建复合索引时应将最常用作限制条件列放在最左边，依次递减

避免出现无用的索引。（很少使用或从未别调用）

INNODB：尽量指定主键，最常用较短数据类型，唯一列做主键。
尽量使用定长字符类型char,而不用varchar 
对于索引的优化，当使用联合索引时，将常用的列在索引的左边，不常用的列，取其前部分字符，只要能精确查到它即可
例如：
create index d_a_p on ad_oldboy_detal(dateline,),ader(ader(20),pos(20));
避免过度使用索引
1.索引的建立对提高检索能力很有用，但是数据库维护它也很费资源。
2.对性别列索引，被称为过度索引。
只有两个值，建索引不仅没优势，还会影响到插入，更新速度，
3，索引会占用磁盘空间，降低更新操作性能，且执行计划要考虑各个索引
4.索引不是越多越好。
5行数比较少的表可以不建索引（100行以内）
创建索引的语法 
help create index
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]
index_col_name:Col_name:[(length)] [ASC|DESC]
Phpadmin:
ALTER TABLE `pw4_group_art` ADD INDEX(`tid`)
create index pw4_group_art_tid on pw4_group_art(tid);
删除索引语法
drop INDEX ind_sage on student;
复合索引及前N个字符索引例子集合；
create index Sage_Sdet on student(sname,sage(100)
查询一个语句，看他有没有走索引；
explain  select * from student where Sno=6
只要key不为空，
如果数据库有缓存，想测试速度，应该
explain SQL_NO_CACHE* from uc_members where email="1234";
查看表的唯一值多少，用distinct;
select count(distinct Sage) from student;
使用索引的条件
1：索引列不能包含空值
2：复合索引中只要有一列含有null值，那么这一列将不会使用索引
3：列类型是字符串，要在where条件中吧字符值用引号括起来。
4：用or分割开的条件，or前条件有索引，而后面列无索引，那么设计的索引都不会被用到
5：条件不是索引列的第一部分
5：like语句操作
一般情况下尽量不使用like操作。like "%aaa%" 不会使用索引，而like,"aaa%"可以使用索引。可以建立fulltext或者sphinx(斯芬克司)
6：不要在列上进行运算
select  * from users where YEAR(adddate)<2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select* from users where addate<'2007-07-01';
7:不使用NOT IN  和<>操作
NOT IN 和<>操作多不会使用索引将进行全表扫描，NOT IN 可以 NOT EXISTS代替，id<>3 则则可使用id>3orid<3来代替
其他；
尽量用连接查询代替子查询（嵌套查询）
order by 的索引问题
show processlist 查看线程
show full processlist
uptime
在生产环境中如果是大表，创建索引会很耗费时间，也许需要几分钟，要把索引放在业务的低谷
explian看看有没有走索引
show processlist;
在看看优化之后负载的变化

优化的起因：
1 网站出问题，很慢show full processlist--->

2：慢查询语句(日志文件)
grep slow my.cnf

只要是
long_query_time=1
log-slow-queries=/data/3306/slow.log
不要强求所有的列进行走索引

让大表常用的列走索引，一般来说就可以了

本文永久更新链接地址：

相关内容