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
不要强求所有的列进行走索引

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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/c3a1eeabd317fc10ffa183e5c7316209.html