ql语句是否用到索引。

语法：explain sql语句\G 或 desc sql语句\G

使用索引时：

没有使用索引时：

explain工具的参数说明：

会产生如下信息：

select_type:表示查询的类型。

table:输出结果集的表

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

rows:扫描出的行数(估算的行数)

Extra:执行情况的描述和说明

六、索引

1、对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。

mysql> alter table user add index (name,email);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0  Warnings: 0

该查询条件没有使用到最左边的列，所以没有使用到索引。

该条件中使用了复合索引的最左边的列，因此该查询会使用到索引。

2、对于使用like的查询，查询如果是”%XXX”,不会使用到索引，‘XXX%’会使用到索引。

注意：在有些情况下，还是会用到like查询，比如通过歌词搜索歌名，通过剧情搜索电影名称。借助于工具，sphinx里面的coreseek软件。

3、如果条件中有or，则要求or的索引字段都必须有索引，否则不能用到索引。

该email添加索引后，在测试，会用到索引

4、如果列类型是字符串，一定要在条件中将数据使用引号引用起来，否则不使用索引。

5、优化group by语句

默认情况下， mysql对所有的group by col1,col2进行排序。这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，则可以使用order by null禁止排序。

6、当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。扫描的行数太多了，优化器认为全表扫描比索引来的块。

7、查看索引的使用情况

注意：

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

8、对应大批量插入数据

对于MyISAM：

先禁用索引：

alter table table_name disable keys;

loading data//insert语句; 执行插入语句

执行完成插入语句后，开启索引，统一添加索引。

alter table table_name enable keys;

对于Innodb：

1，将要导入的数据按照主键排序

2，setunique_checks=0,关闭唯一性校验。

3，setautocommit=0,关闭自动提交。

七、并发处理的锁机制

比如执行如下操作：

（1）从数据库中取出id值，

（2）进行加1操作。

（3）修改完成后，再保存到数据库中。

比如原来 id的值为100,==》101

以上步骤执行100次，最后变成200

有两个用户同时执行的话。

a用户：

100

101

b用户：

100

101

通过锁机制来进行解决

锁机制：在执行时，只有一个用户获得锁，其他用户处于阻塞状态，需要等待解锁。

mysql 的锁有以下几种形式：

表级锁：开销小，加锁快，发生锁冲突的概率最高，并发度最低。myisam引擎属于这种类型。

行级锁：开销大，加锁慢，发生锁冲突的概率最低，并发度也最高。innodb属于这种类型。

1、表锁的演示；

对myisam表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的操作。

表添加读锁后，其他进程对该表只能查询操作，修改时会被阻塞。

当前进程，能够执行查询操作，不能执行修改操作。不能对没有锁定的表进行操作。

锁表的语法：

lock table 表名 read|write

也可以锁定多个表，语法是：locktable 表1 read|wirte,表2 read|wirte

对myisam表的写操作（加写锁），会阻塞其他进程对锁定表的任何操作，不能读写，

表加写锁后，则只有当前进程对锁定的表，可以执行任何操作。其他进程的操作会被阻塞。

2、行锁的演示

innodb存储引擎是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才会使用行级锁，否则，innodb使用表锁。

行锁的语法：

begin

sql语句

commit

开启行锁后，当前进程在针对某条记录执行操作时，其他进程不能操作和当前进程相同id的记录。

php里面有文件锁，在实际的项目中多数使用文件锁，因为表锁，会阻塞，当对一些表添加写锁后，其他进程就不能操作了。这样会阻塞整个网站，会拖慢网站的速度。

类似的问题：

一件商品，库存量还有一件，这时有两个用户同时请求下订单，如何防止都下订单成功，却没有货发。

八、分表

分表：把一个大表分成几个小表：

垂直分割：

在dedecms里面，垂直分割：

在一个数据库中想要存储各种数据，比如说文章数据，电影，音乐，商品数据，

内容主表+附加表：

内容主表：存储各种数据的一些公共信息，比如数据的名称，添加时间等，

可以使用多个附加表，附加表存储一些数据的独特的信息。

主要原因：是内容主表里面的数据访问比较频繁。

水平分割：

通过id取模

九、分区

就是把一个表存储到磁盘不同区域，仍然是一张表。

1、基本的概念：

mysql5.1后有4种分区类型：

（1）Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。

（2）List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割

（3）Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

（4）Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

2 range分区：

假如你创建了一个如下的表，该表保存有20家超市的职员记录，这20家超市的编号从1到20.如果你想将其分成4个小分区，可以采用range分区，创建的数据表如下。

创建range分区语法：

create table emp(
   id int not null,
    namevarchar(32) not null default '' comment ‘职员的名称’,
   store_id int not null comment ‘超市的编号范围是1-20’
)engine myisam charset utf8
partition by range(store_id)(
   partition p0 values less than(6), //是store_id的值小于6的存储区域。
   partition p1 values less than(11), //是store_id的值大于等于

mysql数据库优化小结(二)

六、索引

七、并发处理的锁机制

八、分表

九、分区