ALTER TABLE 表名 ADD 索引类型(unique,primary key,fulltext,index)[索引名](字段名)
//普通索引
alter table table_name add index index_name (column_list);//唯一索引
alter table table_name add unique (column_list);//主键索引
alter table table_name add primary key (column_list);
ALTER TABLE可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式,table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。
(2)使用CREATE INDEX语句对表增加索引
CREATE INDEX可用于对表增加普通索引或UNIQUE索引,可用于建表时创建索引。
CREATE INDEX index_name ON table_name(username(length));
SELECT COUNT(DISTINCT(LEFT(</span><span class="pln">title</span><span class="pun">,10)))/COUNT(*) FROM Arctic;–这个值大于0.31就可以创建前缀索引,Distinct去重复
ALTER TABLE </span><span class="pln">user</span><span class="pun"> ADD INDEX </span><span class="pln">uname</span><span class="pun">(title(10));–增加前缀索引SQL,将人名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度
SELECT </span><span class="pln">sname</span><span class="pun"> FROM </span><span class="pln">stu</span><span class="pun"> WHERE </span><span class="pln">age</span><span class="pun">+10=30;–不会使用索引,因为所有索引列参与了计算
SELECT </span><span class="pln">sname</span><span class="pun"> FROM </span><span class="pln">stu</span><span class="pun"> WHERE LEFT(</span><span class="pln">date</span><span class="pun">,4)<1990;–不会使用索引,因为使用了函数运算,原理与上面相同
SELECT * FROM </span><span class="pln">houdunwang</span><span class="pun"> WHERE </span><span class="pln">uname</span><span class="pun"> LIKE’后盾%’–走索引
SELECT * FROM </span><span class="pln">houdunwang</span><span class="pun"> WHERE </span><span class="pln">uname</span><span class="pun"> LIKE “%后盾%”–不走索引
–正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因
–字符串与数字比较不使用索引; CREATE TABLE </span><span class="pln">a</span><span class="pun">(</span><span class="pln">a</span><span class="pun">char(10)); EXPLAIN SELECT FROM </span><span class="pln">a</span><span class="pun"> WHERE </span><span class="pln">a</span><span class="pun">=“1”–走索引 EXPLAIN SELECT FROM </span><span class="pln">a</span><span class="pun"> WHERE </span><span class="pln">a</span><span class="pun">=1–不走索引
select * from dept where dname=‘xxx’ or loc=‘xx’ or deptno=45–如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引,我们建议大家尽量避免使用or 关键字
1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a =1 and b =2 and c >3 and d =4如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。 2.=和in可以乱序,比如a =1 and b =2 and c =3建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式 3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录 4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time)=’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可